|
View previous topic :: View next topic
|
| Author |
Message |
tarun_bhardwaj
New User
.jpg)
Joined: 18 Jul 2003
Posts: 39
Location: delhi
|
|
|
|
Hi,
Iam currently extracting records from TABLE1 into a flat file using the following SQL:
| Code: |
SELECT ABC.* FROM TABLE1 ABC
WHERE ABC.COL1 NOT IN
(SELECT DEF.COL1 FROM TABLE2 DEF
UNION
SELECT DEF.COL2 FROM TABLE2 DEF
WHERE
DEF.COL2 <> DEF.COL1); |
Here, ABC.COL1, DEF.COL1 and DEF.COL2 have the same data type and length.
Though I think the same is not possible using UNLOAD but still I was wondering if is there a way to implement the same using UNLOAD ? I tried to go thru the manuals but was unable to find a way out to achieve the same. Request you to please provide your inputs for the same.
Thanks in advance ! |
|
| Back to top |
|
 |
Ketan Varhade
Active User
Joined: 29 Jun 2009
Posts: 197
Location: Mumbai
|
|
|
|
Hi Tarun,
What problem did you faced while unloading, which unload tool/utility are you using for this ? |
|
| Back to top |
|
|
tarun_bhardwaj
New User
Joined: 18 Jul 2003
Posts: 39
Location: delhi
|
|
|
|
| I have not tried UNLOADing it since Iam not aware of the sytax for such a situation. Iam just trying to get an idea whether such a thing is possible using UNLOAD or not. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
DSNTIAUL ? google some, this would be the tool to use.
Depending on the sizes of table1 table2 and available indexes following sql could be a lot faster. Not saying it will be so, but maybe worth a try.
| Code: |
SELECT ABC.* FROM TABLE1 ABC
WHERE not exists (select 1 from table2 DEF where ABC.COL1 = DEF.COL1)
and not exists (select 1 from table2 def where ABC.COL1 = DEF.COL2 and DEF.COL2 <> DEF.COL1); |
|
|
| Back to top |
|
|
Ketan Varhade
Active User
Joined: 29 Jun 2009
Posts: 197
Location: Mumbai
|
|
|
|
Hi GuyC,
Will this query able to do the same as removing the duplicate and as well as sorting the data?
or you are thinking one more step to remove the duplicate and sorting the input data. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
both queries are functional the same :
only select rows from table 1 which (don't exist / are not in ) table2 with same COL1 or (if table2.col2 <> table2. col1 with col1 = col2 )
No sorting or removing of duplicates (in the result set) is done in any of the SQL.
any order your rows are returned are purely based on the chosen accespath and could randomly, suddenly change .
If you want a specific order , you'll have to add ORDER BY.
The query shouldn't have duplicates if table1 has a primary key.
if any sorting, duplicate removal has to done , maybe it is faster to do it in a sort step after DSNTIAUL. it depends on the specific case.
I would test both (if I knew enough about sort parms). |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
now that i reread it :
the second criteria can be simplified
tab1.col1 can not be = tab2.col1 (first part )
or
tab1.col1 can not be = tab2.col2 (second part) the test on tab2.col1 <> tab2.col2 is redundant because these rows would get eliminated in the first part
(basic decision table simplification)
so the sql would be even easier :
| Code: |
SELECT ABC.* FROM TABLE1 ABC
WHERE not exists (select 1 from table2 DEF where ABC.COL1 = DEF.COL1)
and not exists (select 1 from table2 def where ABC.COL1 = DEF.COL2) |
|
|
| Back to top |
|
|
Ketan Varhade
Active User
Joined: 29 Jun 2009
Posts: 197
Location: Mumbai
|
|
|
|
I got it , Thanks  |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
Hello Tarun,
I'm not sure but through out your replies you've used unload in CAPS as UNLOAD. Is that some in house utility?
If DSNTIAUL is your choice, search on that in the Forum, there are enough working example aroud to get you started. Or google DSNTIAUL, as GuyC has said. |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
| Back to top |
|
|
tarun_bhardwaj
New User
Joined: 18 Jul 2003
Posts: 39
Location: delhi
|
|
|
|
Hi All,
Thanks for your replies. Actually this is not what Iam looking for. I should have been more precise in drafting my question. Apologies for that !
Background:
My intention is to unload a partitioned tablespace so that each of the unloaded flat files can be processed in parallel streams. The SQL that I had specified earlier takes a lot of time so we decided to unload the rows into a flat file based on the partitions of that tablespace which satisfy the conditions mentioned in my SQL.
In the Utility Reference Guide, I flipped thru the notes on UNLOAD utility. The 'UNLOAD TABLESPACE' has a "WHEN" instead of a "WHERE" clause and we can have the predicates to compare the column with a constant but I could not come across an example where in the predicates we could compare a column with another column of a different table.
So just to summarize, I want to unload different partitions of a tablespace based on the criteria mentioned in my SQL.
Thanks in advance and sprry for the confusion ! |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
AFAIK you are right : you can not use complex criteria.
If you have (or create) a partitioned result table you might want to try crossloading :
| Code: |
EXEC SQL
DECLARE C1 CURSOR FOR SELECT * FROM table1
WHERE EMPNO <= '099999' and not exists ....
ENDEXEC
EXEC SQL
DECLARE C2 CURSOR FOR SELECT * FROM table1
WHERE EMPNO > '099999' AND EMPNO <= '199999' and not exists ....
ENDEXEC
EXEC SQL
DECLARE C3 CURSOR FOR SELECT * FROM table1
WHERE EMPNO > '199999' AND EMPNO <= '299999' and not exists ....
ENDEXEC
EXEC SQL
DECLARE C4 CURSOR FOR SELECT * FROM Table1 WHERE EMPNO > '299999' AND EMPNO <= '999999' and not exists ....
ENDEXEC
LOAD DATA
INTO TABLE table2 PART 1 REPLACE INCURSOR(C1)
INTO TABLE table2 PART 2 REPLACE INCURSOR(C2)
INTO TABLE table2 PART 3 REPLACE INCURSOR(C3)
INTO TABLE table2 PART 4 REPLACE INCURSOR(C4) |
I think you can have complex where clauses for this.
But the performance of the query will probably still be bad. |
|
| Back to top |
|
|
|
|
