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
 

 

DB2 Querry to select from two tables

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

New User


Joined: 19 Feb 2007
Posts: 9
Location: India

PostPosted: Mon May 12, 2008 1:18 pm    Post subject: DB2 Querry to select from two tables
Reply with quote

Could anyone please write a query for me ...

I need the Account numbers pressent in CC_CLNTCLNT_ACCT but not in CC_CLNT_ACCT tables, by validating these 3 cols present in both tables. ABA_BSB_STAT_I
ABA_BSB_BRCH_I
CC_ACCT_N

The account numbers is the combination of above 3 cols.
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10274
Location: italy

PostPosted: Mon May 12, 2008 1:26 pm    Post subject: Reply to: DB2 Querry
Reply with quote

a mild suggestion...

when asking gen it would be better to use a neuter/generic terminology
in order not to force the willing responders to start thinking in Your shop's jargon

it' s OK to call an account number account_numb, but we are not really interested
that in Your table the column name is x@#®t@ and Your table names are tb@kr5##¡ert

please please help us to help You icon_biggrin.gif
Back to top
View user's profile Send private message
the_gautam

Active User


Joined: 05 Jun 2005
Posts: 165
Location: Bangalore

PostPosted: Mon May 12, 2008 1:42 pm    Post subject:
Reply with quote

Code:

SELECT ACCNT_NUM FROM CC_CLNTCLNT_ACCT
WHERE CC_CLNTCLNT_ACCT.ABA_BSB_STAT_I = CC_CLNT_ACCT.ABA_BSB_STAT_I
AND CC_CLNTCLNT_ACCT.ABA_BSB_BRCH_I = CC_CLNT_ACCT.ABA_BSB_BRCH_I
AND CC_CLNTCLNT_ACCT.CC_ACCT_N = CC_CLNT_ACCT.CC_ACCT_N
AND ACCNT_NUM NOT IN CC_CLNT_ACCT;
[/code]
Back to top
View user's profile Send private message
esrraju

New User


Joined: 19 Feb 2007
Posts: 9
Location: India

PostPosted: Mon May 12, 2008 1:50 pm    Post subject: Reply to: DB2 Querry
Reply with quote

Sorry Gautam, This query will not fetch the desired rows.
Back to top
View user's profile Send private message
nabarundas

New User


Joined: 21 Jun 2007
Posts: 28
Location: pune

PostPosted: Mon May 12, 2008 2:03 pm    Post subject: Hii
Reply with quote

Hi esrraju,

Refer the bellow link http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/sqlp/rbafymstexjo.htm


Regards,
Nabarun
Back to top
View user's profile Send private message
sainathvinod

New User


Joined: 01 Apr 2008
Posts: 11
Location: Chennai

PostPosted: Mon May 12, 2008 2:34 pm    Post subject:
Reply with quote

Please try this query. I guess this should work:-

SELECT TAB1.ABA_BSB_STAT_I
,TAB1.ABA_BSB_BRCH_I
,TAB1.CC_ACCT_N
FROM CC_CLNTCLNT_ACCT TAB1
WHERE TAB1.ABA_BSB_STAT_I CONCAT TAB1.ABA_BSB_BRCH_I CONCAT TAB1.CC_ACCT_N
NOT IN
(SELECT TAB2.ABA_BSB_STAT_I CONCAT TAB2.ABA_BSB_BRCH_I CONCAT TAB2.CC_ACCT_N
FROM CC_CLNT_ACCT TAB2
)
Back to top
View user's profile Send private message
acevedo

Active User


Joined: 11 May 2005
Posts: 344
Location: Spain

PostPosted: Mon May 12, 2008 3:02 pm    Post subject: Re: Reply to: DB2 Querry
Reply with quote

esrraju wrote:
Sorry Gautam, This query will not fetch the desired rows.


why don't you 'play' with Gautam's query to get your desired rows?
Back to top
View user's profile Send private message
the_gautam

Active User


Joined: 05 Jun 2005
Posts: 165
Location: Bangalore

PostPosted: Tue May 13, 2008 6:34 am    Post subject:
Reply with quote

can esrraju please specify the requirement with some examples...
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: Tue May 13, 2008 6:47 am    Post subject:
Reply with quote

Hello,

Quote:
Sorry Gautam, This query will not fetch the desired rows.
A completely inappropriate reply. If a suggestion does not do what you want, you need to to say more than "it didn't work".

A better way to post your question is to show some sample rows from both tables (including rows that would be selected and rows that would not) as well as the expected results of the query when the sample data is processed. Yes, this means you have to put in a little effort rather than just asking someone to write code for you.
Back to top
View user's profile Send private message
esrraju

New User


Joined: 19 Feb 2007
Posts: 9
Location: India

PostPosted: Tue May 13, 2008 11:23 am    Post subject: Reply to: DB2 Querry to select from two tables
Reply with quote

This query will work fine, but my Query is too resource intensive to run under QMF since the table has 1 Million records. I have hit a resource limit. So is there any other way around to simplify this query.

SELECT ABA_BSB_STAT_I, ABA_BSB_BRCH_I,CC_ACCT_N FROM CC_CLNTCLNT_ACCT
WHERE
((ABA_BSB_STAT_I,ABA_BSB_BRCH_I, CC_ACCT_N) NOT IN (SELECT ABA_BSB_STAT_I,ABA_BSB_BRCH_I, CC_ACCT_N FROM CC_CLNT_ACCT )
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: Tue May 13, 2008 11:29 am    Post subject:
Reply with quote

Hello,

As was mentioned before
Quote:
show some sample rows from both tables (including rows that would be selected and rows that would not) as well as the expected results of the query when the sample data is processed


If you are unwilling to provide requested info, we may need to clean out the clutter. . .
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 SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am
No new posts Identify top 30 big tables or indexes... ashek15 DB2 0 Fri Jun 16, 2017 10:01 am
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts SPUFI -- Joining 3 tables – data in... Sysaron DB2 2 Wed Mar 08, 2017 4:18 am
No new posts To know activities on db2 tables ashek15 DB2 4 Fri Mar 03, 2017 11:23 pm


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