Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Creating a query with unrelated tables

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

Tags:
Former Member
replied

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

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question