Oracle vs. Access

[pathetic]

I am use to creating reports using an orcale database and I am use to using the "DECODE()" feature for alot of my derived fields. Recently, I have been given a project that can only be accessed thru Access tables....and the DECODE() is not included! Does anyone know how to work this problem? Is there something I can put in SQL that will do the same thing?

 

[pbowen]

Do you have access to the @IF or IIF function

(@IF(number, Value1,Value2) - returns value 1 if number = 0 else returns value 2? i.e.

@IF(tablename.fieldname,7000,0)

This would return "7000" if tablename.fieldname has a value, otherwise would return "0".

Or

=IIf([textboxname.txt]<0,([textboxname.txt]*-1),0)

This would change the &quot;sign&quot; of a negative number in textboxname.txt if it is less than zero, otherwise it would return &quot;0&quot;.

 

[pbowen]

I've been using the SQL version of Report Smith for the last year. Now we've switched to the Oracle version. Could you explain DECODE() to me?

 

[gtarrant]

When you create a derived field, you go to the pull down menu and select @decode() in the &quot;Other Fuctions&quot; category. This will let you select a value of a field and for that value, return a value of your own choosing.

 

[bharned]

You can use the switch function in access...Look that up in help and it will show you an example

 

[brianewo]

Pbowen,

here is an example of a simple decode statement using the oracle version or reportsmith:

DECODE(&quot;PS_JOB&quot;.&quot;EMPL_TYPE&quot;,'S','Salary','H’,'Hourly')

The Xlattable table that holds all the description, doesn't have a value to translate Empl_Type. Therefore IF you want to see Salary instead of a S, you would need to do that simple Derievedfield and usethat decode statement.

I sometimes like using this instead of the translate table.