-
Need help with tricky inner select on SQL
First, I have 3 tables:
TAX_REF
TAX_CODE
PAYER_CODE
TAX_TYPE
ACCT_TAX_REF
TAX_CODE
PAYER_CODE
ACCT_NBR
ACCT_MSTR
ACCT_NBR
ACCT_DESC
For the resultset I return, I would like to have the following columns:
TAX_CODE
PAYER_CODE
TAX_TYPE
EMP_ACCT_NBR
EMP_ACCT_DESC
EMPR_ACCT_NBR
EMPR_ACCT_DESC
Here's the scoop:
I need ALL the records from the TAX_REF table. I need to link to the ACCT_TAX_REF table by TAX_CODE and PAYER_CODE in order to retrieve the ACCT_NBR. Further I have to link to the ACCT_MSTR in order to also bring back the ACCT_DESC for the ACCT_NBR. The problem is with the PAYER_CODE field.
TAX_REF.PAYER_CODE can have values of E, R, or B. The ACCT_TAX_REF.PAYER_CODE can only have values of E or R. Therefore I cannot just do a simple join.
What I want to happen:
If the TAX_REF.PAYER_CODE = E, take the ACCT_TAX_REF.ACCT_NBR and put it into the returning resultset’s EMP_ACCT_NBR field.
If the TAX_REF_PAYER_CODE = R, take the ACCT_TAX_REF.ACCT_NBR and put it into the returning resultset’s EMPR_ACCT_NBR field.
If the TAX_REF.PAYER_CODE = B, I need the query to get both the E and the R values from the ACCT_TAX_REF table’s PAYER_CODE (for that TAX_CODE) and put the ACCT_NBR into the matching EMP_ACCT_NBR (if E) or EMPR_ACCT_NBR (if R) fields.
I hope I’ve explained this well. I know there should be some kind of inner select, but I’m not sure how to go about it with that “B” code throwing a monkey wrench into the mix.
Thanks for your help.
Here’s one attempt:
Select t.TAX_CODE,
t.PAYER_CODE,
t.TAX_TYPE,
nvl (min (b.ACCT_NBR),0) AS EMP_ACCT_NBR,
c.ACCT_DESC AS EMP_ACCT_DESC
from TAX_REF t
left outer join ACCT_TAX_REF b on t.TAX_CODE = b.TAX_CODE and
t.PAYER_CODE = b.PAYER_CODE
left outer join ACCT_MSTR c on b.ACCT_NBR = c.ACCT_NBR
group by t.TAX_CODE,
t.PAYER_CODE,
t.TAX_TYPE,
EMP_ACCT_DESC
That, of course, doesn’t put my “R” values into the EMPR_ACCT_NBR and EMPR_ACCT_DESC fields though. Nor does it handle the “B” scenario. Help.
-
I'm v.confused . . .
Is this the join condition?
on t.TAX_CODE = b.TAX_CODE
and (t.PAYER_CODE = b.PAYER_CODE OR t.PAYER_CODE = 'B')
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
The join is indeed t.TAX_CODE = b.TAX_CODE with the conditional join on PAYER_CODE. If the value of PAYER_CODE is either E or R, then all is fine, but if t.PAYER_CODE = B, then I need to go the the ACCT_TAX_REF table twice basically so I can retrieve both the PAYER_CODE values of E and R for that matching TAX_CODE.
-
Use Inner SQL at col level.
Code:
select a.c1, a.c2,
(select b.c1 from table b
where b.c2 = a.c2 and other condition),
(select c.c3 from table c
where c.c3 = a.c3 and other condition)
from table a
/
Also read this:
http://tkyte.blogspot.com/2005/06/ho...questions.html
Tamil
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|