Batch Query with Batch but need address lines split by column

[Former Member]

Hi folks,

I have the following query I created with the batch number of the sales order:

SELECT T0.[DocDate] as "Date", T1.[DocNum] as "Sales Order Number",  T0.[CardCode] as "Customer Code", T0.[CardName] as "Customer Name",T1.[Address2], T0.[ItemCode],  T0.[ItemName], T0.[BatchNum], T0.[Quantity], T1.pickrmrk FROM IBT1 T0, ORDR T1 WHERE T0.[BaseNum] = T1.[DocNum] and T1.docstatus = 'O' and T0.WHSCODE = '01' order by T0.BaseNum

Now I need to split the address by column so need street, block, city, county, country and zipcode fields from CRD1, but would either need the ship to address of the sales order it is selected or even just the default ship to address of the sales order.  In above query T1.[Adress2] is not quite right for our needs, as this file will have to be exported to 3rd party.  So I have gotten this far:

SELECT T0.[DocDate] as "Date", T1.[DocNum] as "Sales Order Number",  T0.[CardCode] as "Customer Code", T0.[CardName] as "Customer Name",T1.[Address2], T2.[Street], T2.[Street], T2.[Block], T2.[City], T2.[County], T2.[Country], T2.[Zipcode], T0.[ItemCode],  T0.[ItemName], T0.[BatchNum], T0.[Quantity], T1.pickrmrk FROM IBT1 T0, ORDR T1, CRD1 T2 WHERE T0.[BaseNum] = T1.[DocNum] and T1.docstatus = 'O' and T0.WHSCODE = '01' and T0.[CardCode] = T2.[CardCode] and T2.[AdresType] = 'S' order by T0.BaseNum

but will split into multiples if a business partner have more than one shipping address.

Can someone please advise on this one?

Many thanks in advance,

[kothandaraman_nagarajan · Accepted solution]

Hi,

If you got answer, please close this thread by marking helpful answer.

Thanks.

[agustin_marcoscividanes]

Hi

try with RDR12 or INV12.

Kind regards

Agustín Marcos Cividanes

[Former Member]

Thanks Agustin, tried it with RDR12 as the following query:

SELECT T0.[DocDate] as "Date", T1.[DocNum] as "Sales Order Number",  T0.[CardCode] as "Customer Code", T0.[CardName] as "Customer Name",T1.[Address2], T2.[StreetS], T2.[BlockS], T2.[CityS], T2.[ZipCodeS], T2.[CountyS], T2.[CountryS], T2.[ZipCodeS], T0.[ItemCode],  T0.[ItemName], T0.[BatchNum], T0.[Quantity], T1.pickrmrk FROM IBT1 T0, ORDR T1, RDR12 T2 WHERE T0.[BaseNum] = T1.[DocNum] and T1.docstatus = 'O' and T0.WHSCODE = '01' order by T0.BaseNum

but has multiple lines for different addresses (and not just shipping addresses of that businss partner sales order).

Anymore ideas or can you spot error in the query?

Thanks again,

[Former Member]

Actually I know where I went wrong now:

SELECT T0.[DocDate] as "Date", T1.[DocNum] as "Sales Order Number",  T0.[CardCode] as "Customer Code", T0.[CardName] as "Customer Name",T1.[Address2], T2.[StreetS], T2.[BlockS], T2.[CityS], T2.[ZipCodeS], T2.[CountyS], T2.[CountryS], T2.[ZipCodeS], T0.[ItemCode],  T0.[ItemName], T0.[BatchNum], T0.[Quantity], T1.pickrmrk FROM IBT1 T0, ORDR T1, RDR12 T2 WHERE T0.[BaseNum] = T1.[DocNum] and T1.docstatus = 'O' and T0.WHSCODE = '01' and  T1.DocEntry = T2.DocEntry order by T0.BaseNum

had to put in the following:

T1.DocEntry = T2.DocEntry

Thanks again though