View previous topic :: View next topic
|
Author |
Message |
annu zacson
New User
Joined: 19 Aug 2008 Posts: 15 Location: Cochin
|
|
|
|
How to write a Db2 query for retrieving rows with same and duplicate values.
For eg:
Please find the doc attached:
These are the data which are present in the database.
I need to write these into 2 output files OUT1 and OUT2 based on the following conditions.
1) write only one record into output file OUT1(a customer which is having same national id)
2)write both the rows into output file OUT2(a customer which are having 2 different national id).
The sample query that i have written and the output is given in the excel sheet. But i need to write the output into 2 diff output files.
Please help me in doing this. |
|
Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
Here you go
For OUT1
Code: |
SELECT CUSTID,NATID FROM TABLE GROUP BY CUSTID,NATID HAVING COUNT(*) > 1 ;
|
For OUT2
Code: |
SELECT CUSTID,NATID FROM TABLE GROUP BY CUSTID,NATID HAVING COUNT(*) = 1 ;
|
|
|
Back to top |
|
|
Lijo
New User
Joined: 24 Aug 2008 Posts: 41 Location: Cochin
|
|
|
|
Hi,
Above mentioned query will give the desired output for OUT1.
For OUT2:
SELECT CUSTOMER_ID,NATIONAL_ID FROM TABLE A, TABLE B
WHERE A.CUSTOMER_ID=B.CUSTOMER_ID
AND A.NATIONAL_ID<>B.NATIONAL_ID; |
|
Back to top |
|
|
sudhakarreddy.ibm Warnings : 1 New User
Joined: 20 Apr 2009 Posts: 15 Location: chennai
|
|
|
|
Hi All,
IN DB2,
by using cursor you can do it. declare the cursor for OUT1,OUT2
DECLARE CURSOR duplicate-records FOR SELECT CUSTID,NATID, count(*) FROM TABLE GROUP BY CUSTID,NATID HAVING COUNT(*) > 1 ;
DECLARE CURSOR uniq-records FOR SELECT CUSTID,NATID, count(*) FROM TABLE GROUP BY CUSTID,NATID HAVING COUNT(*) = 1 ;
IN JCL
by using sort utility
////job JOB (DBBX16#),'RECEIV. GENERA.', ,,
//STEP205 EXEC PGM=SORT
//SORTIN DD DSN=A.B.C,
// DISP=SHR
//SORTOUT1 DD DSN=A.B.C.DUPLI,
//XSUM DD DSM=A.B.C.UNIQ
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS = (, , , ,)
SORT FIELDS = NONE,XSUM
/*
//
kindly let me know if any wrong
Thanks in advance
sudhakar |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6248 Location: Mumbai, India
|
|
|
|
Hi sudhakar,
How does this SORT solution work for the original question? |
|
Back to top |
|
|
Anush Dhanush
New User
Joined: 07 Nov 2011 Posts: 1 Location: India
|
|
|
|
OUT1:
Code: |
select custid,natid, [b]count(*)[/b] from table group by custid, natid having count(*) > 1 ; |
OUT2:
Code: |
select natid, custid, count(*) from table group by natid, custid having count(*) > 1 ;
|
@cvishu: Yes, we can Unload a Table fro m JCL itself, thru IKJEFT01 Utility. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3076 Location: NYC,USA
|
|
|
|
Please make a use of UNION between two queries suppiled by ashimer and place a cursor.
Code: |
SELECT CUSTID,NATID,Y FROM TABLE GROUP BY CUSTID,NATID ,3 HAVING COUNT(*) > 1
UNION
SELECT CUSTID,NATID,N FROM TABLE GROUP BY CUSTID,NATID,3 HAVING COUNT(*) = 1
Fetch cursor
when sqlcode = 0
if ws-hostt-3 = 'Y'
write out1
else
write out2
end-if
when other
error
end-evaluate |
|
|
Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005 Posts: 275 Location: Mumbai
|
|
|
|
Hi,
Do we really need two cursors or Union here? Not sure I may be missing something here
I guess it can be achievable with a single cursor and without Union as below
Code: |
SELECT CUSTID,NATID,COUNT(*) FROM TABLE GROUP BY CUSTID,NATID
Fetch cursor
when sqlcode = 0
if Host variable Count(*) > 1
write out1
else
write out2
end-if
when other
error
end-evaluate
|
Regards,
Chandan |
|
Back to top |
|
|
RahulG31
Active User
Joined: 20 Dec 2014 Posts: 446 Location: USA
|
|
|
|
I hope we all realize that it is a 7 year old topic. The solution was already given then and now we are stretching it unnecessarily.
Let's get this closed. |
|
Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005 Posts: 275 Location: Mumbai
|
|
|
|
Ha ha ha..my bad.. |
|
Back to top |
|
|
|