|
View previous topic :: View next topic
|
| Author |
Message |
esrraju
New User
Joined: 19 Feb 2007
Posts: 9
Location: India
|
|
|
|
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 |
|
 |
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
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  |
|
| Back to top |
|
|
the_gautam
Active User
Joined: 05 Jun 2005
Posts: 165
Location: Bangalore
|
|
|
|
| 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 |
|
|
esrraju
New User
Joined: 19 Feb 2007
Posts: 9
Location: India
|
|
|
|
| Sorry Gautam, This query will not fetch the desired rows. |
|
| Back to top |
|
|
nabarundas
New User
Joined: 21 Jun 2007
Posts: 28
Location: pune
|
|
| Back to top |
|
|
sainathvinod
New User
Joined: 01 Apr 2008
Posts: 11
Location: Chennai
|
|
|
|
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 |
|
|
acevedo
Active User
Joined: 11 May 2005
Posts: 344
Location: Spain
|
|
|
|
| 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 |
|
|
the_gautam
Active User
Joined: 05 Jun 2005
Posts: 165
Location: Bangalore
|
|
|
|
| can esrraju please specify the requirement with some examples... |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
esrraju
New User
Joined: 19 Feb 2007
Posts: 9
Location: India
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
|
|
