Oracle Correlated Query Scope. Invalid Identifier Error

Consider query

[sourcecode language="sql"]
SELECT (SELECT tab.col  FROM DUAL) a
FROM (SELECT 1 col  FROM DUAL) tab;
[/sourcecode]

Output is

A
---
1

Here I am using tab table value inside subquery that is one level down and it's recognized by system.

Now, If I take it one more level down, that is, using tab value inside subquery's subquery, something like

[sourcecode language="sql"]
SELECT (SELECT * FROM (SELECT tab.col  FROM DUAL)) A
FROM (SELECT 1 col  FROM DUAL) tab
[/sourcecode]

It throws "invalid identifier TAB.COL" error

At Second level its not recognized by system.

Oracle NVL function always evaluates second parameter

NVL function doesn't do short circuit evaluation  i.e, all parameters are evaluated irrespective of condition but, value is returned only when condition returns true.

Lets test this

Consider below query to generate test data

[sourcecode language="sql"]
SELECT '' col
FROM DUAL
UNION ALL
SELECT     DBMS_RANDOM.STRING ('L', 5) col
FROM DUAL
CONNECT BY LEVEL < 5
[/sourcecode]

it returns 5 rows of which one row is always null

Now, Lets write one simple function which returns some value. We will call this function in case column value is null. Like

[sourcecode language="sql"]
SELECT NVL (tab.col, temp_func (tab.col))
FROM (SELECT '' col
FROM DUAL
UNION ALL
SELECT     DBMS_RANDOM.STRING ('L', 5) col
FROM DUAL
CONNECT BY LEVEL < 5) tab;
[/sourcecode]

The above statement should ideally call function temp_func(defined below) only for one row.

--takes input value and prints it in output
--always returns '***'

[sourcecode language="sql"]
CREATE OR REPLACE FUNCTION temp_func (p_text VARCHAR2)
RETURN VARCHAR2
AS
i   VARCHAR2(30) := 'who ';
BEGIN
i := i || p_text;
DBMS_OUTPUT.put_line (i);
RETURN '***';
END;
[/sourcecode]

But above SELECT statement returns five lines of dbms output, one for each row. That means function temp_func got called for each row irrespective of condition.

Unlike NVL, COALESCE function uses short circuit evaluation and so, prints only one line of dbms output

[sourcecode language="sql"]
SELECT coalesce(tab.col, temp_func (tab.col))
FROM (SELECT '' col
FROM DUAL
UNION ALL
SELECT     DBMS_RANDOM.STRING ('L', 5) col
FROM DUAL
CONNECT BY LEVEL < 5) tab;
[/sourcecode]

Resource Allocation System: Secret Revealed

Two senior persons, SP1, working since long time in the Organization, along with SP2, recently joined, organizes freshers induction program.

After praising number of process, practices and policies SP1 now talks about resource allocation system.

SP1 addressing freshers: Our Resource allocation system is one of the best. It is very advanced, and well proven system. The allocation of resources to particular project is done on the basis of number of critetias like their skills, experience, aspirations, personal constraints and, many others.It tries to find the best match for particular project.

After the session finished, SP2 with suprise and amazement says to SP1 ‘I didn’t know we have such a powerful resource allocation system’

SP1:’Yes, It employs one of the best algorithm. Any guess?’

SP1: ‘Okay. Its called Random Number generator’

Oracle Table NULL column validation. Update all error message at once

Requirement:

Consider table stg_table with columns c1 c2 c3 and c4(no index or not null constraints) and values as mentioned below

id    c1    c2    c3    c4
-----------------------------------
#1    1    2              5
#2    3            3
#3                         6

We wish to do NULL validation on all columns and update the error in error_msg column .For some reason NOT NULL constraint is not put on these columns.

Required output

id    c1    c2    c3    c4    error_msg
----------------------------------------------------
#1    1    2               5    C3 NULL
#2    3             3            C2, C4 NULL
#3                           6    C1, C2, C3 NULL

Solution

One simple method is to write as many number of sql UPDATE statement as number of columns that needs to be validated

UPDATE stg_table set error_message = error_msg||'C1, ' where c1 is null
UPDATE stg_table set error_message = error_msg||'C2, ' where c2 is null
.
.
and finally if error message was updated by any of above row then append ' NULL' to error_msg
UPDATE stg_table set error_message = decode(error_message,null,null,error_msg||' NULL')

Other method to perform same thing with single update statement is

[sourcecode language="sql"]
SELECT   id, DECODE (c1, NULL, 'C1,', NULL )
|| DECODE (c2, NULL, 'C2,', NULL)
|| DECODE (C3, NULL, 'C3,',NULL)
|| DECODE (c3, NULL, 'C3,', NULL) err_msg
FROM stg_table
[/sourcecode]

Above statement gives output like

id    err_msg
----------------------
1    C3,
2    C2,C4,
3    C1,C2,C3,

Now we need to remove the extra comma from end and append 'NULL' string

[sourcecode language="sql"]
SELECT id, DECODE (t2.err_msg,
NULL, NULL,
SUBSTR (t2.err_msg, 1, LENGTH (t2.err_msg) - 1)
|| ' NULL'
) error_msg
FROM (SELECT    DECODE (emp_no, NULL, 'EMP NO,', NULL )
|| DECODE (invoice_no, NULL, 'INVOICE NO,', NULL)
|| DECODE (invoice_date, NULL, 'INVOICE DATE,',NULL)
|| DECODE (vendor_no, NULL, 'VENDOR NO,', NULL) err_msg,
ID
FROM xx_invoices_stg) t2
[/sourcecode]

id    err_msg
----------------------
1    C3 NULL
2    C2,C4 NULL
3    C1,C2,C3 NULL

Now simply update stg table correlating above query id with table id

[sourcecode language="sql"]
UPDATE stg_table t1
SET        error_msg =
(SELECT DECODE (t2.err_msg,
NULL, NULL,
SUBSTR (t2.err_msg, 1, LENGTH (t2.err_msg) - 1)
|| ' should Not be NULL'
) error_msg
FROM (SELECT    DECODE (emp_no, NULL, 'EMP NO,', NULL )
|| DECODE (invoice_no, NULL, 'INVOICE NO,', NULL)
|| DECODE (invoice_date, NULL, 'INVOICE DATE,',NULL)
|| DECODE (vendor_no, NULL, 'VENDOR NO,', NULL) err_msg,
ID
FROM xx_invoices_stg) t2
WHERE t2.ID = t1.ID)
[/sourcecode]

DECODE inside IN clause Oracle SQL

Requirement : Consider Emplyoee table emp which has Emplyee number and Deparment number columns.

Table : emp

empno  deptno
---------------------
1       10
2       20
3       20
4       10
5       30

Based on some input parameter var that isnot part of any table we wish to select employees of specific Departments.

If value of var = 1 then
select dept 10 employees
empno 1 and 4

If value of var = 2 then
select dept 20 and 30 employees.
empno 2, 3 and 5

var variable has no significance with respect to Employee data

Solution :

[sourcecode language="sql"]
SELECT empno,deptno
FROM emp
WHERE deptno IN (DECODE(:var,1,10),decode(:var,2,20),decode(:var,2,30))
[/sourcecode]

when var value is 1 then
both second and third decode returns null, and evaluates to false for all rows, and first decode returns 10. The where clause translates into something like
where deptno = 10 or deptno = null or deptno = null
So, it returns deptno 10 employees only.

Similarly, for var value of 2 the where clause translates into
where deptno = null or deptno = 20 or deptno = 30
and returns deptno 20 and 30 employees.