Results 1 to 8 of 8

Thread: sql query

  1. #1
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11

    Unanswered: sql query

    I am not a great sql specialist and hope Tonkuma from Japan looks at this,
    because I know he is the special.x in this area
    I have 3 tables as described below

    db2 describe table CBSSCMDB.ARTIFACT

    Data type Column
    Column name schema Data type name Length Scale Nulls
    ------------------------------- --------- ------------------- ---------- ----- ------
    ID SYSIBM INTEGER 4 0 No
    ARTIFACTID SYSIBM CHARACTER 64 0 No
    GROUPID SYSIBM CHARACTER 64 0 Yes
    APPLICATIONID SYSIBM INTEGER 4 0 No
    ARTIFACTTYPEID SYSIBM INTEGER 4 0 Yes



    (0)[db2inst1@dlx00005 ~]$ db2 describe table CBSSCMDB.SERVICE

    Data type Column
    Column name schema Data type name Length Scale Nulls
    ------------------------------- --------- ------------------- ---------- ----- ------
    ID SYSIBM INTEGER 4 0 No
    NAME SYSIBM CHARACTER 32 0 No
    SERVICETYPE SYSIBM CHARACTER 16 0 No
    URL_TEST SYSIBM CHARACTER 180 0 Yes
    URL_ACC SYSIBM CHARACTER 180 0 Yes
    URL_PROD SYSIBM CHARACTER 180 0 Yes
    SLA SYSIBM CHARACTER 16 0 Yes
    EXTERNAL_CONTACT SYSIBM CHARACTER 64 0 Yes

    (0)[db2inst1@dlx00005 ~]$ db2 describe table CBSSCMDB.ARTIFACT_REL_SERVICE

    Data type Column
    Column name schema Data type name Length Scale Nulls
    ------------------------------- --------- ------------------- ---------- ----- ------
    ARTIFACTID SYSIBM INTEGER 4 0 No
    SERVICEID SYSIBM INTEGER 4 0 No
    RELTYPE SYSIBM CHARACTER 16 0 No

    I execute a query like this with needed joins

    (0)[db2inst1@dlx00005 ~]$ db2 "SELECT A.ARTIFACTID, B.NAME, C.RELTYPE FROM CBSSCMDB.ARTIFACT A, CBSSCMDB.SERVICE B, CBSSCMDB.ARTIFACT_REL_SERVICE C WHERE A.ID=C.ARTIFACTID AND B.ID=C.SERVICEID"

    and get this result
    (0)[db2inst1@dlx00005 ~]$ db2 "SELECT A.ARTIFACTID, B.NAME, C.RELTYPE FROM CBSSCMDB.ARTIFACT A, CBSSCMDB.SERVICE B, CBSSCMDB.ARTIFACT_REL_SERVICE C WHERE A.ID=C.ARTIFACTID AND B.ID=C.SERVICEID"

    ARTIFACTID NAME RELTYPE
    ---------------------------------------------------------------- -------------------------------- ----------------
    LotMessageDmzEar AliveCheck uses
    VierdeWegEnterprise IdentifyPerson uses
    AliveCheckEnterprise AliveCheck provides

    the problem is now they want an additional col that contains data from a depended row : like
    they want AliveCheckEnterprise (artifactid) from provides row in uses row
    where name is identical

    ARTIFACTID NAME RELTYPE uses
    ---------------------- ------------------------ -------------
    LotMessageDmzEar AliveCheck uses AliveCheckEnterprise
    VierdeWegEnterprise IdentifyPerson uses NULL
    AliveCheckEnterprise AliveCheck provides NULL


    I don't know if this possible, but if anyone has an idea
    I tried this
    SELECT ARTIFACTID, serviceNAME, ( select y.ARTIFACTID FROM CBSSCMDB.V_ARTIFACT_SERVICES x,CBSSCMDB.V_ARTIFACT_SERVICES y where y.reltype='provides' and x.serviceNAME=y.serviceNAME and x.reltype='uses' ) as xx , RELTYPE FROM CBSSCMDB.V_ARTIFACT_SERVICES
    but this value is returned for each row. I will try with case ...
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    It is better to provide sample data of three tables.

    Note: If traditional join syntax and new join syntax were mixed,
    you can't reference table names(and correlation names) across comma(',' : traditional inner join) in ON conditions.
    So, it is better to use only new join syntax for all joins including inner joins.

    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     ARTIFACT(ID , ARTIFACTID) AS (
    VALUES
      ( 1 , 'LotMessageDmzEar'     )
    , ( 2 , 'VierdeWegEnterprise'  )
    , ( 3 , 'AliveCheckEnterprise' )
    )
    , SERVICE(ID , NAME) AS (
    VALUES
      ( 7 , 'AliveCheck'     )
    , ( 8 , 'IdentifyPerson' )
    )
    , ARTIFACT_REL_SERVICE(ARTIFACTID , SERVICEID , RELTYPE) AS (
    VALUES
      ( 1 , 7 , 'uses'     )
    , ( 2 , 8 , 'uses'     )
    , ( 3 , 7 , 'provides' )
    )
    SELECT A.ARTIFACTID
         , B.NAME
         , C.RELTYPE
         , A_dep.ARTIFACTID AS uses
     FROM  ARTIFACT              A
     INNER JOIN
           SERVICE               B
     INNER JOIN
           ARTIFACT_REL_SERVICE  C
       ON  B    .ID        = C    .SERVICEID
       ON  A    .ID        = C    .ARTIFACTID
     LEFT  OUTER JOIN
           SERVICE               B_dep
       ON  C    .RELTYPE   =      'uses'
       AND B_dep.NAME      = B    .NAME
     LEFT  OUTER JOIN
           ARTIFACT_REL_SERVICE  C_dep
       ON  C_dep.RELTYPE   =      'provides'
       AND C_dep.SERVICEID = B_dep.ID
     LEFT  OUTER JOIN
           ARTIFACT              A_dep
       ON  A_dep.ID        = C_dep.ARTIFACTID
    ;
    ------------------------------------------------------------------------------
    
    ARTIFACTID           NAME           RELTYPE  USES                
    -------------------- -------------- -------- --------------------
    LotMessageDmzEar     AliveCheck     uses     AliveCheckEnterprise
    VierdeWegEnterprise  IdentifyPerson uses     -                   
    AliveCheckEnterprise AliveCheck     provides -                   
    
      3 record(s) selected.
    Last edited by tonkuma; 12-06-11 at 08:33. Reason: Change position of "only" in "So, it is better to use only new join syntax for all joins including inner joins."

  3. #3
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    thanks for the instant answer..
    it works as expected...
    I knew you are a special.x and will be..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Two variations.

    Example 2: use more inner join.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     ARTIFACT
    ( ID , ARTIFACTID ) AS (
    VALUES
      ( 1 , 'LotMessageDmzEar'     )
    , ( 2 , 'VierdeWegEnterprise'  )
    , ( 3 , 'AliveCheckEnterprise' )
    )
    , SERVICE
    ( ID , NAME ) AS (
    VALUES
      ( 7 , 'AliveCheck'     )
    , ( 8 , 'IdentifyPerson' )
    )
    , ARTIFACT_REL_SERVICE
    ( ARTIFACTID , SERVICEID , RELTYPE ) AS (
    VALUES
      ( 1 , 7 , 'uses'     )
    , ( 2 , 8 , 'uses'     )
    , ( 3 , 7 , 'provides' )
    )
    SELECT A.ARTIFACTID
         , B.NAME
         , C.RELTYPE
         , A_dep.ARTIFACTID AS uses
     FROM  ARTIFACT                A
     INNER JOIN
           SERVICE                 B
     INNER JOIN
           ARTIFACT_REL_SERVICE    C
       ON  B    .ID        = C    .SERVICEID
       ON  A    .ID        = C    .ARTIFACTID
     LEFT  OUTER JOIN
           SERVICE                 B_dep
     INNER JOIN
           ARTIFACT_REL_SERVICE    C_dep
       ON  C_dep.RELTYPE   =      'provides'
       AND C_dep.SERVICEID = B_dep.ID
     INNER JOIN
           ARTIFACT                A_dep
       ON  A_dep.ID        = C_dep.ARTIFACTID
       ON  C    .RELTYPE   =      'uses'
       AND B_dep.NAME      = B    .NAME
    ;
    ------------------------------------------------------------------------------
    
    ARTIFACTID           NAME           RELTYPE  USES                
    -------------------- -------------- -------- --------------------
    LotMessageDmzEar     AliveCheck     uses     AliveCheckEnterprise
    VierdeWegEnterprise  IdentifyPerson uses     -                   
    AliveCheckEnterprise AliveCheck     provides -                   
    
      3 record(s) selected.

    Example 3: use scalar subselect.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     ARTIFACT
    ( ID , ARTIFACTID ) AS (
    VALUES
      ( 1 , 'LotMessageDmzEar'     )
    , ( 2 , 'VierdeWegEnterprise'  )
    , ( 3 , 'AliveCheckEnterprise' )
    )
    , SERVICE
    ( ID , NAME ) AS (
    VALUES
      ( 7 , 'AliveCheck'     )
    , ( 8 , 'IdentifyPerson' )
    )
    , ARTIFACT_REL_SERVICE
    ( ARTIFACTID , SERVICEID , RELTYPE ) AS (
    VALUES
      ( 1 , 7 , 'uses'     )
    , ( 2 , 8 , 'uses'     )
    , ( 3 , 7 , 'provides' )
    )
    SELECT A.ARTIFACTID
         , B.NAME
         , C.RELTYPE
         , (SELECT A_dep.ARTIFACTID
             FROM
                   SERVICE                 B_dep
             INNER JOIN
                   ARTIFACT_REL_SERVICE    C_dep
               ON  C_dep.RELTYPE   =      'provides'
               AND C_dep.SERVICEID = B_dep.ID
             INNER JOIN
                   ARTIFACT                A_dep
               ON  A_dep.ID        = C_dep.ARTIFACTID
             WHERE C    .RELTYPE   =      'uses'
               AND B_dep.NAME      = B    .NAME
           ) AS uses
     FROM  ARTIFACT                A
     INNER JOIN
           SERVICE                 B
     INNER JOIN
           ARTIFACT_REL_SERVICE    C
       ON  B    .ID        = C    .SERVICEID
       ON  A    .ID        = C    .ARTIFACTID
    ;
    ------------------------------------------------------------------------------
    
    ARTIFACTID           NAME           RELTYPE  USES                
    -------------------- -------------- -------- --------------------
    LotMessageDmzEar     AliveCheck     uses     AliveCheckEnterprise
    VierdeWegEnterprise  IdentifyPerson uses     -                   
    AliveCheckEnterprise AliveCheck     provides -                   
    
      3 record(s) selected.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Some more examples.
    Example 6 assumed that values of RELTYPE are only two values('uses' and 'provides').

    Example 4: used common-table-expression(like CBSSCMDB.V_ARTIFACT_SERVICES in OP) and self join it.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    /****************************************
    ******** test data were omitted. ********
    ****************************************/
     ARTIFACT_SERVICES AS (
    SELECT A.ARTIFACTID
         , B.NAME
         , C.RELTYPE
     FROM  ARTIFACT              A
     INNER JOIN
           SERVICE               B
     INNER JOIN
           ARTIFACT_REL_SERVICE  C
       ON  B.ID = C.SERVICEID
       ON  A.ID = C.ARTIFACTID
    )
    SELECT x.ARTIFACTID
         , x.NAME
         , x.RELTYPE
         , y.ARTIFACTID AS uses
     FROM  ARTIFACT_SERVICES x
     LEFT  OUTER JOIN
           ARTIFACT_SERVICES y
       ON  x.reltype = 'uses'
       AND y.reltype = 'provides'
       AND y.NAME    = x.NAME
    ;
    ------------------------------------------------------------------------------
    
    ARTIFACTID           NAME           RELTYPE  USES                
    -------------------- -------------- -------- --------------------
    LotMessageDmzEar     AliveCheck     uses     AliveCheckEnterprise
    VierdeWegEnterprise  IdentifyPerson uses     -                   
    AliveCheckEnterprise AliveCheck     provides -                   
    
      3 record(s) selected.

    Example 5: used common-table-expression and scalar-subselect.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    /****************************************
    ******** test data were omitted. ********
    ****************************************/
     ARTIFACT_SERVICES AS (
    SELECT A.ARTIFACTID
         , B.NAME
         , C.RELTYPE
     FROM  ARTIFACT              A
     INNER JOIN
           SERVICE               B
     INNER JOIN
           ARTIFACT_REL_SERVICE  C
       ON  B.ID = C.SERVICEID
       ON  A.ID = C.ARTIFACTID
    )
    SELECT ARTIFACTID
         , NAME
         , RELTYPE
         , (SELECT ARTIFACTID
             FROM  ARTIFACT_SERVICES y
             WHERE x.reltype = 'uses'
               AND y.reltype = 'provides'
               AND y.NAME    = x.NAME
           ) AS uses
     FROM  ARTIFACT_SERVICES x
    ;
    ------------------------------------------------------------------------------
    
    ARTIFACTID           NAME           RELTYPE  USES                
    -------------------- -------------- -------- --------------------
    LotMessageDmzEar     AliveCheck     uses     AliveCheckEnterprise
    VierdeWegEnterprise  IdentifyPerson uses     -                   
    AliveCheckEnterprise AliveCheck     provides -                   
    
      3 record(s) selected.

    Example 6: assumed that values of RELTYPE are only two values('uses' and 'provides').
    Code:
    ------------------------------ Commands Entered ------------------------------
    /****************************************
    WITH
    ******** test data were omitted. ********
    ****************************************/
    SELECT A.ARTIFACTID
         , B.NAME
         , C.RELTYPE
         , LAG(A.ARTIFACTID)
              OVER( PARTITION BY NAME
                        ORDER BY RELTYPE ) AS uses
     FROM  ARTIFACT              A
     INNER JOIN
           SERVICE               B
     INNER JOIN
           ARTIFACT_REL_SERVICE  C
       ON  B.ID = C.SERVICEID
       ON  A.ID = C.ARTIFACTID
     ORDER BY
           A.ID
    ;
    ------------------------------------------------------------------------------
    
    ARTIFACTID           NAME           RELTYPE  USES                
    -------------------- -------------- -------- --------------------
    LotMessageDmzEar     AliveCheck     uses     AliveCheckEnterprise
    VierdeWegEnterprise  IdentifyPerson uses     -                   
    AliveCheckEnterprise AliveCheck     provides -                   
    
      3 record(s) selected.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If there were more RELTYPE,
    try...

    Example 7: test data was added.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    /****************************************
    ********** Begin of test data. **********
    ****************************************/
     ARTIFACT
    ( ID , ARTIFACTID ) AS (
    VALUES
      ( 1 , 'LotMessageDmzEar'     )
    , ( 2 , 'VierdeWegEnterprise'  )
    , ( 3 , 'AliveCheckEnterprise' )
    , ( 4 , 'SupportEnterprise'    ) /* added */
    ) ,
     SERVICE
    ( ID , NAME ) AS (
    VALUES
      ( 7 , 'AliveCheck'     )
    , ( 8 , 'IdentifyPerson' )
    ) ,
     ARTIFACT_REL_SERVICE
    ( ARTIFACTID , SERVICEID , RELTYPE ) AS (
    VALUES
      ( 1 , 7 , 'uses'     )
    , ( 2 , 8 , 'uses'     )
    , ( 3 , 7 , 'provides' )
    , ( 4 , 7 , 'supports' ) /* added */
    )
    /****************************************
    **********   End of test data. **********
    ****************************************/
    SELECT A.ARTIFACTID
         , B.NAME
         , C.RELTYPE
         , LAG( CASE RELTYPE WHEN 'provides' THEN A.ARTIFACTID END )
              OVER( PARTITION BY NAME
                        ORDER BY CASE /*RELTYPE*/
                                 WHEN RELTYPE IN('uses' , 'provides') THEN
                                      RELTYPE
                                 END  NULLS FIRST
                  ) AS uses
     FROM  ARTIFACT              A
     INNER JOIN
           SERVICE               B
     INNER JOIN
           ARTIFACT_REL_SERVICE  C
       ON  B.ID = C.SERVICEID
       ON  A.ID = C.ARTIFACTID
     ORDER BY
           A.ID
    ;
    ------------------------------------------------------------------------------
    
    ARTIFACTID           NAME           RELTYPE  USES                
    -------------------- -------------- -------- --------------------
    LotMessageDmzEar     AliveCheck     uses     AliveCheckEnterprise
    VierdeWegEnterprise  IdentifyPerson uses     -                   
    AliveCheckEnterprise AliveCheck     provides -                   
    SupportEnterprise    AliveCheck     supports -                   
    
      4 record(s) selected.
    Last edited by tonkuma; 12-06-11 at 19:55. Reason: Add "NULLS FIRST"

  7. #7
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    all these solution are very helpfull..
    thanks..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  8. #8
    Join Date
    Nov 2011
    Posts
    1
    Thanks .przytula_guy!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •