Is there anyway to have an IF statement?

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

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 ?

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.

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?

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

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

I am using Oracle 8.0.4.4.0

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

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