Showing posts with label sql. Show all posts
Showing posts with label sql. Show all posts

Thursday, December 3, 2015

Oracle SQL

Nice tutorial



We had a scenario , where we broke a master table CLOB column , into multiple rows in child tables VARCHAR2.
Functions LISTAGG and XMLAGG didn't help us , we keep getting 'concatinated string too large' error.... the above link helped us

Wednesday, October 14, 2015

Using Case in WHERE clause

Dynamic Conditional Where Clause in SQL

SELECT < col1 > , < col2 >
FROM < table >
WHERE col1 like DECODE(:Param_one,'ALL','%',:Param_one) or
    NVL(col2,'#') = case
        when :Param_one = 'ALL' then
            '#'
        else
            col2
        end

Thursday, April 23, 2015

See Trigger Code

Query to see Trigger Code

SELECT text FROM dba_source 
WHERE owner = 'owner name' AND name = 'trigger name' 
ORDER BY line

 Other Important tables
  • All_triggers
  • All_Views
  • All_Tables

Thursday, April 16, 2015

Select a substring in Oracle SQL up to a specific character

Using a combination of SUBSTR and INSTR you can get a substring in Oracle SQL up to a specific character

SELECT SUBSTR('ABC_blahblahblah', 0, INSTR('ABC_blahblahblah', '_')-1) AS output
  FROM DUAL

Result:
output
------
ABC

General Syntax
:

SELECT SUBSTR(t.column, 0, INSTR(t.column, '_')-1) AS output
  FROM YOUR_TABLE t

Saturday, May 31, 2008

Implicit VS explicit cursor

  1. PL/SQL employs an implicit cursor for each UPDATE, DELETE, or INSERT statement you execute in a program. You cannot, execute these statements within an explicit cursor, even if you want to.
  2. You have a choice between using an implicit or explicit cursor only when you execute a single-row SELECT statement (a SELECT that returns only one row).
  3. When SELECT returns more than one row you may or may not have to use explicit cursor depends on usecase.
  4. If you use an implicit cursor, Oracle performs the open, fetches, and close for you automatically; these actions are outside of your programmatic control.

SQL Faqs

find the two minimum salaries among table
This Query returns duplicate value.
select sal from (select * from order by sal asc) where rownum < 3

This Query retuns only the second minimum salary
Select min(salary) from where Salary>(Select min(salary) from )

Whereas the following Query returns the distinct first two minmum salary in a table
Select min(salary) from union
Select min(salary) from where salary>(Select min(salary) from )

Find out nth highest salary from emp table
directly we can not give the condition as rownum=2....this is the most optimal soluton for the given problem
select * from (select rownum r,salary from (select distinct(salary)from employees where salary is NOT NULL order by salary desc)) where r=2
Best way is using CORRELATED SUBQUERY : as below
Select * From TableName T1 Where (N-1) = (Select Count(Distinct(E2.ColumnName)) From TableName T2 Where T2.ColumnName > T1.ColumnName)
For Required Case We can use it as :-
Select * From EMP T1 Where (2-1) = (Select Count(Distinct(E2.sal)) From EMP T2 Where T2.sal > T1.sal)