Pushing 'If then Else' selection crieteria to a Oracle DB

[Flopper]

Greetings,

I'm trying to push the following formula, through a native connection in Crystal v 8, to an Oracle database to speed up the processing of the report:

If {?ProductType}='B' then {TASK_DTL.INVN_NEED_TYPE}=53
else
If {?ProductType}='H' then {TASK_DTL.INVN_NEED_TYPE}=2
else
If {?ProductType}='S' then ({TASK_DTL.INVN_NEED_TYPE}=1 or {TASK_DTL.INVN_NEED_TYPE}=50)

However the 'else' statement will not allow me to pass this selection criteria through. I've tried using a CASE statement but i don't know how to write it for Oracle and i'm unconvinced that this will work anyway.

Can anyone help me?!?

Thanks

 

[Reebo99]

Try:

(If {?ProductType}='B' then {TASK_DTL.INVN_NEED_TYPE}=53 else true)
and
(If {?ProductType}='H' then {TASK_DTL.INVN_NEED_TYPE}=2
else true)
and
(If {?ProductType}='S' then ({TASK_DTL.INVN_NEED_TYPE}=1 or {TASK_DTL.INVN_NEED_TYPE}=50) else true)


Reebo
UK

 

[Flopper]

Thanks for the reply, but any formula with an 'else' statement will not be passed through.

I've tried most functions from SWITCH and CASE to IIF, without any luck...

 

[Reebo99]

Flopper,

Maybe I've been working with CRv9 too long, but I can't see why this wouldn't pass through.

I can't test this as I haven't got CR this week. Sorry.

Reebo
UK

 

[Flopper]

Thanks Reebo,

I too have CR9, and you are correct in that an 'If then Else' formulas would be pushed to the server. Alas... the company has still to evolve from the Jurassic period and install CR9 throughout the business. I therefore have to move on with CR8. Oh woe is me...

Can anyone else help me?!?

Thanks

 

[synapsevampire]

SQL pass thru is part voodoo with CR, no matter the version, but explicitly defining every if and else condition does wonders:

(
If {?ProductType}='B' then
{TASK_DTL.INVN_NEED_TYPE}=53
else {?ProductType}<>'B' then
true
)
and
(
If {?ProductType}='H' then
{TASK_DTL.INVN_NEED_TYPE}=2
else If {?ProductType}<>'H' then
true
)
and
(
If {?ProductType}='S' then
({TASK_DTL.INVN_NEED_TYPE} in [1,50] else
If {?ProductType}<>'S' then
true
)

-k

 

[Flopper]

synapsevampire,

Simply... you're a legend. Thanks very much. I'll make a note of this.

Thanks again.

Flopper

 

[Flopper]

SV,

The formula works correctly but i'm trying to figure out how it works. Its the 'True' part which is confusing me. I understand that the result of the formula is a boolean but if i state formula=true in the selection criteria then shouldn't {?ProductType}<>'B' be returned as you have stated:

If {?ProductType}<>'B' then true

The data is returning correctly but i don't understand how this part isn't being selected.

Thanks