Query Manager replace Tablename

[former_member342635]

Hello Guys,

i want to create a query over every transaction of a bank account in the query Manager. Now this is no problem but now i want to get the SlpCode from the BaseRef and don't know how i can change the table name in the Query. I mean if i join the ODLN to join the Delivery i only get the SlpCodes for the Delivery but there are Sales Orders and Invoices too. Do you have any idea if there is a function that the query takes the table name by itself?

[Johan_H · Accepted solution]

Hi Bastian,

The trick is in starting off with the common denominator table (OSLP ?), and then LEFT OUTER JOINing the other transaction tables, or the same transaction table multiple times.

Regards,

Johan

[former_member342635 · Accepted solution]

Dear Johan,

first of all thank you for your fast answer. But i don't really get it. Here my query until now:

SELECT T0.[BaseRef] AS 'Herkunftsnr.',T0.[RefDate], T0.[TransId], T0.[Project], T0.[ProfitCode], T0.[ContraAct], T2.SlpName

FROM JDT1 T0 inner join ODLN T1 on T1.DocNum = T0.BaseRef inner join OSLP T2 on T2.SlpCode = T1.SlpCode

WHERE T0.[RefDate] >= '01.01.2016' and 

T0.[RefDate] <= '01.07.2016' and  T0.[Account] = '3000'

My problem here is now that the inner join to ODLN isn't useful because i need the ORDR and the others too. So what do you mean with left outer joining the other transaction tables?

[Johan_H · Accepted solution]

Hi Bastian,

Okay, I wasn't entirely sure what you wanted to achieve. So if I understand you correctly, you need all transactions over a certain account, including the responsible sales person?

The trouble with JDT1 is that it is connected to pretty much all possible transaction tables (sales, purchase, inventory, etc.)

So I would suggest to use a derived table. Good to know is that technically you need join ODLN, OINV, u.s.w. on both the Baseref and TransType fields. The TransType field tells JDT1 which business transaction table to get the DocNum from (OINV, ODLN, etc).

That means that you have to find out which tables have the Sales Person field, and what the TransType numbers of these respective tables are. Just as an example Delivery Notes (ODLN) is 13, and A/P Invoices (OPCH) is 18.

You would get something like this:

SELECT TOP 100 T0.[BaseRef] AS 'Herkunftsnr.',T0.[RefDate], T0.[TransId], T0.[Project], T0.[ProfitCode], T0.[ContraAct], T2.SlpName

FROM JDT1 T0

     LEFT OUTER JOIN (SELECT 13 as TransType, del.DocNum, del.SlpCode

                      FROM ODLN del

   UNION ALL

   SELECT 18, pin.DocNum, pin.SlpCode

   FROM OPCH pin

   /*

   UNION ALL

   next similar select here

   UNION ALL

   next similar select here

   UNION ALL

   next similar select here

   UNION ALL

   next similar select here

   u.s.w.,

   u.s.w.,

   u.s.w.*/) X ON T0.TransType = X.TransType AND T0.BaseRef = X.DocNum

     LEFT OUTER JOIN OSLP T2 ON X.SlpCode = T2.SlpCode

WHERE T0.[RefDate] >= '01.01.2016' and

T0.[RefDate] <= '01.07.2016' and  T0.[Account] = '3000'

You will need to extend derived table X to include all necessary tables, and you can apply parameters to the JDT1 and/or OSLP tables

Regards,

Johan