Order By Case

ORDER BY CASE:



Requirement:

There are four types of codes ‘CN’, ‘CNA’, ‘CX’ and ‘CNX’ which are there in a table. We want to process in following order CN, CNA, CX and then CNX. Order By Code will result in CN, CAN, CNX and then CX which is not required. Hence we will use ORDER BY CASE.



Data Set:

Solution:

SQL Fun : Connect By Level

Yesterday evening while watching Chris Gayle’s blistering knock against Mumbai Indians, my friend Sarkar came up with a requirement. He asked me to write a single SQL query which will return first day and last day of the month starting from today’s date till next 100 months.

Requirement: 

1-Apr-2013 30-Apr-2013

1-May-2013 31-May-2013

1-Jun-2013 30-Jun-2013

1-Jul-2013 31-Jul-2013

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

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

100th Record 100th Record

    Solution:



SELECT LEVEL,
TO_CHAR (TRUNC (ADD_MONTHS (SYSDATE, LEVEL - 1), 'MM'),
'DD-MON-RRRR'
),
TO_CHAR (LAST_DAY (ADD_MONTHS (SYSDATE, LEVEL - 1)), 'DD-MON-RRRR')
FROM DUAL
CONNECT BY LEVEL <= 100;



We can get Last Day of month using LAST_DAY (SYSDATE) and First Day using TRUNC (SYSDATE, 'MM'). To repeat 100 times we can use CONNECT BY LEVEL





Result:

He is happy now  J

Special/French Characters Conversion

Sometimes you may get special/french characters in your data which can cause
'ORA-06502: PL/SQL: numeric or value error: character string buffer too small' error.

Example : ÖÄ é è
Reason   : These single characters taking two byte length.

To overcome this issue, either increase the length of your column(sometimes it is not advisable due to your business need/design) or use convert function to convert these characters.

--> Get the NLS Character set defined
select value
  from nls_database_parameters
where parameter='NLS_CHARACTERSET';

--> Use Convert function
CONVERT(<char>, <destination_char_set>, <source_char_set>)

> Use destination Character set as 'US7ASCII' -> US 7-bit ASCII character set
> Use Source Character set as Character set returned above.

Example : select convert('Repport ÖÄ é è  de öäå','US7ASCII','<char set returned above>')
                  from dual;

>> If it will not find any valid conversion for any character it will put '?' for that character

Dates in Hours/Minutes/Secs

--> Extract date/hours/minutes/seconds/mseconds from a date
--------------------------------------------------------------

select to_char(day, 'dd-mon-yyyy hh24:mi:ss' )                 Input_Date,
         trunc(day)                                                             Day,
         trunc(time*24)                                                        Hours,
         trunc(time*24*60 - 60*trunc(time*24))                       Minutes,
         trunc(time*24*60*60 - 60*trunc(time*24*60))              Seconds,
         trunc(time*24*60*60*100 - 100*trunc(time*24*60*60)) mseconds
  from (select &day                      day,
                    &day - trunc(&day)  time
             from dual
          ) 

--> Get difference of two dates in days/hours/minutes/seconds/mseconds
-------------------------------------------------------------------------

select to_char( date1, 'dd-mon-yyyy hh24:mi:ss' )     Date1,
          to_char( date2, 'dd-mon-yyyy hh24:mi:ss' )     Date2,
          trunc( date2-date1 )                                      Days,
          trunc( mod( (date2-date1)*24, 24 ) )                Hours,
          trunc( mod( (date2-date1)*24*60, 60 ) )           Minutes,
          trunc( mod( (date2-date1)*24*60*60, 60 ) )      Seconds,
          trunc( mod( (date2-date1)*24*60*60*60, 60 ) ) mSeconds
  from (select date_column1 date1
                    ,date_column2 date2
            from &table_name
          )

--> Adding specific number of Days/Hours/Minutes/Secs to a Date
--------------------------------------------------------------------
 select sysdate inputdate
         , sysdate + (:day) + (:Hours/24) + (:Minutes/(24*60)) + (:Secs/(24*60*60)) newdate
   from dual;

