SQL Query help

[Former Member]

Dear Experts,

I want BP Master Data of Supplier and Customer form given date with address.

for that i make below query for supplier i.e CardType='S'

SELECT Distinct T0.[CardCode], T0.[CardName],T2.Address,T2.Street,T2.Block,T2.City,T2.ZipCode,T2.County

FROM OCRD T0 left join JDT1 T1 on  T1.[ShortName]  = T0.cardcode inner join CRD1 T2 on T0.CardCode = T2.CardCode

WHERE T0.[CardType] = 'S' and  T1.[RefDate] >= [%0]

But This query is not working for Customer.

Can anybody help me to understand this?

Thanks,

Santosh.

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this query:

SELECT T0.[CardCode], T0.[CardName], T1.[Address], T1.[Street], T1.[Block], T1.[ZipCode], T1.[City], T1.[County] FROM OCRD T0  INNER JOIN CRD1 T1 ON T0.CardCode = T1.CardCode WHERE T0.[CardType] = [%0] and  T0.[CreateDate] between [%1] and [%2]

If does not meet your requirement, please explain your requirement. Why did you link OCRD with JDT1 table?

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Hi sir,

I want BP Master data i.e Supplier and Customer who have transactions from 1st April 2012 to till date.

so that i used JDT1 table.

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this query:

SELECT Distinct T0.[CardCode], T0.[CardName],T2.Address,T2.Street,T2.Block,T2.City,T2.ZipCode,T2.County

FROM OCRD T0 left join JDT1 T1 on  T1.[ShortName]  = T0.cardcode inner join CRD1 T2 on T0.CardCode = T2.CardCode

WHERE T0.[CardType] = 'c' and  T1.[RefDate] between [%0] and [%1]

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Hi

No sir its not working.

[kothandaraman_nagarajan · Accepted solution]

Hi,

Is there is no data or any error message? I am getting result from above query.

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

hi sir,

Their is not any error message.

But it is showing me all the addresses (i.e Bill to and Ship to )for particular Customer.

i want to display default one only.

Thank you,

Santosh

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this:

SELECT Distinct T0.[CardCode], T0.[CardName],T2.Address,T2.Street,T2.Block,T2.City,T2.ZipCode,T2.County

FROM OCRD T0 left join JDT1 T1 on  T1.[ShortName]  = T0.cardcode left join CRD1 T2 on T0.CardCode = T2.CardCode and T0.[ShipToDef] = T2.Address

WHERE T0.[CardType] = 'c' and  T1.[RefDate] between [%0] and [%1]

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Hi

Your awesome sir

Thanks a lot...

Thank you,

Santosh

[former_member209066]

Hi,

Try this Query

SELECT Distinct T0.[CardCode], T0.[CardName],T2.Address,T2.Street,T2.Block,T2.City,T2.ZipCode,T2.County

FROM OCRD T0 left join JDT1 T1 on  T1.[ShortName]  = T0.cardcode LEFT OUTER join CRD1 T2 on T0.CardCode = T2.CardCode

WHERE T0.[CardType] = 'C' and  T1.[RefDate] >= '[%0]'

Thanks,

Nithi

[Former Member]

Hi,

Try Below Query,

SELECT distinct T1.[CardCode], T1.[CardName],T1.[CardType],  max(T2.[Address])Address, max(T2.[Street])Street, max(T2.[Block])Block,max(T2.[City])City,max(T2.[County])County FROM JDT1 T0 inner join OCRD T1 On T1.[CardCode]=T0.[ShortName] INNER JOIN CRD1 T2 ON T1.CardCode = T2.CardCode WHERE T0.[RefDate] <=[%0] GROUP BY T1.[CardCode], T1.[CardName],T1.[CardType] ORDER BY T1.[CardType], T1.[CardCode]

regards,

Ravuraj

[former_member205766]

Hi

Try with This query and Change the Cardtype as "c"

SELECT Distinct T0.[CardCode], T0.[CardName],T2.Address,T2.Street,T2.Block,T2.City,T2.ZipCode,T2.County

FROM OCRD T0 left join JDT1 T1 on  T1.[ShortName]  = T0.cardcode inner join CRD1 T2 on T0.CardCode = T2.CardCode

WHERE T0.[CardType] = 'C' and  T1.[RefDate] >= [%0]

With Regards

Balaji

[Former Member]

Hi Balaji,

i already tried this but its not giving me result.