SELECT CASE problem - 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

Transact-SQL (2000)

SELECT CASE problem

Author

Topic

doristan
Starting Member

12 Posts

Posted - 2006-08-29 : 07:43:13

Hi;
I have a query which does not ooutput the result that i wanted, please take a look at my query:

SELECT user_10= CASE
WHEN S.ship_to_ADDR_NO IS NULL THEN ''
ELSE u.USER_10
END,
r.total_amount
FROM receivable r, SHIPPER S, CUST_ADDRESS CA,
(SELECT ca.user_10
FROM cust_address ca, receivable R, SHIPPER S
WHERE R.INVOICE_id= '06/22990'
AND R.INVOICE_ID = S.INVOICE_ID
AND ca.customer_id= R.customer_id
AND ca.addr_no= CAST(S.SHIP_TO_ADDR_NO AS SMALLINT))u
WHERE R.invoice_id= '06/22990'
AND S.packlist_id = '181790'

This query will list the result only if there is SHIP_TO_ADDR_NO, But anyhow, if there is no SHIP_TO_ADDR_NO (which is = NULL) this result display nothing, which is not what i want, i want it to display out '' even there is no SHIP_TO_ADDR_NO, can anyone help me here?
Thanks and regards;
Doris

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-08-29 : 07:53:06

consider using ANSI JOIN syntax. also look into using an OUTER JOIN, this will bring rows back even if there are NULLs. This information is available in books online.

-ec

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-29 : 08:29:20

give this a try . . .

select user_10 = case when s.ship_to_addr_no is null then '' else c.user_10 end,
       r.total_amount
from   cust_address c inner join receivable r
       on  c.customer_id = r.customer_id
       left join shipper s
       on  r.invoice_id   = s.invoice_id
       and c.addr_no      = case(s.ship_to_addr_no as smallint)
       and s.packlist_id  = '181790'
where  r.invoice_id = '06/22990'

doristan
Starting Member

12 Posts

Posted - 2006-08-29 : 20:09:25

Thanks khtan, but my SHIPPER table consist of few ADDRESS_NO, say this invoice '06/22990' with customer 'M123', in CUST_ADDRESS there are 13 records found for this customer 'M123' and when test on your query, it actually listed out 13 rows, how can i restrict it to return only one exact answer?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-29 : 21:52:55

quote:
Originally posted by doristan

Thanks khtan, but my SHIPPER table consist of few ADDRESS_NO, say this invoice '06/22990' with customer 'M123', in CUST_ADDRESS there are 13 records found for this customer 'M123' and when test on your query, it actually listed out 13 rows, how can i restrict it to return only one exact answer?

So which one do you one of the 13 do you want ?

doristan
Starting Member

12 Posts

Posted - 2006-08-31 : 23:12:09

I want it to retun me WHERE C.ADDR_NO = S.SHIP_TO_ADDR_NO, only this record
thanks :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-01 : 02:00:44

Can you post the table structure and some sample data with the result that you want ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-01 : 05:38:36

Try using ISNULL in where condition...

AND ca.addr_no= CAST(IsNull(S.SHIP_TO_ADDR_NO,'-1') AS SMALLINT))u

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources