Group by Rollup

I can’t remember how I came across this, but it’s useful as it can be used to count “things” across months and years, and tally them up across years at the end of each row, and via months, at the bottom of each row.

-- ##############################################################################
--   PROJECTS COUNT SETUP
-- ############################################################################*/

SELECT
NVL(TO_CHAR(extract(YEAR FROM creation_date)),'TOTAL') CREATION_YEAR,
SUM(DECODE(extract (MONTH FROM creation_date),1,1,0)) JAN,
SUM(DECODE(extract (MONTH FROM creation_date),2,1,0)) FEB,
SUM(DECODE(extract (MONTH FROM creation_date),3,1,0)) MAR,
SUM(DECODE(extract (MONTH FROM creation_date),4,1,0)) APR,
SUM(DECODE(extract (MONTH FROM creation_date),5,1,0)) MAY,
SUM(DECODE(extract (MONTH FROM creation_date),6,1,0)) JUN,
SUM(DECODE(extract (MONTH FROM creation_date),7,1,0)) JUL,
SUM(DECODE(extract (MONTH FROM creation_date),8,1,0)) AUG,
SUM(DECODE(extract (MONTH FROM creation_date),9,1,0)) SEP,
SUM(DECODE(extract (MONTH FROM creation_date),10,1,0)) OCT,
SUM(DECODE(extract (MONTH FROM creation_date),11,1,0)) NOV,
SUM(DECODE(extract (MONTH FROM creation_date),12,1,0)) DEC,
SUM(1) total
FROM pa.pa_projects_all
GROUP BY rollup(extract(YEAR FROM creation_date));


-- ##############################################################################
--   iSupplier COUNT SETUP
-- ############################################################################*/

SELECT
NVL(TO_CHAR(extract(YEAR FROM creation_date)),'TOTAL') CREATION_YEAR,
SUM(DECODE(extract (MONTH FROM creation_date),1,1,0)) JAN,
SUM(DECODE(extract (MONTH FROM creation_date),2,1,0)) FEB,
SUM(DECODE(extract (MONTH FROM creation_date),3,1,0)) MAR,
SUM(DECODE(extract (MONTH FROM creation_date),4,1,0)) APR,
SUM(DECODE(extract (MONTH FROM creation_date),5,1,0)) MAY,
SUM(DECODE(extract (MONTH FROM creation_date),6,1,0)) JUN,
SUM(DECODE(extract (MONTH FROM creation_date),7,1,0)) JUL,
SUM(DECODE(extract (MONTH FROM creation_date),8,1,0)) AUG,
SUM(DECODE(extract (MONTH FROM creation_date),9,1,0)) SEP,
SUM(DECODE(extract (MONTH FROM creation_date),10,1,0)) OCT,
SUM(DECODE(extract (MONTH FROM creation_date),11,1,0)) NOV,
SUM(DECODE(extract (MONTH FROM creation_date),12,1,0)) DEC,
SUM(1) total
FROM applsys.fnd_registrations
GROUP BY rollup(extract(YEAR FROM creation_date));

-- ##############################################################################
--   STAFF COUNT SETUP
-- ############################################################################*/

SELECT
NVL(TO_CHAR(extract(YEAR FROM creation_date)),'TOTAL') CREATION_YEAR,
SUM(DECODE(extract (MONTH FROM creation_date),1,1,0)) JAN,
SUM(DECODE(extract (MONTH FROM creation_date),2,1,0)) FEB,
SUM(DECODE(extract (MONTH FROM creation_date),3,1,0)) MAR,
SUM(DECODE(extract (MONTH FROM creation_date),4,1,0)) APR,
SUM(DECODE(extract (MONTH FROM creation_date),5,1,0)) MAY,
SUM(DECODE(extract (MONTH FROM creation_date),6,1,0)) JUN,
SUM(DECODE(extract (MONTH FROM creation_date),7,1,0)) JUL,
SUM(DECODE(extract (MONTH FROM creation_date),8,1,0)) AUG,
SUM(DECODE(extract (MONTH FROM creation_date),9,1,0)) SEP,
SUM(DECODE(extract (MONTH FROM creation_date),10,1,0)) OCT,
SUM(DECODE(extract (MONTH FROM creation_date),11,1,0)) NOV,
SUM(DECODE(extract (MONTH FROM creation_date),12,1,0)) DEC,
SUM(1) total
FROM applsys.fnd_user
GROUP BY rollup(extract(YEAR FROM creation_date));

