Inline IF

[Deleco]

Hi,

I would like to use a Command to narrow down my record selection. This is OK but i would like to use an in-line IF statment in the command as follows

IIF([FieldName]='Expert', 1, 0)

Has anyone done anything like this before as i do not seem to be able to make it work. I am accessing an Oracle datbase if that makes any difference.

Many thanks in Advance

Deleco

 

[Katy44]

Oracle uses the 'decode' function rather than iif, doesn't it?

 

[Deleco]

Thanks the DECODE statement seems to be what i need to use.


Deleco

 

[synapsevampire]

The Decode is used in Oracle only, not Crystal, unless you're creating a SQL Expression, so this has nothing to do with limiting rows.

Not posting the version of Crystal, example data nor expected output will result in vague responses, if you want quality assistance, spend a little time describing your requierments accurately.

Your iif example wouldn't filter anything, it simply states that it should return a 1 or a 0.

To narrow your record selection formula (of course this is Crystal version dependent), use Report->Selection Formulas->Record

{table.FieldName}='Expert'

This would limit your rows to 'Expert' only, and would be reflected in the SQL passed to the database by checking Database->Show SQL Query

-k

 

[Deleco]

Hi synapsevampire,

in Crystal Reports 9 you can define an SQL Command in the database expert. And in the Crystal help it explains that this SQL command statement is bound to the driver you are using to access the database. Here is the exact statement from the Crystal On-line help

If the database you are using supports a query language such as SQL, you can write your own command which will be represented in Crystal Reports as a Table object. This allows database users complete control of the data processing that gets pushed down to the database server. A user who has experience with databases and the SQL language can write a highly optimized command that can considerably reduce the size of the set of data returned from the server.

This is all i was trying to do and all i wanted to know was the syntax ORACLE SQL used in place of the Inline IF as i have quite an extensive knowledge of SQL used in Access and SQL Server but no experience of ORACLE. I did not want to over complicate the issue by getting into an in depth discussion of what i am trying to do.

Katy44 told me about the DECODE function which was exactly what i needed. Thanks Katy44

Thanks for the advice from both of you

Deleco

 

[rosemaryl]

Dear Deleco,

Just as an FYI, you can also use the case (case when then end) function in Oracle starting with version 8.17... I just mention this because it can be more flexible then decode.

An excellent site that allows you to see syntax in various db backend is

http://sqlzoo.net/

This is a tutorial site on SQL and you can select Oracle/Conditions and see an example of Decode and Case ...

http://sqlzoo.net/howto/source/s.pl.htm?file=tip667449&e=oracle

Regards,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[synapsevampire]

Your post stated "I would like to use a Command to narrow down my record selection.", hence I addressed that.

I'm painfully aware of Crystal's SQL methods

-k