Time Interval

Simple example of converting time-time to hrs/minutes. Change the times or date in the subquery to see what happens.

SELECT start_tm                                                       AS start_time
     , end_tm                                                         AS end_time
     , NUMTODSINTERVAL(end_tm - start_tm, 'DAY')                      AS NUMTODSINTERVAL
     , SUBSTR(NUMTODSINTERVAL(end_tm - start_tm, 'DAY'), 12, 5)       AS substr
     , EXTRACT(hour FROM NUMTODSINTERVAL(end_tm - start_tm, 'DAY'))   AS hour
     , EXTRACT(minute FROM NUMTODSINTERVAL(end_tm - start_tm, 'DAY')) AS min 
  FROM (
        SELECT TO_DATE('1/21/2015 9:00', 'mm/dd/yyyy hh24:mi')  AS start_tm 
             , TO_DATE('1/21/2015 19:45', 'mm/dd/yyyy hh24:mi') AS end_tm 
          FROM dual
       ); 

Fetch!

In 12c, Oracle released the Fetch/Offset clauses of the Select statement. It can replace "WHERE ROWNUM <". Besides having more functionality, it also leaves the WHERE statement to true conditions.

Some things to keep in mind:

• 'First' is the same as 'Last'. Use whichever reads better.
• 'Row' is the same as 'Rows'. Ditto
• If you don't use ORDER BY, records are typically returned in the order in which they were inserted into the table.
• You can use all of these in subqueries.
• Percent doesn't round, it truncates. (For example, if you ask for 30% of a table with 10 records, you get exactly 3 records.)
• Offset used by itself (without the Fetch), will pull from that record to the end of the table.

Display First Row Only

SELECT employee_id, first_name, last_name, job_id, salary
  FROM hr.employees
 ORDER BY employee_id
 FETCH FIRST ROW ONLY;

Or First 5

SELECT employee_id, first_name, last_name, job_id, salary
  FROM hr.employees
 ORDER BY employee_id
 FETCH FIRST 5 ROWS ONLY; 

Display Next 5

SELECT employee_id, first_name, last_name, job_id, salary
  FROM hr.employees
 ORDER BY employee_id
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

Or 2%

SELECT employee_id, first_name, last_name, job_id, salary
  FROM hr.employees
 ORDER BY employee_id
 FETCH FIRST 2 PERCENT ROWS ONLY;

With ties

Here we just ask for 2 records, but record 2 and 3 tie with the same salary so 3 records are displayed.

SELECT employee_id, first_name, last_name, job_id, salary
  FROM hr.employees
  WHERE department_id = 60
 ORDER BY salary
 FETCH FIRST 2 ROWS WITH TIES ;

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	JOB_ID	SALARY
107	Diana	Lorentz	IT_PROG	4200
105	David	Austin	IT_PROG	4800
106	Valli	Pataballa	IT_PROG	4800

In the Ballpark

When 'close enough' is 'good enough', try the 12c statement APPROX_COUNT_DISTINCT. (It was actually available in 11g, but not documented.)

Neither Count(Distinct) or Approx_Count_Distinct count nulls, nor do they work on long/lob data types.

Approx_Count_Distinct beat Count(Distinct) consistently in my tests. The first query shown below ran for 3 seconds -- the second for less than one.

SELECT test_state
     , COUNT(DISTINCT test_address)
  FROM master_test
 GROUP BY test_state  ;

SELECT test_state
     , APPROX_COUNT_DISTINCT(test_address)
  FROM master_test
 GROUP BY test_state ;

Who's on First?

It's important to know which SQL statements happen before others. Hopefully, this list will make it obvious why you can't use an alias in a WHERE statement, and why analytic functions can only be a SELECT:

1. From / Join
2. Connect By
3. Where
4. Group By
5. Having
6. Select / Analytic Functions
7. Distinct
8. Order By
9. Fetch / Offset

First or Last of a Set

If you need the 1st or last of a set, try this code:

SELECT year_code                                                     AS "Year"
     , MIN(month_title) KEEP (DENSE_RANK FIRST ORDER BY period_code) AS "First"
     , MAX(month_title) KEEP (DENSE_RANK LAST  ORDER BY period_code) AS "Last"
  FROM dlvr_time
 GROUP BY year_code
 ORDER BY year_code;
 

Cumulative Count

This is similar to the post on cumulative sum using the windowing function. This was needed because we had an old application that would only accept a certain number of dist_id's at a time. In this report, you can see how many rows you can take in to only get 100 rows at a time.

SELECT dist_id
     , COUNT(*)                                             AS ctr
     , SUM(COUNT(*)) OVER ( ORDER BY COUNT(*)
                            ROWS BETWEEN UNBOUNDED PRECEDING
                                  AND CURRENT ROW
                          )                                 AS tot_count
  FROM ods_prd.xref_store
 WHERE master_oid = 1
 GROUP BY dist_id
 ORDER BY 2 ;

1000 = 1k

I found this bit of code when I was trying to write a report for work. I like it and wanted to save it as sometimes links seem to go away. So here it is for the second time.

