Microsoft Visual Sudio - 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

General SQL Server Forums

New to SQL Server Programming

Microsoft Visual Sudio

Author

Topic

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-04-28 : 14:22:24

I am having issues with sql commands. I have 2 tables joined that shows me set sales and equipment sold to customers. Now my issue is I dont want all the customers showing. Their is a field that is types of sales (aka on rent, reserved). When I do a querie it shows all cust for all types of sales. What would the command and how would it look. I thought about it and i figured I should use a If cust type sale is = to on rent and reserved then show cust name. The next thing is I dont want this to be the primary filter and it should only be a filter for cust name and not affect the results of the info already gathered. any help would be great thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 14:26:52

Please post some sample data from your table and your desired o/p out of them for quick and correct solution.

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-04-28 : 14:33:31

send me a pm or your email add and I can send you the data that you can see and ill send you the code as well

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 14:36:45

Just post a couple of records here and your expected output out of them

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-04-28 : 14:46:08

Equip # Sts Cust # Cust Name
4204U AV 215 John
45647 On 245 Mike
25456 on 456 Rich
54842 Av 758 Tom

What I want it to do is show everything its showing now but the cust names of john and tom since the item is AV=available. basicly those 2 people rented that equipment awhile back and its now in stock to be rented again so no name should be shown. thats why I was thinking of a if then show. But i dont know how to set that up.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 14:48:12

So you want to show null value for cust name & # for those rows

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-04-28 : 14:52:03

yes

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 15:03:17

ok try this:-

SELECT [Equip #],
 Sts,
 CASE WHEN Sts='AV' THEN NULL ELSE [Cust #] END,
 CASE WHEN Sts='AV' THEN NULL ELSE [Cust Name] END
FROM YourTable

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-04-28 : 15:22:05

The only problem i have with that is I am already using a distinct. here is the code we are talking about.

SELECT DISTINCT equip.kequipnum AS Equipment#, equip.kmfg, equip.kmodel, equip.oeitemnum, equip.kserialnum, equip.icqtypro01, equip.eqpphybr, equip.eqpstatus,equip.eqpdesc, equip.custdisad1, equip.eqponrdt, equip.eqpoffrdt, equip.ubrphone04, equip.apgrossamt, equip.eqprntmnth, equip.eqprntweek, equip.custcity, equip.eqptype, equip.eqprntday, equip.eqprntcust, custmast.custname
FROM equip INNER JOIN custmast ON equip.eqprntcust = custmast.kcustnum
WHERE (equip.ubrphone04 > '0') AND (equip.eqpstatus <> 'SO') AND (equip.kmfg IN ('WO', 'BT', 'BO', 'CM', 'CD', 'DY', 'IR', 'ZZ', 'BM', 'EA', 'ER', 'HU', 'HH', 'IT', 'JC', 'MS', 'LB', 'PU', 'GH', 'MI', 'MQ', 'PE', 'RW', 'RS', 'SC', 'SN', 'TR', 'DD', 'YN', 'ZA', 'BC', 'JD'))

you would want to look at under the select statment its equip.eqpstatus this is the field that controls the AV, ON, RE, OR fields. and then its the equip.eqprntcust, custmast.custname. Those 2 are the actuall table joins that I am linking.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 15:24:33

quote:
Originally posted by midpenntech

The only problem i have with that is I am already using a distinct. here is the code we are talking about.

SELECT DISTINCT equip.kequipnum AS Equipment#, equip.kmfg, equip.kmodel, equip.oeitemnum, equip.kserialnum, equip.icqtypro01, equip.eqpphybr, equip.eqpstatus,equip.eqpdesc, equip.custdisad1, equip.eqponrdt, equip.eqpoffrdt, equip.ubrphone04, equip.apgrossamt, equip.eqprntmnth, equip.eqprntweek, equip.custcity, equip.eqptype, equip.eqprntday, equip.eqprntcust, custmast.custname
FROM equip INNER JOIN custmast ON equip.eqprntcust = custmast.kcustnum
WHERE (equip.ubrphone04 > '0') AND (equip.eqpstatus <> 'SO') AND (equip.kmfg IN ('WO', 'BT', 'BO', 'CM', 'CD', 'DY', 'IR', 'ZZ', 'BM', 'EA', 'ER', 'HU', 'HH', 'IT', 'JC', 'MS', 'LB', 'PU', 'GH', 'MI', 'MQ', 'PE', 'RW', 'RS', 'SC', 'SN', 'TR', 'DD', 'YN', 'ZA', 'BC', 'JD'))

you would want to look at under the select statment its equip.eqpstatus this is the field that controls the AV, ON, RE, OR fields. and then its the equip.eqprntcust, custmast.custname. Those 2 are the actuall table joins that I am linking.

Place the CASE WHEN for code in blue

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-04-28 : 15:28:26

i dont understand. as you can see i did an interjoin (2 tables) matching up equip.eqprntcust=custmast.custnum (this links the cust # from both tables. then i display the eqprntcust and custmast.custname. (showing the cust # and cust name.

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-04-28 : 15:29:04

I dont have a case when just Select, FROM, and WHERE.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 15:31:56

[code]SELECT DISTINCT equip.kequipnum AS Equipment#, equip.kmfg, equip.kmodel, equip.oeitemnum, equip.kserialnum, equip.icqtypro01, equip.eqpphybr, equip.eqpstatus,equip.eqpdesc, equip.custdisad1, equip.eqponrdt, equip.eqpoffrdt, equip.ubrphone04, equip.apgrossamt, equip.eqprntmnth, equip.eqprntweek, equip.custcity, equip.eqptype, equip.eqprntday,
CASE WHEN equip.eqpstatus='AV' THEN NULL ELSE equip.eqprntcust END, CASE WHEN equip.eqpstatus='AV' THEN NULL ELSE custmast.custname END
FROM equip INNER JOIN custmast ON equip.eqprntcust = custmast.kcustnum
WHERE (equip.ubrphone04 > '0') AND (equip.eqpstatus <> 'SO') AND (equip.kmfg IN ('WO', 'BT', 'BO', 'CM', 'CD', 'DY', 'IR', 'ZZ', 'BM', 'EA', 'ER', 'HU', 'HH', 'IT', 'JC', 'MS', 'LB', 'PU', 'GH', 'MI', 'MQ', 'PE', 'RW', 'RS', 'SC', 'SN', 'TR', 'DD', 'YN', 'ZA', 'BC', 'JD'))[/code]

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-04-28 : 15:40:08

That did not work.
Error in SELECT clause: expression near 'CASE'.
Missing FROM clause.
Unable to parse query text.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-29 : 00:19:12

Can you post the full query used?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources