How to implements Oracle CASE WHEN....THEN.... in Natural

, ,
1

hi all.
How to implements the syntax Oracle : CASE WHEN…THEN… in Natural ?
i’ve this query in oracle:

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

2

Tagan,

You are looking for the DECIDE ON VALUE statement.

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.

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
3

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:


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 :frowning:

4

Natural supports few SQL language extensions. 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

5

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]

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

6

Thank you George.
It work. i used the syntax


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