sql query

przytula_guy · #1 (**permalink**) 12-06-11, 05:16

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 ...

tonkuma · #2 (**permalink**) 12-06-11, 07:24

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.

przytula_guy · #3 (**permalink**) 12-06-11, 08:04

thanks for the instant answer..
it works as expected...
I knew you are a special.x and will be..

tonkuma · #4 (**permalink**) 12-06-11, 08:05

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.

tonkuma · #5 (**permalink**) 12-06-11, 18:01

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.

tonkuma · #6 (**permalink**) 12-06-11, 18:23

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.

przytula_guy · #7 (**permalink**) 12-07-11, 02:02

all these solution are very helpfull..
thanks..

colen219 · #8 (**permalink**) 12-07-11, 05:33

Thanks .przytula_guy!

Internet Services

Web Development

Digital Marketing

Consumer Tech