Skip to Content
avatar image
Former Member

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Oct 22, 2015 at 08:02 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 22, 2015 at 08:00 AM

    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

    Add comment
    10|10000 characters needed characters exceeded