LEFT JOIN in NPR

Home/Support/OpenGate/LEFT JOIN in NPR

Home Forums Support OpenGate LEFT JOIN in NPR

Viewing 1 post (of 1 total)
  • Author
    Posts
  • Alex Walker
    Moderator
    Post count: 1

    A “LEFT JOIN” allows rows from two (or more) segments to be included in a single result set even when the rows in one segment do not have a matching key in the joining segment. Here are the steps for recreating a “LEFT JOIN” in OpenGate:

    1. Create a SELECT/FROM for the parent segment
    2. Add a JOIN clause for the child segment
    3. Create a second SELECT clause to collect the fields from the child segment
    4. Add the fields from the child segment to the parent
    5. Use the TABLE keyword on the SELECT statements to create a single result
    6. Add a CHECK condition to the parent SELECT to only return rows which fail the join (i.e. exist in parent segment not child)

    1. Create a SELECT/FROM for the parent segment

    SELECT 
    ADM.BB.PRI.mnemonic AS [mnemonic],
    ADM.BB.PRI.active AS [active],
    FROM ADM.BB.PRI.main

    2. Add a JOIN clause for the child segment

    SELECT
    ADM.BB.PRI.mnemonic AS [mnemonic],
    ADM.BB.PRI.active AS [active],
    FROM ADM.BB.PRI.main
    JOIN ADM.BB.PRI.functions

    Note that the result now only includes rows which have ADM.BB.PRI.main AND ADM.BB.PRI.functions segments

    3. Create a second SELECT clause to collect the fields from the child segment

    SELECT LEVEL ADM.BB.PRI.main
    ADM.BB.PRI.mnemonic AS [mnemonic],
    ADM.BB.PRI.active AS [active],
    
    SELECT LEVEL ADM.BB.PRI.functions
    ADM.BB.PRI.mnemonic AS [mnemonic]
    ADM.BB.PRI.active as [active],
    ADM.BB.PRI.function as [function]
    
    FROM ADM.BB.PRI.main
    JOIN ADM.BB.PRI.functions

    OpenGate will now return two tables, once containing all of the ADM.BB.PRI.main instances the other all of the ADM.BB.PRI.functions instances

    4. Add the fields from the child segment to the parent

    SELECT LEVEL ADM.BB.PRI.main
    ADM.BB.PRI.mnemonic AS [mnemonic],
    ADM.BB.PRI.active AS [active],
    ADM.BB.PRI.function as [function]
    
    SELECT LEVEL ADM.BB.PRI.functions
    ADM.BB.PRI.mnemonic AS [mnemonic]
    ADM.BB.PRI.active as [active],
    ADM.BB.PRI.function as [function]
    
    FROM ADM.BB.PRI.main
    JOIN ADM.BB.PRI.functions

    5. Use the TABLE keyword on the SELECT statements to create a single result

    SELECT TABLE AdmBbPri LEVEL ADM.BB.PRI.main
    ADM.BB.PRI.mnemonic AS [mnemonic],
    ADM.BB.PRI.active AS [active],
    ADM.BB.PRI.function as [function]
    
    SELECT TABLE AdmBbPri LEVEL ADM.BB.PRI.functions
    ADM.BB.PRI.mnemonic AS [mnemonic]
    ADM.BB.PRI.active as [active],
    ADM.BB.PRI.function as [function]
    
    FROM ADM.BB.PRI.main
    JOIN ADM.BB.PRI.functions

    OpenGate will ‘union’ the results of multiple SELECT statements when the TABLE keyword and the number of columns (and datatypes) matches.

    6. Add a CHECK condition to the parent SELECT to only return rows which fail the join (i.e. exist in parent segment not child)
    The CHECK clause can be used to apply logic directly to a single SELECT clause (rather than all SELECT clauses which is what happens to the predicates in the WHERE clause).

    SELECT table bbpri level ADM.BB.PRI.main
    CHECK {+ADM.BB.PRI.functions[""]_.=.}
    ADM.BB.PRI.mnemonic AS [mnemonic],
    ADM.BB.PRI.active AS [active],
    ADM.BB.PRI.name AS [name],
    ADM.BB.PRI.function as [function]
    
    SELECT table bbpri level ADM.BB.PRI.functions
    ADM.BB.PRI.mnemonic AS [mnemonic],
    ADM.BB.PRI.active AS [active],
    ADM.BB.PRI.name AS [name],
    ADM.BB.PRI.function as [function]
    
    FROM ADM.BB.PRI.main
    JOIN ADM.BB.PRI.functions
    • This topic was modified 3 years ago by  Alex Walker.
Viewing 1 post (of 1 total)

You must be logged in to reply to this topic.

Blue Elm Eventts

December 2018
S M T W T F S
« Aug    
 1
2345678
9101112131415
16171819202122
23242526272829
3031