Home » SQL & PL/SQL » SQL & PL/SQL » Needed a simplified sql querry
Needed a simplified sql querry [message #215452] Mon, 22 January 2007 06:06 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,
I just need a sql querry which should return updated databse column names
Here I am having three tables 
EMP
EMP1
EMP2

Three tables having similar structure. But emp2 is having one extra column from other two tables

EMP
empno empname dname
1 a aa
2 b bb
c c cc

EMP1
empno empname dname
1 a ab
2 c bb
c d ee

I Have to compare these two tables and I have to insert the rows into emp2 if any rows got updated

EMP2
empno empname dname updated_columnss
1 a ab dname
2 c bb ename
c d ee ename, dname


I have written sql in the foillowing manner. Its executing fine. But I am having 217 columns in a table and it should be dynamically executed.

INSERT INTO 
    EMP2(DNAME,EMPNAME,EMPNO, UPDATED_COLUMNS) 
            ( SELECT     
              t1.DNAME,
              t1.EMPNAME,
              t1.EMPNO,
              t3.UPDATEDCOLS 
   FROM EMP t1 ,
        EMP1 t2 ,
        ( select t1.EMPNO , 
                       RTRIM 
                         ( 
                          CASE WHEN( (t1.DNAME <> t2.DNAME) OR 
                            (t1.DNAME IS NULL AND t2.DNAME IS NOT NULL ) OR
                            (t1.DNAME IS NOT NULL AND t2.DNAME IS NULL )) 
                            THEN ' DNAME ,'ELSE NULL END || 

                 CASE WHEN( (t1.EMPNAME <> t2.EMPNAME) OR 
                           (t1.EMPNAME IS  NULL AND t2.EMPNAME IS NOT NULL ) OR 
                            (t1.EMPNAME IS  NOT NULL AND t2.EMPNAME IS NULL )) 
                           THEN ' EMPNAME , 'ELSE NULL END , ' , ' )
as updatedcols 
                               from EMP t1 ,EMP1 t2 where t1.EMPNO = t2.EMPNO ) t3 
WHERE t1.empno= t2.empno and t1.empno=t3.empno and
( (t1.DEPTNO <> t2.DEPTNO OR (t1.DEPTNO IS NULL AND t2.DEPTNO IS NOT NULL ) OR (t1.DEPTNO IS NOT NULL AND  t2.DEPTNO IS NULL )) OR 
(t1.DNAME <> t2.DNAME OR (t1.DNAME IS NULL AND t2.DNAME IS NOT NULL ) OR (t1.DNAME IS NOT NULL AND t2.DNAME IS NULL )) OR 
(t1.EMPNAME <> t2.EMPNAME OR (t1.EMPNAME IS NULL AND t2.EMPNAME IS NOT NULL ) OR (t1.EMPNAME IS NOT NULL AND t2.EMPNAME IS NULL )) OR 
(t1.EMPNO <> t2.EMPNO OR (t1.EMPNO IS NULL AND t2.EMPNO IS NOT NULL ) OR (t1.EMPNO IS NOT NULL AND t2.EMPNO IS NULL )));


This is working fine. But I we are having more than 150 plus columns in atable. Its creating abig sql querry of 20 to 30 pages. While executing that querry does not produce any results. If i run the same querry from sql editor. It is able to produce desired result.

Thanks in advance


Edit:
OP needs to format the code manually.
CODE tags does not help much Embarassed
Mahesh Rajendran

[Updated on: Mon, 22 January 2007 23:03]

Report message to a moderator

Re: Needed a simplified sql querry [message #215479 is a reply to message #215452] Mon, 22 January 2007 07:50 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Unreadable. Use CODE tags.
Re: Needed a simplified sql querry [message #215481 is a reply to message #215479] Mon, 22 January 2007 07:56 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
No ./fa/1579/0/. You should set the _improve_readability property (in Tools - Options menu) of your browser to 'Enable'.
Re: Needed a simplified sql querry [message #215563 is a reply to message #215479] Mon, 22 January 2007 22:57 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hey I have formatted the code. Just take a look and let me know if you have any solution.

Thanks in advance
Re: Needed a simplified sql querry [message #215584 is a reply to message #215563] Tue, 23 January 2007 00:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I would say: create a package with basically two functions, each overloaded for varchar2, number and date datatypes.
First function is used to compare two values, returns Y or N (booleans are not usable in SQL).
This is the (t1.DEPTNO <> t2.DEPTNO OR (t1.DEPTNO IS NULL AND t2.DEPTNO IS NOT NULL ) OR (t1.DEPTNO IS NOT NULL AND t2.DEPTNO IS NULL ) part, used in the where clause.
Second function returns the changed value, comparable to the CASE part in your select. This function has three input parameters: Column name, value1 and value2.

Somehow, I more and more think my advice to find a tutor was a very valid advice...

[Updated on: Tue, 23 January 2007 00:30]

Report message to a moderator

Re: Needed a simplified sql querry [message #215592 is a reply to message #215584] Tue, 23 January 2007 00:41 Go to previous message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi

I finally found a solution on my own.

I suggest you to search a tutor for yourself.
Previous Topic: DBMS_SQL with clobs (Merged)
Next Topic: why <> null is not equal to is not null?
Goto Forum:
  


Current Time: Thu Dec 08 14:34:23 CST 2016

Total time taken to generate the page: 0.08853 seconds