Home » SQL & PL/SQL » SQL & PL/SQL » Need a SQL Expert to solve my SQL dilema
Need a SQL Expert to solve my SQL dilema [message #193079] Thu, 14 September 2006 16:20 Go to next message
wendybme
Messages: 6
Registered: September 2006
Location: Saint Louis, Missouri
Junior Member
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. Embarassed Mad Embarassed
Re: Need a SQL Expert to solve my SQL dilema [message #193081 is a reply to message #193079] Thu, 14 September 2006 17:30 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member

Can we get soem Create table staements , sample data and the expected output for that data .
Re: Need a SQL Expert to solve my SQL dilema [message #193257 is a reply to message #193079] Fri, 15 September 2006 08:06 Go to previous messageGo to next message
wendybme
Messages: 6
Registered: September 2006
Location: Saint Louis, Missouri
Junior Member
TAX_REF
TAX_CODE     PAYER_CODE     TAX_TYPE[/b]
FED            E             SS
SOC            R             SS
ST             B             SUTA
FIN            E             FUE
WC             R             WCOM

ACCT_TAX_REF
TAX_CODE     PAYER_CODE     ACCT_NBR[/b]
FED            E             107.2
SOC            R             111.8
ST             E             389.1
ST             R             456.2
WC             R             333.1

ACCT_MSTR
ACCT_NBR     ACCT_DESC[/b]
107.2        EMP BENEFITS
111.8        IDENTIFICATION
389.1        MISCELLANEOUS
333.1        TESTING
456.2        FEDERAL INCOME

RESULT SET DESIRED UPON SUCCESSFUL SQL
1ST RECORD:
TAX_CODE: FED
PAYER_CODE: E
TAX_TYPE: SS
EMP_ACCT_NBR: 107.2
EMP_ACCT_DESC: EMP BENEFITS
EMPR_ACCT_NBR: 0
EMPR_ACCT_DESC:

2ND RECORD:
TAX_CODE: SOC
PAYER_CODE: R
TAX_TYPE: SS
EMP_ACCT_NBR: 0
EMP_ACCT_DESC:
EMPR_ACCT_NBR: 111.8
EMPR_ACCT_DESC: IDENTIFICATION

3RD RECORD:
TAX_CODE: ST
PAYER_CODE: B
TAX_TYPE: SUTA
EMP_ACCT_NBR: 389.1
EMP_ACCT_DESC: MISCELLANEOUS
EMPR_ACCT_NBR: 456.2
EMPR_ACCT_DESC: FEDERAL INCOME

etc.....
Hope this helps in understanding what I'm talking about. Thanks for looking at it.

[Updated on: Fri, 15 September 2006 08:08]

Report message to a moderator

Re: Need a SQL Expert to solve my SQL dilema [message #193259 is a reply to message #193081] Fri, 15 September 2006 08:13 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
srinivnp wrote on Thu, 14 September 2006 18:30


Can we get soem Create table staements , sample data and the expected output for that data .


Please satisfy this requirement first.
Re: Need a SQL Expert to solve my SQL dilema [message #193269 is a reply to message #193079] Fri, 15 September 2006 09:01 Go to previous messageGo to next message
wendybme
Messages: 6
Registered: September 2006
Location: Saint Louis, Missouri
Junior Member
That's what I just did: give you sample data and expected outcome. What else are you needing? I don't understand.
Re: Need a SQL Expert to solve my SQL dilema [message #193270 is a reply to message #193257] Fri, 15 September 2006 09:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
select tr.tax_code
      ,tr.payer_code
      ,tr.tax_type
      ,max(decode(atr.payer_code,'E',nvl(emp.acct_nbr,0),0))  emp_acct_nbr
      ,max(decode(atr.payer_code,'E',emp.acct_Desc,''))       emp_acct_desc
      ,max(decode(atr.payer_code,'R',nvl(empr.acct_nbr,0),0)) empr_acct_nbr
      ,max(decode(atr.payer_code,'R',empr.acct_Desc,''))      empr_acct_desc
from   tax_Ref tr
      ,acct_Tax_ref  atr
      ,acct_mstr     emp
      ,acct_mstr     empr
where  tr.tax_code = atr.tax_code
and    (tr.payer_code = atr.payer_code or tr.payer_code = 'B')
and    atr.acct_nbr = emp.acct_nbr(+) 
and    atr.acct_nbr = empr.acct_nbr(+)
group by tr.tax_code
        ,tr.payer_code
        ,tr.tax_type;

TAX_C P TAX_T EMP_ACCT_N EMP_ACCT_DESC                  EMPR_ACCT_ EMPR_ACCT_DESC
----- - ----- ---------- ------------------------------ ---------- ------------------------------
ST    B SUTA  389.1      MISCELLANEOUS                  456.2      FEDERAL INCOME
WC    R WCOM  0                                         333.1      TESTING
FED   E SS    107.2      EMP BENEFITS                   0
SOC   R SS    0                                         111.8      IDENTIFICATION
Re: Need a SQL Expert to solve my SQL dilema [message #193279 is a reply to message #193269] Fri, 15 September 2006 09:44 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
For future reference wendy:

create table x (a number, b char(2000), d date);
insert into x values(1,'Bungle',sysdate);
insert into x values(2,'George',sysdate);
insert into x values(3,'Zippy',sysdate);
insert into x values(4,'Jane',sysdate);
insert into x values(5,'Rod',sysdate);


Is how you supply your data, rather than in plain text. Smile

Jim
Re: Need a SQL Expert to solve my SQL dilema [message #193282 is a reply to message #193079] Fri, 15 September 2006 09:48 Go to previous messageGo to next message
wendybme
Messages: 6
Registered: September 2006
Location: Saint Louis, Missouri
Junior Member
Thank you Jim. I'm not a dba though, just a lowly programmer. Therefore I didn't know the syntax to create a table. Embarassed
Re: Need a SQL Expert to solve my SQL dilema [message #193283 is a reply to message #193282] Fri, 15 September 2006 09:49 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
check out http://tahiti.oracle.com for documentation on all the syntax you'll ever need Smile

Jim
Re: Need a SQL Expert to solve my SQL dilema [message #193298 is a reply to message #193079] Fri, 15 September 2006 10:31 Go to previous messageGo to next message
wendybme
Messages: 6
Registered: September 2006
Location: Saint Louis, Missouri
Junior Member
JRowbottom: THANK YOU, THANK YOU, THANK YOU!!! It works and is a VERY beautiful thing! Thanks so much for your help. I did not know anything about this "decode" syntax you've used. It is fabulous. You have a great day! Cool
Re: Need a SQL Expert to solve my SQL dilema [message #193325 is a reply to message #193282] Fri, 15 September 2006 12:53 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
wendybme wrote on Fri, 15 September 2006 10:48

Thank you Jim. I'm not a dba though, just a lowly programmer. Therefore I didn't know the syntax to create a table. Embarassed



And just to add to Jim's suggestions, are you saying you did not know the syntax for INSERT either? If you would like us to do work for you, you should at least supply INSERT statements so we do not have to spend time writing them ourselves. Just seeing the data makes us have to do all the work.
Re: Need a SQL Expert to solve my SQL dilema [message #193329 is a reply to message #193079] Fri, 15 September 2006 13:09 Go to previous message
wendybme
Messages: 6
Registered: September 2006
Location: Saint Louis, Missouri
Junior Member
Wow. I was just asking a sql question. Not a database creation question. Jim was quite nice in explaining something to me. And yes, I do know how to INSERT. You, Joy, I think crossed the line a bit and are not very nice. There was no need for you to further post and be snotty when Jim covered everything and, more importantly, my question was already answered.

[Updated on: Fri, 15 September 2006 13:09]

Report message to a moderator

Previous Topic: Relation between sequence and column (not the autoincrement problem)
Next Topic: Trigger Problem
Goto Forum:
  


Current Time: Tue Dec 06 14:15:09 CST 2016

Total time taken to generate the page: 0.17298 seconds