-- ##############################################################################
--   PO COUNT
-- ############################################################################*/

SELECT
NVL(TO_CHAR(extract(YEAR FROM creation_date)),'TOTAL') CREATION_YEAR,
SUM(DECODE(extract (MONTH FROM creation_date),1,1,0)) JAN,
SUM(DECODE(extract (MONTH FROM creation_date),2,1,0)) FEB,
SUM(DECODE(extract (MONTH FROM creation_date),3,1,0)) MAR,
SUM(DECODE(extract (MONTH FROM creation_date),4,1,0)) APR,
SUM(DECODE(extract (MONTH FROM creation_date),5,1,0)) MAY,
SUM(DECODE(extract (MONTH FROM creation_date),6,1,0)) JUN,
SUM(DECODE(extract (MONTH FROM creation_date),7,1,0)) JUL,
SUM(DECODE(extract (MONTH FROM creation_date),8,1,0)) AUG,
SUM(DECODE(extract (MONTH FROM creation_date),9,1,0)) SEP,
SUM(DECODE(extract (MONTH FROM creation_date),10,1,0)) OCT,
SUM(DECODE(extract (MONTH FROM creation_date),11,1,0)) NOV,
SUM(DECODE(extract (MONTH FROM creation_date),12,1,0)) DEC,
SUM(1) total
FROM po.po_headers_all pha
where pha.TYPE_LOOKUP_CODE = 'STANDARD'
GROUP BY rollup(extract(YEAR FROM creation_date));

-- ##############################################################################
--   REQUISITION COUNT
-- ############################################################################*/

SELECT
NVL(TO_CHAR(extract(YEAR FROM creation_date)),'TOTAL') CREATION_YEAR,
SUM(DECODE(extract (MONTH FROM creation_date),1,1,0)) JAN,
SUM(DECODE(extract (MONTH FROM creation_date),2,1,0)) FEB,
SUM(DECODE(extract (MONTH FROM creation_date),3,1,0)) MAR,
SUM(DECODE(extract (MONTH FROM creation_date),4,1,0)) APR,
SUM(DECODE(extract (MONTH FROM creation_date),5,1,0)) MAY,
SUM(DECODE(extract (MONTH FROM creation_date),6,1,0)) JUN,
SUM(DECODE(extract (MONTH FROM creation_date),7,1,0)) JUL,
SUM(DECODE(extract (MONTH FROM creation_date),8,1,0)) AUG,
SUM(DECODE(extract (MONTH FROM creation_date),9,1,0)) SEP,
SUM(DECODE(extract (MONTH FROM creation_date),10,1,0)) OCT,
SUM(DECODE(extract (MONTH FROM creation_date),11,1,0)) NOV,
SUM(DECODE(extract (MONTH FROM creation_date),12,1,0)) DEC,
SUM(1) total
FROM po.po_requisition_headers_all
GROUP BY rollup(extract(YEAR FROM creation_date));

-- ##############################################################################
--   AP INVOICE COUNT
-- ############################################################################*/

SELECT
NVL(TO_CHAR(extract(YEAR FROM creation_date)),'TOTAL') CREATION_YEAR,
SUM(DECODE(extract (MONTH FROM creation_date),1,1,0)) JAN,
SUM(DECODE(extract (MONTH FROM creation_date),2,1,0)) FEB,
SUM(DECODE(extract (MONTH FROM creation_date),3,1,0)) MAR,
SUM(DECODE(extract (MONTH FROM creation_date),4,1,0)) APR,
SUM(DECODE(extract (MONTH FROM creation_date),5,1,0)) MAY,
SUM(DECODE(extract (MONTH FROM creation_date),6,1,0)) JUN,
SUM(DECODE(extract (MONTH FROM creation_date),7,1,0)) JUL,
SUM(DECODE(extract (MONTH FROM creation_date),8,1,0)) AUG,
SUM(DECODE(extract (MONTH FROM creation_date),9,1,0)) SEP,
SUM(DECODE(extract (MONTH FROM creation_date),10,1,0)) OCT,
SUM(DECODE(extract (MONTH FROM creation_date),11,1,0)) NOV,
SUM(DECODE(extract (MONTH FROM creation_date),12,1,0)) DEC,
SUM(1) total
FROM ap.ap_invoices_all
GROUP BY rollup(extract(YEAR FROM creation_date));