Thanks to Vincent Malgrat on StackOverflow for this.
( http://stackoverflow.com/questions/19522582/1000000-to-1m-and-1000-to-1k-in-oracle-query )

From Oracle documentation: e or E indicates that the number is specified in scientific notation. The digits after the E specify the exponent. The exponent can range from -130 to 125.

WITH data
     AS (SELECT POWER(10, ROWNUM) num
           FROM dual
         CONNECT BY LEVEL <= 9)
SELECT num
     , CASE
         WHEN num >= 1e6 THEN ROUND(num / 1e6) || 'M' -- 1e6 Exponentiation 
         WHEN num >= 1e3 THEN ROUND(num / 1e3) || 'k'
         ELSE TO_CHAR(num)
       END conv
  FROM data;

Making a Connection

If you want to change your login in your code, use the following statement. It can be either 'CONN' or 'CONNECT'.

I find this useful in code that you're possibly crossing a schema with a DB_Link and you want to be in one particular schema to start.

conn schema/password@database

Count a Field in Varying Tables with Subtotals

This code was written like this because the table names were not constant in which I need a count of a certain field. This sets variables for

Environment (Production or UAT)

Partition Name

Output Excel Name

A count of tables found

Once I got it done, I decided I wanted totals so a 'Create table' had to be added to the generated code so I could go back and decode the grouping values.

--SQL+ Commands to leave only SQL statements in the spool file
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET TIMING OFF

-- set for either environment (Prd/UAT)
COLUMN env NEW_VALUE env
SELECT SUBSTR(USER,-3) AS env
  FROM DUAL;

-- get latest partitions for MDSs
COLUMN part NEW_VALUE part
SELECT 'P'||MAX(ctl_master_del_store_oid) part
  FROM fss_&env..ctl_master_store;

-- save a file name for the excel export
COLUMN file_name NEW_VALUE file_name
SELECT '\\fs2\ims\Stores\VIP_Year_Count_'
    || TO_CHAR(SYSDATE,'YYYYMMDD') AS file_name
  FROM DUAL;

-- Find # of tables for union vs ';' in next query
COLUMN max_line NEW_VALUE max_line
SELECT COUNT(*)     AS max_line
  FROM dba_tab_columns      dtc
  JOIN dba_tables           dt
 USING (owner, table_name)
 WHERE column_name = 'VIP_YEAR'
   AND dt.num_rows > 0               -- ignore empty tables
   AND owner IN ( 'ODS_&env'
                , 'FSS_&env'
                , 'FEED_&env'
                );

--write SQL here from the following query to execute afterwards
SPOOL h:\Count_VIP_Years.sql

SELECT ------------------------------------------------------------------------
       -- Create table so a rollup can be done. Add to 1st line
       -- Add Union to each line except the last which takes a ';'
       ------------------------------------------------------------------------
       CASE WHEN line_no = 1 then 'CREATE TABLE sbx_str_&env..vip_year AS ' END
    || sql_line
    || CASE WHEN line_no < &max_line THEN ' union ' ELSE ';  ' END
  FROM (
        SELECT 'SELECT ''' || owner||'.'||table_name || ''' AS tbl '
            || ', TO_NUMBER(vip_year)                       AS vip_year '
            || ', COUNT(*)                                  AS cnt '
            || 'FROM '|| owner||'.'||table_name
            || CASE WHEN table_name LIKE 'MASTER_STORE%' -- only MSs have partitions
                    THEN ' PARTITION (&PART)' END
            || ' GROUP BY vip_year'
                                                            AS sql_line
             , ROW_NUMBER() OVER (PARTITION BY NULL
                                      ORDER BY owner DESC
                                             , table_name
                                 )                          AS line_no -- to find last line
         FROM dba_tab_columns dtc
         JOIN dba_tables      dt
        USING (owner, table_name)
        WHERE dtc.column_name = 'VIP_YEAR'
          AND dt.num_rows > 0             --get rid of empty tables
          AND owner IN ( 'ODS_&env', 'FSS_&env', 'FEED_&env' )
        ORDER BY owner DESC
               , table_name
       );

-- close this sql file
SPOOL OFF

-- run the code generated above to generate vip_year table in the sbx
@h:\Count_VIP_Years

-- run this against the vip_year table created in count_vip_years.sql
SELECT INITCAP(tbl) AS "Table"
     , vip_year     AS "VIP Year"
     , cnt          AS "Count"
  FROM (
        SELECT tbl
             , GROUPING(tbl)                                            AS tblgrp
             , GROUPING(vip_year)                                       AS yrgrp
             , DECODE(GROUPING(vip_year), 1, '* Table Total', vip_year) AS vip_year
             , TO_CHAR(SUM(cnt), '999,999,999')                         AS cnt
          FROM vip_year
         GROUP BY rollup ( tbl, vip_year )
       )
 WHERE tblgrp||yrgrp <> 11 -- do not display grand total
 ORDER BY tbl DESC
        , CASE vip_year
              WHEN '* Table Total'
              THEN TO_NUMBER(9999)        -- display total last
              ELSE TO_NUMBER(vip_year)
          END nulls first;

-- Export to Excel
EXPORT EXCEL [NONE] ["&file_name"]

-- Cleanup
DROP TABLE sbx_str_&env..vip_year;

-- Message
SELECT '\\fs2\ims\Stores\VIP_Year_Count_'
    || TO_CHAR(SYSDATE,'YYYYMMDD')
    || '.xlsx'         AS "Your file has been written to:"
  FROM DUAL;

Outer Join with Where

I may be the last one to figure this out, but a WHERE statement causes grief with an outer JOIN. Here I’m trying to join on a code table to get a description, but I only want those descriptions used for ‘Status,’ and not all records in Store have a status value.

Doesn't Work

Don't use ON key and WHERE condition.

SELECT client_id                       AS "Client#"
     , cs.status                       AS "Status"
     , dc.cd_meaning                   AS "Description"
     , TO_CHAR(cs.dcnt, '999,999,999') AS "Rec Cnt"
  FROM (
        SELECT client_id
             , status
             , COUNT(*)        AS dcnt
          FROM Store
         GROUP BY client_id, status
       ) cs
  LEFT
  JOIN def_codes dc
    ON cs.status = dc.cd
 WHERE column_name = 'STATUS';

Works

Use ON (key and AND condition).

SELECT client_id                       AS "Client#"
     , cs.status                       AS "Status"
     , dc.cd_meaning                   AS "Description"
     , TO_CHAR(cs.dcnt, '999,999,999') AS "Rec Cnt"
  FROM (
        SELECT client_id
             , status
             , COUNT(*)        AS dcnt
          FROM Store
         GROUP BY client_id, status
       ) cs
  LEFT
  JOIN def_codes dc
    ON ( cs.status = dc.cd
         AND
        column_name = 'STATUS'
       );

Where Case

I wish I could think of a good reason to have a CASE statement in a WHERE statement -- but I can't so I'll just show something that works.

One day...

SELECT ename
     , job
     , deptno
  FROM scott.emp
WHERE job LIKE
              CASE
                 WHEN deptno = '20' THEN '%AL%'
                 WHEN deptno = '30' THEN '%MAN%'
              END
ORDER BY deptno, job
;

Order By Case

This is a simple example of using CASE to get a specific order in your output.

SELECT ename
     , job
  FROM scott.emp
ORDER BY CASE job
               WHEN 'PRESIDENT' THEN 1
               WHEN 'MANAGER'   THEN 2
               WHEN 'ANALYST'   THEN 3
               WHEN 'SALESMAN'  THEN 4
                                ELSE 5
          END
;

Backup Tables in a Sandbox

This script is to backup a set of common tables. The owner and table_name are converted to a shorter version so there is room to add a date on the end of the name.

These are all written to a sandbox area, but are gathered from 5 different schemas.

If this is written in Golden, the SETs have to be on separate lines.

A report is produced at the end to show the names of the new tables.

/* SQL+ Commands to leave only SQL statements in the spool file */
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET TIMING OFF

/* write SQL to this file from the following query */
SPOOL h:\Bkup_Parm.sql

-- Generate Create statements for all support_parameter tables
SELECT 'CREATE TABLE sbx_str_uat.'
    || CASE owner
          WHEN 'ODS_UAT'     THEN 'ODSUAT__'
          WHEN 'SBX_STR_UAT' THEN 'SBXSTRU_'
          WHEN 'SBX_BRD_UAT' THEN 'SBXBRDU_'
          WHEN 'ODS_UAT1'    THEN 'ODSUAT1_'
          WHEN 'FSS_UAT'     THEN 'FSSUAT__'
         END
    || CASE table_name
          WHEN 'SUPPORT_PARAMETER_VALUES'  THEN 'SPV_'
          WHEN 'SUPPORT_PARAMETER_GROUP'   THEN 'SPG_'
          WHEN 'SUPPORT_PARAMETER_CHOICES' THEN 'SPC_'
          WHEN 'SUPPORT_PARAMETER'         THEN 'SP__'
         END

    || TO_CHAR(SYSDATE,'MMDDYYYY')
    || ' AS SELECT * FROM '
    || owner
    || '.'
    || table_name
    ||';'
  FROM all_tables
 WHERE REGEXP_LIKE(table_name , '^PARAMETER') -- starts with
   AND NOT REGEXP_LIKE(table_name,'[[:digit:]]') -- don't get other backups
 ;

-- close this sql file
SPOOL OFF

-- run the code generated above
@h:\Bkup_Parm

-- list tables backed-up
SELECT owner      AS "Owner"
     , table_name AS "UAT Parameter Backups"
  FROM all_tables
 WHERE table_name like '%_SP%'||TO_CHAR(SYSDATE,'MMDDYYYY')
 ORDER BY table_name;

Really Null Columns

I was asked to find a column on a table that was null that we could use for another purpose. Since on the requested table, there is close to 200 fields, this didn't seem like a manual task so I wrote some code.

All_Tab_Columns does have the Num_Distinct column, but our site doesn't always analyze each field so some of these say zero, but it's not true. So I start with that list and then check to see if they're truly zero by doing a count on the column.

The first query will generate a spool file with contents that look something like the following: (The two single quotes are needed so the output has one single quote in it.)

SELECT 'insert into nullcols values (''DIST_PHONE_INTL_CODE'');' 
  FROM (SELECT COUNT(DIST_PHONE_INTL_CODE) AS cnt 
          FROM ods_prd.xref_dist ) 
 WHERE cnt = 0 
UNION ALL                                                                                   
SELECT 'insert into nullcols values (''SP_NAME'');' 
  FROM (SELECT COUNT(SP_NAME) AS cnt 
          FROM ods_prd.xref_dist ) 
  WHERE cnt = 0;

This then will spool to the second sql file an insert statement for any column that has a count of zero. When this piece runs, the column names get inserted into a temp table that is joined to All_Tab_Columns to find the type and length.

-- Set In Golden, Press F5 to run all statements after tbl and sch are set

DEFINE sch = ods_prd
DEFINE tbl = xref_dist

-- SQL+ Commands to leave only SQL statements in the spool file
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET TIMING OFF

-- Nullcols is needed to hold the columns that needs to be compared to all_tab_columns
-- Drop if it exists and then create.
SPOOL h:\drop.sql
SELECT 'DROP TABLE nullcols;'
  FROM all_tables
 WHERE owner = 'SBX_STR_PRD'
   AND table_name = 'NULLCOLS';
SPOOL OFF
@h:\drop

CREATE TABLE nullcols (nullcol VARCHAR2(30));

--write SQL to this file from the following query
SPOOL h:\test.sql

------------------------------------------------------------------------------------
-- Generate lines as shown to be run next
-- insert into nullcols values ('CLIENT_CHAIN_ID');
------------------------------------------------------------------------------------
WITH max_id AS
        -------------------------------------------------
        -- All lines end with 'UNION ALL' except the last
        -- which needs a ';'
        -------------------------------------------------
        (SELECT MAX(column_id) MAX_ID
           FROM all_tab_columns
          WHERE owner = UPPER('&sch')
            AND table_name = UPPER('&tbl')
            AND num_distinct = 0
         )
SELECT 'SELECT ''insert into nullcols values ('''''
    || column_name
    || ''''');'
    || ''' FROM (SELECT COUNT('
    || column_name
    || ') AS cnt FROM &sch..&tbl ) WHERE cnt = 0'
    || CASE
         WHEN column_id = (SELECT max_id
                             FROM max_id) THEN ';'
         ELSE ' UNION ALL'
       END
  FROM all_tab_columns
 WHERE owner      = UPPER('&sch')
   AND table_name = UPPER('&tbl')
   AND num_distinct = 0
 ORDER BY column_id;

-- close this sql file
SPOOL OFF

-- spool a new sql file with the insert statements
SPOOL h:\test2.sql

-- run the code generated above
@h:\test

-- close this sql code file (insert statements)
spool off

-- run the insert statements
@h:\test2

-- produce a report that shows those columns and their
-- type/length from all_tab_columns
SELECT INITCAP(column_name) AS column_name
     , INITCAP(data_type)
       || CASE
            WHEN data_type <> 'DATE'
            THEN '('
                  || CASE
                       WHEN data_precision IS NULL
                       THEN data_length
                       ELSE TO_NUMBER(data_precision ||'.' || data_scale)
                     END
                  ||')'
          END AS type_length
  FROM all_tab_columns
 WHERE column_name IN (SELECT *
                         FROM nullcols)
   AND owner = UPPER('&sch')
   AND table_name = UPPER('&tbl');

Generate Basic Select Except for All-Null Columns

I’m building your standard Select statement, but I hate looking at null fields so I’m tossing those out. It does take a little while to generate depending on the size of the table and how many null fields there are. Like our client's table has about 20 null fields and 3.5M records and it took 3 minutes to write my query. I spool a generated SQL query to a .sql file and then execute it.

DEFINE schema = fss_prd
DEFINE tbl = client_table

-- Press F5 --
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET TIMING OFF
SPOOL h:\test.sql

SELECT 'SELECT CASE WHEN '
    || TO_CHAR(column_id)
    || '= 1 THEN ''SELECT '' ELSE ''     , '' END ||LOWER('''
    || column_name
    || ''') FROM &schema..&tbl WHERE ROWNUM < 2 AND '
    || column_name
    || ' IS NOT NULL UNION ALL'
  FROM all_tab_columns
 WHERE owner = UPPER('&schema')
   AND table_name = UPPER('&tbl')
 ORDER BY column_id
;
SELECT 'SELECT ''  FROM &schema..&tbl;'' FROM DUAL;'
FROM DUAL;

SPOOL OFF

@h:\test

Partitioned Tables Report for a Schema

This query -- once you adjust it for the schemas you’re interested in (and tables you’re not) -- will show pertinent information on the tables that are partitioned.

As you may know, large tables are often partitioned. A partitioned table is actually many small tables joined into a larger one so that any one part can be read for speed or the entire table can be read if required. Reading a partition improves query performance.

For example:

SELECT *
  FROM ods_prd.log_helpdesk_error PARTITION (P568266); 

You will usually need to find the latest partition needed for your query as they are constantly being added (and possibly dropped). This doesn’t necessarily apply to tables partitioned on dist_id or period_code.

WITH atp
     AS (SELECT table_owner
              , table_name
              , partition_name
              , partition_position
              , num_rows
              , blocks
              , last_analyzed
           FROM (SELECT ROW_NUMBER() OVER (PARTITION BY table_owner
                                                      , p.table_name
                                               ORDER BY partition_name DESC) AS row#
                      , p.*
                   FROM all_tab_partitions p
                  WHERE table_owner = 'ODS_PRD'
                    AND NOT REGEXP_LIKE(p.table_name, 'PROMO')
                    AND num_rows > 0
                )
          WHERE row# = 1 -- shown only the last one
        )
SELECT apt.owner
     , table_name
     , partitioning_type                     AS type
     , column_name
     , column_position                       AS col#
     , TO_CHAR(partition_count, '9,999,999') AS part#
     , status
     , partition_name                        AS last_name
     , TO_CHAR(num_rows, '999,999,999')      AS last_#_rows
     , TO_CHAR(last_analyzed, 'MM/DD/YYYY')  AS analyzed
  FROM all_part_tables apt
  JOIN all_part_key_columns apc
    ON apt.table_name = apc.name
   AND apt.owner = apc.owner
  JOIN atp
    ON apt.table_name = atp.table_name
   AND apt.owner = atp.table_owner
WHERE apt.owner  = 'ODS_PRD'
   AND apc.owner = 'ODS_PRD' 
   AND NOT REGEXP_LIKE(table_name, 'PROMO')
   AND partition_count > 1 -- don't show tables with 1 partition
   AND num_rows > 0 -- don't show empty tables
ORDER BY owner
        , table_name;

Finding a Word in any Table that Stores Code

This might come in handy if you have to find all the places in your code that a word is used. The following code generates a SQL query with multiple ‘Unions’ that can be executed to show all the tables and report IDs to be reviewed.

SQL-Generating Code:

/* change the word 'account' to the word you want to find in your code */
DEFINE word = account

SELECT 'SELECT '''                                                            -- literal 'select'
       || owner||'.'||table_name                                              -- owner.table name as literal in output
       || ''' AS tbl, report_id, report_title, report_desc, report_sql FROM ' -- literal column names and 'from'
       || owner||'.'||table_name                                              -- the table to pull the code from
       || q'[ WHERE REGEXP_LIKE(report_sql,'&word','i') UNION ALL]'           -- a literal piece of SQL code 
                                                                              -- You must use 'UNION ALL' with CLOBs
  FROM all_tab_columns                                -- look here to find
 WHERE table_name like 'PREP%'                        -- all tables that start with prep
   AND column_name = 'REPORT_SQL'                     -- that have this field which holds our code
   AND data_type   = 'CLOB'                           -- we cannot union together CLOBs and VARCHAR2s
   AND NOT REGEXP_LIKE (table_name,'[[:digit:]]') ;   -- toss those tables that have backup dates on them

SQL Code Generated:

This will generate the following, which can be copied and pasted into the editor area. You'll have to get rid of the last 'Union all' before executing.

SELECT 'SBX_STR_PRD.PREP_DR_ALTVIP' AS tbl, report_id, report_title, report_desc, report_sql FROM SBX_STR_PRD.PREP_DR_ALTVIP WHERE REGEXP_LIKE(report_sql,'account','i') UNION ALL
SELECT 'SBX_STR_PRD.PREP_DR_CENROLL' AS tbl, report_id, report_title, report_desc, report_sql FROM SBX_STR_PRD.PREP_DR_CENROLL WHERE REGEXP_LIKE(report_sql,'account','i') UNION ALL
SELECT 'SBX_STR_PRD.PREP_DR_DISTMNT' AS tbl, report_id, report_title, report_desc, report_sql FROM SBX_STR_PRD.PREP_DR_DISTMNT WHERE REGEXP_LIKE(report_sql,'account','i') UNION ALL

Sample Report of Code to be Reviewed:

It's easiest to paste this into Word to find and highlight code that might need to be changed.

Roll-up without Subtotals

Rollup usually will show subtotals on every difference in the group. To show only a grand total, add 'grouping sets' and a '1' as shown below:

SELECT deptno 
     , job 
     , SUM(sal) 
  FROM emp 
 WHERE deptno <> 10                  -- narrow down this example 
   AND job IN ( 'MANAGER', 'CLERK' ) -- narrow down this example 
 GROUP BY grouping sets ( ( deptno, job ) -- One row for every distinct combination of these 
                          , 1             -- Grand total super-aggregate 
                        ); 

DEPTNO	JOB	SUM(SAL)
30	CLERK	950
20	CLERK	1900
20	MANAGER	2975
30	MANAGER	2850
		8675

Or you can add 'Total' to it by decoding the 1st column. The Grouping value is 1 when it's a total.

SELECT DECODE(
              GROUPING(deptno) -- Decode the GROUPING value for DeptNo
             ,1                -- If it is 1,
             ,'Total'          -- Then print 'Total'
             ,deptno           -- otherwise, print the actual DeptNo
             ) AS dept         -- call this column 'Dept_Tot'
     , job
     , SUM(sal)
  FROM scott.emp
 WHERE deptno <> 10                  -- narrow down this example
   AND job IN ( 'MANAGER', 'CLERK' ) -- narrow down this example
 GROUP BY grouping sets ( ( deptno, job ) -- One row for every distinct combination of these
                          , 1             -- Grand total super-aggregate
                        );

DEPT	JOB	SUM(SAL)
30	CLERK	950
20	CLERK	1900
20	MANAGER	2975
30	MANAGER	2850
Total		8675

UAT vs PRD

If you’re tired of switching your schemas in code from UAT to PRD, you can use ‘USER’, which will return the schema.

-- create amper variable named environ
COLUMN environ NEW_VALUE environ 

SELECT SUBSTR(USER,-3) AS environ  -- get last 3 characters of schema
FROM DUAL;

---

SELECT *
  FROM xyz_ods_&environ..client_store /* use two dots
                                         1 for concatenation after variable and
                                         the normal 1 between table_name.owner */
;

---

SELECT *   
  FROM xyz_sbx_str_&environ..master_map 
;

Cumulative Sum

To get a cumulative sum, you need to use a window function ‘Rows Between Unbounded Preceding and Current Row’, which tells Oracle that the window it’s summing is between the beginning and the current record.

SELECT deptno, empno, ename, sal
     , SUM(sal) OVER (PARTITION BY deptno
                          ORDER BY empno
                                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                     ) AS
       cum_sal
  FROM scott.emp
 GROUP BY deptno, empno, ename, sal
 ORDER BY deptno, empno;

Connecting to SQL Developer

A while ago, the disk on my laptop crashed, and so now I'm trying to download and set up some of the tools I used before. I got SQL Developer installed, but naturally couldn't remember what I did to get the connection.  This time I decided to put it in writing.  I honestly haven't done any studying of Oracle's material on installation so it probably is all there, but one of the main reasons I blog is so I can find things again. 

Here I'm connecting to Oracle's HR schema, using the password I selected when I installed the database.
Click on 'Service name' at the bottom, and supply the three values found in your tnsnames.ora file.

Voila! Click on 'Test' or 'Connect.'

Wanna' Date?

Just a picture of 99% of the date variations listed in the book, although I'm saving timestamps for another post.

The Easy Way

 Select To_Char(Sysdate,'DL'   ) DL   /* long date */

      , To_Char(Sysdate,'DS'   ) DS   /* short date */

      , To_Char(Sysdate,'TS'   ) TS   /* short time */

      , To_Char(Sysdate,'DS TS') DSTS /* short date, time */

  From Dual;

DL                        | DS       | TS         | DSTS                

------------------------- | -------- | ---------- | --------------------

Saturday, August 06, 2011 | 8/6/2011 | 9:27:37 AM | 8/6/2011 09:27:37 AM

Day

Select To_Char(Sysdate,'DAY'   ) "DAY"    /* Name (Uppercase) */

     , To_Char(Sysdate,'Day'   ) "Day"    /* Name (Title case) */

     , To_Char(Sysdate,'DY'    ) "DY"     /* Abbreviated (Uppercase). */

     , To_Char(Sysdate,'Dy'    ) "Dy"     /* Abbreviated (Title case). */

     , To_Char(Sysdate,'D'     ) "D"      /* Day of week (1-7). */

     , To_Char(Sysdate,'DD'    ) "DD"     /* Day of month (1-31). */

     , To_Char(Sysdate,'fmDD'  ) "fmDD"   /* Day of month (1-31) zero suppress */

     , To_Char(Sysdate,'DDD'   ) "DDD"    /* Day of year (1-366). */

     , To_Char(Sysdate,'DDsp'  ) "DDsp"   /* spell out (Uppercase)*/

     , To_Char(Sysdate,'Ddsp'  ) "Ddsp"   /* spell out (Titlecase)*/

     , To_Char(Sysdate,'DDth'  ) "DDth"   /* ordinal (Uppercase) */

     , To_Char(Sysdate,'Ddth'  ) "Ddth"   /* ordinal (Title case) */

     , To_Char(Sysdate,'fmDdth') "fmDdth" /* ordinal zero suppress */

     , To_Char(Sysdate,'J'     ) "J"      /* Julian:# days since 1/1/4712BC */

  From Dual;

DAY      | Day      | DY  | Dy  | D | DD | fmDD | DDD | DDsp | Ddsp | DDth | Ddth | fmDdth | J

-------- | -------- | --- | --- | - | -- | ---- | --- | ---- | ---- | ---- | ---- | ------ | -------

SATURDAY | Saturday | SAT | Sat | 7 | 06 | 6    | 218 | SIX  | Six  | 06TH | 06th | 6th    | 2455780

Embed in Format - / , ; : . "text"

Select To_Char(Sysdate,'"Day": Dy, "Month": Month')

  From Dual;

TO_CHAR(SYSDATE,'"DAY":DY,

--------------------------

Day: Sat, Month: August

Century

Select To_Char(Sysdate,'AD')  "AD"   /* AD or BC */

     , To_Char(Sysdate,'A.D.')"A.D." /* A.D or B.C. */

     , To_Char(Sysdate,'B.C.')"B.C." /* A.D or B.C. */

  From Dual;

AD | A.D. | B.C.

-- | ---- | ----

AD | A.D. | A.D.

Month /* use fm in front to remove padding */

Select To_Char(Sysdate,'MM')      "MM"     /* 01-12 */

     , To_Char(Sysdate,'Month')   "Month"  /* Name padded with blanks to widest name */

     , To_Char(Sysdate,'fmMonth') "fmMonth"/* Name not padded */

     , To_Char(Sysdate,'MONTH')   "MONTH"  /* NAME padded with blanks to widest name */

     , To_Char(Sysdate,'Mon')     "Mon"    /* Abbreviated name (title case) */

     , To_Char(Sysdate,'MON')     "MON"    /* Abbreviated name (uppercase) */

     , To_Char(Sysdate,'RM')      "RM"     /* Roman numeral month */

  From Dual;

MM | Month     | fmMonth | MONTH  | Mon | MON | RM

-- | --------- | ------- | ------ | --- | --- | ----

08 | August    | August  | AUGUST | Aug | AUG | VIII

Week

Select To_Char(Sysdate,'WW')     "WW"    /* Week of year (1-53) where week 1 = 1/1 to 1/7. */

     , To_Char(Sysdate,'WWth')   "WWth"  /* Week of year (1ST-53RD) */

     , To_Char(Sysdate,'Wwth')   "Wwth"  /* Week of year (1st-53rd) */

     , To_Char(Sysdate,'WWsp')   "WWsp"  /* Week of year spelled out in caps */

     , To_Char(Sysdate,'Wwsp')   "Wwsp"  /* Week of year spelled out in title case*/

     , To_Char(Sysdate,'WWthsp') "WWthsp"/* Week of year (1st-53rd) spelled out*/

     , To_Char(Sysdate,'W')      "W"     /* Week of month (1-5) where week 1 = 1st to 7th*/

     , To_Char(Sysdate,'Wth')    "Wth"   /* Week of month (1st-5th) */

  From Dual;

WW | WWth | Wwth | WWsp       | Wwsp       | WWthsp        | W | Wth

-- | ---- | ---- | ---------- | ---------- | ------------- | - | ---

32 | 32ND | 32nd | THIRTY-TWO | Thirty-Two | THIRTY-SECOND | 1 | 1st

Quarter

Select To_Char(Sysdate,'Q')     "Q"     /* Quarter of year (1=Jan-Mar, 2=Apr-Jun, etc)*/

     , To_Char(Sysdate,'Qth')   "Qth"   /* Quarter of year (1st - 4th */

     , To_Char(Sysdate,'Qsp')   "Qsp"   /* Quarter of year (One - Four) */

     , To_Char(Sysdate,'Qthsp') "Qthsp" /* Quarter of year (First - Fourth) */

  From Dual;

Q | Qth | Qsp   | Qthsp

- | --- | ----- | -----

3 | 3rd | Three | Third

Year

Select To_Char(Sysdate,'Year')  "Year"  /* Spell out year */

     , To_Char(Sysdate,'RR')    "RR"    /* store 20th century dates in 21st using 2 digits */

     , To_Char(Sysdate,'RRRR')  "RRRR"  /* Round for 4 or 2. If 2, provides the same return as RR. */

     , To_Char(Sysdate,'Y,YYY') "Y,YYY" /* Year with comma */

     , To_Char(Sysdate,'YEAR')  "YEAR"  /* spelled out */

     , To_Char(Sysdate,'YYYY')  "YYYY"  /* 4-digit year */

     , To_Char(Sysdate,'YYY')   "YYY"   /* Last 3 digits */

     , To_Char(Sysdate,'YY')    "YY"    /* Last 2 digits */

     , To_Char(Sysdate,'Y')     "Y"     /* Last digit */

  From Dual;

Year          | RR | RRRR | Y,YYY | YEAR          | YYYY | YYY | YY | Y

------------- | -- | ---- | ----- | ------------- | ---- | --- | -- | -

Twenty Eleven | 11 | 2011 | 2,011 | TWENTY ELEVEN | 2011 | 011 | 11 | 1

Time

Select To_Char(Sysdate,'AM')    "AM"   /* Meridian indicator */

     , To_Char(Sysdate,'a.m.')  "a.m." /* Meridian indicator */

     , To_Char(Sysdate,'HH')    "HH"   /* Hour of day (1-12). */

     , To_Char(Sysdate,'fmHH')  "fmHH" /* Hour of day (1-12). */

     , To_Char(Sysdate,'HH24')  "HH24" /* Hour of day (0-23). */

     , To_Char(Sysdate,'MI')    "MI"   /* Minute (0-59). */

     , To_Char(Sysdate,'SS')    "SS"   /* Second (0-59). */

     , To_Char(Sysdate,'SSSSS') "SSSSS"/* Seconds past midnight (0-86399)*/

  From Dual;

AM | a.m. | HH | fm | HH | MI | SS | SSSSS

-- | ---- | -- | -- | -- | -- | -- | -----

AM | a.m. | 09 | 9  | 09 | 27 | 37 | 34057

My Ah-ha! Moment with Self-Joins

I was recently taking an online class in SQL from Oracle. I will admit that sometimes self-joins still baffle my mind. So I decided to try what they said.

Oracle's statement was:

"To join a table to itself, the table is given two names or aliases. This will make the database “think” that there are two tables. Choose alias names that relate to the data's association with that table."

So I decided to create two EMP tables in the Scott schema with just the information I needed.

create table e_emp as select empno, ename, mgr from emp;

create table m_emp as select empno, ename, mgr from emp;

Now it's very easy to see that 'MGR' in the 'E' table will have to connect to 'EMPNO' in the 'M' table to get the report I want.

So I'll code a very basic query from these two separate tables with the aliases 'E' and 'M' and will end up with the report shown below:

Select e.empno as Emp#, e.ename as Employee

     , m.empno as Mgr#, m.ename as Manager

  from e_emp e

     , m_emp m

 where e.mgr = m.empno;

But Oracle doesn't make me create two tables. I can run the same query listing EMP twice and get the same thing. The only thing to do is change the two table names 'e_emp' and 'm_emp' both to 'emp.'

Select e.empno as Emp#, e.ename as Employee

     , m.empno as Mgr#, m.ename as Manager

  from emp e

     , emp m

 where e.mgr = m.empno;

HR Hierarchy

A friend asked me to explain the hierarchical query, and when I went back to look at what I had on the blog, there was little explanation, and more of just a sample for me to follow next time I had to write one. So I decided to redo it in the HR schema and add a few comments. All the special words used for hierarchical queries are shown in red.

Line 3, 7, 8, 9 have to do with padding the name on the right with some dots. (for example: "3. Smith . . . .") Totally unnecessary, this was just for fun.

Line 4 is where I'm indenting the level#. I don't want to indent level 1 so I first subtract 1 from the level. I then multiply by 3, but you can make this any number you want. In my case:
Level 2 is indented by 3 spaces (2-1)*3
Level 3 is indented by 6 spaces (3-1)*3
Level 4 is indented by 9 spaces (4-1)*3
(If this is going to be HTML output, you'll have to follow this post.)

Line 5 concatenates a period and one space onto the back of the level number. (for example, "3. ")

Line 6 concatenates the last name, a comma, a space, and the first name together. (for example: "King, Steven")

Line 15 just got thrown in there to create a shorter output for this example.

Line 16 and 17 say that we want to use Steven King as level 1.

Line 18: I can't explain this better than Tom does in this post.

Line 19 says if there is more than one sibling on a level, sort by last_name, and within that, first_name.

/* 1*/ Select Employee_Id,
/* 2*/        -----------------------------------------
/* 3*/               Rpad(
/* 4*/                      Lpad(' ',3*(Level-1))
/* 5*/                    ||Level||'. '
/* 6*/                    ||Last_Name||', '||First_Name
/* 7*/                   ,30
/* 8*/                   ,'. '
/* 9*/                   )
/*10*/               As
/*11*/        Employee_Name,
/*12*/        -----------------------------------------
/*13*/        Manager_Id
/*14*/   From Employees
/*15*/  Where Department_Id Not in (50, 80)
/*16*/  Start With Last_Name  = 'King'
/*17*/         And First_Name = 'Steven'
/*18*/Connect By Manager_Id = Prior Employee_Id
/*19*/  Order Siblings By Last_name, First_name;

SQL*Plus Output -> HTML

*I saw this on Uwe Hesse's blog, and it was too cool not to keep a copy where I knew I could find it again.  The only thing I added to his script was the 'Start' in front of 'Firefox' that's needed by Windows (and the comment about removing the 'l').

HTML.SQL:
set termout off

set markup HTML ON HEAD " -
 -
" -
BODY " " -
TABLE "border='1' align='center' summary='Script output'" -
SPOOL ON ENTMAP ON PREFORMAT OFF

spool myoutput.html

--------------------------------------------------------------------------
-- if you want your query to display in firefox, uncomment the 'l' below.
--------------------------------------------------------------------------
--l
/

spool off
set markup html off spool off
host start firefox myoutput.html
set termout on

Run your query in SQL+.

conn hr/hr
set linesize 2000 pagesize 60 feedback off

Select *
  From Employees
 Where Job_Id = 'SH_CLERK'
 Order by Last_Name;

Hmm.. this is a bit too much for the screen.  You can either start formatting with 'Column' or run HTML.SQL.

Beautiful! Thanks Uwe Hesse and Tanel Poder.