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

Thread: Query result adjustment?

  1. #1
    Join Date
    Nov 2000
    Posts
    198

    Query result adjustment?

    I am getting this result when runing this query

    TDY_occurances----TDY_issue----PCS_occurances----PCS_issue
    ----0-----------------null-------------2-----------------xy
    ----3-----------------xu--------------0-----------------null

    How can I make the query to display it in this format? Thanks!

    TDY_occurances----TDY_issue----PCS_occurances----PCS_issue
    ----3-----------------xu--------------2-----------------xy


    SELECT COUNT(ISSUE_CATEGORY) as TDY_occurances ,
    (select desc_text from iotv_code where code_id = issue_category and code_type in ('3')) as TDY_issue,
    0 as PCS_occurances,
    '' as PCS_issue
    FROM iotv_desk
    WHERE travel_type = 'T'
    AND system_type = nvl('',system_type)
    GROUP BY issue_category
    UNION
    SELECT 0 as tdy_occurance ,
    '' as tdy_issue,
    COUNT(ISSUE_CATEGORY) as pc_occurances ,
    (select desc_text from iotv_code where code_id = issue_category and code_type in ('4')) as PCS_issue
    FROM iotv_desk
    WHERE travel_type = 'P'
    AND system_type = nvl('',system_type)
    GROUP BY issue_category

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492
    Use the MAX() function.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Nov 2000
    Posts
    198
    I am not sure what do you mean by using MAX() !
    can you please elaborate?

    Thanks.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    How about something like this?...

    Code:
    select  max(TDY_occurances),
            max(TDY_issue),
            max(PCS_occurances),
            max(PCS_issue)
    from
    (
    SELECT COUNT(ISSUE_CATEGORY) as TDY_occurances , 
          (select desc_text  from iotv_code where code_id = issue_category and code_type in ('3')) as TDY_issue, 
           0 as PCS_occurances, 
           '' as PCS_issue 
       FROM  iotv_desk 
      WHERE   travel_type = 'T' 
        AND     system_type = nvl('',system_type) 
       GROUP BY issue_category 
    UNION
     SELECT 0 as tdy_occurance , 
           '' as tdy_issue, 
    	   COUNT(ISSUE_CATEGORY) as pc_occurances , 
          (select desc_text from iotv_code where code_id = issue_category and code_type in ('4')) as PCS_issue 
      FROM iotv_desk 
     WHERE travel_type = 'P' 
       AND    system_type = nvl('',system_type) 
      GROUP BY issue_category
    );
    Man, as days come by I feel safer and safer about keeping my job for a loooong time lol
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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