Union woes

Silly Me!

I always thought a Union should and would accept a Select clause that is exactly identical w.r.t number of columns, data types and column names. i.e if I want to say display the list of all employees who are in Salary Grade 'A' and the list of employees who are in HR Department in the same result set (for some strange reason) I was expecting this to be the correct query:

SELECT
E.EMPLOYEE_NAME,
S.SAL_GRADE AS GRADE_DEPT
FROM EMPLOYEE E , SALARY_GRADE S
WHERE E.SAL > S.LOW_SAL
AND E.SAL < S.HIGH_SAL
AND S.SAL_GRADE = 'A'

UNION

SELECT
E.EMPLOYEE_NAME,
D.DEPT_NAME AS GRADE_DEPT
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.DEPT_CODE = D.DEPT_CODE
AND D.DEPT_CODE = 'HR'


But apparently my assumption made using my "profound knowledge" was wrong. All a Union requires is for the number of columns and the data type of the columns in the Select list to be the same (refer Wiki). So even if I interchange the columns in the second query above, the query will still run and give results, but not necessarily the results you would want!

I learn this the hard way after discovering a stange bug in one of the reports that I had developed. After looking into the SQL I realized that I had interchanged the column names in one of the SQLs in the Union. The SQL still worked fine because each query was still having equal number of columns and same data types for each column!

Felt like pulling my hair out after discovering the bug!

Correlated Update : A tricky Situation

Hi Folks,

Long time no see, any post.
Last day I saw a bewildering issue. It may not strike some of you if you already know it.
But i thought this should be shared.

In a correlated update :
eg:
UPDATE MASTER_ORDERS X
SET QTY=(SELECT Y.QTY
FROM ORDERS Y
WHERE X.ORDER_NUM = Y.ORDER_NUM);


We know one thing ; that is it will scan the whole table and consider each row for updating.
But in this case if the inner query returns not result, still the update will complete. It will update even the row for which the inner query did not give any data with NULL. Some kind of force update .Hence resulting in loss of data.
Tom Kyte says this not the right way to do such an update.

To avoid this there are two methods:

- Use an updatable join (updatable view), if the row to be updated is key preserved (i.e; only one row is fetched for the join condition).

- Use an exists clause in the outer where condition, so that only records with change are picked for update. It also makes the query faster as the update is done on a smaller subset.

UPDATE MASTER_ORDERS X
SET QTY=(SELECT Y.QTY
FROM ORDERS Y
WHERE X.ORDER_NUM = Y.ORDER_NUM)
WHERE EXISTS (SELECT 1
FROM ORDERS Y
WHERE X.ORDER_NUM = Y.ORDER_NUM);

Moving To New Tablespace.

Hi Guys,

This is a small thing. But may be useful.
When we move tables and indexes to another tablespace, what all needs to be done (Oracle queries are used).
1) move the table.
alter table tablename move tablespace tablespacename;
2) now the indexes are of no use. You have to rebuild them in the new table space.
alter index indexname rebuild tablespacename;
3) Finally the packages need to be recompile. There is no need for explicit compilation as during execution it will be recompiled.
alter package packagename compile package;

Thats it!!

Concatenation on group by

Hi all,
As an extension to Chandan's previous post i would like to post this sample code which shows the usage of sys_connect_by_path operator in Oracle.

Using SYS_CONNECT_BY_PATH operator

Source table “temp”:

Name Deptno
------ -------
jagan 1
guru 2
varu 2
bharath 1
manju 1
giri 3
chandan 3

SELECT
deptno, substr(SYS_CONNECT_BY_PATH(name, ','),2) name_list
FROM
(
SELECT name,
deptno,
count(*) OVER ( partition by deptno ) cnt,
ROW_NUMBER () OVER ( partition by deptno order by name) seq
FROM temp
WHERE deptno is not null
)
WHERE
seq = cnt
START WITH
seq=1
CONNECT BY PRIOR
seq+1 = seq
AND PRIOR
deptno = deptno;

Result:
deptno Name_list
------- -------------
1 bharath,jagan,manju
2 guru,varun
3 chandan,giri

Concatenation on group by

Hi,
I had this requirement in my project to group by a column of a table and produce a concatenation of row values for a second column for each group. For example consider a table with two columns:

Department Employee
1 Chandan
1 Jagannath
2 Rahul
2 Pradeep
2 Manjunath

I want the result like this:

Department Name_List
1 Chandan ,Jagannath
2 Rahul,Pradeep,Manjunath

And since this was in SQL SERVER 2000 , there was no recursive query and I had to write using sql only. I finally wrote it by transposing the rows and then concatenating the result. Something like:

Department Name1 Name2 Name3
1 Chandan Jagannath -
2 Rahul Pradeep Manjunath

As you can see, here there is a fundamental limit. How many columns will you consider for transposing as you don't know how many rows are there for each department. Well the only way to proceed is to find the maximum count of rows for any department and transpose it to that many (or more) number of columns. I did just that and it worked.

Now in Oracle there is something called SYS_CONNECT_BY_PATH which can be sued with a recursive query to achieve this. But wonders of wonders , MYSQL has a function called GROUP_CONCAT which does exactly this. A concatenation of rows for each group. Now I am changing my perception of MySql. With features like this, it is easily the coolest database for developers. May be it is not ideal for Data warehouses but who cares about that!!

So if you guys ever come across this kind of requirement in Oracle or MySQL make sure to use these features:)

See this links for more:
Oracle
http://www.oracle.com/technology/oramag/code/tips2006/101606.html
MySQL http://www.oreillynet.com/databases/blog/2007/05/debunking_group_by_myths.html

Enjoy!!

T-SQL : Sweet and Sour

