[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Complex Query Help!

Jami

3/30/2007 2:59:00 PM

Dear All

I have following query


select Address = case
when c.EngDesc = d.EngDesc and urban_rural = 1 then
Address_details +','+ Locality+','+c.EngDesc
when c.EngDesc <> d.EngDesc and urban_rural = 1 then
Address_details +','+Locality+','+ d.Engdesc +','+ c.EngDesc
when c.EngDesc = d.EngDesc and urban_rural = 2 then
Address_details +','+ Post_office+','+City_village+','+c.EngDesc
when c.EngDesc <> d.EngDesc and urban_rural = 2 then
Address_details +','+Post_Office+','+City_village+','+ d.Engdesc +','+
c.EngDesc end
from employee a join addresses b on
a.form_no = b.form_no join lib_district c on b.district = c.distcode
join lib_tehsil d on b.tehsil = d.tehsilcode


here i m concatinating multiple fields but if there is null in any of
the concatenated field then total result come as 'NULL' how should i
change the query that when there is null any column only that fields
omitted rather all the result come as null and also coma doesnt come
againt the null value


Regards,
Jami

*** Sent via Developersdex http://www.develop... ***
3 Answers

Tom Moreau

3/30/2007 3:08:00 PM

0

Check out ISNULL() in the BOL.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Jami" <jami.khan@yahoo.com> wrote in message
news:u0WvtvtcHHA.2088@TK2MSFTNGP05.phx.gbl...
Dear All

I have following query


select Address = case
when c.EngDesc = d.EngDesc and urban_rural = 1 then
Address_details +','+ Locality+','+c.EngDesc
when c.EngDesc <> d.EngDesc and urban_rural = 1 then
Address_details +','+Locality+','+ d.Engdesc +','+ c.EngDesc
when c.EngDesc = d.EngDesc and urban_rural = 2 then
Address_details +','+ Post_office+','+City_village+','+c.EngDesc
when c.EngDesc <> d.EngDesc and urban_rural = 2 then
Address_details +','+Post_Office+','+City_village+','+ d.Engdesc +','+
c.EngDesc end
from employee a join addresses b on
a.form_no = b.form_no join lib_district c on b.district = c.distcode
join lib_tehsil d on b.tehsil = d.tehsilcode


here i m concatinating multiple fields but if there is null in any of
the concatenated field then total result come as 'NULL' how should i
change the query that when there is null any column only that fields
omitted rather all the result come as null and also coma doesnt come
againt the null value


Regards,
Jami

*** Sent via Developersdex http://www.develop... ***

Jami

3/30/2007 3:45:00 PM

0



Dear Tom i have used the isnull function thanx for that but how will i
eliminate the coma ',' for separation

regards,


*** Sent via Developersdex http://www.develop... ***

Tom Moreau

3/30/2007 4:02:00 PM

0

Try:

ISNULL (', ' + MyCol, '')

Note the two single quotes - not a double quote.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Jami" <jami.khan@yahoo.com> wrote in message
news:OmDEPJucHHA.4632@TK2MSFTNGP03.phx.gbl...


Dear Tom i have used the isnull function thanx for that but how will i
eliminate the coma ',' for separation

regards,


*** Sent via Developersdex http://www.develop... ***