Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
sql query to compare tables

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

New User


Joined: 14 Nov 2008
Posts: 23
Location: bangalore

PostPosted: Mon Jan 12, 2009 1:11 pm    Post subject: sql query to compare tables
Reply with quote

Hi,
I need to write a simple SQL query to test whether a order number occurs in one table but DOESNT appear in the other table and need to Give the list of orders which are present and not present.


Example: 1st table has a field called ordnum, the 2nd table also has a field called ordnum, I want to be able to test for the possibility that a number in the first table is present or not present in 2nd table.
Back to top
View user's profile Send private message

snehasai

New User


Joined: 14 Nov 2008
Posts: 23
Location: bangalore

PostPosted: Mon Jan 12, 2009 1:28 pm    Post subject: does this works
Reply with quote

does this query works.... for the
ORD_NUM shld present in Table1 but should NOT present in Table2 shld display


SELECT * FROM Table1
WHERE ORD_NUM NOT IN
(
SELECT ORD_NUM FROM Table2
);
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2285
Location: @my desk

PostPosted: Mon Jan 12, 2009 1:43 pm    Post subject:
Reply with quote

Quote:
does this query works
I think it should. What did you see when you ran a test?
Back to top
View user's profile Send private message
snehasai

New User


Joined: 14 Nov 2008
Posts: 23
Location: bangalore

PostPosted: Mon Jan 12, 2009 1:57 pm    Post subject:
Reply with quote

hi arun,
The query has ran fine... but my new requirement is..
I have 3 tables

Tab1 = INTRFC_HDR in which i need column field called APPLC_USER_KEY which will be like CC65239 for condition TRANS_NUM = A999

example: table display
APPLC_USER_KEY CHAR(30) PRIM CC65239
TRANS_NUM CHAR(4) PRIM A999

Tab2 = SR_XIN in which column fields called EXPORT_NUM =65239(of APPLC_USER_KEY last 5 digits) and DESK_ID=CC( of APPLC_USER_KEY
1st 2 digits) which makes total combined as ORD_NUM field in this table.

example:

EXPORT_NUM CHAR(5) PRIM 65239
DESK_ID CHAR(2) PRIM CC
ORD_NUM CHAR(8) PRIM 65239530

Tab3 = SR_INVOIC in which there is column field ORD_NUM.

so now i should write a query using 1st two tables to find out that CC65239 is present in tab3.SR_INVOIC or not using 1st 2 tables and condition TRANS_NUM = A999 for 1st table and this condition
EXPORT_NUM CHAR(5) PRIM 65239
DESK_ID CHAR(2) PRIM CC
ORD_NUM CHAR(8) PRIM 65239530

How to write that...can u help me out.
Back to top
View user's profile Send private message
snehasai

New User


Joined: 14 Nov 2008
Posts: 23
Location: bangalore

PostPosted: Mon Jan 12, 2009 2:05 pm    Post subject: expansion
Reply with quote

hi,
Actually.. if v provide the details for
EXPORT_NUM CHAR(5) PRIM 65239
DESK_ID CHAR(2) PRIM CC
in tab2.SR_XIN means it will provide the ORD_NUM CHAR(8) PRIM 65239530 so with this order number we shld see in tab3.SR_INVOIC that ORD_NUM which we got from tab2 is present in table3 or not.

Am clear with the requirement.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2285
Location: @my desk

PostPosted: Mon Jan 12, 2009 4:16 pm    Post subject:
Reply with quote

Quote:
Am clear with the requirement
I m afraid you're not. In order to explain it better, you could post some 5-6 rows from each table and the expected output using the "Code" tag rather than a single row.

Also before writing the sql, you should have an idea about the columns constituting the unique indexes built on these tables if there are any.
Back to top
View user's profile Send private message
snehasai

New User


Joined: 14 Nov 2008
Posts: 23
Location: bangalore

PostPosted: Mon Jan 12, 2009 4:45 pm    Post subject:
Reply with quote

this is some wt sql query.. for the conditions i have provided...

SELECT * FROM tab1.INTRFC_HDR A
WHERE NOT EXISTS ( SELECT 1 FROM
Tab2.SR_XIN B, Tab4.XIN C
WHERE B.EXPORT_NUM = C.EXPORT_NUM
AND B.DESK_ID = C.DESK_ID
AND SUBSTR(A.APPLC_USER_KEY, 1, 2) = B.DESK_ID
AND SUBSTR(A.APPLC_USER_KEY, 3, 5) = B.EXPORT_NUM)
AND A.TRANS_NUM = 'A999'

