|
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 |
|
|
|
|