-- ##############################################################################
--   SUPPLIER HEADER COUNT
-- ############################################################################*/

SELECT
NVL(TO_CHAR(extract(YEAR FROM creation_date)),'TOTAL') CREATION_YEAR,
SUM(DECODE(extract (MONTH FROM creation_date),1,1,0)) JAN,
SUM(DECODE(extract (MONTH FROM creation_date),2,1,0)) FEB,
SUM(DECODE(extract (MONTH FROM creation_date),3,1,0)) MAR,
SUM(DECODE(extract (MONTH FROM creation_date),4,1,0)) APR,
SUM(DECODE(extract (MONTH FROM creation_date),5,1,0)) MAY,
SUM(DECODE(extract (MONTH FROM creation_date),6,1,0)) JUN,
SUM(DECODE(extract (MONTH FROM creation_date),7,1,0)) JUL,
SUM(DECODE(extract (MONTH FROM creation_date),8,1,0)) AUG,
SUM(DECODE(extract (MONTH FROM creation_date),9,1,0)) SEP,
SUM(DECODE(extract (MONTH FROM creation_date),10,1,0)) OCT,
SUM(DECODE(extract (MONTH FROM creation_date),11,1,0)) NOV,
SUM(DECODE(extract (MONTH FROM creation_date),12,1,0)) DEC,
SUM(1) total
FROM apps.po_vendors
GROUP BY rollup(extract(YEAR FROM creation_date));

-- ##############################################################################
--   SUPPLIER SITE COUNT
-- ############################################################################*/

SELECT
NVL(TO_CHAR(extract(YEAR FROM creation_date)),'TOTAL') CREATION_YEAR,
SUM(DECODE(extract (MONTH FROM creation_date),1,1,0)) JAN,
SUM(DECODE(extract (MONTH FROM creation_date),2,1,0)) FEB,
SUM(DECODE(extract (MONTH FROM creation_date),3,1,0)) MAR,
SUM(DECODE(extract (MONTH FROM creation_date),4,1,0)) APR,
SUM(DECODE(extract (MONTH FROM creation_date),5,1,0)) MAY,
SUM(DECODE(extract (MONTH FROM creation_date),6,1,0)) JUN,
SUM(DECODE(extract (MONTH FROM creation_date),7,1,0)) JUL,
SUM(DECODE(extract (MONTH FROM creation_date),8,1,0)) AUG,
SUM(DECODE(extract (MONTH FROM creation_date),9,1,0)) SEP,
SUM(DECODE(extract (MONTH FROM creation_date),10,1,0)) OCT,
SUM(DECODE(extract (MONTH FROM creation_date),11,1,0)) NOV,
SUM(DECODE(extract (MONTH FROM creation_date),12,1,0)) DEC,
SUM(1) total
FROM apps.po_vendor_sites_all
GROUP BY rollup(extract(YEAR FROM creation_date));

SQL Like – Percent vs Underscore

Sections:

  1. Intro
  2. Use case for the underscore
  3. Escaping the Percent and Underscore

Intro

I recently found out about the Oracle underscore pattern matching character.

References:

I’m used to using the % sign when using LIKE in SQL statements. For example, find all records which include the word “all”:

WITH table_data
    AS (SELECT 'Sarah Sausage'     item_name FROM DUAL UNION ALL
        SELECT 'Safehouses Stalls' item_name FROM DUAL UNION ALL
        SELECT 'Sarah Smithy'      item_name FROM DUAL UNION ALL
        SELECT 'Sticks And Stones' item_name FROM DUAL UNION ALL
        SELECT 'Shocking Holly'    item_name FROM DUAL UNION ALL
        SELECT 'Table Form'        item_name FROM DUAL UNION ALL
        SELECT 'Bouncy Balls'      item_name FROM DUAL UNION ALL
        SELECT 'Soho Chairs'       item_name FROM DUAL UNION ALL
        SELECT 'Terrific Tonsils'  item_name FROM DUAL)
