Sql Functions

Aggregate  functions

• Aggregate functions return a single result row based on groups of rows, rather than on single rows.
• Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses.

They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle divides the rows of a queried table or view into groups.
All aggregate functions except COUNT(*)and GROUPING ignore nulls.

These are Aggregate functions
SUM()
SELECT SUM(salary) as Sum_Sal FROM emp;
AVG()
SELECT AVG(salary) as Avg_Sal FROM emp;
Max()
MAX returns maximum value of expression.
SELECT Max(salary) as Max_Sal FROM emp;
Min()
Min returns minimum value of expression.
SELECT Min(salary) as Min_Sal FROM emp;
Count()
Count returns the number of rows in the query.
SELECT COUNT(*) as total_rec FROM emp;
Example of Aggregate Function using GROUP BY:
To return the minimum and maximum salaries for each department in the emp table,we can use:group by clause
SELECT department_id,MIN(salary),MAX(salary)
FROM emp
GROUP BY dept_id;
To return the minimum and maximum salaries for each department in the emp table where minimum salary should be more than 5000
SELECT department_id, MIN(salary), MAX (salary)
FROM emp
WHERE MIN(salary) > 5000
GROUP BY dept_id;

Character Functions

• It calculates the ASCII equivalent of the first character of the given input string.

ASCII(<Character>)
ascii('A')     would return 65
ascii('a')     would return 97
ascii('a8')     would return 97

CHR(<Character>)
Returns the character equivalent of the given integer.
Example
SELECT CHR(65), CHR(97) FROM dual;
Output : A a

CONCAT(<string1>,<string2>)
This function returns String2 appended to String1.
Example:
SELECT CONCAT('Fname', 'Lname') Emp_name FROM emp;

INITCAP(<String>)
This function returns String with the first character of each word in upper case and rest of all in lower case.
Example:
SELECT INITCAP('oracle tutorial') FROM Dual;
Output :  Oracle Tutorial

instr( string1, string2 [, start_position [,  nth_Appearance ] ] ):
string1 is the string to search.

string2 is the substring to search for in string1.

start_position is the position in string1 from where the search will start. This argument is optional. If not mentioned, it defaults to 1.
The first position in the string is 1. If the start_position is negative, the function counts backward direction

nth_appearance is the nth appearance of string2. This is optional. If not defined, it defaults to 1.

Example
SELECT INSTR('Character','r',1,1) POS1
, INSTR('Character','r',1,2) POS2
, INSTR('Character','a',-1,2) POS3
,INSTR('character','c',) POS4
FROM Dual;
pos1 pos2 pos3 pos4
  4          9          3        6

LENGTH(<Str>)
Returns length of a string
secect length('Sql Tutorial') as len from dual;
O/p len
     12
LOWER(<Str>)
This function returns a character string with all characters in lower case.

UPPER(<Str>)
This function returns a character string with all characters in upper case.

LPAD(<Str1>,<i>[,<Str2>])
This function returns the character string Str1 expanded in length to i characters, using Str2 to fill in space as needed on the left side of Str1.
Example
SELECT LPAD('Oracle',10,'.') lapd_doted from Dual, would return ....Oracle
SELECT  LPAD('RAM', 7)  lapd_exa  from Dual    would return    '    RAM'

RPAD(<Str1>,<i>[,<Str2>])
RPAD is same as LPAD but Str2 is padded at the right side

LTRIM(<Str1>[,<Str2>])
The LTRIM function removes characters from the left side of the character Srting, with all the leftmost characters that appear in another text expression removed.
This function returns Str1 without any leading character that appears in Str2.If  Str2 characters are leading character in Str1, then Str1 is returned unchanged.
Str2 defaults to a single space.
Example
Select LTRIM('datawarehousing','ing') trim1
, LTRIM('datawarehousing   ') trim2
, LTRIM('         datawarehousing') trim3
, LTRIM('datawarehousing','data') trim4 from dual
O/P trim1                                      trim2                                     trim3                                        trim4
       datawarehousing               datawarehousing               datawarehousing                warehousing

