Hi all,
I am trying to have a decode in a UDO.
The default decode syntax looks like this…
decode( )
This does not have any parameters to it. What is the number of parameters we can add in the decode. Can anyone give an example to it ?
Do we need to make any changes to the ora7EN.prm file ?
Please help.
Ashish
Listserv Archives (BOB member since 2002-06-25)
I don’t know about changes to the ora7EN.prm file, because I am currently using BO against MS SQL Server, but I do know about the decode syntax. Here is an example of using decode to print a full name for gender with a default of ‘Unknown’:
decode(gender,‘M’,‘Male’,‘F’,‘Female’,'Unknown)
This basically says, look at the gender column, if it is ‘M’ return ‘Male’, if it is ‘F’, return ‘Female’, else return ‘Unknown’. The last argument (the default) is optional. I don’t know if there is a limit to the number of cases you have, I have used several at a time.
I hope this helps.
Marian Cooney
McKessonHBOC
Malvern, PA
Listserv Archives (BOB member since 2002-06-25)
Thanks… I do know the syntax…but all I see in the decode function is …
decode( )…with no commas for parameters… If I try to add commas, it says…“Wrong number of parameters”
My decode is…
decode({Schooling\Schooling Degree},‘Associates’,1,‘Bachelors’,2’,‘Masters’,3,‘Doctorate’,4,0)
How can I write this in the UDO ?
Ashish
Listserv Archives (BOB member since 2002-06-25)
You have an extra ’ - after the 2
I think that is the problem, good luck
Brent
Thanks… I do know the syntax…but all I see in the decode function is …
decode( )…with no commas for parameters… If I try to add commas, it says…“Wrong number of parameters”
My decode is…
decode({Schooling\Schooling
Degree},‘Associates’,1,‘Bachelors’,2’,‘Masters’,3,‘Doctorate’,4,0)
How can I write this in the UDO ?
Ashish
Listserv Archives (BOB member since 2002-06-25)
Thanks…that was just a TYPO…does not exist in the actual decode that I have written. Pardon me.
Anymore suggestions…
Ashish
Listserv Archives (BOB member since 2002-06-25)
At 02:55 PM 3/22/2000 -0500, Ashish wrote:
I am trying to have a decode in a UDO.
The default decode syntax looks like this…
decode( )
This does not have any parameters to it. What is the number of parameters we can add in the decode.
Ashish, the functions for defining a UDO should work the same way as they do when you set up a new object in designer, and the syntax will vary based on your database platform. And, it appears that the reporter module doesn’t give users quite as free reign
Since I don’t use Oracle, I can’t tell you the exact syntax. (We don’t have a decode in Sybase, but use a CASE statement instead.)
However, I thought I’d seen this question asked once before, so I checked the listserv, and found the reply that comes below from Dave Rathbun, who is probably the most articulate contributor to this list. See his answer below, but first…
Listserv Archives (BOB member since 2002-06-25)
I tried the options suggested by Dave…but I keep getting the message… “Returns type incompatible with user object type” …which untrue… I have given the correct data type.
If i put double quotes for a alphanumeric ( " " ), then I get “bad character (not a function name?)”. what are these messages ?
I know decode syntax very well… this BO is diving me crazy…and making me forget my Oracle fundas.
Ashish
Listserv Archives (BOB member since 2002-06-25)
Ashish,
If your Decode is truly :
decode( {Schooling\Schooling Degree}, ‘Associates’, 1, ‘Bachelors’, 2, ‘Masters’, 3, ‘Doctorate’, 4, 0)
You might want to consider making this a to_number function… i.e. to_number(decode( {Schooling\Schooling Degree}, ‘Associates’, ‘1’, ‘Bachelors’, ‘2’, ‘Masters’, ‘3’, ‘Doctorate’, ‘4’, ‘0’))
Thanks!
-RM
From: “Mirji, Ashish (CRD, TATA)” mirji@CRD.GE.COM
I tried the options suggested by Dave…but I keep getting the message… “Returns type incompatible with user object type” …which untrue… I have given the correct data
type.
If i put double quotes for a alphanumeric ( " " ), then I get “bad character (not a function name?)”.
what are these messages ?
I know decode syntax very well… this BO is diving me crazy…and making me forget my Oracle
fundas.
Listserv Archives (BOB member since 2002-06-25)
Bob,
I tried it…but still getting the same message… what is your entry in the ORA7EN.prm file for decode ? There seems to be some problem with the single quotes ( ’ ). It says …wrong data type.
When I put double quotes ( " ), it does not give this error …instead says…“bad character(not a function name) ?”
Ashish,
Listserv Archives (BOB member since 2002-06-25)