CODEWITHSUNDEEP
sqloracle-databasepivot
Eoin2211
Eoin2211

Reputation: 911

Issue With SQL Pivot Function

I have a SQL query where I am trying to replace null results with zero. My code is producing an error [1]: ORA-00923: FROM keyword not found where expected I am using an Oracle Database.

Select service_sub_type_descr,
nvl('Single-occupancy',0) as 'Single-occupancy',
nvl('Multi-occupancy',0) as 'Multi-occupancy'
From 
(select  s.service_sub_type_descr as service_sub_type_descr, ch.claim_id,nvl(ci.item_paid_amt,0) as item_paid_amt
from table_1 ch, table_" ci, table_3 s, table_4 ppd 
where ch.claim_id = ci.claim_id and ci.service_type_id = s.service_type_id 
and ci.service_sub_type_id = s.service_sub_type_id and ch.policy_no = ppd.policy_no) 
Pivot (
count(distinct claim_id), sum(item_paid_amt) as paid_amount For service_sub_type_descr IN ('Single-occupancy', 'Multi-occupancy')
)

Upvotes: 0

Views: 99

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

This expression:

 nvl('Single-occupancy',0) as 'Single-occupancy',

is using an Oracle bespoke function to say: If the value of the string Single-occupancy' is not null then return the number 0.

That logic doesn't really make sense. The string value is never null. And, the return value is sometimes a string and sometimes a number. This should generate a type-conversion error, because the first value cannot be converted to a number.

I think you intend:

coalesce("Single-occupancy", 0) as "Single-occupancy",

The double quotes are used to quote identifiers, so this refers to the column called Single-occupancy.

All that said, fix your data model. Don't have identifiers that need to be quoted. You might not have control in the source data but you definitely have control within your query:

coalesce("Single-occupancy", 0) as Single_occupancy,

EDIT:

Just write the query using conditional aggregation and proper JOINs:

select s.service_sub_type_descr, ch.claim_id,
       sum(case when service_sub_type_descr = 'Single-occupancy' then item_paid_amt else 0 end) as single_occupancy,
       sum(case when service_sub_type_descr = 'Multi-occupancy' then item_paid_amt else 0 end) as multi_occupancy
from table_1 ch join
     table_" ci
     on ch.claim_id = ci.claim_id join
     table_3 s
     on ci.service_type_id = s.service_type_id join
     table_4 ppd 
     on ch.policy_no = ppd.policy_no
group by s.service_sub_type_descr, ch.claim_id;

Much simpler in my opinion.

Upvotes: 3

R. Du
R. Du

Reputation: 633

for column aliases, you have to use double quotes !

don't use 

 as 'Single-occupancy'

but :

 as "Single-occupancy",

Upvotes: 1

Related Questions