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
 

 

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

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Match substr(lit-no,1,4) with RETIR_NO
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    Post subject:
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

Site Director


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

PostPosted: Fri Feb 20, 2009 5:42 am    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Fri Feb 20, 2009 8:20 am    Post subject:
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    Post subject: Reply to: DB2 query
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: 1013
Location: India

PostPosted: Fri Feb 20, 2009 10:43 am    Post subject:
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    Post subject:
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: 1013
Location: India

PostPosted: Fri Feb 20, 2009 11:49 am    Post subject:
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    Post subject:
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: 1013
Location: India

PostPosted: Fri Feb 20, 2009 6:36 pm    Post subject:
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    Post subject: Reply to: Match substr(lit-no,1,4) with RETIR_NO
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: 1013
Location: India

PostPosted: Mon Feb 23, 2009 4:50 pm    Post subject:
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    Post subject: Reply to: Match substr(lit-no,1,4) with RETIR_NO
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: 1013
Location: India

PostPosted: Mon Feb 23, 2009 5:12 pm    Post subject:
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

Site Director


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

PostPosted: Mon Feb 23, 2009 7:59 pm    Post subject:
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    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 Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
No new posts DFSORT to match two files and write John F Dutcher DFSORT/ICETOOL 6 Thu May 12, 2016 5:17 pm
No new posts Match and NoMatch using Sort raj_mainframe08 DFSORT/ICETOOL 1 Sat Feb 06, 2016 4:48 pm
No new posts MATCH records in 2 files rally DFSORT/ICETOOL 5 Tue Oct 13, 2015 1:40 pm
No new posts Whats the assambler code for SUBSTR()... jackzhang75 PL/I & Assembler 3 Sat Aug 29, 2015 1:59 am


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