I have working on SQL server 2005 (t-sql) lately. This blog is a comparison between the pl/sql features available in t-sql,db2 v 8.2 and oracle 9i pl/sql.
This blog will be updated when anything new turns up.
So here goes...

# Feature Available in SQL server 2005? DB2 Oracle 1 Exception handling Yes. yes yes 2 Transaction Yes. yes yes 3 ELSIF construct No yes yes 4 IF exists () Yes. yes no 5 Max column size 32000 32000 4000 6 Implicit Cursor No Yes Yes 7 Recursive query Yes. Similar to db2 but only 100 literations yes yes : using connect byprior 8 Print Command Yes No Yes 9 Dynamic SQL Yes Yes Yes 10 Dynamic SQL STRING DATATYPE nvarchar and nchar varchar varchar 11 Variable and cursor name same yes yes no 12 Default value while variable declaration no yes yes 13 Sequences No Yes yes

Extracting parts from a Comma seperated string

One of the requirements in our project was to extract individual column names from a string containing comma seperated values i.e if the string has say:
v_column_string := ',country_code,country_name,country_descripton,'
I wanted to get each column name from this string. Here's how I wrote the query for that..


[CODE]
SELECT *
FROM(
SELECT
SUBSTR(v_column_string ,
INSTR (v_column_string , ',' , 1 , A.LVL)+ 1,
INSTR (v_column_string , ',' , 1 , A.LVL + 1 ) -
INSTR (v_column_string , ',' , 1, A.LVL)-1
) AS COLUMNS
FROM
( SELECT ROWNUM LVL FROM ALL_OBJECTS WHERE ROWNUM <= no_of_words)A

) B
WHERE B.COLUMNS IS NOT NULL

OUTPUT :

country_code
country_name
country_desc

where no_of_words is the number of words in the String(in this case it is 3). In case you don't know how many words might be there, you can go ahead and give the length of the string here(although this will be an overhead as many nulls will be returned , which is why i have a filter for not null).

Notice that I have given the string with a leading and ending comma i.e ',country_code,country_name,country_desc,'. This is basically to extract the first word and the last. If you don't want to give these two comma's you can tweak the query to handle it.

Hope you wil find this useful someday:)

CLOB Datatype

Ah!! The first Tech Post...
Some time back, i had tried out some things to get the features of the CLOB datatype in DB2.
I will share the details, so that you guys need not spend more time on it if in some point of time you need to find the features of CLOB.
Some of the features are w.r.t storing a procedure text in a clob variable.

Features Of CLOB Variables

No Parameter Supports
1 Size upto 2GB - 1 byte

2 Logging Yes

3 Reading Directly from disk. Buffer Pool Not Used.

4 Execute immediate
of DMLs specified in
CLOB Yes
[Code]
CREATE PROCEDURE GTT_CORE.CLOB_TEST
LANGUAGE SQL
BEGIN
DECLARE V_CLOB CLOB;

SET V_CLOB ='INSERT INTO GTT_CORE.DUMMY VALUES(1)';
EXECUTE IMMEDIATE V_CLOB;

END

5 Compilation of Procedure
as dynamic statement Yes
[Code]
BEGIN
DECLARE V_CLOB CLOB;

SET V_CLOB =
'Create procedure GTT_CORE.PROC
LANGUAGE SQL
BEGIN
DECLARE V_COUNT INTEGER;
SELECT COUNT(1) INTO V_COUNT FROM GTT_CORE.DUMMY;
INSERT INTO GTT_CORE.DUMMY VALUES (V_COUNT);
END';
EXECUTE IMMEDIATE V_CLOB;

END


6 Indentation of procedure Yes

7 Global Temporary Table

With a CLOB column NO. DB2 SQL error: SQLCODE: -350, SQLSTATE: 42962,SQLERRMC: V
Message: LOB, DATALINK, or structured type column ""V"" cannot be used in an index, a key, a unique constraint, a functional dependency, a generated column, or a declared temporary table.

[Code]
DROP PROCEDURE GTT_CORE.CLOB_TEST
GO
CREATE PROCEDURE GTT_CORE.CLOB_TEST
LANGUAGE SQL
BEGIN
DECLARE V_CLOB CLOB;
declare GLOBAL TEMPORARY TABLE DUMMY_CLOB (V CLOB) ON COMMIT PRESERVE ROWS NOT LOGGED WITH REPLACE;
SET V_CLOB =
'Create procedure GTT_CORE.PROC
LANGUAGE SQL
BEGIN
DECLARE V_COUNT INTEGER;';
SET V_CLOB = V_CLOB||'
SELECT COUNT(1)
INTO V_COUNT
FROM GTT_CORE.DUMMY;

INSERT INTO GTT_CORE.DUMMY VALUES (V_COUNT);
END';

insert into SESSION.DUMMY_CLOB values(v_clob);

END

8 Storage of CLOB in a table.
Is it with indentation?? Yes
[Code]
DROP PROCEDURE GTT_CORE.CLOB_TEST
GO
CREATE PROCEDURE GTT_CORE.CLOB_TEST
LANGUAGE SQL
BEGIN
DECLARE V_CLOB CLOB;

SET V_CLOB =
'Create procedure GTT_CORE.PROC
LANGUAGE SQL
BEGIN
DECLARE V_COUNT INTEGER;';
SET V_CLOB = V_CLOB||'
SELECT COUNT(1)
INTO V_COUNT
FROM GTT_CORE.DUMMY;

INSERT INTO GTT_CORE.DUMMY VALUES (V_COUNT);
END';

insert into gtt_core.dummy1 values(v_clob);


END


Hope it will be useful someday

-- Rahul Nair