IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Match substr(lit-no,1,4) with RETIR_NO


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
akshaiya

New User


Joined: 17 Sep 2005
Posts: 49

PostPosted: Fri Feb 20, 2009 4:37 am
Reply with quote

My requirement is to write a db2 query for the following. I tried but couldnt fetch the exact result.
Code:
Table1

pgm-name     lit-no char(8)   
char(8)   
A234567            9910LLC
B542189            0010ALB
C132590            0001TLI
D434564            9100LLV
E353457            1000LHE
F234561            9911LCD



Table2

Code:
RETIR_NO       INDEX-no S9(4) COMP   
SMALL INT

2347                 12
2189                 10
1325                 0011
4564                 100
353                  10
23                   99


By joining two tables, I want to match substr(lit-no,1,4) with RETIR_NO in the where clause condition

Code:
select A.pgm_name, A.lit_no, B.RETIR_NO, B.INDEX_NO
FROM TABLE1 A, TABLE2 B
WHERE
      SUBSTR(A.LIT_NO,1,4) BETWEEN '1' AND '9999'
AND   B.RETIR_NO    = SUBSTR(A.LIT_NO,1,4);

can anyone help how do we acheive acheive this?
Back to top
View user's profile Send private message
akshaiya

New User


Joined: 17 Sep 2005
Posts: 49

PostPosted: Fri Feb 20, 2009 5:17 am
Reply with quote

I would like to add one more on this.
If in table 2, I have 6 rows; Each and every LIT_NO in table1, I have to extract 6 rows. for eg., If I have 100 rows on table1 and 6 rows on table2, I need to create 600 rows only if i have a match on the table. This is to create input file for regression testing.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Fri Feb 20, 2009 5:42 am
Reply with quote

Hello,

There is probably something i am missing, but the posted tables/values appear to have no matches. . . icon_confused.gif icon_redface.gif

If the posted examples do match and there should be hits, please explain.

From the posted data (or some other input), please show what you want as the result/output. Hundreds of lines of output isn't needed, just enough to clarify the requirement.
Back to top
View user's profile Send private message
akshaiya

New User


Joined: 17 Sep 2005
Posts: 49

PostPosted: Fri Feb 20, 2009 6:54 am
Reply with quote

There are many hits. It will result in thousands of rows. Table 2 will generally contain less than 500 records and table 1 contains less than 200 records.

Table1
Code:

pgm-name     lit-no char(8)   
char(8)   
A234567            9910LLC
B542189            0010ALB
C132590            0001TLI
D434564            9100LLV
E353457            1000LHE
F234561             9911LCD



Tabe 2
Code:
RETIR_NO       INDEX-no S9(4) COMP   
SMALL INT

0010                 12
9910                 10
1000                 0011
9911                 100
353                  10
0001                99
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Fri Feb 20, 2009 8:20 am
Reply with quote

Hello,

Why was the original table data re-posted? Why is there no output shown as requested?

Keep in mind that your requirement is completely clear to you but may not be so clear to others who read the topic.

Quote:
I need to create 600 rows only if i have a match on the table
Quote:
There are many hits.
Please clarify. . .
Back to top
View user's profile Send private message
anandinmainframe

Active User


Joined: 31 May 2007
Posts: 171
Location: India

PostPosted: Fri Feb 20, 2009 9:32 am
Reply with quote

Hi akshaiya,
There should be a Unique key or foreign key for both the tables. but as per your example
Table1 has pgm-name lit-no char(8) and
Table2 has RETIR_NO INDEX-no.
so how can you join two different tables without a matching field.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Fri Feb 20, 2009 10:43 am
Reply with quote

Akshaiya,

You could try something like this,
Code:
select pgm-name
FROM TABLE1 A, TABLE2 B
WHERE SUBSTR(A.LIT_NO,1,4) = char(retir_no)


NOT TESTED!

Sushanth Bobby
Back to top
View user's profile Send private message
akshaiya

New User


Joined: 17 Sep 2005
Posts: 49