1)MY TAB1: If i provide trans_num as 'a999' i will get the app_user key value is first condition.

File-AID for DB2 Browse TAB1.INTRFC_HDR
COLUMN NAME TYPE(LEN) KEY COLUMN VALUE
--- ------------------ ---------- ---- ----+---10----+---20----+---30----+---40
001 APPLC_USER_KEY CHAR(30) PRIM CC65239
002 TRANS_NUM CHAR(4) PRIM A999
**********************BOTTOM OF DATA ***********************

2) My Tab2.SR_XIN in which if u give details for export number and desk id we will get order number
File-AID for DB2 Browse Tab2.SR_XIN
COLUMN NAME TYPE(LEN) KEY COLUMN VALUE
--- ------------------ ---------- ---- ----+---10----+---20----+---30
001 EXPORT_NUM CHAR(5) PRIM 65239
002 DESK_ID CHAR(2) PRIM CC
003 ORD_NUM CHAR(8) PRIM 65239530
004 HAZRD_MATL_IND CHAR(1) N
************************** BOTTOM OF DATA *****************

3) mY TAB3.XIN where condition should be checked for export number n desk id.
File-AID for DB2 Browse Tab3.XIN
COLUMN NAME TYPE(LEN) KEY COLUMN VALUE
--- ------------------ ---------- ---- ----+---10----+---20----+---30
001 EXPORT_NUM CHAR(5) PRIM 65239
002 DESK_ID CHAR(2) PRIM CC
003 XIN_LOCK_CD CHAR(1)
*******************BOTTOM OF DATA *******************

4) so now we got a order number in tab2.sr_XIN i.e. 65239530
so we shld check this order num is present in 4th table.
like...

File-AID for DB2 ------ BROWSE Selection Template -----No rows qualified
COMMAND ===> SCROLL ===> CSR
SSID: DB2T
Use EXEC to continue; SQL to view/edit SQL; SAVE ; SIZE
Table Name: Tab4.SR_INVOIC
Max Rows to Select ===> 2000 (* = ALL ROWS)

SEL COLUMN NAME TYPE(LEN) ORDER (A/D) WHERE More: >
--- ------------------ ------------------ -- - ----+----1----+----2----+
___ ORD_NUM CHAR(8) ='65239530'


so it will provide no rows qualified for that while checking if order not present and if it is present it shld display all order numbers in the query o/p.
Back to top
View user's profile Send private message
snehasai

New User


Joined: 14 Nov 2008
Posts: 23
Location: bangalore

PostPosted: Mon Jan 12, 2009 4:57 pm    Post subject:
Reply with quote

the query i have provided at top is only half part... as am unable to find out the next half part to get o/p...can u help me out

SELECT * FROM tab1.INTRFC_HDR A
WHERE EXISTS ( SELECT 1 FROM
Tab2.SR_XIN B, Tab4.XIN C
WHERE B.EXPORT_NUM = C.EXPORT_NUM
AND B.DESK_ID = C.DESK_ID
AND SUBSTR(A.APPLC_USER_KEY, 1, 2) = B.DESK_ID
AND SUBSTR(A.APPLC_USER_KEY, 3, 5) = B.EXPORT_NUM)
AND A.TRANS_NUM = 'A999'

The above query will give me all the APPLC_USER_KEY values like

---+---------+---------+---------+---------+---------+
APPLC_USER_KEY SGMENT_ID TRANS_NUM
---+---------+---------+---------+---------+---------+
CC65239 050 A999
CC75659 050 A999
CC81521 050 A999
CN32357 050 A999

so with that APPLC_USER_KEY we can get order numbers from tab2.So from tab2 order number we shld check in tab4 is it present or not by completing the query..
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 File Aid tool to compare numeric data balaji81_k Compuware & Other Tools 0 Tue Sep 26, 2017 3:35 am
No new posts Compare yesterday's date to the one o... migusd SYNCSORT 8 Fri Sep 22, 2017 11:35 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Identify top 30 big tables or indexes... ashek15 DB2 0 Fri Jun 16, 2017 10:01 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us