| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
06-24-09, 16:41
|
|
Registered User
|
|
Join Date: Sep 2003
Posts: 32
|
|
|
where condition with if
|
|
I have a query like this
select A
from file.lib
where h<>0
I want to add conditon like if A = 'abc', where h <>1
else where h<>0
How can I do this in one query?
Thanks,
Lisa
|
|
06-24-09, 16:46
|
|
Registered User
|
|
Join Date: May 2003
Location: USA
Posts: 5,196
|
|
select A
from file.lib
where (h<>0 and A <> 'abc')
or (h <> 1 and A = 'abc');
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
|
|
06-24-09, 16:48
|
|
Registered User
|
|
Join Date: Sep 2003
Posts: 32
|
|
|
|
06-24-09, 17:04
|
|
Registered User
|
|
Join Date: Sep 2003
Posts: 32
|
|
I have another trouble:
Actually, my query is more complicated than the example
select A, B
from file.lib
where (h<>0 and A <> 'abc')
or (h <> 1 and A = 'abc');
But actually, I want field B's condition always be h<>0
|
|
06-24-09, 17:09
|
|
Registered User
|
|
Join Date: Sep 2003
Posts: 32
|
|
Actually, it is:
select A, sum(B), sum(c)
from file.lib
where (h<>0 and A <> 'abc')
or (h <> 1 and A = 'abc');
For sum(B), the query result is correct. For sum(C), the conditon is always where (h<>0)
|
|
06-24-09, 18:20
|
|
Registered User
|
|
Join Date: May 2009
Posts: 472
|
|
Newer, try the CASE expression:
Code:
SELECT A
, SUM(CASE WHEN H <> 0 THEN B
ELSE 0
END)
, SUM(CASE WHEN H <> 1 AND A = 'abc' THEN C
WHEN H <> O THEN C
ELSE 0
END)
FROM file.lib
GROUP BY A
|
|
06-25-09, 09:44
|
|
Registered User
|
|
Join Date: Sep 2003
Posts: 32
|
|
Thank you so much. SQL could be so beautiful!
Just another question, I want to the T as follow, is there any better way to write the SQL?
SELECT A
, SUM(CASE WHEN H <> 0 THEN B
ELSE 0
END)
, SUM(CASE WHEN H <> 1 AND A = 'abc' THEN C
WHEN H <> O THEN C
ELSE 0
END)
, (SUM(CASE WHEN H <> 0 THEN B
ELSE 0
END)/SUM(CASE WHEN H <> 1 AND A = 'abc' THEN C
WHEN H <> O THEN C
ELSE 0
END)) AS T
FROM file.lib
|
|
06-25-09, 09:58
|
|
Registered User
|
|
Join Date: May 2009
Posts: 472
|
|
I haven't done the math to verify this but I believe this is equivalent:
Code:
SELECT A, SUM_B, SUM_C, SUM_B / SUM_C AS T
FROM (
SELECT A
, SUM(CASE WHEN H <> 0 THEN B
ELSE 0
END) AS SUM_B
, SUM(CASE WHEN H <> 1 AND A = 'abc' THEN C
WHEN H <> O THEN C
ELSE 0
END) AS SUM_C
FROM file.lib
GROUP BY A
) AS NT
|
|
06-25-09, 10:24
|
|
Registered User
|
|
Join Date: Sep 2003
Posts: 32
|
|
OK, never mind. I think the previous one without subquery and performance is probabely better.
|
|
06-25-09, 10:38
|
|
Registered User
|
|
Join Date: May 2009
Posts: 472
|
|
Actually, it isn't using a subquery. It is using a Nested table. You do need to compare both to see if there is a significant difference in performance but they should run in about the same time.
|
|
06-25-09, 16:04
|
|
Registered User
|
|
Join Date: Dec 2007
Location: Richmond, VA
Posts: 782
|
|
Also, for a performance boost change the else 0 to else null. Reason being is a zero is added to your sum, while a null is ignored. This has been shown in some cases to improve query performance by up to 40%.
Dave Nance
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
where condition with if
