WHERE CLAUSE with CONDITIONS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

WHERE CLAUSE with CONDITIONS

Author

Topic

ra_truman
Starting Member

5 Posts

Posted - 2005-04-29 : 10:24:02

Good Morning,

I have a query with several conditions.

SELECT PROD_NAME, PROD_DESC, PROD_WK1_COUNT, PROD_WK2_COUNT,
PROD_SALES
FROM PROD_TABLE

If this condition is met
WHERE PROD_WK1_COUNT = PROD_WK2_COUNT

I want the following conditions to be met:
WHERE PROD_NAME = 'GENERIC' AND
PROD_DC = 01

Else, if this condition is met
WHERE PROD_WK1_COUNT <> PROD_WKS_COUNT

I want the following condition to be met:
WHERE PROD_NAME = 'GENERIC' AND
PROD_DC = 03

So, basically I want the information for different DC's (distribution centers) based on the product week 1 and week 2 counts.

HELP! Thanks!

mr_mist
Grunnio

1870 Posts

Posted - 2005-04-29 : 10:29:40

So, you construct your WHERE clause appropriately

WHERE (prod_wk1_count = prod_wk2_count AND PROD_name = 'GENERIC' and prod_dc = '01') OR (PROD_WK1_COUNT <> PROD_WKS_COUNT AND PROD_NAME = 'GENERIC' AND
PROD_DC = 03)

-------
Moo. :)

ra_truman
Starting Member

5 Posts

Posted - 2005-04-29 : 10:38:38

Moo,

Should there not be two WHERE clauses? Because if the first WHERE clause is met,

WHERE PROD_WK1_COUNT = PROD_WK2_COUNT

I then want to pull the information from DC 01, not DC 03 based on the following conditions,

WHERE PROD_NAME = 'GENERIC' AND
PROD_DC = 01

But, if the other condition is met,
WHERE PROD_WK1_COUNT <> PROD_WK2_COUNT

I then want to pull the information from DC 03, not DC 01 based on the following conditions,

WHERE PROD_NAME = 'GENERIC' AND
PROD_DC = 03

Thanks for your help!

mr_mist
Grunnio

1870 Posts

Posted - 2005-04-29 : 10:49:00

I don't really know what you mean, but, if you want to selectively choose which table to get info from then you'd need a similar thing to

SELECT
CASE WHEN PROD_WK1_COUNT = PROD_WK2_COUNT then dc_01.prod_dc ELSE dc_02.prod_dc END as prod_dc
FROM yourtable
LEFT OUTER JOIN dc_01 ON .... AND PROD_WK1_COUNT = PROD_WK2_COUNT
LEFT OUTER JOIN dc_03 ON .... AND PROD_WK1_COUNT <> PROD_WK2_COUNT
WHERE ....

-------
Moo. :)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-29 : 11:31:19

>>LEFT OUTER JOIN dc_03 ON .... AND PROD_WK1_COUNT <> PROD_WK2_COUNT

I'm not following what the requirement is very clear, but beware of joining on inequalities .... you'll get a cartesian product. It is very rare that it would be a good design to join using operators other than =.

- Jeff

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-29 : 11:54:43

What about something like this?


SELECT	PROD_NAME, 
	PROD_DESC, 
	PROD_WK1_COUNT, 
	PROD_WK2_COUNT, 
	PROD_SALES
FROM	PROD_TABLE
WHERE	PROD_NAME = 'GENERIC' 
AND	PROD_DC = 
		case 
		when PROD_WK1_COUNT = PROD_WK2_COUNT then 01
		when PROD_WK1_COUNT <> PROD_WKS_COUNT then 03
		else -99 --what if neither condition is met?
		end

Be One with the Optimizer
TG

mr_mist
Grunnio

1870 Posts

Posted - 2005-04-29 : 11:59:38

quote:
Originally posted by jsmith8858

>>LEFT OUTER JOIN dc_03 ON .... AND PROD_WK1_COUNT <> PROD_WK2_COUNT

I'm not following what the requirement is very clear, but beware of joining on inequalities .... you'll get a cartesian product. It is very rare that it would be a good design to join using operators other than =.

- Jeff

True. I don't actually think I was answering his question anyway, because I'm not sure exactly what he wants to do.

-------
Moo. :)

ra_truman
Starting Member

5 Posts

Posted - 2005-04-29 : 15:12:56

TG,

Thanks for the query, it makes perfect sense. But this query is ran in Oracle Report Builder and it has FUNCTIONS that act against the data in order to calculate some outputted fields. After I enter the CASE statement into the query, I then attempt to compile the FUNCTIONS and I receive an error indicating the that variables in the function are not defined. Why would they not be defined? All that I did to change the query was enter the case statement.

WHERE
PROD.PROD_NAME = 'APPLES' AND
PROD_DC =
CASE
when PROD_WK1_COUNT = PROD_WK2_COUNT then 01
when PROD_WK1_COUNT <> PROD_WKS_COUNT then 03
END

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-29 : 15:37:00

Oracle Report Builder...What's that? some sort of sql server add-in?

just kidding :)

This forum is specifically MS Sql Server. There are people here who can probably help you but I'm not one of them. sorry...

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources