Home » SQL & PL/SQL » SQL & PL/SQL » Trying to return 1 column but with multiple alias's (PL/SQL Developer version 7.1.1)
Trying to return 1 column but with multiple alias's [message #322951] Mon, 26 May 2008 23:06 Go to next message
wcbssup
Messages: 2
Registered: May 2008
Junior Member
Hi all,

Hopefully everything I have included below will assist you in assisting me and thanks in advance for reading this incredibly long post.

I am trying to extract data that will be loaded (on a daily basis) into another application to support a business tool that will soon be used by our business. As such there are certain criteria that I need to meet in order to load each extract file successfully. The sample below only contains a single CLIENT_REF records, but I could potentially be retrieving between 10,000 and 15,000 each time I run this SQL.

What I am trying to do is basically have the below data extracted (into a .CSV file) as a single line.

I have included a sample of the primary table I am querying, my desired end result, and the SQL I have written (but with limited success).

The SQL I have written only works if the CLIENT_REF record has all the PAYMENT_CODEs stored against it. The problem, is that not every CLIENT_REF record will have the same PAYMENT_CODEs.
In this situation I am after PAYMENT_CODEs '1', '8' ,'4' (which represent the MEDICAL_COSTS, OTHER_COSTS, LEGAL_COSTS respectively in my desired results sample), but the next CLIENT_REF may only have PAYMENT_CODEs '1' and '4'. In this situation my query won't return any records. I understand why no records are being returned in this situation, but my question is how can I get around this? Is my SQL completely wrong and should be it totally re-written? If so, does anyone have any suggestions?

Any help would be greatly appreciated.


PRIMARY TABLE BEING QUERIED (cp_master.cl_client_financial)

CLIENT_REF PAYMENT_CODE PAID_TO_DATE
102023299122 1
102023299122 3 30857.50
102023299122 4 8546.72
102023299122 5 3349.50
102023299122 6 1470.14
102023299122 8 888.50
102023299122 10 0.00

DESIRED END RESULT
CLIENT_REF MEDICAL_COSTS OTHER_COSTS LEGAL_COSTS
102023299122 30857.5 888.5 8546.72


MY SQL

select c.client_ref,
AA.paid_to_date as medical_costs,
BB.paid_to_date as other_costs,
CC.paid_to_date as legal_costs

from cp_master.cl_client c,

(select paid_to_date from cp_master.cl_client_financial cf, cp_master.cl_cient c
where c.client_ref = cf.client_ref and cf.payment_code ='1'
and c.client_ref = '102023299122')AA,

(select paid_to_date from cp_master.cl_client_financial cf, cp_master.cl_cient c
where c.client_ref = cf.client_ref and cf.payment_code ='8'
and c.client_ref = '102023299122')BB,

(select paid_to_date from cp_master.cl_client_financial cf, cp_master.cl_cient c
where c.client_ref = cf.client_ref and cf.payment_code ='4'
and c.client_ref = '102023299122')CC

where c.client_ref = '102023299122'
Re: Trying to return 1 column but with multiple alias's [message #322953 is a reply to message #322951] Mon, 26 May 2008 23:14 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
First thing that comes to my mind is what is sometimes called

Quote:
CONDITIONAL SUMMATION

Something like this:

select client_ref,sum(decode(payment_code,1,paid_to_date,null)) medical_costs
                 ,sum(decode(...)) ...
                 ,sum(decode(...)) ...
from ...
group by client_ref
/

Get the idea?

Kevin
Re: Trying to return 1 column but with multiple alias's [message #322984 is a reply to message #322951] Tue, 27 May 2008 00:16 Go to previous message
wcbssup
Messages: 2
Registered: May 2008
Junior Member
That worked perfectly. Exactly what I was after.

Thanks very much for the help.

Ben.
Previous Topic: How to Read Registry from Oracle
Next Topic: Oracle
Goto Forum:
  


Current Time: Mon Dec 05 03:14:04 CST 2016

Total time taken to generate the page: 0.07791 seconds