Results 1 to 4 of 4

Thread: SQL Help

  1. #1
    Join Date
    Apr 2005
    Location
    USA
    Posts
    130

    Unanswered: SQL Help

    I have SQL output like this.

    ADMISSION FULL PATIENT DATE OF
    KEY NAME ID BIRTH ALIAS
    ----------- ------------------ ------- ---------- -----------------------
    562587 ROBINSON, LORRIE 496297 01/09/1957 JOHNSON, LAURIE
    562587 ROBINSON, LORRIE 496297 01/09/1957 JOHNSON, LORRIE
    Need to get the output in one row if there is more than one alias. Is this some thing can be done in SQL? We are in z/OS V9.

    ADMISSION FULL PATIENT DATE OF
    KEY NAME ID BIRTH ALIAS1 ALIAS2
    --------- ------------------ ------- ---------- --------------- -------------
    562587 ROBINSON, LORRIE 496297 01/09/1957 JOHNSON, LAURIE JOHNSON, LORRIE
    The original SQL as follows
    SELECT
    ADMISSION.ADMISSION_KEY,
    RTRIM(RELATION.LAST_NAME) || ', ' || RTRIM(RELATION.FIRST_NAME) AS FULL_NAME,
    ADMISSION.PATIENT_ID,
    RELATION.DATE_OF_BIRTH,
    RTRIM(ALIAS.LAST_NAME) || ', ' || RTRIM(ALIAS.FIRST_NAME) AS ALIAS
    FROM IDVMB001.IT_HL7_ADMISSION AS ADMISSION,
    IDVMB001.IT_FIN_RELATION AS RELATION, IDVMB001.IT_FIN_RELATION_ALIAS AS ALIAS

    WHERE
    RELATION.RELATION_KEY = 113013
    AND RELATION.RELATION_INDICATOR = 'P'
    AND ADMISSION.ADMISSION_KEY = RELATION.ADMISSION_KEY
    AND RELATION.RELATION_KEY = ALIAS.RELATION_KEY

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Need to get the output in one row if there is more than one alias.
    If maximum number of aliases were two.

    How about this?
    Code:
    SELECT 
           ADMISSION.ADMISSION_KEY
         , RTRIM(RELATION.LAST_NAME) || ', ' || RTRIM(RELATION.FIRST_NAME) AS FULL_NAME
         , ADMISSION.PATIENT_ID
         , RELATION.DATE_OF_BIRTH
         , MIN( RTRIM(ALIAS.LAST_NAME) || ', ' || RTRIM(ALIAS.FIRST_NAME) ) AS ALIAS1
         , NULLIF(
              MAX( RTRIM(ALIAS.LAST_NAME) || ', ' || RTRIM(ALIAS.FIRST_NAME) )
            , MIN( RTRIM(ALIAS.LAST_NAME) || ', ' || RTRIM(ALIAS.FIRST_NAME) )
           ) AS ALIAS2
     FROM  IDVMB001.IT_HL7_ADMISSION      AS ADMISSION
         , IDVMB001.IT_FIN_RELATION       AS RELATION
         , IDVMB001.IT_FIN_RELATION_ALIAS AS ALIAS
     WHERE 
           RELATION.RELATION_KEY       = 113013
       AND RELATION.RELATION_INDICATOR = 'P'
       AND ADMISSION.ADMISSION_KEY     = RELATION.ADMISSION_KEY
       AND RELATION.RELATION_KEY       = ALIAS.RELATION_KEY
     GROUP BY
           ADMISSION.ADMISSION_KEY
         , RELATION.LAST_NAME
         , RELATION.FIRST_NAME
         , ADMISSION.PATIENT_ID
         , RELATION.DATE_OF_BIRTH
    ;

    If maximum number of aliases were more than two.
    Try to apply string concatenation technique using XMLAGG function rather than the technique using MIN, MAX and NULLIF .

  3. #3
    Join Date
    Apr 2005
    Location
    USA
    Posts
    130
    Thank you so much.

  4. #4
    Join Date
    Apr 2005
    Location
    USA
    Posts
    130
    Thank you tonkuma for the clue. This is my final sql.

    SELECT
    ADMISSION.ADMISSION_KEY
    , RTRIM(RELATION.LAST_NAME) || ', ' || RTRIM(RELATION.FIRST_NAME)
    AS FULL_NAME
    , REPLACE(REPLACE(CAST(XML2CLOB(XMLAGG(
    XMLELEMENT(
    NAME "X",RTRIM(ALIAS.LAST_NAME) || ', ' || RTRIM(ALIAS.FIRST_NAME))))
    AS VARCHAR(1000)) ,'<X>',''),'</X>',';')


    FROM IDVMB001.IT_HL7_ADMISSION AS ADMISSION
    , IDVMB001.IT_FIN_RELATION AS RELATION
    , IDVMB001.IT_FIN_RELATION_ALIAS AS ALIAS
    WHERE
    RELATION.RELATION_INDICATOR = 'P'
    AND ADMISSION.ADMISSION_KEY = RELATION.ADMISSION_KEY
    AND RELATION.RELATION_KEY = ALIAS.RELATION_KEY
    GROUP BY
    ADMISSION.ADMISSION_KEY
    , RELATION.LAST_NAME
    , RELATION.FIRST_NAME
    ;

Posting Permissions

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