Is there an IF statement that I can use in an Oracle SQL statement.
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...
Any help would be greatly appreciated...
There's DECODE, so it would be
decode(orderdate, a, qty, b, qty, 0) week1,
decode(orderdate, c, qty, d, qty, 0) week2,
and so forth...
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
where orderdate between A and B
SELECT item, description, 0 as wk_name
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
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
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 :-)
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.
I am using Oracle 188.8.131.52.0
And what do you mean use Decode and Sign at the same time?
"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.