Getting error while parsing "Case statement"

[Former Member]

Hi,

Getting  error when trying to parse the following case statement in BOXI 3.1 Universe.

here i am using 2 tables.

ORDE.DW_CUST and ORDE.DW_CUST_SEG

let me know if i am doing wrong.

CASE

WHEN ORDE.DW_CUST.BILL_CODE='IP1' THEN  ORDE.DW_CUST_SEG.PRCSSNG_CODE='ICP'

ELSE

WHEN ORDE.DW_CUST.BILL_CODE='IP2' THEN  ORDE.DW_CUST_SEG.PRCSSNG_CODE='ICP'

ELSE

  ORDE.DW_CUST_SEG.PRCSSNG_CODE='OTH'

END

Error Message: Parse Failed: Exception: DBD, ORA-00905: missing keyword  State:N/A

[Former Member · Accepted solution]

Hi Praveen,

For your case single case is sufficient I guess. Try below

CASE

WHEN (ORDE.DW_CUST.BILL_CODE='IP1' AND ORDE.DW_CUST_SEG.PRCSSND_CIDE='ICP')   OR (ORDE.DW_CUST.BILL_CODE='IP2' AND ORDE.DW_CUST_SEG.PRCSSND_CIDE='ICP')

THEN 'ICP'

ELSE

'OTHER'

END

[Former Member · Accepted solution]

thanks suresh, it worked

[Former Member]

Hi,

Try below

CASE

WHEN ORDE.DW_CUST.BILL_CODE='IP1' AND ORDE.DW_CUST_SEG.PRCSSNG_CODE='ICP' THEN *********

ELSE

WHEN ORDE.DW_CUST.BILL_CODE='IP2' AND ORDE.DW_CUST_SEG.PRCSSNG_CODE='ICP' THEN  ***********

ELSE

  *************

END

Cheers,

Suresh Babu Aluri.

[Former Member]

Hi Suresh,

        it worked only for single CASE statement

WORKED:

CASE

WHEN ORDE.DW_CUST.BILL_CODE='IP1' AND ORDE.DW_CUST_SEG.PRCSSND_CIDE='ICP'

THEN 'ICP'

ELSE 'OTHER'

END

FAILED:

CASE

WHEN ORDE.DW_CUST.BILL_CODE='IP1' AND ORDE.DW_CUST_SEG.PRCSSND_CIDE='ICP'

THEN 'ICP'

ELSE

WHEN ORDE.DW_CUST.BILL_CODE='IP2' AND ORDE.DW_CUST_SEG.PRCSSND_CIDE='ICP'

THEN 'ICP'

ELSE

'OTHER'

END

[Former Member]

Hi Praveen,

Have you tried executing this code directly against Oracle (say using Toad or SqlPlus)? Do you get any error?

Also are you using this code in SELECT clause or WHERE clause?

Regards,

Yuvraj Singh

[Former Member]

Hi Yuvraj,

        using the above statement in SELECT clause

[Former Member]

Have you tried executing this code DIRECTLY against Oracle (say using Toad or SQLPlus)?

Do you get any error?