View previous topic :: View next topic
|
Author |
Message |
akshaiya
New User
Joined: 17 Sep 2005 Posts: 49
|
|
|
|
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 |
|
|
akshaiya
New User
Joined: 17 Sep 2005 Posts: 49
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
There is probably something i am missing, but the posted tables/values appear to have no matches. . .
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 |
|
|
akshaiya
New User
Joined: 17 Sep 2005 Posts: 49
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
anandinmainframe
Active User
Joined: 31 May 2007 Posts: 171 Location: India
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
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 |
|
|
akshaiya
New User
Joined: 17 Sep 2005 Posts: 49
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
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 |
|
|
akshaiya
New User
Joined: 17 Sep 2005 Posts: 49
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Thank You Akshaiya for posting the RESULT. |
|
Back to top |
|
|
anandinmainframe
Active User
Joined: 31 May 2007 Posts: 171 Location: India
|
|
|
|
Hi,
Without mapping any fields how does it work? please let me know. |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Anand,
Can you elaborate more ?
Thank You,
Sushanth |
|
Back to top |
|
|
anandinmainframe
Active User
Joined: 31 May 2007 Posts: 171 Location: India
|
|
|
|
sushanth bobby,
There is no common fields in both the table so how can we join two tables. |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
|