We use a field in our database that stores single characters. How do I associate Strings with these in the universe so that I may prompt for the values. For example, rather than see ‘A’, ‘B’, etc., I would be prompting with ‘Devices’, 'Peripherals", etc.
Jay Donelson Ide
Visionael Corp.
Listserv Archives (BOB member since 2002-06-25)
Hi Jay,
You wrote:
For example, rather than see ‘A’, ‘B’, etc., I would be prompting with ‘Devices’, 'Peripherals", etc.
In Oracle you would use
decode(,‘A’,‘Devices’,‘B’,‘Peripherals’,etc.) to create the description object. This object you can then add to the list of values
of the original object next to the actual value (object properties, advanced tab).
The user can select on the description, the value used to compare would be the actual ‘A’.
Which is nice if this field has an index on it.
Have a look at the functions your rdbms supports, look for things like case, iif, etc.
Independent of your RDBMS, once you have the code,description somewhere in
a table you can join it (as lookup table). This description object you can use in the
same way in the list of values.
Good luck,
Marianne Wagt
IDETA
The Netherlands
Listserv Archives (BOB member since 2002-06-25)
We use a field in our database that stores single characters. How do I associate Strings with these in the universe so that I may prompt for the values. For example, rather than see ‘A’, ‘B’, etc., I would be prompting with ‘Devices’, 'Peripherals", etc.
In the select for the object representing a column containing single characters we use a DECODE statement to associate a full word with each character that might be returned. We are using Oracle, I’m not sure if there are similar commands if you use a different database.
Louise Priest
Fraser Williams Pharma Systems
lpriest@pharma.fraser-williams.com
Listserv Archives (BOB member since 2002-06-25)
Lou,
You can create a Table that will handle your conversions…
Create a table that contains the Values (A,B,C) and a second Column that contains the String Desired (‘Devices’, ‘Peripherals’, etc…).
Create a prompt from this table and have it select the VALUE (under the Select statement) and the prompt show you the String values for the prompt. You will need an alias or self-join for this to work.
Anyway… once that is done, you can use the VALUE returned from this prompt as a comparison on your database to return the devices that you choose.
(You can also query off this table as a second query… use the LINK, and then use this as a DECODE after the fact).
Thanks!
-rm
Listserv Archives (BOB member since 2002-06-25)