CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Caffeinated
Caffeinated

Reputation: 12480

In SQL , how do I create a query that will display this type of chart?

I'm looking for general pointers about how to do the following :

I have a chart that looks like this :

enter image description here

The following is my SQL so far , where I am using a WITH clause:

WITH WithSubquery1 AS ( 
      SELECT  AUTHORIZATION_STATUS AS "TOTAL AUTHO",  COUNT(authorization_status) AS "Requisition Lines Count",  LOCATION_CODE AS "Location",
            imcb.SEGMENT1 AS "CATEGORY"  

      FROM 
           apps.po_requisition_headers_all   prha  
                    JOIN apps.po_requisition_lines_all prla ON prla.REQUISITION_HEADER_ID = prha.REQUISITION_HEADER_ID
                        AND (prha.CANCEL_FLAG = 'N' OR prha.CANCEL_FLAG IS NULL )

           JOIN INV.MTL_CATEGORIES_B imcb  ON prla.category_id  = imcb.category_id    
           JOIN HR.PER_ALL_PEOPLE_F P ON P.person_id = prha.preparer_id  
           JOIN apps.HR_LOCATIONS ahl ON prla.deliver_to_location_id =  ahl.location_id
           JOIN apps.FND_USER  afu ON    p.person_id = afu.employee_id

      WHERE  prla.CREATION_DATE >= '13-JUN-14'    
                        AND  P.effective_start_date >= 
                        ALL (SELECT p_temp.EFFECTIVE_START_DATE
                        FROM HR.PER_ALL_PEOPLE_F p_temp
                        WHERE P.PERSON_ID = p_temp.PERSON_ID)
                         AND P.current_employee_flag = 'Y' 


      AND ahl.country IN ( 'US', 'CA', 'MX' ) /* countries in NA */ 
      AND imcb.SEGMENT1 = 'NONBOM'
      GROUP BY 
               imcb.SEGMENT1 , authorization_status, LOCATION_CODE
      ORDER BY Location_code Asc          

    ) ,

    WithSubquery2 AS 
    (
     SELECT  AUTHORIZATION_STATUS AS "APPROVED AUTHO",  COUNT(authorization_status) AS  "Requisition Lines Apprvd Count",  LOCATION_CODE AS "Location",
            imcb.SEGMENT1 AS "CATEGORY"  

      FROM 
           apps.po_requisition_headers_all   prha  
                    JOIN apps.po_requisition_lines_all prla ON prla.REQUISITION_HEADER_ID = prha.REQUISITION_HEADER_ID
                        AND (prha.CANCEL_FLAG = 'N' OR prha.CANCEL_FLAG IS NULL )

           JOIN INV.MTL_CATEGORIES_B imcb  ON prla.category_id  = imcb.category_id    
           JOIN HR.PER_ALL_PEOPLE_F P ON P.person_id = prha.preparer_id  
           JOIN apps.HR_LOCATIONS ahl ON prla.deliver_to_location_id =  ahl.location_id
           JOIN apps.FND_USER  afu ON    p.person_id = afu.employee_id

      WHERE  prla.CREATION_DATE >= '13-JUN-14'    
                        AND  P.effective_start_date >= 
                        ALL (SELECT p_temp.EFFECTIVE_START_DATE
                        FROM HR.PER_ALL_PEOPLE_F p_temp
                        WHERE P.PERSON_ID = p_temp.PERSON_ID)
                         AND P.current_employee_flag = 'Y' 
                       and AUTHORIZATION_STATUS = 'APPROVED'

      AND ahl.country IN ( 'US', 'CA', 'MX' ) /* countries in NA */ 
      AND imcb.SEGMENT1 = 'NONBOM'
      GROUP BY 
               imcb.SEGMENT1 , authorization_status, LOCATION_CODE
      ORDER BY Location_code Asc          



    )
          SELECT WithSubquery1."TOTAL AUTHO", WithSubquery1."Requisition Lines Count", WithSubquery2."APPROVED AUTHO",  WithSubquery2."Requisition Lines Apprvd Count"
          FROM 
                WithSubquery1 JOIN WithSubquery2 
           ON  
                WithSubquery1."Location"  = WithSubquery2."Location"

The problem I'm having is that I'm not sure how to generate SQL so that it has the indentation shown, with the "Ship to" location having spaces underneath it for each of the 5 subcategories ("#Requisition Lines", "#Requisition Lines Approved" etc) . The results I get so far look like this :

enter image description here

This is confusing to read , and doesn't have the hanging -indent.

any tips appreciated, thanks !

Upvotes: 0

Views: 189

Answers (1)

Matthew McPeak
Matthew McPeak

Reputation: 17924

Usually, I'd say you should worry about display issues in your front end and not in your SQL. However, you can employ this technique if you want to:

WITH d AS ( 
SELECT 'Canada' loc, 'APPROVED' status, 5 this_week FROM DUAL UNION ALL
SELECT 'Canada' loc, 'NOT APPROVED' status, 6 this_week FROM DUAL UNION ALL
SELECT 'New York' loc, 'APPROVED' status, 15 this_week FROM DUAL UNION ALL
SELECT 'Philadelphia' loc, 'APPROVED' status, 8 this_week FROM DUAL UNION ALL
SELECT 'Philadelphia' loc, 'NOT APPROVED' status, 2 this_week FROM DUAL )
SELECT case when row_number() over ( partition by loc order by status ) = 1 THEN loc else null end loc_display, status, this_week From d
-- make sure your order by matches your partition and order by 
order by loc, status;

Upvotes: 1

Related Questions