Finding Null or Empty fields in Case Statement 1

[mirogak]

Hi there,

Using CR 8.5 and Oracle db 10g.

I have a variable that maps provinces/states in a certain way. Some times the province field (from db) can be empty or null. And whenever that happens, I want that province to be mapped to the province called "Other". I obviously use a formula with a case statement but I can't pickup the null/empty province. It doesn't map to 'Other', it just shows up as blank/null on the report.

Switch (
{Query1.Ryerson School Imprint} = 'SRA','All',
{Query1.Ryerson School Imprint} = 'TWG','All',
{Query1.Ryerson School Imprint} = 'CANADA SRA ADAPTATION','All',
{Query1.Ryerson School Imprint} = 'CANADA TWG ADAPTATION','All',
{Query1.Bill To Province}='AB','AB',
{Query1.Bill To Province}='NU','AB',
{Query1.Bill To Province}='NT','AB',
{Query1.Bill To Province}='NB','Atl',
{Query1.Bill To Province}='NF','Atl',
{Query1.Bill To Province}='PE','Atl',
{Query1.Bill To Province}='NS','Atl',
{Query1.Bill To Province}='BC','BC',
{Query1.Bill To Province}='YT','BC',
{Query1.Bill To Province}='MB','MB',
{Query1.Bill To Province}='ON','ON',
{Query1.Bill To Province}='QC','ON',
{Query1.Bill To Province}='SK','SK',
isnull({Query1.Bill To Province}),'N/A',
True, 'Other'
)

What do you guys think I am doing wrong here. Even if I remove the "isnull" line out of the statement, it still doesn't work.

Any thoughts?

Thanks,
mirogak

 

[fisheromacse]

you need to check for the nulls first. Something like the below.

IF isnull({Query1.Bill To Province}) then "Other"
ELSE
Switch (
{Query1.Ryerson School Imprint} = 'SRA','All',
{Query1.Ryerson School Imprint} = 'TWG','All',
{Query1.Ryerson School Imprint} = 'CANADA SRA ADAPTATION','All',
{Query1.Ryerson School Imprint} = 'CANADA TWG ADAPTATION','All',
{Query1.Bill To Province}='AB','AB',
{Query1.Bill To Province}='NU','AB',
{Query1.Bill To Province}='NT','AB',
{Query1.Bill To Province}='NB','Atl',
{Query1.Bill To Province}='NF','Atl',
{Query1.Bill To Province}='PE','Atl',
{Query1.Bill To Province}='NS','Atl',
{Query1.Bill To Province}='BC','BC',
{Query1.Bill To Province}='YT','BC',
{Query1.Bill To Province}='MB','MB',
{Query1.Bill To Province}='ON','ON',
{Query1.Bill To Province}='QC','ON',
{Query1.Bill To Province}='SK','SK'
)

 

[mirogak]

Thank you. That worked.