SELECT item_name
  FROM table_data
 WHERE item_name LIKE '%all%';
 
-----------------------
 
ITEM_NAME        
-----------------
Safehouses Stalls
Bouncy Balls     

2 rows selected.

Using % in your SQL wouldn’t help if you wanted to return all records where the item_name started with “S” and then had an “h” as the 5th letter.

Use case for the underscore

Using ‘S___h%’ (e.g. three underscores between the S and the h), returns any records starting with an S, and where there is an “h” after 5 characters.

    
WITH table_data
    AS (SELECT 'Sarah Sausage'     item_name FROM DUAL UNION ALL
        SELECT 'Safehouses Stalls' item_name FROM DUAL UNION ALL
        SELECT 'Sarah Smithy'      item_name FROM DUAL UNION ALL
        SELECT 'Sticks And Stones' item_name FROM DUAL UNION ALL
        SELECT 'Shocking Holly'    item_name FROM DUAL UNION ALL
        SELECT 'Table Form'        item_name FROM DUAL UNION ALL
        SELECT 'Bouncy Balls'      item_name FROM DUAL UNION ALL
        SELECT 'Soho Chairs'       item_name FROM DUAL UNION ALL
        SELECT 'Terrific Tonsils'  item_name FROM DUAL)
SELECT item_name
  FROM table_data
 WHERE item_name LIKE 'S___h%';
 
-----------------------
 
ITEM_NAME        
-----------------
Sarah Sausage    
Safehouses Stalls
Sarah Smithy     

3 rows selected.

This time, only those records starting with an “S” with an “h” in position 5 are returned.

Escaping the Percent and Underscore

What if your data contained a percent, or underscore, and you actually wanted to find those records?

Not Escaped

All the records are found since the %_% is saying to return all records which are at least 1 character long.

WITH table_data
     AS (SELECT 'test123' str FROM DUAL UNION ALL
         SELECT 'pj_pf'   str FROM DUAL UNION ALL
         SELECT 'Sid%Bob' str FROM DUAL UNION ALL
         SELECT 'cat'     str FROM DUAL)
SELECT *
  FROM table_data
 WHERE str LIKE '%_%';
 
-----------------------
 
STR        
-----------------
test123
pj_pf  
Sid%Bob
cat     

4 rows selected.

Find records containing a percent sign

When you do an SQL Like on an Oracle database, the SQL will normally include a % character. However, what if you want to find text which contains a percent sign? You can’t do select this from that where this LIKE '%%%';. Instead, you have to escape the percentage sign so that the SQL knows to treat the percentage sign differently.

WITH table_data
     AS (SELECT 'test123' str FROM DUAL UNION ALL
         SELECT 'pj_pf'   str FROM DUAL UNION ALL
         SELECT 'Sid%Bob' str FROM DUAL UNION ALL
         SELECT 'cat'     str FROM DUAL)
SELECT *
  FROM table_data
 WHERE str LIKE '%\%%' ESCAPE '\';
 
STR        
-----------------
Sid%Bob

Find records containing an underscore

As in the escape-your-percentage-character example above, if you want to find text containing an underscore, you also have to escape the underscore character in your like statement – as in this example:

WITH table_data
     AS (SELECT 'test123' str FROM DUAL UNION ALL
         SELECT 'pj_pf'   str FROM DUAL UNION ALL
         SELECT 'Sid%Bob' str FROM DUAL UNION ALL
         SELECT 'cat'     str FROM DUAL)
SELECT *
  FROM table_data
 WHERE str LIKE '%\_%' ESCAPE '\';

STR        
-----------------
pj_pf

Oracle vs ANSI SQL Syntax

Sections:

  1. Intro
  2. A way to avoid ORA-01417
  3. Advantages of ANSI syntax
  4. Example SQL – comparing Oracle vs. ANSI syntax

Intro

You might notice that I mainly use ANSI SQL syntax on this site, instead of Oracle syntax.

I used to write SQL in Oracle using Oracle syntax – such as:

    SELECT fu.user_name
      FROM applsys.fnd_user fu
         , ar.hz_parties hp
         , hr.per_all_people_f papf
     WHERE fu.person_party_id = hp.party_id(+)
       AND papf.party_id = hp.party_id(+)
       AND hp.party_type = 'PERSON';

