9 Replies Latest reply: Jun 7, 2004 12:35 PM by mcs328 RSS

    LOV that works more like the LOV in Oracle Forms

      I am sorry if this is a gumby question but I have searched the doco and this forum and I have not found an answer.
      What I have is an OLTP system with Oracle Forms on top of it.
      I am implementing Discoverer 4.1 to give the users a adhoc tool.

      Now we have code values and what I mean by that is we have the column client.gender and the values are M, F, U.
      We have a code table that has the valid codes and their corresponding Display value. Such as M = Male, F = Female and U = Unknown.

      Now in Forms the users only ever see the display value and we do this by building a record group and attaching it to a pop-list (a windows LOV).

      I want to do the same in Discoverer. For example I don't want the users to see the raw data values of M,F,U I want them to see the Display Values of Male, Female and Unknown.

      The LOV's in Discoverer seem to not be for this purpose but rather a predefined list of values, ie. to save the expense of performing a select DISTINCT everytime.

      I can not see an easy way of doing this ? Pls help.

      This problem has to had come up before and there must be a solution.
      A lov in Oracle Forms has a code and a "Display to the user value" but the Discoverer tool does not have this. It's almost like Discoverer is not an Oracle product.

      Also I would like to do this all from the Admin tool, IE The users have no idea that this translate is going on.

      Thanks for you help and once again I am sorry if this is a simple question but it has me stumped.

      Chris .....
        • 1. re:LOV that works more like the LOV in Oracle Forms
          I am very surprised that no one had a solution to this, THIS must be a common problem.

          Well I have found a solution and here it is, if anyone thinks this is the wrong way then pls let me know.
          I created a function where I pass in the code and the name of the code set and the function returns the display valuye of the code. The function is deterministic so it can be used in MV's.

          I then register the function within Discoverer.

          I then create a new item and base it on the registered function.

          and it all works as I want.

          Chris .......
          • 2. re:LOV that works more like the LOV in Oracle Forms
            Hi Chris,
            I think I have a manipulated solution for the problem. First you create a customized folder where u write a SQL making 'M','F' etc. as values and 'Male','Female' etc. are as alliases to it. Then make a LOV on that Item and attach it to wherever u want.
            I think this will help u.
            • 3. re:LOV that works more like the LOV in Oracle Forms
              I'm not sure if our way of doing this is the easy way but we have a similar situation. We've tried it a couple of different ways. One way is we join the main table to the code table. We then create a custom folder that combines the values from the code tables we want and the values from the main table that don't have code tables. We then create a LOV based on the code table and select all of the items in the custom folder that would use that same code table.

              We actually don't use this solution any more because we needed to use outer joins for this to work and that had a serious impact on performance. Instead what we did was we created a custom folder where we decode the fields we want to have a LOV attached to them. This makes them have the same names that are contained in the code table. We then create a LOV from the code table and attach it to the items in custom folders. The initial set up of this is rather lengthy but it provided the results we want. And actually in the end we found it best to create a view that has all the decodes done for us and we just attached the LOVs to the view. This ultimately has given us our best performance results.
              • 4. re:LOV that works more like the LOV in Oracle Forms

                I recently started working with Oracle Discoverer and wanted to get more technical knowledge about this tool. This forum a really a boon to developers like me. Please let me know if you can give any kiind of assistance.

                Appreciate it.
                • 5. re:LOV that works more like the LOV in Oracle Forms
                  What I've done is create an item class in the business area for the column.
                  Create a new item in the folder and then create a function to decode the column (M,F,U) to the description (Male, Female, Unknown). Create an item class that will list the values based on the new item you created. The item you created in the folder should have a + sign next to it that lists the values.

                  In the desktop or Viewer, that column is now an LOV with the full description whenever you want to create a condition on it or a parameter.
                  • 6. Re: LOV that works more like the LOV in Oracle Forms
                    I am new to Discoverer and I am trying to duplicate the solutions presented here.

                    As I understand, a function has to be created and then registered within Discoverer. This function will accept a code parameter and then will return a display value. A new item is created in Administrator using the function
                    and then an item class is created that uses the new item to create a LOV of the display values.

                    If this is correct, how should the function be constructed? Specifically, what do you put in the function to return the display value? For example,
                    I have a table of state codes and state names and in the LOV I want to display state name but use state code.

                    CREATE OR REPLACE FUNCTION state_nm_from_code_sf
                         (st_code IN NUMBER)
                    RETURN VARCHAR2
                         st_name VARCHAR2;
                         st_name := whatgoeshere;
                    RETURN st_name;

                    Thanks for your help.
                    • 7. Re: LOV that works more like the LOV in Oracle Forms
                      To duplicate the solutions proposed here, specifically, Shannon's solution, do the following:

                      1) Create a view (or a custom folder) which contains the fields you are interested in. Obviously, if you create the view at the database level, you'll need to create a folder for it anyway in Discoverer.

                      2) Write a DECODE calculation either at the view level, or as a calculated item on the folder.

                      Following on from the Gender example, if your code set contains the values M, F, U (for Male, Female, Unknown), your decode calculation will look like this:

                      DECODE(gender, 'M', 'Male', 'F', 'Female', 'Unknown')

                      I've missed out the case for 'U', so that anything that isn't 'M' or 'F' = 'Unknown' - this includes null values

                      3) Create a custom folder is Discoverer, call it something like 'LOV Genders', and write the following SQL to create the LOV:

                      SELECT 'Female' gender from sys.dual
                      SELECT 'Male' gender from sys.dual
                      SELECT 'Unknown' gender from sys.dual

                      3) Create an Item Class based on this custom folder, and retrieve the values for the item class by clicking on the (+) next to it. You can call it LOV Genders too.

                      4) Attach the Item Class to your Gender field in the original folder.

                      The user will be presented with the (list 'Female', 'Male', 'Unknown') when you create parameters based on this field, or when selecting from the folder.

                      Hope this helps.

                      • 8. Re: LOV that works more like the LOV in Oracle Forms
                        I think this is an area that should be improved in future releases. I am adding this comment to get this closer to the front, I had to go 18 pages to find it.
                        • 9. Re: LOV that works more like the LOV in Oracle Forms
                          Like make this thread a "sticky" at the top? Or improve the LOV feature in future Discoverer upgrades? For my users I've created views that bring in the description (Male,Female, etc) as part of the folder and let Discoverer create the LOV via item classes. They don't like to click and retrieve the desciption in the related detail folder. Idealy they would like everything denormalized as one table with a billion columns they can scroll through but that's not going to happen. =)