Is there anyway to have an IF statement?

[aarians]

Is there an IF statement that I can use in an Oracle SQL statement.

Example:

SELECT
item,
description,
Week1 = If orderdate between A and B Then qty, else 0
Week2 = If orderdate between C and D then qty, else 0
and so forth...
FROM
orders;

Any help would be greatly appreciated...

Thank you

[m1l]

There's DECODE, so it would be

SELECT
item,
description,
decode(orderdate, a, qty, b, qty, 0) week1,
decode(orderdate, c, qty, d, qty, 0) week2,
and so forth...
FROM
orders;

As you can see, it can't do ranges, it is a one to one, then an others value.

Is this what you want ?

[harpal]

The dcode() function is good only if you have predefined values to check against, it can't help in range comparisions.
What you need is a UNION to join two SQL statements like this:

SELECT item, description, qty as wk_name
FROM orders
where orderdate between A and B
UNION
SELECT item, description, 0 as wk_name
FROM orders
where orderdate not between A and B
;

Hope this helps.

[aarians]

Well a Union would be nice, but really i am looking to having several columns
7 weeks
and this is a VERY large table, so 7 queries unioned together would be VERY large indeed.

I can't believe there is no logic commands besides strict = in Oracle SQL

I think this is a HUGE disadvantage

any suggestions?

[m1l]

You're right, that is probably the only way you can do it, but there are likely to be major performance implicants .......... it could run like a dog :-)

[mber]

Two ways:

1. Using decode and sign at same time, this two functions are existed in diferent versions.

2. If U are in latest version, probably U can use "case" function. Lookup the corresponding docus for the usage.

Take care

[aarians]

I am using Oracle 8.0.4.4.0

And what do you mean use Decode and Sign at the same time?

[mber]

Hi,

"Sign" is a function which indicating the value > 0, = 0, and <0, it becomes more powerful in conjunction with "decode" function.

P.l. check "The complete reference, ORACLE 8" P418, hope it will give you a hint.

Take Care