However, if I wrote the SQL above and tried to run it, I’d get this error when doing so because I am trying to outer join the hz_parties table to the per_all_people_f and fnd_user at the same time:

ORA-01417: a table may be outer joined to at most one other table

A way to avoid ORA-01417

A colleague said he uses ANSI syntax, where you explicitly write the join names in your SQL.

    SELECT hp.party_name
         , hp.party_number
         , papf.employee_number
         , fu.user_name
      FROM ar.hz_parties hp
 LEFT JOIN hr.per_all_people_f papf ON hp.party_id =        papf.party_id
 LEFT JOIN applsys.fnd_user fu      ON fu.person_party_id = hp.party_id
     WHERE hp.party_type = 'PERSON'
       AND hp.creation_date > '01-OCT-2015';

Advantages of ANSI syntax

There are various advantages to using this approach:

  • You can outer join a table to more than one other table – in the example above, the hz_parties table is outer joined to the per_all_people_f and fnd_user, and there is no error.
  • You can introduce a table name and join all in the same line, so that it looks a lot tidier – if you want to comment out a table, just comment out the line with the table name and join on it, instead of in two places when using Oracle syntax. Also all of the info is in one place – e.g. the table name and join details.
  • The “WHERE” part of your SQL can be used to restrict your query to include specific records, instead of needing to include many lines of joins, so the SQL is more readable.
  • If you get using to writing SQL using ANSI syntax, you can use that in other databases such as MySQL, Access and SQL Server.

This is a useful post on Stackoverflow which goes into more detail: Oracle Joins – Comparison between conventional syntax VS ANSI Syntax

The SQLs below compare two statements, one written with Oracle syntax, the other with ANSI. There is no difference in the cost when viewing the Explain Plan for the 2 statements.

It’s probably a matter of preference, and doesn’t make a lot of difference really, as the statements below look very similar, but I think I prefer the ANSI format as I think it looks tidier and I find it easier to understand.

Example SQL – comparing Oracle vs. ANSI syntax

-- requisition headers and lines and line_locations_all, joined through to PO Headers and Lines
-- outer joined to po tables so can get REQ details which are not linked to PO

  SELECT prha.segment1 requisition, prha.requisition_header_id
       , prha.creation_date
       , prha.authorization_status
       , prla.line_num
       , prla.unit_price
       , prla.quantity
       , pha.segment1 po
       , pha.po_header_id
       , pla.line_num po_line_number
       , pla.po_line_id
       , plla.line_location_id
    FROM po.po_requisition_headers_all prha
       , po.po_requisition_lines_all prla
       , po.po_line_locations_all plla
       , po.po_lines_all pla
       , po.po_headers_all pha
   WHERE prha.requisition_header_id = prla.requisition_header_id
     AND prla.line_location_id = plla.line_location_id(+)
     AND plla.po_line_id = pla.po_line_id(+)
     AND plla.po_header_id = pha.po_header_id(+)
     AND prha.creation_date > '23-NOV-2015'
ORDER BY prha.creation_date DESC
       , prha.segment1
       , prla.line_num;
       
-- requisition headers and lines and line_locations_all, joined through to PO Headers and Lines
-- left joined to po tables so can get REQ details which are not linked to PO

      SELECT prha.segment1 requisition, prha.requisition_header_id
           , prha.creation_date
           , prha.authorization_status
           , prla.line_num
           , prla.unit_price
           , prla.quantity
           , pha.segment1 po
           , pha.po_header_id
           , pla.line_num po_line_number
           , pla.po_line_id
           , plla.line_location_id
        FROM po.po_requisition_headers_all prha
        JOIN po.po_requisition_lines_all prla ON prha.requisition_header_id = prla.requisition_header_id
   LEFT JOIN po.po_line_locations_all plla    ON prla.line_location_id =      plla.line_location_id
   LEFT JOIN po.po_lines_all pla              ON plla.po_line_id =            pla.po_line_id
   LEFT JOIN po.po_headers_all pha            ON plla.po_header_id =          pha.po_header_id
       WHERE prha.creation_date > '23-NOV-2015'
    ORDER BY prha.creation_date DESC
           , prha.segment1
           , prla.line_num;