Help needed please (DECODE(RTRIM)

[Cognonsense]

Hi,
I have a piece of SQL from a package which I'm now trying to get running in Cognos 8 instead. When I get to a specific data item, below is the logic in the SQL statement:

CASE WHEN C.ANAL0 IN ('P') AND ANAL2 = 'UNITS' THEN
CASE RTRIM(ELECODE)
WHEN '301' THEN DECODE(RTRIM(U.SHIFT_HOURLY) , 'Shift', 9.6, 'Security', 12, 1)
WHEN '501' THEN    7.8
WHEN '505' THEN    1
WHEN '551' THEN    1
ELSE    0
END
ELSE 0 END * TO_NUMBER(ANAL1)

Now this is what I've done to try and get it working as my data item:

CASE WHEN [ANAL0] IN ('P') AND [ANAL2] = 'UNITS' THEN
CASE RTRIM([ELECODE])
WHEN '301' THEN DECODE(RTRIM([Query2].[Shift_Hourly]), 'Shift', 9.6, 'Security', 12, 1)
WHEN '501' THEN 7.8
WHEN '505' THEN 1
WHEN '551' THEN 1
ELSE 0
END
ELSE 0 END * TO_NUMBER([ANAL1])

It errors with the following: "The operation "subtract" is invalid for the following combination of data types: "date2" and "integer"

I don't believe that I am subtracting anything in the above code, only multiplying?

Could anyone please point out where my code is going wrong?
Thanks

[MFGF]

Hi,

You are using what appears to be Oracle-specific syntax within Cognos syntax - it might be easier to debug if you switch to all-Cognos syntax?

You could replace the DECODE() function with either an if-then-else or another CASE, and change the TO_NUMBER() function to a Cognos cast() function.

What are the data types of ANAL0, ANAL1, ANAL2 and Shift_Hourly?

Cheers!

MF.

[Cognonsense]

Thanks for the quick response.
Would i not need the DECODE function to search in column Shift_Hourly in Query2 or would a CASE statement still do the same job? I have switched the TO_NUMBER statement at the end to cast([ANAL1],integer) which i believe will work, the issue still remains when i try to verify the report with the error date2 to integer.

Ive looked on the DB and columns ANAL0 ANAL1 ANAL2 are all VARCHAR2 (50 Byte). The Shift_Hourly column is a data item which i have created in Query2, this isn't being displayed on my report as i only need it for Query1 to look up against to find a 'Shift' or 'Hourly' to make its calculation. The syntax behind the Shift_Hourly data item is below:

CASE
    WHEN [Organisation Structure].[Level 8: Structure] LIKE '%Shift%' THEN 'Shift'
    WHEN [Organisation Structure].[Level 7: Structure] LIKE '%Shift%' THEN 'Shift'
    WHEN [Organisation Structure].[Level 6: Structure] LIKE '%Shift%' THEN 'Shift'
    WHEN [Organisation Structure].[Level 5: Structure] LIKE '%Shift%' THEN 'Shift'
    WHEN [Organisation Structure].[Level 4: Structure] LIKE '%Shift%' THEN 'Shift'
    WHEN [Organisation Structure].[Level 3: Structure] LIKE '%Shift%' THEN 'Shift'
    WHEN [Organisation Structure].[Level 2: Structure] LIKE '%Shift%' THEN 'Shift'
    WHEN [Organisation Structure].[Level 1: Structure] LIKE '%Shift%' THEN 'Shift'
    ELSE    CASE
                WHEN [Organisation Structure].[Level 8: Structure] LIKE '%Security%' THEN 'Security'
                WHEN [Organisation Structure].[Level 7: Structure] LIKE '%Security%' THEN 'Security'
                WHEN [Organisation Structure].[Level 6: Structure] LIKE '%Security%' THEN 'Security'
                WHEN [Organisation Structure].[Level 5: Structure] LIKE '%Security%' THEN 'Security'
                WHEN [Organisation Structure].[Level 4: Structure] LIKE '%Security%' THEN 'Security'
                WHEN [Organisation Structure].[Level 3: Structure] LIKE '%Security%' THEN 'Security'
                WHEN [Organisation Structure].[Level 2: Structure] LIKE '%Security%' THEN 'Security'
                WHEN [Organisation Structure].[Level 1: Structure] LIKE '%Security%' THEN 'Security'
                ELSE 'Hourly'
            END
END

All levels are VARCHAR2 (80 Byte) on the database.

Any help much appreciated!
Thanks

[Cognonsense]

I have tried to re-work my expression as per below:

CASE
WHEN [ANAL0] IN ('P') AND [ANAL2] = 'UNITS' AND [ELECODE] = '301' THEN CASE
WHEN [Query2].[Shift_Hourly] = 'Shift' THEN [ANAL1] * 9.6
WHEN [Query2].[Shift_Hourly] = 'Security' THEN [ANAL1] * 12
WHEN [Query2].[Shift_Hourly] = 'Hourly' THEN [ANAL1] * 1
ELSE
CASE
WHEN [ELECODE] = '501' THEN [ANAL1] * 7.8
WHEN [ELECODE] = '505' THEN [ANAL1] * 1
WHEN [ELECODE] = '551' THEN [ANAL1] * 1
ELSE [ANAL1] * 0
END
END
END

But i still end up with the "The operation "subtract" is invalid for the following combination of data types: "date2" and "integer" error :(

edit: The error is somewhere in here:

WHEN [Query2].[Shift_Hourly] = 'Shift' THEN [ANAL1] * 9.6
WHEN [Query2].[Shift_Hourly] = 'Security' THEN [ANAL1] * 12
WHEN [Query2].[Shift_Hourly] = 'Hourly' THEN [ANAL1] * 1

When i take this out of the expression and verify, there are no errors.

[Cognonsense]

This has been resolved  8)