Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

find unmatch records with query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
suresh1624

New User


Joined: 21 Nov 2007
Posts: 28
Location: chennai

PostPosted: Tue Jul 28, 2009 9:18 pm    Post subject: find unmatch records with query
Reply with quote

Hi experts,
I have table where it has the following columns with the data given below

Dept , item , qty

208 , 12 , 30
208 , 12 , 60
208 , 12 , 90
208 , 13 , 30
208 , 13 , 55
208 , 13 , 65


The output of the query should be as follows. The unmatched quantities should apply for each item within the dept.

item , qty
12 , 55
12 , 65
13 , 60
13 , 90
Back to top
View user's profile Send private message

ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Jul 28, 2009 10:13 pm    Post subject:
Reply with quote

What are you tryign to achieve here ? wht is the purpose of this o/p ?
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Jul 28, 2009 10:26 pm    Post subject:
Reply with quote

Code:


SELECT DISTINCT C.ITEM,D.QUANTITY FROM
(
SELECT * FROM TABLE  A
WHERE QUANTITY  NOT IN
(SELECT QUANTITY FROM TABLE B
WHERE ITEM <> A.ITEM)) AS C
INNER JOIN
(
SELECT * FROM TABLE  A
WHERE QUANTITY  NOT IN
(SELECT QUANTITY FROM TABLE B
WHERE ITEM <> A.ITEM)) AS D
ON C.DEPT = D.DEPT
AND C.ITEM <> D.ITEM ;



Try this code ... May work ...
Back to top
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Wed Jul 29, 2009 10:28 am    Post subject:
Reply with quote

Hi Suresh,
Please the requirement in details .
Back to top
View user's profile Send private message
suresh1624

New User


Joined: 21 Nov 2007
Posts: 28
Location: chennai

PostPosted: Wed Jul 29, 2009 12:21 pm    Post subject:
Reply with quote

Hi Ashimer,

I need to sync both the items quantities. If get the data in that form i will be able to directly insert into the the table.

Looks like you are thinking table a and table b are different.but both are same. so the above query might not work. i ll confirm you
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Wed Jul 29, 2009 1:21 pm    Post subject:
Reply with quote

Ashimer is also thinking that both tables are same. Please replace TABLE by your table name in the query given by Ashimer and see if it works for you. You may need to tweak the query to make it work. If you face any difficulty, post the problems you encountered. Someone will be here to help you.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Wed Jul 29, 2009 1:34 pm    Post subject:
Reply with quote

Suresh, i have used only one table here ... if the correlation is confusing you , you may change the correlation names ... Run the query and let us know .. actually your requirement is getting data from different rows into a single row ... it might require a lil bit of going out of the way ...
Back to top
View user's profile Send private message
suresh1624

New User


Joined: 21 Nov 2007
Posts: 28
Location: chennai

PostPosted: Wed Jul 29, 2009 2:41 pm    Post subject:
Reply with quote

Thanks alot ashimer. I ll let you know soon whether it worked or not.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts find RACF group for access to spooled... jzhardy JCL & VSAM 1 Mon May 08, 2017 11:46 am
No new posts To find out size allocated to a seque... ashek15 JCL & VSAM 15 Thu Apr 27, 2017 9:42 am
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us