-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|