sql query to compare tables

snehasai · New User Joined: 14 Nov 2008 Posts: 23 Location: bangalore

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.

snehasai · New User Joined: 14 Nov 2008 Posts: 23 Location: bangalore

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

Arun Raj · Posted: Mon Jan 12, 2009 1:43 pm

snehasai · New User Joined: 14 Nov 2008 Posts: 23 Location: bangalore

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.

snehasai · New User Joined: 14 Nov 2008 Posts: 23 Location: bangalore

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.

Arun Raj · Posted: Mon Jan 12, 2009 4:16 pm

snehasai · New User Joined: 14 Nov 2008 Posts: 23 Location: bangalore

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.

snehasai · New User Joined: 14 Nov 2008 Posts: 23 Location: bangalore

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