creating a query for multiple address points

[Former Member]

hi sql expert,

i need to find out the list of my secondary ship to address from sap b1. How can i do this? what table should i get this?

thanks.

Harith

[Former Member]

Try:

SELECT T0.[CardCode], T0.[Address], T0.[LineNum]

FROM CRD1 T0

JOIN OCRD T1 ON T1.CardCode=T0.CardCode

WHERE T0.[AdresType] ='S' and T0.Address != T1.ShipToDef AND T1.CardCode LIKE '[%0]%'

[Former Member]

Hi Harith,

Your requirement is not very clear to me. Please have an example for what you exactly want.

Thanks,

Gordon

[Former Member]

Hi gordon,

I want to query out all of the secondary addresses onwards from all of my customers. for example customer A, has 5 delivery points..by default, the 1st point is the same as the bill to address right? so, i need to know what are the rest of the delivery address from 2 to 5.

Hope this clears.

Thanks.

Harith

[Former Member]

Hi,

Use this Query...

SELECT T0.[CardCode], T0.[Address], T0.[AdresType],  T0.[LineNum] FROM CRD1 T0 WHERE T0.[AdresType] ='S' and   T0.[LineNum] between  2 and 20 order by CardCode,linenum

Regards,

Sudhir B.

[jitin_chawla]

Hi,

Check this :

select cardcode, Address, Address2, Address3

from CRD1

where AdresType = 'S'

Change 'S' to 'B' if you want the Bill to Address.

Kind Regards,

Jitin

SAP Business One Forum Team

[Former Member]

Hi,

Use CRD1 Table

Eg Query Modify this..

SELECT T0.[CardCode], T0.[Address], T0.[AdresType],  T0.[LineNum] FROM CRD1 T0 WHERE T0.[AdresType] ='S' and  T0.[LineNum] =2

Regards,

Sudhir B.

[Former Member]

hi sudhir,

thanks for the quick reply. What about if i want to know from secondary address onwards? regardless how many shipping points are there in a particular customer? should i put a range to the lineNum? how is that?

thanks.
Harith

[jitin_chawla]

Hi,

Linenum is the sequence in which the address is displayed in the Application Address tab of the Business Partner Master Data.

So if you want to have other than the first one, then you can enter in query like Linenum in ('1','2','3')

Kind Regards,

Jitin

SAP Business One Forum Team

[Former Member]

Hi Harith.........

Please check CRD1 table and make necessary filters wherever you require........

Regards,

Rahul

[Former Member]

Hi,

Modify the query accordingly.. like below

SELECT T0.[CardCode], T0.[Address], T0.[AdresType],  T0.[LineNum] FROM CRD1 T0 WHERE T0.[AdresType] ='S' and   T0.[CardCode] =[%0]

Regards,

Sudhir B.

[Former Member]

hi sudhir,

maybe i can do like this

SELECT T0.[CardCode], T0.[Address], T0.[AdresType],  T0.[LineNum] FROM CRD1 T0 WHERE T0.[AdresType] ='S' and  T0.[LineNum] >= '2'

Thanks.