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
 

 

How to link 2 tables using substring values or alias fields?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
noorkh

New User


Joined: 06 Mar 2006
Posts: 76
Location: Chennai

PostPosted: Mon Sep 27, 2010 5:45 pm    Post subject: How to link 2 tables using substring values or alias fields?
Reply with quote

Hi,

here is my requirement:

Table 1: Values are as explained below:
Descr Post_code
Name1 NR1 2DA
Name2 E6 2EA
Name3 NR1 1XY

Table 2: Values are as explained below:
Post code
NR1 2DA

1. I need to make search from TABLE2 and need to trim value into length of 3 but it would always return 1 row as it is mentioned above.
2. I need to make search from Table1 and need to trim value into length of 3 but it would be always more than 1.
3. I need to use trimmed value from 1 and 2 to fetch single matching record and show output of Postcode and descrip from Table1.

Can i do this in single query?

I want output to be displayed as
NR1 2DA Name1.
Back to top
View user's profile Send private message

Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1755
Location: Bloomington, IL

PostPosted: Mon Sep 27, 2010 8:51 pm    Post subject: Re: How to link 2 tables using substring values or alias fie
Reply with quote

noorkh wrote:
Can i do this in single query?

Certainly; I recommend that, as you hinted at in the title, you use the SUBSTR function in the WHERE clause.
Back to top
View user's profile Send private message
noorkh

New User


Joined: 06 Mar 2006
Posts: 76
Location: Chennai

PostPosted: Tue Sep 28, 2010 9:42 am    Post subject: Reply to: How to link 2 tables using substring values or ali
Reply with quote

How would you link 2 tables using their substring values( substring values derived after going through CASE condition)?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Tue Sep 28, 2010 4:33 pm    Post subject:
Reply with quote

Noor,

why don't you show us what you have developed so far.

you keep mentioning 'trim to length of 3',
yet the column displays 7 char of data,
and to achieve the match (join) that you are describing, you need to use 7.

and why the need for the CASE?

why not select post_code, desc from table-1 where post_code exists (select post_code from table 2 where table-1.post_code = table-2.post_code)
Back to top
View user's profile Send private message
pawaria

New User


Joined: 06 Apr 2006
Posts: 7
Location: Noida

PostPosted: Wed Sep 29, 2010 3:16 pm    Post subject:
Reply with quote

And if want to have post code trimmed to length of 3 in both tables

Select substr(post_code,1,3), Desc from table-1 where post_code exists (select post_code from table-2 where substr(table-1.post_code,1,3) = substr(table-2.post_code,1,3))

Guys pls let me know if made any mistakes in above query

Thanks
Pawaria
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 Sorting of hex values Saurabh_mi DFSORT/ICETOOL 11 Thu May 25, 2017 3:49 pm
No new posts Validate date and numeric fields and ... Rick Silvers DFSORT/ICETOOL 6 Thu May 11, 2017 6:51 pm
No new posts Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 pm
No new posts Sum Fields ballaswaroop DFSORT/ICETOOL 4 Tue May 02, 2017 11:07 am
No new posts DIrect link of dynam/nodynam nkjain87 COBOL Programming 2 Mon Apr 24, 2017 6:23 pm


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