MVSFORUMS.com

rajeev5174 · Beginner Joined: 05 Jun 2007 Posts: 25 Topics: 10

Hi,

I have 2 tables t1 and t2 , here i need to compare the tables and pick those records which do not a empno in T1

table 1

NASCAR9 · Posted: Mon Mar 26, 2012 9:43 am Post subject:

If I understand you correctly this should work:

rajeev5174 · Beginner Joined: 05 Jun 2007 Posts: 25 Topics: 10

ok. by this we will not be getting the desired o/p as "2" , and also none of the fields will have null values.

Thanks,
Raj

kolusu · Posted: Mon Mar 26, 2012 11:05 am Post subject:

rajeev5174,

Please use meaningful titles to your questions. It would have been nice if you had showed the desired output.

Assuming you want the rows from table1 that does not have a matching empno from table2 then you can use the following untested sql

rajeev5174 · Beginner Joined: 05 Jun 2007 Posts: 25 Topics: 10

ok Kolusu, the issue here is the datatype of one is binary and the other is character , i used the cast function, it is saying the "OPERANDS OF AN ARITHMETIC OR COMPARISON OPERATION ARE NOT COMPARABLE "

Thanks,
Raj

kolusu · Posted: Mon Mar 26, 2012 12:22 pm Post subject:

rajeev5174 · Beginner Joined: 05 Jun 2007 Posts: 25 Topics: 10

ok. empno in table (t1) is binary and that empno in table t2 is character of length 30.

Regards
Ron

kolusu · Posted: Mon Mar 26, 2012 12:55 pm Post subject:

rajeev5174 · Beginner Joined: 05 Jun 2007 Posts: 25 Topics: 10

ok kolusu.
The Empno in T1 is S9(09) COMP & in the 2'nd table the data is x(30).
The data in t2 is shown as '000000008'

Regards
Raj

dbzTHEdinosauer · Posted: Tue Mar 27, 2012 7:55 am Post subject:

still don't know if:
1. left or right justified
2. is the values decimal data, or is a binary field moved to a char column.

if it would not bother you to much, Raj, to provide answers to above questions.
_________________
Dick Brenholtz
American living in Varel, Germany

kolusu · Posted: Tue Mar 27, 2012 10:30 am Post subject:

rajeev5174,

You are wasting your time as well as our time. I am assuming the character data is left justified in the character column and it is just 10 bytes long.

rajeev5174 · Beginner Joined: 05 Jun 2007 Posts: 25 Topics: 10

Thanks kolsu, the issue is query is hanging as the tables are having more 7 billons reords.. so we need to unload to flat file and do a file matching..that is my thought

Regards
Rajeev V

kolusu · Posted: Thu Mar 29, 2012 10:14 am Post subject:

rajeev5174 · Beginner Joined: 05 Jun 2007 Posts: 25 Topics: 10

Apologies for spelling your name incorrectly.. The issue i am facing is that the query is hanging when i ran in the system, working fine the data size is of a smaller size , so i believe we may need to unload to a flat file and using a easytrive or cobol match logic we can find the unmatched empno's

Regards
Raj

papadi · Supermod Joined: 20 Oct 2009 Posts: 594 Topics: 1

Anything done with 7 billion entries is going to take time. . .

If you unload compare data, and all that is wanted is the list of "missing key values", only unload the keys and make sure that both are in the same data format (which will allow direct cmpares with no data conversion in the compare operation. This will also give the smallest sets of "compare data" which will both run faster and use less transient dasd space.
_________________
All the best,

di