# How to implements Oracle CASE WHEN....THEN.... in Natural **URL:** \ **Category:** Forum\ **Tags:** Adabas-Natural, Natural, Natural-Code-Samples\ **Created:** [February 4, 2020, 10:19am UTC](https://techcommunity.softwareag.com/t/how-to-implements-oracle-case-when-then-in-natural/207132 "2020-02-04T10:19:06Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Tagan\_Anto](https://avatars.discourse-cdn.com/v4/letter/t/2acd7d/32.png) [@Tagan\_Anto](https://techcommunity.softwareag.com/u/Tagan_Anto)\ **Post date:** [February 4, 2020, 10:19am UTC](https://techcommunity.softwareag.com/t/how-to-implements-oracle-case-when-then-in-natural/207132/1 "2020-02-04T10:19:06Z")
hi all. How to implements the syntax Oracle : CASE WHEN…THEN… in Natural ? i’ve this query in oracle: ```auto select NVL(case when flag_type='A' then sum(price) when flag_type='B' sum(price * 1,22) end,0) as price_tot ``` exist in Natural, case when… syntax? Thanks Tagan ---
**Author:** ![Jerome\_LeBlanc](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/jerome_leblanc/32/111_2.png) [@Jerome\_LeBlanc](https://techcommunity.softwareag.com/u/Jerome_LeBlanc)\ **Post date:** [February 4, 2020, 3:19pm UTC](https://techcommunity.softwareag.com/t/how-to-implements-oracle-case-when-then-in-natural/207132/2 "2020-02-04T15:19:08Z")
Tagan, You are looking for the DECIDE ON VALUE statement. ```auto DECIDE ON FIRST VALUE OF #FLAG VALUE 'A' WRITE '#FLAG is A' VALUE 'B' WRITE '#FLAG is B' VALUE 'C':'F' WRITE '#FLAG is between C and F' ANY VALUE WRITE '#FLAG is either A or B or between C and F' NONE VALUE WRITE '#FLAG is NEITHER A nor B' END-DECIDE ``` There is also DECIDE FOR CONDITION statement that comes in handy when you need to evaluate more complex conditions. ```auto DECIDE FOR FIRST CONDITION WHEN #FLAG = 'A' AND #PARM = 'X' WRITE 'Flag A with parameter X selected.' WHEN #FLAG = 'B' AND #PARM = 'X' WRITE 'Flag B with parameter X selected.' WHEN #FLAG = 'C' THRU 'D' WRITE 'Flag C or D selected.' WHEN NONE WRITE 'Invalid #FLAG' END-DECIDE ``` ---
**Author:** ![Tagan\_Anto](https://avatars.discourse-cdn.com/v4/letter/t/2acd7d/32.png) [@Tagan\_Anto](https://techcommunity.softwareag.com/u/Tagan_Anto)\ **Post date:** [February 4, 2020, 3:34pm UTC](https://techcommunity.softwareag.com/t/how-to-implements-oracle-case-when-then-in-natural/207132/3 "2020-02-04T15:34:32Z")
Hello. I badly explained myself. I meant if it’s possible to use the “case when” command in the select end-select construct in Natural example: ```auto SELECT ID_BRAND, NVL(case when flag_type='A' then sum(price) when flag_type='B' sum(price * 1,22) end,0) as price_tot FROM TB1, TB2, WHERE TB1.ID = TB2.ID AND ID_BRAND = 'MYBRAND' * PERFORM MY_SUBROUTINE * END-SELECT ``` I’m also trying another solution: WITH\_CTE but does not work: error “Variable / field / view must be defined in DEFINE DATA.” does not recognize the WITH\_CTE command I was left with no ideas ☹ ---
**Author:** ![Ralph\_Zbrog](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/ralph_zbrog/32/78_2.png) [@Ralph\_Zbrog](https://techcommunity.softwareag.com/u/Ralph_Zbrog)\ **Post date:** [February 4, 2020, 9:16pm UTC](https://techcommunity.softwareag.com/t/how-to-implements-oracle-case-when-then-in-natural/207132/4 "2020-02-04T21:16:04Z")
Natural supports few SQL language extensions. [http://techcommunity.softwareag.com/ecosystem/documentation/natural/nat911mf/sm/sql-common-extended-set.htm](http://techcommunity.softwareag.com/ecosystem/documentation/natural/nat911mf/sm/sql-common-extended-set.htm) The CASE construct doesn’t appear to be one of them. [http://techcommunity.softwareag.com/ecosystem/documentation/natural/nat911mf/sm/select-sql.htm](http://techcommunity.softwareag.com/ecosystem/documentation/natural/nat911mf/sm/select-sql.htm) ---
**Author:** ![George\_Cooper](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/george_cooper/32/7831_2.png) [@George\_Cooper](https://techcommunity.softwareag.com/u/George_Cooper)\ **Post date:** [February 4, 2020, 10:15pm UTC](https://techcommunity.softwareag.com/t/how-to-implements-oracle-case-when-then-in-natural/207132/5 "2020-02-04T22:15:48Z")
Tagan, You might try Natural’s “Flexible SQL” syntax, explained here: [url][http://techcommunity.softwareag.com/ecosystem/documentation/natural/nat911mf/sm/sql-flexible-sql.htm[/url]](http://techcommunity.softwareag.com/ecosystem/documentation/natural/nat911mf/sm/sql-flexible-sql.htm%5B/url%5D) It looks like you might be able to surround your CASE WHEN clause with \<\< \>\> and Natural will pass everything between the \<\< \>\> brackets to Oracle without attempting to translate or mess with it. Host variables do need to be prefixed with a : though. Hope that helps. Good luck. George ---
**Author:** ![Tagan\_Anto](https://avatars.discourse-cdn.com/v4/letter/t/2acd7d/32.png) [@Tagan\_Anto](https://techcommunity.softwareag.com/u/Tagan_Anto)\ **Post date:** [February 5, 2020, 12:13pm UTC](https://techcommunity.softwareag.com/t/how-to-implements-oracle-case-when-then-in-natural/207132/6 "2020-02-05T12:13:42Z")
> [@George Cooper](#): > > Tagan, > > You might try Natural’s “Flexible SQL” syntax, explained here: > > [http://techcommunity.softwareag.com/ecosystem/documentation/natural/nat911mf/sm/sql-flexible-sql.htm](http://techcommunity.softwareag.com/ecosystem/documentation/natural/nat911mf/sm/sql-flexible-sql.htm) > > It looks like you might be able to surround your CASE WHEN clause with \<\< \>\> and Natural will pass everything between the \<\< \>\> brackets to Oracle without attempting to translate or mess with it. Host variables do need to be prefixed with a : though. > > Hope that helps. Good luck. > George Thank you George. It work. i used the syntax ```auto SELECT SUM(table1.IMP_FIN) IMP_FIN, SUM(table1.IMP_INT) IMP_INT, table2.ID_prg, << NVL(CASE WHEN table1.FLAG NOT IN ('AA', 'BB', 'CC') THEN SUM(table1.IMP_FIN) END,0) >> IMP_CREDIT, << NVL(CASE WHEN table1.FLAG IN ('AA', 'BB', 'CC') THEN SUM(table1.IMP_FIN + (table1.IMP_FIN * 22 / 100)) END,0) >> IMP_LEASING FROM ..... ..... ``` thank you very much. Tagan ---
**Author:** ![system](https://global.discourse-cdn.com/techcommunity/original/3X/c/d/cd19967bb605d28a72d77eb83a1cae22f3df2eb8.svg) [@system](https://techcommunity.softwareag.com/u/system)\ **Post date:** [April 2, 2021, 3:44pm UTC](https://techcommunity.softwareag.com/t/how-to-implements-oracle-case-when-then-in-natural/207132/7 "2021-04-02T15:44:11Z")