Case statement in an object

[Former Member]

PFA

Hi,

I am using the below case statement in an object of a BO Universe but i am getting the oracle errors while parsing the object. please suggest.

  CASE WHEN ( LINE_ITEM_DN.AUTH_DISC_OT_PC/100 )<( Case

                                                                                                    When LINE_ITEM_DN.LINE_TYPE = 'BD'

                                                                                                     Then LINE_ITEM_DN.BL_GUIDANCE_FLOOR_PCT

                                                                                                      else LINE_ITEM_DN.LDS_GUIDANCE_FLOOR_PCT

                                                                                                    end )  THEN "BF"

          WHEN ( LINE_ITEM_DN.AUTH_DISC_OT_PC/100 )=( Case

                                                                                          When LINE_ITEM_DN.LINE_TYPE = 'BD'

                                                                                          Then LINE_ITEM_DN.BL_GUIDANCE_FLOOR_PCT

                                                                                           else LINE_ITEM_DN.LDS_GUIDANCE_FLOOR_PCT

                                                                                            end ) THEN "AF"

          WHEN ( LINE_ITEM_DN.AUTH_DISC_OT_PC/100 ) BETWEEN (( Case

                                                                                                              When LINE_ITEM_DN.LINE_TYPE = 'BD'

                                                                                                               Then LINE_ITEM_DN.BL_GUIDANCE_FLOOR_PCT

                                                                                                                else LINE_ITEM_DN.LDS_GUIDANCE_FLOOR_PCT

                                                                                                                   end )

                                                                                                          AND ( Case

                                                                                                                        When LINE_ITEM_DN.LINE_TYPE = 'BD'

                                                                                                                        Then LINE_ITEM_DN.BL_GUIDANCE_TYPICAL_PCT

                                                                                                                             else LINE_ITEM_DN.LDS_GUIDANCE_TYPICAL_PCT

                                                                                                                   end )) THEN "FT"

          ELSE "NG"

Thanks,

Bhaskar

[Former Member · Accepted solution]

You need to have a "END" after the case statement to work..

[Former Member · Accepted solution]

Yes Mishra. Initially I was forgot to use End. But later I tried with END as well, but still getting the same  operator error.

I think we can't use between operator in CASE function in SELECT clause

What do you think?

[former_member211127 · Accepted solution]

It looks like syntax error, try single case staments first and nested them.

[Former Member · Accepted solution]

I am not sure on if Between is supported in case or not but the best way to check is to replace the between with a equal (=) and see if it works ...else most likely between is not supported with Case

[Former Member · Accepted solution]

Ya I will try an alternative for Between then we will see

[Former Member · Accepted solution]

Yes, BETWEEN operator is not working in Case stmt for an object. So i am using AND operator as an alternative for BETWEEN operator

Any suggests !!

[Former Member · Accepted solution]

Correction:

We can use between operator in CASE:

Example:

CASE

          WHEN object1 < object2 THEN 'AF'

          WHEN object1 between object2 and object3 THEN 'AF'

          ELSE 'NG' 

END

Thanks,

Bhaskar

[Former Member · Accepted solution]

Correction:

We can use between operator in CASE:

Example:

CASE

          WHEN object1 < object2 THEN 'AF'

          WHEN object1 between object2 and object3 THEN 'AF'

          ELSE 'NG'

END

Thanks,

Bhaskar