Let's Play with SQL

1> Function  REGEXP_SUBSTR

REGEXP_SUBSTR, a superset of SUBSTR function.
While SUBSTR extracts a string from a specific location in the target, REGEXP_SUBSTR extracts a string that matches a given pattern, specified with a regular expression, from anywhere in the target string.

Recently I have used this function to get a comma separated string into different rows.
like if a table coulmn has value 'Str1,Str2,Str3,Str4' then I was required to get as
Str1
Str2
Str3
Str4

Examples1 : Target string value 'Str1,Str2,Str3,Str4'

  SELECT REGEXP_SUBSTR ('Str1,Str2,Str3,Str4', '[^,]+', 1, LEVEL) Val
    FROM dual 
  CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE('Str1,Str2,Str3,Str4','[^,]*'))+1

Example2 : Table xx_tab has Column xx_col with target string then

  WITH Tab AS (SELECT xx_col Col
                 FROM xx_tab)
  SELECT TRIM(REGEXP_SUBSTR (Col, '[^,]+', 1, LEVEL))
    FROM Tab 
  CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(Col,'[^,]*'))+1


2> Current Oracle Application/Database version

DataBase:

SELECT *
  FROM product_component_version;
OR
SELECT *
  FROM v$version;

Application :
SELECT release_name
  FROM fnd_product_groups;

3> Getting Patch Related Details

SELECT *
  FROM ad_applied_patches ;

Note : it is not storing the Database patches info.

BUG Details

SELECT *
  FROM ad_bugs;

PATCH Set Level

SELECT patch_level
             ,fa.application_short_name
            ,application_name
  FROM fnd_product_installations fp
             ,fnd_application fa
            ,fnd_application_tl fat
 WHERE patch_level is not null
     AND fp.application_id = fa.application_id
     AND fp.application_id = fat.application_id
     AND fat.language = 'US'
ORDER BY fat.application_name;

4> Total Time Taken To Complete a Concurrent Request

select trunc(mod((nvl(actual_completion_date,sysdate) - nvl(actual_start_date,sysdate))*24,60),.1)||' Hr     '||trunc(mod((nvl(actual_completion_date,sysdate) - actual_start_date)*24*60,60),1)||' Mins' "Total Time"
         ,actual_start_date
         ,actual_completion_date
         ,phase_code
         ,status_code
  from fnd_concurrent_requests fcr
 where concurrent_program_id = (select concurrent_program_id
                                                    from fnd_concurrent_programs_tl
                                                  where user_concurrent_program_name = :Conc_program_name
                                                        and language = 'US')

5> Getting multiple rows of data into a single row (11g)

LISTAGG function -
------------------
This function is doing grouping and concatenation of multiple rows of data into a single row per group.

Syntax : LISTAGG(<Column_name> , [<delimiter>]) WITHIN GROUP (ORDER BY <Column_name>)  
        >> [] - optional value

Example :

Data in table xx_receipt_tbl:

   receipt_number       invoice_number
   --------------       --------------
   A30958               A0005475
   A30958               A0005730
   A32249               A5013328
   A32249               A5014550
   A32249               A0053997
   A30787               A0004163

Query:
   select receipt_number
         ,listagg(invoice_number,'~') within group (order by invoice_number)  invoice_numbers
     from xx_receipt_tbl
     group by receipt_number

Result:
   receipt_number         invocie_numbers
   --------------         ---------------
   A30958                 A0005475~A0005730
   A32249                 A5013328~A5014550~A0053997
   A30787                 A0004163   

6> Trimming/Removing leading digits/characters

>Frequently used when you have to remove leading zeros

Ex:
select trim(leading 'A' from 'ATrimTest')
  from dual;

select trim(leading 0 from 0099887766)
  from dual;

OR

 select replace(ltrim(replace(0099007766, '0', ' ')) , ' ', '0')
   from dual
  
 select replace(ltrim(replace('ATrAATest', 'A', ' ')) , ' ', 'A')
   from dual