Query Giving Null Value

[Former Member]

Hi

Please help in query it is showing null value.Is there any Wrong in joining

SELECT CRD1.Address','CRD1.Street','CRD1.Block','CRD1.City

','cast(CRD1.ZipCode as varchar(255))','

CRD1.County','

CRD1.State','

CRD1.Country ','

cast(CRD1.StreetNo as varchar(255))','

cast(CRD1.Building as varchar(255))

FROM OCRD

inner JOIN CRD7 ON OCRD.CARDCODE=CRD7.CARDCODE

left outer JOIN CRD1 ON OCRD.CardCode=CRD1.CardCode

WHERE OCRD.CardCode='VA00022'

AND CRD7.AddrType='S'

Regards

Rajesh

[Former Member]

Ramu ,

Pls find the below query

SELECT max(T0.ShortName) 'Customer', Max(T2.CardName) 'Customer Name', ISNULL (sum(T0.[Debit]),0) "Debit",ISNULL (sum(T0.[Credit]),0) "Credit", SUM(ISNULL(T0.Debit,0) - ISNULL(T0.Credit,0)) as "Amount(LC)" FROM dbo.JDT1 T0 INNER JOIN dbo.OJDT T1 ON T1.TransID = T0.TransID INNER JOIN dbo.OCRD T2 ON T2.CardCode = T0.ShortName WHERE t1.RefDate >= '20150401' and t1.RefDate

Regards,

Ramasamy

[Former Member]

Closed

[Former Member]

Hi,

You can try this way:

SELECT T1.CardCode,T1.CardName, T0.Address','T0.Street','Isnull(T0.Block,'')','IsNull(T0.City,'')

','IsNull(cast(T0.ZipCode as varchar(255)),'')','

Isnull(T0.County,0)','

Isnull(T0.State,'')','

IsNull(T0.Country,'')

FROM OCRD T1

INNER JOIN CRD7 T2 ON T2.CARDCODE=T1.CARDCODE AND T2.AddrType='S'

left JOIN CRD1 T0 ON T1.CardCode=T0.CardCode AND T0.AdresType='S'

WHERE T1.CardCode LIKE '[%0\]%'

However, if there are null value in certain fields, you will end up with more comas than needed.

Thanks,

Gordon

[Former Member]

Hi Rajesh,

Try to give ISNULL function in each field you want to retrieve. Something like this

SELECT ISNULL(CRD1.Address,'')','ISNULL(CRD1.Street,'')','ISNULL(CRD1.Block,'') ... and so on

Best Regards,

Hendry Wijaya

[Former Member]

Hi Rajesh......

This Query will work only when you put each and every details of Address Field wise....

try in any one of the Master Data........

Regards,

Rahul