Case Statement with Isnull

[Former Member]

Hi gurus,

I am in process of converting a forumla field from Crystal report to Universe Objects. Please help me out in converting

if {?Dept}="NONE" then "NONE GRP" else

if isnull({CLARITY_DEP.DEPARTMENT_ID}) then "Unknown Department" else

if isnull({CLARITY_DEP.DEPARTMENT_NAME}) then "No Name for Department # " + totext({CLARITY_DEP.DEPARTMENT_ID},0,"") else

{CLARITY_DEP.DEPARTMENT_NAME} + " - " + totext({CLARITY_DEP.DEPARTMENT_ID},0,"")

Thanks in Advance

Regards

R

[Former Member]

Hi,

It depends on what is your database.

So for instance, in Oracle, you can do this:

CASE 
WHEN Dept = 'NONE' then 'NONE GRP'
WHEN nvl(CLARITY_DEP.DEPARTMENT_ID, -1) = -1 then 'Unknown Department' 
WHEN CLARITY_DEP.DEPARTMENT_NAME = '' then 'No Name for Department # ' || to_char(CLARITY_DEP.DEPARTMENT_ID) 
ELSE CLARITY_DEP.DEPARTMENT_NAME || ' - ' || to_char(CLARITY_DEP.DEPARTMENT_ID)
END

I assume that the variables you are using are table columns.

I also converted "totext" function in "to_char" function but I am not sure that it is you want to do.

Didier

[Former Member]

Hi Didier,

Our back end is SQL 2008, Please let me know Is the syntax same for SQL too. Also we are converting Crystal formula field to Universe Objects. In crystal we are using Crystal Syntax.

Thanks

[Former Member]

It is almost same.

You have to change below :

NVL ==============>> ISNULL

TO_CHAR ===================>> STR

See example:

USE AdventureWorks2008R2;

GO

SELECT ProductNumber, Category =

CASE ProductLine

WHEN 'R' THEN 'Road'

WHEN 'M' THEN 'Mountain'

WHEN 'T' THEN 'Touring'

WHEN 'S' THEN 'Other sale items'

ELSE 'Not for sale'

END,

Name

FROM Production.Product

ORDER BY ProductNumber;

GO

Thank You!!

[Former Member]

Hi gurus,

Thanks for the replys, I tried using the syntax but it didn't help me out.

CASE

WHEN Dept = 'NONE' then 'NONE GRP'

WHEN dbo.CLARITY_DEP.DEPARTMENT_ID ISNULL then 'Unknown Department'

WHEN dbo.CLARITY_DEP.DEPARTMENT_NAME ISNULL then 'No Name for Department # ' +

convert(varchar(40),dbo.CLARITY_DEP.DEPARTMENT_ID)

dbo.CLARITY_DEP.DEPARTMENT_NAME + ' - ' + convert(varchar(40),dbo.CLARITY_DEP.DEPARTMENT_ID)

END

Here is the Original synatax in Crystal Reports, Our back end is SQL Server 2008

if {?Dept}="NONE" then "NONE GRP" else

if isnull({CLARITY_DEP.DEPARTMENT_ID}) then "Unknown Department" else

if isnull({CLARITY_DEP.DEPARTMENT_NAME}) then "No Name for Department # " + totext({CLARITY_DEP.DEPARTMENT_ID},0,"") else

{CLARITY_DEP.DEPARTMENT_NAME} + " - " + totext({CLARITY_DEP.DEPARTMENT_ID},0,"")

Please help me out What I am missing ..

Thanks

Reddy

[Former Member]

need to use &

Thanks,

-G

[Former Member]

Hello,

Your code should be

CASE 
WHEN Dept = 'NONE' then 'NONE GRP' 
WHEN COALESCE(dbo.CLARITY_DEP.DEPARTMENT_ID,-1)=-1 then 'Unknown Department' 
WHEN COALESCE( dbo.CLARITY_DEP.DEPARTMENT_NAME,-1)=-1 then 'No Name for Department # ' + 
convert(varchar(40),dbo.CLARITY_DEP.DEPARTMENT_ID) 
ELSE dbo.CLARITY_DEP.DEPARTMENT_NAME + ' - ' + convert(varchar(40),dbo.CLARITY_DEP.DEPARTMENT_ID)
END

Regards,

Mark

[Former Member]

Does the code written over an universe object..................then i dont think we can use CASE function

some other function should be in place

Thanks,

Ganesh