1. Registered User
Join Date
Jul 2008
Posts
16

Hi

I'm trying to retrieve multiple calculated fields (ala excel) from the same table each with its own conditions. Consider the following two select tables:

Code:
SELECT (GMM_DRY_LAB_TX1/(GMM_DRY_LAB_TX1+GMM_WET_LAB_TX1-GMM_SMP_LAB_TX1)) AS GR_LAB_TX1 FROM GMM WHERE (GMM_DRY_LAB_TX1+GMM_WET_LAB_TX1-GMM_SMP_LAB_TX1)<>0
SELECT (GMM_DRY_LAB_TX2/(GMM_DRY_LAB_TX2+GMM_WET_LAB_TX2-GMM_SMP_LAB_TX2)) AS GR_LAB_TX2 FROM GMM WHERE (GMM_DRY_LAB_TX2+GMM_WET_LAB_TX2-GMM_SMP_LAB_TX2)<>0
How would one combine these two select stmts to output GR_LAB_TX1,GR_LAB_TX2

The code below doesn't work for me as the AND statement may eliminate one result if the other is not satisfied and vice versa.

Code:
SELECT (GMM_DRY_LAB_TX1/(GMM_DRY_LAB_TX1+GMM_WET_LAB_TX1-GMM_SMP_LAB_TX1)) AS GR_LAB_TX1,
(GMM_DRY_LAB_TX2/(GMM_DRY_LAB_TX2+GMM_WET_LAB_TX2-GMM_SMP_LAB_TX2)) AS GR_LAB_TX2
FROM GMM
WHERE
(GMM_DRY_LAB_TX1+GMM_WET_LAB_TX1-GMM_SMP_LAB_TX1)<>0 AND
(GMM_DRY_LAB_TX2+GMM_WET_LAB_TX2-GMM_SMP_LAB_TX2)<>0
Not sure I explained this correctly. Condition 1 should apply to the first select and independently condition 2 applies to the second regardless if the first is true. Any suggestions how to tackle this?
Last edited by Andre Smit; 09-11-08 at 13:49.

2. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912

Change the AND to OR

3. Registered User
Join Date
Jul 2008
Posts
16
Using OR wont work since a division by zero may occur if all the conditions are not met.

4. another indirection layer
Join Date
May 2004
Location
Seattle
Posts
1,313
put the value you are selecting in a CASE to check for the div0 case.

5. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Ok - the where condition is right.

Code:
..../NULLIF(GMM_DRY_LAB_TX2+GMM_WET_LAB_TX2-GMM_SMP_LAB_TX2, 0).....

6. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Originally Posted by jezemine
put the value you are selecting in a CASE to check for the div0 case.
Pffff - if you get paid by the keystroke

7. Registered User
Join Date
Jul 2008
Posts
16
Thanks guys

Both work - pootle's solution is a surprise - I've never used NULLIF b4

Here's the code

Code:
SELECT
CASE
WHEN (GMM_DRY_LAB_TX1+GMM_WET_LAB_TX1-GMM_SMP_LAB_TX1)<>0 THEN
(GMM_DRY_LAB_TX1/(GMM_DRY_LAB_TX1+GMM_WET_LAB_TX1-GMM_SMP_LAB_TX1))
END AS GR_LAB_TX1,
CASE
WHEN (GMM_DRY_LAB_TX2+GMM_WET_LAB_TX2-GMM_SMP_LAB_TX2)<>0 THEN
(GMM_DRY_LAB_TX2/(GMM_DRY_LAB_TX2+GMM_WET_LAB_TX2-GMM_SMP_LAB_TX2))
END AS GR_LAB_TX2,
CASE
WHEN (GMM_DRY_LAB_TX3+GMM_WET_LAB_TX3-GMM_SMP_LAB_TX3)<>0 THEN
(GMM_DRY_LAB_TX3/(GMM_DRY_LAB_TX3+GMM_WET_LAB_TX3-GMM_SMP_LAB_TX3))
END AS GR_LAB_TX3
.
.
.
FROM GMM

SELECT
GMM_DRY_LAB_TX1/NULLIF(GMM_DRY_LAB_TX1+GMM_WET_LAB_TX1-GMM_SMP_LAB_TX1,0) AS GR_LAB_TX1,
GMM_DRY_LAB_TX2/NULLIF(GMM_DRY_LAB_TX2+GMM_WET_LAB_TX2-GMM_SMP_LAB_TX2,0) AS GR_LAB_TX2,
GMM_DRY_LAB_TX3/NULLIF(GMM_DRY_LAB_TX3+GMM_WET_LAB_TX3-GMM_SMP_LAB_TX3,0) AS GR_LAB_TX3
.
.
.
FROM GMM

8. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Originally Posted by Andre Smit
pootle's solution is a surprise
You are no more surprised than me - I get one right about every 3000 posts: ask anyone.

Glad they worked out for you.

9. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Wow, I wonder if that means that I'm due to get one right soonish.

-PatP

10. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
yes, i have had three or four correct myself too

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•