Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Decode
Oracle Decode [message #36247] Tue, 13 November 2001 06:35 Go to next message
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 Go to previous messageGo to next message
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 Go to previous message
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).

----------------------------------------------------------------------
Previous Topic: Trigger - using select into
Next Topic: Insert statement incorrect
Goto Forum:
  


Current Time: Fri Mar 29 00:27:50 CDT 2024