Page 2 of 2 FirstFirst 12
Results 16 to 17 of 17
  1. #16
    Join Date
    Oct 2017
    Posts
    12
    Actually I am using IBM JRS for reporting and in which automatically create query as SQL

  2. #17
    Join Date
    Oct 2017
    Posts
    12
    Hi Mark,
    This my exact SQL code ,

    select
    a.ref1 as VAL1,
    a.ref2 as VAL2,
    a.ref3 as VAL3

    from

    (SELECT DISTINCT T1.PROJECT_NAME,
    T1.REFERENCE_ID AS REF,
    T1.NAME AS URL1_title,
    T1.URL AS URL1,
    T1.REQUEST_TYPE,

    T2.VAL AS owner1,

    T2.VAL ||'_'|| cast(T3.VAL AS CHAR(10)) AS ref1 ,
    T4.VAL AS owner2,
    T4.VAL ||'_'|| cast(T5.VAL AS CHAR(10)) AS ref2 ,
    T6.VAL AS owner3,
    T6.VAL ||'_'|| cast(T7.VAL AS CHAR(10)) AS ref3
    FROM RIDW.VW_REQUEST T1
    LEFT OUTER JOIN RICALM.VW_RQST_STRING_EXT T2
    ON T2.REQUEST_ID=T1.REQUEST_ID AND T2.NAME='owner_1'
    LEFT OUTER JOIN RICALM.VW_RQST_DECIMAL_EXT T3
    ON T3.REQUEST_ID=T1.REQUEST_ID AND T3.NAME='resource1'
    LEFT OUTER JOIN RICALM.VW_RQST_STRING_EXT T4
    ON T4.REQUEST_ID=T1.REQUEST_ID AND T4.NAME='owner_2'
    LEFT OUTER JOIN RICALM.VW_RQST_DECIMAL_EXT T5
    ON T5.REQUEST_ID=T1.REQUEST_ID AND T5.NAME='resource2'
    LEFT OUTER JOIN RICALM.VW_RQST_STRING_EXT T6
    ON T6.REQUEST_ID=T1.REQUEST_ID AND T6.NAME='owner_3'
    LEFT OUTER JOIN RICALM.VW_RQST_DECIMAL_EXT T7
    ON T7.REQUEST_ID=T1.REQUEST_ID AND T7.NAME='resource'
    WHERE T1.PROJECT_ID = 0 AND
    ( T1.REQUEST_TYPE = 'SMIT-Task'
    ) AND
    (T1.ISSOFTDELETED = 0) AND
    (T1.REQUEST_ID <> -1 AND T1.REQUEST_ID IS NOT NULL) ORDER BY T2.VAL asc,
    T4.VAL asc,
    T6.VAL asc)a
    here is output image
    Click image for larger version. 

Name:	te.png 
Views:	3 
Size:	9.2 KB 
ID:	17436
    Last edited by amrutaraut; 10-05-17 at 08:14.

Tags for this Thread

Posting Permissions

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