Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Decode
Oracle Decode [message #36247] |
Tue, 13 November 2001 06:35 |
Jay Quinn
Messages: 1 Registered: November 2001
|
Junior Member |
|
|
Hi, I am having a hard time trying to work out the decode issue with this code. It keeps giving me an error on row 9, says invalid column name yet it is valid. I can hack it and it will end up saying that it is invalid on some otehr valid column name. Can some Oracle SQL guru please take a look as see if there is something obvious I'm missing.
select decode (customer_type, 'I', 'Consumer', 'B', 'Business','NONE'),
cycle_run_year,
cycle_run_month,
decode (jurisdiction, 'ILUS', 'USIL', 'ILIL', 'Unknown', sum (reported_time_dur)),
decode (jurisdiction, 'ASEL', 'ASAL', 'Unknown', sum (reported_time_dur)),
decode (jurisdiction, 'ESEL', 'ESAL', 'Unknown', sum (reported_time_dur)),
decode (jurisdiction, 'ILUS', 'USIL', 'ILIL', 'Unknown', sum (charge_amt)),
decode (jurisdiction, 'ASEL', 'ASAL', 'Unknown', sum (charge_amt)),
decode (jurisdiction, 'ESAL', 'ESAL', 'Unknown', sum (charge_amt)) end
-- count (distinct(billing_no))
-- count (distinct(aaa.connect_date)) "Calls"
from (select customer_type,
cycle_run_year,
cycle_run_month,
jurisdiction,
-- count (distinct(billing_no)),
sum (reported_time_dur),
sum (charge_amt)
-- count (connect_date) "Calls"
from us0610
where cancel_record_ind <> 'U'
group by 1,2,3,4) aaa
group by 1,2,3
order by 1,2,3
/
----------------------------------------------------------------------
|
|
|
Re: Oracle Decode [message #36258 is a reply to message #36247] |
Tue, 13 November 2001 09:47 |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
I'd avoid using "reserved words" as column aliases but a simple test in my Oracle version is OK with using "END". Have you tried a different alias??
----------------------------------------------------------------------
|
|
|
Re: Oracle Decode [message #36261 is a reply to message #36247] |
Tue, 13 November 2001 10:28 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
Jay, I have some concerns about your query in general, but your specific error probably is caused by the fact that this line:
count (distinct(AAA.connect_date)) "CALLS"
is referencing AAA.connect_date instead of AAA.calls (which is how you are aliasing it in the inline view).
----------------------------------------------------------------------
|
|
|
Goto Forum:
Current Time: Sat Jun 15 17:04:38 CDT 2024
|