Tabular SQL If else logic 3

[DavidJS]

Does anyone know "If...else...end if" statements work in tabular SQL?

DavidJS

 

[drlex]

Your DB might prefer the 'CASE WHEN...THEN....ELSE...END' sequence.

soi la, soi carré

 

[DavidJS]

Hi drlex

I've implemented the case statement shown below with native SQL:

CASE WHEN ((#prompt('choice')#)=0) THEN

(select /*+ FIRST ROWS */ "Request"."REQUESTID" "Request_ID"
from "ECASH"."REQUEST" "Request" where "Request"."VOID"=0)

ELSE

(select /*+ FIRST ROWS */ "Request"."REQUESTID" "Request_ID"
from "ECASH"."REQUEST" "Request" where "Request"."VOID"=1)

END

Unfortunately, I get the error:

QE-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-9'. UDA-SQL-0446 Oracle returned an error message. ORA-24333: zero iteration count UDA-SQL-0450 An unexpected error encountered during the "prepare" operation.

Any idea why how to resolve this?

DavidJS

 

[drlex]

David,
Sorry - no Oracle experience, so I'd only be guessing from here as to the zero iteration count. It might be worth posting this on one of the Oracle Fora.

soi la, soi carré

 

[almeids]

David-
Try using the Oracle decode function in lieu of a CASE statement.
HTH
Steve

 

[DavidJS]

Hi Steve,

No luck, I'm getting the same error message.

 

[Draoued]

try your sql as individual query, to check if your sql is correct.

First this one only.
(select /*+ FIRST ROWS */ "Request"."REQUESTID" "Request_ID"
from "ECASH"."REQUEST" "Request" where "Request"."VOID"=0)

and the second after.


Or try the following

(select /*+ FIRST ROWS */ "Request"."REQUESTID" "Request_ID"
from "ECASH"."REQUEST" "Request" where "Request"."VOID"= decode(#prompt('choice')#), 0 , 0 , 1 )

 

[Draoued]

one additional comment:
I think that by default (#prompt('choice')#) is returning a STRING and not a number , so try it with

#prompt('choice')# = '0'

or use

#prompt('choice','integer')#

 

[Draoued]

so in order to correct my previous post.

(select /*+ FIRST ROWS */ "Request"."REQUESTID" "Request_ID"
from "ECASH"."REQUEST" "Request" where "Request"."VOID"= decode(#prompt('choice')#), '0' , 0 , 1 )

This should work , note the quotes around the first 0 in decode.

 

[DavidJS]

Thanks guys, Draoued code works well. I also got a variation using 'CASE WHEN...THEN....ELSE...END' as suggested by drlex. For anyone wanting to use Draoued's code, the compiler requests a couple more brackets. I've shown the final code below.

(select /*+ FIRST ROWS */ "Request"."REQUESTID" "Request_ID"
from "ECASH"."REQUEST" "Request" where "Request"."VOID"= decode((#prompt('choice')#), '0' , 0 , 1 ))