Creating a query with unrelated tables

[Former Member]

Dear all,

I am trying to create a query to do trend analysis on breakdown rate (No. of Service Calls created) and costing (based on invoices to customer) over a time period for an item.

I have drawn data from SCL4, OSCL, and OINV, but realised there is no link between the Service tables and OINV. Is it possible to link up tables that are unrelated?

Appreciate any form of assistance

Regards,

Joseph Ooi

[Johan_H · Accepted solution]

Hi Joseph,

You can link tables that are not originally related, if you can think of any (combination of) common denominator(s).

You can do this manually, or in the case of B1, there can often be found "paths" through which you can combine otherwise unrelated tables. If you use the Query Generator, you will see that some fields (in the middle pane) are bolded. Just drag such a field onto the table pane on the left, and you will see to what table this field is related. You may find that this table is also related to one of the other tables you wish to use. OCRD is a good example of such a "bridge" table.

You can also combine unrelated tables into a single query,

And finally you can combine query results from unrelated tables into a single result set, for example with the UNION and UNION ALL statements:

SELECT CardCode

FROM OINV

UNION ALL

SELECT CardCode

FROM ORIN

Regards,

Johan

[Former Member · Accepted solution]

Hi Johan,

Previously I tried bridging those tables via SCL4 to OSCL to OINS to OINV by looking out for the bolded fields and using the SAP Help file, but the query threw up a blank.

I have yet to try UNION, but managed to link up the tables in a rather crude manner:

FROM OINV T0 INNER JOIN SCL4 T1 ON T0.DocNum = T1.DocNumber INNER JOIN OSCL T2 ON T1.SrcvCallID = T2.callID

I will give UNION a shot at my next query trial, thanks for the tips.

By the way, what is the code for carrying out a summation for a column? I read about the "CTRL + LEFT CLICK" idea, but is there anything more permanent that I can implement into the query?

Regards,

Joseph

[Johan_H · Accepted solution]

Hi Joseph,

When you use bridging tables to connect unrelated tables, you need to look into the JOIN statement.

The B1 Query Generator tool will always use INNER JOIN, which means that only results from the joined tables will be shown where the common denominator can be found in both tables. In your case, where you have joined 4 different tables, the common denominator needs to be present in all 4 tables. This is very unlikely to ever be true, and thus you get an empty result set.

Please try LEFT OUTER JOIN, where the bridging table should preferably be the first. So for example something like this:

SELECT T0.CardCode

, SUM(ISNULL(T1.DocTotal, 0)) AS DocTotalInvoice

, SUM(ISNULL(T2.DocTotal, 0)) AS DocTotalCreditNote

FROM OCRD T0

LEFT OUTER JOIN OINV T1 ON T0.CardCode = T1.CardCode

LEFT OUTER JOIN ORIN T2 ON T0.CardCode = T2.CardCode

GROUP BY T0.CardCode

About the UNION statement, please note that if you use UNION, you will get all results from the upper query, and only results that differ from the bottom query. With UNION ALL, you will get all results from both queries, and you could get "doubles".

The nice thing about UNION ALL is that you can also use it to add a total row at the bottom.

For Example:

SELECT DocNum, DocTotal

FROM OINV

UNION ALL

SELECT 'Total', SUM(DocTotal)

FROM OINV

Regards,

Johan

[Former Member · Accepted solution]

Hi Johan,

I manage to implement LEFT OUTER JOIN into the query, everything seems to be working fine. But once I try implementing SUM, the query throws up an error.

I will post my query here, perhaps I am missing out on something?

SELECT T2.callID, T2.manufSN, T2.internalSN, T2.itemName, T1.DocPstDate, T0.DocNum, SUM(T0.DocTotal) AS 'Invoice Total'

FROM OINV T0

LEFT OUTER JOIN SCL4 T1 ON T0.DocNum = T1.DocNumber

INNER JOIN OSCL T2 ON T1.SrcvCallID = T2.callID

WHERE (T2.[internalSN] =[%0] AND T1.[Object] = (N'13')) AND (T0.[DocDate] >=[%1] AND T0.[DocDate] <=[%2])

I am trying to get the sum for T0.DocTotal, but when I tried SUM(T0.DocTotal) AS 'Total Invoice' or SUM(ISNULL(T1.DocTotal, 0)) AS DocTotalInvoice, I get an error.

Appreciate your assistance in this matter

Regards,

Joseph

[Johan_H · Accepted solution]

Hi Joseph,

When you use so-called aggregate functions, like SUM, you need to group all fields that are not in such a function.

So please add the GROUP BY statement at the end of your query like this:

GROUP BY T2.callID, T2.manufSN, T2.internalSN, T2.itemName, T1.DocPstDate

As you can see, we group all fields except for T0.DocTotal, because it is in the SUM function

Also, you cannot have the T0.DocNum field in the result set, so please remove that from the SELECT statement

When you want to add a totals row using the UNION statement, you need to put the same number of "fields" into the SELECT statement. My previous example would work because there were 2 fields in the top query, and 2 fields in the bottom (totals) query.

Regards,

Johan

[Former Member · Accepted solution]

Great, everything seems to be working now.

Thanks for helping!

[agustin_marcoscividanes]

Hi

you can relate OINV and SCL4 tables:

OINV.docentry = SCL4.docabs and SCL4.ObjectType = '13'

you can relate SCL4 and OSCL tables:

OSCL.Callid = SCL4.SrcvCallid

Kind regards

Agustín Marcos Cividanes