can anyone explain below oracle Decode syntax ? 2

[nkshah]

can anyone pls explain me below Oracle Decode parameter and syntax ?

DECODE(trim('{?Entity}'),NULL,table.entity,'{?Entity}')

Thanks.

 

[Turkbear]

Hi,

If the Trimmed form of the parameter named Entity is NULL then return the value in the table field entity, otherwise return the parameter value ( not sure the ' marks are correct in this usage,it may make {?entity} a literal and not pass its value)

It is the same as using :

If Trim({?entity}) IS NULL
  Then table.entity
else
 {?entity}

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

Something like the following, although in CRXI, you can't have a null parameter as far as I know--just a blank one:

If isnull({?Entity}) or
trim({?Entity}) = "" then
{table.entity} else
{?Entity}

-LB

 

[nkshah]

Can you explain below thing ?

DECODE(rs.id,'{?org_ID}',rs.entity_type,'{?Entity_type}')

thanks.

 

[lbass]

From website techonthenet.com:

"In Oracle/PLSQL, the decode function has the functionality of an IF-THEN-ELSE statement.

The syntax for the decode function is:

decode( expression , search , result [, search , result]... [, default] )

expression is the value to compare.

search is the value that is compared against expression.

result is the value returned, if expression is equal to search.

default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return null (if no matches are found)."

So that would be:

if {rs.id} = {?org_ID} then
{rs.entity_type} else
{?Entity_type}

-LB

 

[nkshah]

HI Thanks for giving me reply.

i implemented Max(Decode()) function in my SELECT statement.

in the search part of DECODE function , i written manually each product name and it given me perfect output.

Now, my question is that, if in the future they will add new productid and product name in the table , then according to my implementation , it will not give and display new product name on reports.

how can i dynamically put Search values in Decode(), so it will give newly added productid ?

if you need more information let me know.

Thanks.

 

[lbass]

I think it would help to know what you are trying to do instead of assuming that using decode() is the best way to go.

-LB

 

[nkshah]

Thanks for given me reply.

Decode(productid,'1',product 1,'2', product 2,'3',product 3)

i am using this above Decode() in my SQL Command of CR.

Suppose, now in the future, in database table, they will add new productid in database. in that case, above Decode() will not display newly added productid in CR.

How can we make it dynamic, so newly added productid also display ?

Let me know if u need more information.

Thanks.

 

[Turkbear]

Hi,
Decode probably cannot be made dynamic since it depends on specific information about what value to return to 'translate' or decode the values found in the underlying data field - those values have to be manually entered by the formula creator.
The best solution is to have a value in the table that coordinates with the productid , or have a lookup table that can be linked to the productid in the main table.

Speak to the data folks about creating what is needed.







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[nkshah]

Thanks for given me promt reply.

based on my reporting req., i also need to convert all raws into column using decode() - for that i am using SQL Command.

So anyways, Decode() comes into picture.

your solutions is helpful - when we will used Tables for CR, but in my case i am using SQL Command that includes conveting raws into columns and Sum based on grouping of productid.

Let me know if you need more information.

Thanks.

 

[Turkbear]

Hi,
Please post your command , if possible. ( a command can access more than one table so adding a lookup table to handle the decodes should be possible)






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[nkshah]

Hi, thanks for the reply.

is Cursor is solutions of this ?

Cursor is coming in my thoughts.

Let me know.

i am newbie for CR and Oracle.

Thanks./