DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Query question

  1. #1
    Join Date
    Mar 2001
    Posts
    63
    OK, this should be an easy one, I don't know why I can't figure it out though.

    I have:
    SELECT
    PERFMR.PROJ_ID
    , DECODE(PERFMR.ROLE_TYPE_CD, 'TYPE_DESK', PERSON.LAST_NM, NULL) TD
    , DECODE(PERFMR.ROLE_TYPE_CD, 'SPECIFICATION_WRITER', PERSON.LAST_NM, NULL) SW
    , DECODE(PERFMR.ROLE_TYPE_CD, 'CONTRACTS_ANALYST', PERSON.LAST_NM, NULL) CA
    FROM
    PERFMR
    , PERSON
    WHERE
    PERFMR.PERSON_ID = PERSON.PERSON_ID
    and PERFMR.END_DT Is Null
    AND PERFMR.ROLE_TYPE_CD IN ('TYPE_DESK','SPECIFICATION_WRITER','CONTRACTS_ANALYST')
    group BY PERFMR.PROJ_ID
    , DECODE(PERFMR.ROLE_TYPE_CD, 'TYPE_DESK', PERSON.LAST_NM, NULL)
    , DECODE(PERFMR.ROLE_TYPE_CD, 'SPECIFICATION_WRITER', PERSON.LAST_NM, NULL) SW
    , DECODE(PERFMR.ROLE_TYPE_CD, 'CONTRACTS_ANALYST', PERSON.LAST_NM, NULL) CA


    I want the results to look like:

    1 a b c
    2 a b c
    3 a b c

    etc...

    instead, I get:
    1 a - -
    1 - b -
    1 - - c
    2 a - -
    2 - b -
    2 - - c
    3 a - -
    3 - b -
    3 - - c

    (no, I don't get dashes, it's for the purpose of spacing).

    Anyway, I know this can be done, if anyone has any insights, it would be certainly welcomed.


  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    If I'm not missing your point, you can try it like this:

    SELECT
    PERFMR.PROJ_ID
    , MAX(DECODE(PERFMR.ROLE_TYPE_CD, 'TYPE_DESK', PERSON.LAST_NM, NULL)) TD
    , MAX(DECODE(PERFMR.ROLE_TYPE_CD, 'SPECIFICATION_WRITER', PERSON.LAST_NM, NULL)) SW
    , MAX(DECODE(PERFMR.ROLE_TYPE_CD, 'CONTRACTS_ANALYST', PERSON.LAST_NM, NULL)) CA
    FROM
    PERFMR
    , PERSON
    WHERE
    PERFMR.PERSON_ID = PERSON.PERSON_ID
    and PERFMR.END_DT Is Null
    AND PERFMR.ROLE_TYPE_CD IN ('TYPE_DESK','SPECIFICATION_WRITER','CONTRACTS_ANALYST')
    group BY PERFMR.PROJ_ID;

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Mar 2001
    Posts
    63
    Perfect!

    Thanks

Posting Permissions

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


Click Here to Expand Forum to Full Width