PostPosted: Fri Feb 20, 2009 11:30 am
Reply with quote

I changed the table2 data. hence posted the table again with different values in it and it looked like reposting.
original output reqd as below.
Code:
pgm-name     lit-no char(8)     RETIR_NO       INDEX-no
char(8)   
A234567            9910LLC             0010                 12
A234567            9910LLC             9910                 10
A234567            9910LLC             1000                 0011
A234567            9910LLC             9911                 100
A234567            9910LLC             353                  10
A234567            9910LLC            0001                 99

B542189            0010ALB             0010                 12
B542189            0010ALB             9910                 10
B542189            0010ALB             1000                 0011
B542189            0010ALB              9911                 100
B542189            0010ALB              353                  10
B542189            0010ALB             0001                 99

.
.
.
.



Every lit-no will match retir-no in table2. Please let me know if this is clear.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Fri Feb 20, 2009 11:49 am
Reply with quote

Akshaiya,

Hi!, You have 2 tables,
TABLE 1 has pgm-name & lit-no as columns with CHAR(8).
TABLE 2 has RETIR_NO(SMALLINT) & INDEX-no as one of the columns.

You want to join these 2 tables by LIT-NO of TABLE 1 & RETIR_NO of TABLE 2.
Only way LIT-NO can match with RETIR_NO is by extracting first 4 characters from it.

Did you try my previous query?
Did you get any errors ?

Sushanth
Back to top
View user's profile Send private message
akshaiya

New User


Joined: 17 Sep 2005
Posts: 49

PostPosted: Fri Feb 20, 2009 6:29 pm
Reply with quote

Thanks for all your answers.

I tried this query and it worked well.

Code:
SELECT                                                                 
         A.PGM_NAME                                                   
         ,A.LIT_NO                                                     
         ,B.RETIR_NO                                               
         ,B.INDEX_NO                                             
    FROM TABLE1 A,TABLE2 B
       WHERE SUBSTR(A.LIT_NO,1,4) BETWEEN '0' AND '9999' ;     
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Fri Feb 20, 2009 6:36 pm
Reply with quote

Thank You Akshaiya for posting the RESULT.
Back to top
View user's profile Send private message
anandinmainframe

Active User


Joined: 31 May 2007
Posts: 171
Location: India

PostPosted: Mon Feb 23, 2009 4:46 pm
Reply with quote

Hi,
Without mapping any fields how does it work? please let me know.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Mon Feb 23, 2009 4:50 pm
Reply with quote

Anand,

Can you elaborate more ?

Thank You,
Sushanth
Back to top
View user's profile Send private message
anandinmainframe

Active User


Joined: 31 May 2007
Posts: 171
Location: India

PostPosted: Mon Feb 23, 2009 4:56 pm
Reply with quote

sushanth bobby,
There is no common fields in both the table so how can we join two tables.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Mon Feb 23, 2009 5:12 pm
Reply with quote

Anand,

To join 2 tables, there has to be something common. Otherwise result will be a cartesian product of both the tables. Just go through the above posts, comparing columns in 2 tables of different data type,

Table 1 - lit-no CHAR(8)
Table 2 - RETIR_NO SMALL INT

Find something common & join the table.

Thank You,
Sushanth
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Mon Feb 23, 2009 7:59 pm
Reply with quote

Hello,

Quote:
I need to create 600 rows only if i have a match on the table.
I believe this contributed to the confusion as what the requirement recall is. We were looking for something from one table to "match" against the other.

This is why it saves a lot of time if not only a description of a requirement is posted, but also some representative input(s) and the output needed from that specific input.

And as Sushanth mentioned - thank you for posting your solution.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts JOINKEYS to match two input files on ... DFSORT/ICETOOL 3
No new posts Print next line strings when a condit... DFSORT/ICETOOL 9
No new posts Need to loop & extract from varia... CLIST & REXX 2
No new posts Match two files using multiple keys a... SYNCSORT 11
No new posts SUBSTR for Date functions DB2 4
Search our Forums:

Back to Top