Query with standard delivery address

[Former Member]

Hi there,

I built a query with the query manager where I just want a sheet with the basic customer data for which I used the tables: OCDR, CDR1, OCTG, OCPR. All is fine, however certain customers have several delivery addresses, for those customers I get all addresses including a doubling or trebling etc. of the contact persons. I was wondering if there is a possiblity to only get the standard delivery address as output data and how I would implement this in my query.

I am thankful for any suggestions.

Ute de Vries

[Former Member · Accepted solution]

Hi Ute de Vries,

Please post your query so that some one can check and give the solution.

Please refer the following

SELECT T0.[CardCode], T0.[CardName], T0.[Address] FROM OCRD T0  INNER JOIN CRD1 T1 ON T0.CardCode = T1.CardCode AND T0.[BillToDef]=T1.[Address]

Hope this helps

[Former Member]

Tthanks for cleaning up my query.

Of course, the first answer was already the right one I just had to take the [ShipToDef] intsead of [BillToDef] to answer my question. Many thanks again for the quick and helpful answers.

Ute

[Former Member]

Let´s see customer XY has this default billing address:

Name XY

Postcode ABC

4565 Village People

This delivery address:

Name XY

Street 45

4565 Village People

and additionally this delivery address (at the moment the default one):

Holmenkolmen

Street 23

8678 Somewhere

I would like to generate a query output where only both the default adresses are shown. My query shows all delivery addresses and doubles the contact persons.

{SELECT T1.[CardCode], T1.[CardName], T1.[CardType] ,T1.[Address], T1.[ZipCode], T1.[City], T1.[MailAddres], T1.[MailZipCod], T1.[MailCity], T1.[Phone1], T1.[Fax], T1.[E_Mail], T1.[IntrntSite], T1.[LicTradNum], T2.[U_kmBisOrt], T2.[U_zeitBsOrt], T3.[Name], T3.[Position], T3.[Tel1], T3.[Fax], T3.[Cellolar], T3.[E_MailL], T4.[PymntGroup] FROM .[OCTG] T4 INNER JOIN .[OCRD] T1 ON T1.[GroupNum] = T4.[GroupNum] INNER JOIN .[CRD1] T2 ON T2.[CardCode] = T1.[CardCode] INNER JOIN .[OCPR] T3 ON T3.[CardCode] = T1.[CardCode] WHERE (T1.[CardCode] = ) AND T2.[AdresType] = 'S'}

Thanks again,

Ute

[Former Member]

Hi Ute,

Try a simple one first. Hopefully it could reduce some duplicate lines:

SELECT Distinct T1.CardCode, T1.CardName, T1.CardType ,T1.Address, T1.ZipCode, T1.City, T1.MailAddres, T1.MailZipCod, T1.MailCity, T1.Phone1, T1.Fax, T1.E_Mail, T1.IntrntSite, T1.LicTradNum, T2.U_kmBisOrt, T2.U_zeitBsOrt, T3.Name, T3.Position, T3.Tel1, T3.Fax, T3.Cellolar, T3.E_MailL, T4.PymntGroup FROM dbo.OCTG T4 INNER JOIN dbo.OCRD T1 ON T1.GroupNum = T4.GroupNum INNER JOIN dbo.CRD1 T2 ON T2.CardCode = T1.CardCode AND T2.AdresType = 'S' AND T1.ShipToDef = T2.Address

INNER JOIN dbo.OCPR T3 ON T3.CardCode = T1.CardCode WHERE (T1.CardCode = [%0\] )

Thanks,

Gordon

[Former Member]

Thanks for replying (and explaining how to post properly).

I tried your suggestion and it works fine if both the billing and delivery address head are the same. But we have customers who act as channels and therefore we have totally different billing and delivery addresses. So I am wondering if there is any way to tell SAP to use both the chosen (Standard) billing and delivery address via a query.

Thanks again

Ute

[Former Member]

Hi

Can you give some sample data?? like

CustomerName------ Default Bill To Address----- Default Ship to address