Announcement

Collapse
No announcement yet.

Combining results from 2 tables into one table

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Dennis See
    replied
    Hi Bryn,
    I don't think you can extract multiple rows of info, into multiple columns of info (ie USRPF#1, USPRF#2,... columns). However, to combine the tables, the statement below would do the job, with the addition of the WITH common-table-expression prefix. However, it isn't efficient (probably to do with indexes) when I tried it as it took a long time to run and I didn't wait for completion. I suppose you need to add a "CREATE TABLE XXX AS " statement (or INSERT statement) to save the data in a batch run, as it would take a long time to run.
    WITH TEMPFILE AS (
    SELECT ATTACHED_JOURNAL_RECEIVER_NAME AS JRNRCV,
    ATTRCVLIB AS JRNRCVLIB FROM QSYS2.JOURNAL_INFO
    WHERE JOURNAL_LIBRARY NOT LIKE 'Q%'
    )
    SELECT SYSTEM_OBJECT_NAME AS JRNRCV,
    SYSTEM_OBJECT_SCHEMA AS JRNRCVLIB,
    OBJECT_TYPE AS OBJTYPE,
    OWNER AS OWNER,
    AUTHORIZATION_NAME AS USRPRF,
    OBJECT_AUTHORITY AS OBJ_AUTH
    FROM QSYS2.OBJECT_PRIVILEGES INNER JOIN TEMPFILE
    ON TEMPFILE.JRNRCVLIB = SYSTEM_OBJECT_SCHEMA
    AND TEMPFILE.JRNRCV = SYSTEM_OBJECT_NAME
    ORDER BY SYSTEM_OBJECT_NAME

    Leave a comment:


  • bryn_griffith
    started a topic Combining results from 2 tables into one table

    Combining results from 2 tables into one table

    Hi all

    Hoping someone can help me with this ... fingers crossed.

    I'm trying to create a table which contains the following information:
    1. Name and library of currently attached journal receivers
    2. Object authorities and object owner for the currently attached journal receivers

    I can get the information for step 1 by doing the following:
    SELECT ATTACHED_JOURNAL_RECEIVER_NAME AS JRNRCV,ATTRCVLIB AS JRNRCVLIB FROM QSYS2.JOURNAL_INFO WHERE JOURNAL_LIBRARY NOT LIKE 'Q%'
    ORDER BY ATTACHED_JOURNAL_RECEIVER_NAME

    What I'd then like to do is retrieve the object authority and owner details for the currently attached journal receivers and combine them into one table with the results from step 1.
    I know this information is in QSYS2.OBJECT_PRIVILEGES, the following statement is an example of what I've used to identify this info:
    SELECT SYSTEM_OBJECT_NAME AS JRNRCV,SYSTEM_OBJECT_SCHEMA AS JRNRCVLIB,OBJECT_TYPE AS OBJTYPE,OWNER AS OWNER,AUTHORIZATION_NAME AS USRPRF,OBJECT_AUTHORITY AS OBJ_AUTH
    FROM QSYS2.OBJECT_PRIVILEGES WHERE SYSTEM_OBJECT_NAME = 'JRNRCV1' OR SYSTEM_OBJECT_NAME = 'JRNRCV2' OR SYSTEM_OBJECT_NAME = 'JRNRCV3'
    ORDER BY SYSTEM_OBJECT_NAME

    I’ve specified the (made up) name of some attached receivers into the above statement to create an example, but this isn't something I want to do on a permanent basis.

    Ideally, I’d like the table from the combined results to look like this – if possible:
    JRNRCV JRNRCVLIB OBJTYPE OWNER USRPRF#1 USRPRF#1 OBJ_AUTH USRPRF#2 USRPRF#2 OBJ_AUTH USRPRF#3 USRPRF#3 OBJ_AUTH
    JRNRCV1 RCVLIB1 *JRNRCV OWNER1 OWNER1 *ALL USER1 *USE *PUBLIC *EXCLUDE
    JRNRCV2 RCVLIB2 *JRNRCV OWNER2 OWNER2 *ALL USER2 *USE *PUBLIC *EXCLUDE
    JRNRCV3 RCVLIB3 *JRNRCV OWNER3 OWNER3 *ALL USER3 *USE *PUBLIC *EXCLUDE
    Any help is greatly appreciated as this is something I need to make repeatable as we have 40+ LPARs to administer.

    Thanks

    Bryn

    P.S. I'm open to any suggestions that will help me achieve my goal of one table containing the attached receivers and their associated authorities - I won't be offended if someone rips up the above suggestion and replaces it with something entirely different :-)
Working...
X