RTRIM(<Str1>[,<Str2>])
Same as LTRIM but the characters are trimmed from the right side

TRIM([[<Str1>]<Str2> FROM]<Str3>)

• If present Str1 can be one of the following literal: LEADING, TRAILING, BOTH.
• This function returns Str3 with all C1(leading trailing or both) occurrences of characters in Str2 removed.
• If any of Str1,Str2 or Str3 is Null, this function returns a Null.

Str1 defaults to BOTH, and Str2 defaults to a space character.
Example
SELECT TRIM(' Oracle   ') trim1, TRIM('Oracle ') trim2 FROM Dual;
Ans trim1             trim2
        Oracle          Oracle
It'll remove the space from both string.

REPLACE(<Str1>,<Str2>[,<Str3>]
This function returns Str1 with all occurrence of Str2 replaced with Str3
Example
SELECT REPLACE (‘Oracle’, ’Ora’, ’Arti’) replace_exa FROM Dual;
O/p replace_exa
       Atricle

Essential Numeric Functions
ABS()
Select Absolute value
SELECT ABS(-25) "Abs" FROM DUAL;

  Abs
-------
  15
ACOS ()
Select cos value
SELECT ACOS(.28)"Arc_Cosine" FROM DUAL;
ASIN ()
Select sin value
SELECT ASIN(.6)"Arc_Cosine" FROM DUAL;
ATAN()
Select tan value
SELECT ATAN(.6)"Arc_Cosine" FROM DUAL;
CEIL()
Returns the smallest integer greater than or equal to the order total of a specified orde
SELECT  CEIL(239.8) FROM Dual would return 240

FLOOR()
Returns the largest integer equal to or less than value.

SELECT FLOOR(15.65) "Floor" FROM DUAL;

 Floor
------
15

MOD()
Return modulus value
SELECT MOD(11,3) "Mod" FROM DUAL;

 Modulus
-------
   2

POWER()
SELECT POWER(3,2) "Power" FROM DUAL;

 power
-------
   9
ROUND (number)
SELECT ROUND(43.698,1) "Round" FROM DUAL;

     Round
----------
      43.7

TRUNC (number)
The TRUNC (number) function returns n1 truncated to n2 decimal places. If n2 is omitted, then n1 is truncated to 0 places. n2 can be negative to truncate (make zero) n2 digits left of the decimal point.
SELECT TRUNC(12.75,1) "Trunc" FROM DUAL;

  Trunc
----------
  12.75

SELECT TRUNC(12.75,-1) "Trunc" FROM DUAL;

  Trunc
------
  10
Date And Time Function

ADD_MONTHS(date,number_of_month)
SELECT SYSDATE, ADD_MONTHS(SYSDATE,2), ADD_MONTHS(SYSDATE,-2) FROM DUAL;
Result:
SYSDATE     ADD_MONTH ADD_MONTH
-------------    ------------------------------------------
10-Feb-13       10-Apr-13 10-Dec-13

EXTRACT(<type> FROM <date>)
'Type' can be YEAR, MONTH, DAY, HOUR, MIN, SECOND, TIME_ZONE_HOUR, TIME_ZONE_MINUTE, TIME_ZONE_REGION
SELECT SYSDATE, EXTRACT(YEAR FROM SYSDATE)YEAR, EXTRACT(DAY FROM SYSDATE)DAY , EXTRACT(TIMEZONE_HOUR FROM SYSTIMESTAMP) TZH FROM DUAL;
LAST_DAY(<date>)
Extract last day of month
Example:
SELECT SYSDATE, LAST_DAY(SYSDATE) END_OF_MONTH  FROM DUAL;
Result: SYSDATE      END_OF_MO
               ----------    ---------------
             10-Feb-13      28-Feb-13

NEXT_DAY(<date>,<day>)
SELECT NEXT_DAY('28-Feb-13','SUN') "FIRST MONDAY OF MARCH”  FROM DUAL;
O/P FIRST MONDAY OF MARCH
               03-Mar-13
ROUND (date[,<fmt>])
SELECT SYSDATE, ROUND(SYSDATE,'MM'), ROUND(SYSDATE,'YYYY') FROM DUAL;
Result:SYSDATE   ROUND(SYS   ROUND(SYS
                                         -----------       ---------------
                                10-FEB-13 01-MAR-13 01-JAN-13
TRUNC(date[,<fmt>])
SELECT SYSDATE, TRUNC(SYSDATE,'MM'), TRUNC(SYSDATE,'YYYY') FROM DUAL;
Result: SYSDATE   TRUNC(SYS    TRUNC(SYS
                    -------      - ------------              --------
                    10-FEB-13 01-FEB-13    01-JAN-13

Most frequently asked in interviews.

  These questions are the most frequently asked in interviews.

1. To fetch ALTERNATE records from a table. (EVEN NUMBERED)
   select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from emp);
2. To select ALTERNATE records from a table. (ODD NUMBERED)
   select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from emp);
3. Find the 3rd MAX salary in the emp table.
   select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);
4. Find the 3rd MIN salary in the emp table.
   select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2where e1.sal >= e2.sal);
5. Select FIRST n records from a table.
   select * from emp where rownum <= &n;
6. Select LAST n records from a table
   select * from emp minus select * from emp where rownum <= (select count(*) - &n from emp);
7. List dept no., Dept name for all the departments in which there are no employees in the department.
   select * from dept where deptno not in (select deptno from emp); 
   alternate solution:  select * from dept a where not exists (select * from emp b where a.deptno = b.deptno);
   altertnate solution:  select empno,ename,b.deptno,dname from emp a, dept b where a.deptno(+) = b.deptno and empno is null;
8. How to get 3 Max salaries ?
   select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal desc;
9. How to get 3 Min salaries ?
   select distinct sal from emp a  where 3 >= (select count(distinct sal) from emp b  where a.sal >= b.sal);
10. How to get nth max salaries ?
    select distinct hiredate from emp a where &n =  (select count(distinct sal) from emp b where a.sal >= b.sal);
11. Select DISTINCT RECORDS from emp table.
    select * from emp a where  rowid = (select max(rowid) from emp b where  a.empno=b.empno);
12. How to delete duplicate rows in a table?
    delete from emp a where rowid != (select max(rowid) from emp b where  a.empno=b.empno);
13. Count of number of employees in  department  wise.
    select count(EMPNO), b.deptno, dname from emp a, dept b  where a.deptno(+)=b.deptno  group by b.deptno,dname;
14.  Suppose there is annual salary information provided by emp table. How to fetch monthly salary of each and every employee?
    select ename,sal/12 as monthlysal from emp;
    
15. Select all record from emp table where deptno =10 or 40.
    select * from emp where deptno=30 or deptno=10;
    
16. Select all record from emp table where deptno=30 and sal>1500.
    select * from emp where deptno=30 and sal>1500;
    
17. Select  all record  from emp where job not in SALESMAN  or CLERK.
    select * from emp where job not in ('SALESMAN','CLERK');
    
18. Select all record from emp where ename in 'BLAKE','SCOTT','KING'and'FORD'.
    select * from emp where ename in('JONES','BLAKE','SCOTT','KING','FORD');
    
19. Select all records where ename starts with ‘S’ and its lenth is 6 char.
    select * from emp where ename like'S____';
    
20. Select all records where ename may be any no of  character but it should end with ‘R’.
    select * from emp where ename like'%R';
    
21. Count  MGR and their salary in emp table.
    select count(MGR),count(sal) from emp;
    
22. In emp table add comm+sal as total sal  .
    select ename,(sal+nvl(comm,0)) as totalsal from emp;
    
23. Select  any salary <3000 from emp table. 
    select * from emp  where sal> any(select sal from emp where sal<3000);
    
24. Select  all salary <3000 from emp table. 
    select * from emp  where sal> all(select sal from emp where sal<3000);
    
25. Select all the employee  group by deptno and sal in descending order.
    select ename,deptno,sal from emp order by deptno,sal desc;
    
26. How can I create an empty table emp1 with same structure as emp?
    Create table emp1 as select * from emp where 1=2;
    
27. How to retrive record where sal between 1000 to 2000?
    Select * from emp where sal>=1000 And  sal<2000
    
28. Select all records where dept no of both emp and dept table matches.
    select * from emp where exists(select * from dept where emp.deptno=dept.deptno)
    
29. If there are two tables emp1 and emp2, and both have common record. How can I fetch all the recods but common records only once?
    (Select * from emp) Union (Select * from emp1)
    
30. How to fetch only common records from two tables emp and emp1?
    (Select * from emp) Intersect (Select * from emp1)
    
31.  How can I retrive all records of emp1 those should not present in emp2?
    (Select * from emp) Minus (Select * from emp1)
    
32. Count the totalsa  deptno wise where more than 2 employees exist.
    SELECT  deptno, sum(sal) As totalsal
    FROM emp
    GROUP BY deptno
    HAVING COUNT(empno) > 2
    
       
        

Order to Cash query

SELECT hp.party_name customer
      ,hca.account_number
      ,hp.party_number
      ,ooh.order_number
      ,ooh.flow_status_code Order_status
      ,rcta.trx_date Invoice_date
      ,rcta.trx_number Invoice_number
      ,rcta.status_trx Invoice_Status
      ,rcta.invoice_currency_code Invoice_currency
      ,rctla.line_number
      ,msi.segment1 Item_number
      ,rctla.description
      ,rctla.extended_amount line_amt
      ,arp.amount_due_original 
      ,arp.amount_due_remaining 
      ,rcta.org_id 
      ,rcta.customer_trx_id
  FROM RA_CUSTOMER_TRX_ALL RCTA,
       RA_CUSTOMER_TRX_LINES_ALL RCTLA,
       RA_CUST_TRX_TYPES_ALL rctt,
       AR_PAYMENT_SCHEDULES_ALL arp,
       HZ_CUST_ACCOUNTS_ALL HCA,
       HZ_PARTIES HP,
       MTL_SYSTEM_ITEMS_B msi, 
       OE_ORDER_LINES_ALL ool,
       OE_ORDER_HEADERS_ALL ooh
WHERE rcta.customer_trx_id = rctla.customer_trx_id
  AND msi.inventory_item_id = rctla.inventory_item_id
  AND msi.organization_id = 116
  AND rcta.cust_trx_type_id = rctt.cust_trx_type_id
  AND arp.customer_trx_id = rcta.customer_trx_id
  AND rctla.line_type =  'LINE'
  AND rcta.org_id = rctla.org_id
  AND rcta.bill_to_customer_id = hca.cust_account_id
  AND hca.party_id = hp.party_id
  AND rctla.interface_line_attribute6 = ool.line_id
  AND ool.header_id = ooh.header_id
--  AND ooh.order_number = '13904756'
--  AND hp.party_name = 'BANQUE MAGNETIQUE'
 ORDER BY hp.party_name,rcta.trx_number,rctla.line_number;

Invoice List for a Customer query

SELECT hp.party_name customer
      ,hca.account_number
      ,rcta.trx_date
      ,rcta.trx_number Invoice_number
      ,rcta.status_trx
      ,rcta.invoice_currency_code Invoice_currency
      ,rctla.line_number
      ,msi.segment1 Item_number
      ,rctla.description
      ,rctla.extended_amount line_amt
      ,rcta.org_id 
      ,rctt.cust_trx_type_id     
  FROM RA_CUSTOMER_TRX_ALL RCTA,
       RA_CUSTOMER_TRX_LINES_ALL RCTLA,
       RA_CUST_TRX_TYPES_ALL rctt,
       HZ_CUST_ACCOUNTS_ALL HCA,
       HZ_PARTIES HP,
       MTL_SYSTEM_ITEMS_B msi
WHERE rcta.customer_trx_id = rctla.customer_trx_id
  AND msi.inventory_item_id = rctla.inventory_item_id
  AND msi.organization_id = 116
  AND rcta.cust_trx_type_id = rctt.cust_trx_type_id
  AND rctt.TYPE = 'INV'
  AND rctla.line_type =  'LINE'
  AND rcta.org_id = rctla.org_id
  AND rcta.bill_to_customer_id = hca.cust_account_id
  AND hca.party_id = hp.party_id
  AND hp.party_name = 'A. C. Networks'
--  AND hca.account_number = '1581'
--  AND rcta.status_trx = 'OP'
  ORDER BY hp.party_name,rcta.trx_number,rctla.line_number;

Get List of responsibilities assigned to a user

select fu.user_name,
  frt.responsibility_name,
  furg.start_date,
  furg.end_date
from fnd_user fu ,
  fnd_user_resp_groups_direct furg ,
  fnd_responsibility_vl frt
where fu.user_id                 = furg.user_id
and frt.responsibility_id        = furg.responsibility_id
and frt.application_id           = furg.responsibility_application_id
and nvl(furg.end_date,sysdate+1) > sysdate
and nvl(frt.end_date,sysdate +1) > sysdate
and fu.user_name                   = :p_user_name
--and fu.user_name                   = :p_userid;
;

Trace details for a concurrent request along with the log and output file location details

SELECT 'Request id: '
  || request_id,
  'Trace id: '
  || oracle_process_id,
  'Trace Flag: '
  || req.enable_trace,
  'Trace Name: '
  || dest.VALUE
  || '/'
  || LOWER (dbnm.VALUE)
  || '_ora_'
  || oracle_process_id
  || '.trc',
  'Prog. Name: '
  || prog.user_concurrent_program_name,
  'File Name: '
  || execname.execution_file_name
  || execname.subroutine_name,
  'Status : '
  || DECODE (phase_code, 'R', 'Running')
  || '-'
  || DECODE (status_code, 'R', 'Normal'),
  'SID Serial: '
  || ses.sid
  || ','
  || ses.serial#,
  'Module : '
  || ses.module ,
  TO_CHAR(req.actual_start_date,'DD-MON-YYYY HH24:MI:SS') Request_start_date ,
  TO_CHAR(req.actual_completion_date,'DD-MON-YYYY HH24:MI:SS') Request_completion_date ,
  req.logfile_name ,
  req.outfile_name ,
  req.argument_text parameters_passed
FROM fnd_concurrent_requests req,
  v$session ses,
  v$process PROC,
  v$parameter dest,
  v$parameter dbnm,
  fnd_concurrent_programs_vl prog,
  fnd_executables execname
WHERE req.request_id           = :p_request_id
AND req.oracle_process_id      = proc.spid(+)
AND proc.addr                  = ses.paddr(+)
AND dest.NAME                  = 'user_dump_dest'
AND dbnm.NAME                  = 'db_name'
AND req.concurrent_program_id  = prog.concurrent_program_id
AND req.program_application_id = prog.application_id
AND prog.application_id        = execname.application_id
AND prog.executable_id         = execname.executable_id
;

List Concurrent Programs using a specific valueset in its parameter list

SELECT fcp.user_concurrent_program_name,
  fat.application_name,
  fdfc.column_seq_num Seq,
  fdfc.form_left_prompt "Parameter Name",
  fdfc.enabled_flag Active
FROM FND_DESCR_FLEX_COL_USAGE_VL fdfc ,
  fnd_flex_value_sets ffvs ,
  fnd_concurrent_programs_vl fcp ,
  fnd_application_tl fat
WHERE 1                             =1
AND ffvs.flex_value_set_id          = fdfc.flex_value_set_id
and fdfc.descriptive_flexfield_name = '$SRS$.'||fcp.concurrent_program_name
and fcp.application_id              = fat.application_id
and ffvs.flex_value_set_name        = :p_valuesetname -- Enter value set name
;