Case Statement in SQL

[Karen99]

Why does the following work in SQL*Plus and not in a store procedure ?

select sum(case when VCICATEGORY = 'Very Good' then
processendkm-processstartkm
end) LenVCIVG,
sum(case when VCICATEGORY = 'Good' then
processendkm-processstartkm
end) LenVCIG,
sum(case when VCICATEGORY = 'Fair' then
processendkm-processstartkm
end) LenVCIF,
sum(case when VCICATEGORY = 'Poor' then
processendkm-processstartkm
end) LenVCIP,
sum(case when VCICATEGORY = 'Very Poor' then
processendkm-processstartkm
end) LenVCIVP
into LenVCIVG,LenVCIG,LenVCIF,LenVCIP,LenVCIVP
from processhistory;

Into clause are naturally only added for store procedure and taken out for SQL*Plus.

Thanks
Karen

 

[sem]

CASE clause is not implemented in 8i PL/SQL engine. As a workaround you may use dynamic sql (or upgrade to 9i)

Regards, Dima

http://seminihin.narod.ru

 

[Karen99]

I've forgot ... This is the error I'm getting in store procedure :

PLS-00103: Encountered the symbol "CASE" when expecting one of the following:

( - + all mod null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current di

 

[Karen99]

Can't upgrade to 9i.

Will I have any performance problems if I use dynamic sql ?

What will be the best :

Braking this up in smaller queries
or using dynamic sql ?

Thanks
Karen

 

[sem]

Use DECODE

Regards, Dima

http://seminihin.narod.ru

 

[Karen99]

And in the following scenario :

select
sum(case when PROCESSTHICK <= 50 and PROCESSTHICK > 25 then
processendkm-processstartkm
end) LenThick50,
sum(case when PROCESSTHICK <= 75 and PROCESSTHICK > 50 then
processendkm-processstartkm
end) LenThick75,
sum(case when PROCESSTHICK <= 100 and PROCESSTHICK > 75 then
processendkm-processstartkm
end) LenThick100,
sum(case when PROCESSTHICK <= 125 and PROCESSTHICK > 100 then
processendkm-processstartkm
end) LenThick125,
sum(case when PROCESSTHICK <= 150 and PROCESSTHICK > 125 then
processendkm-processstartkm
end) LenThick150,
sum(case when PROCESSTHICK > 150 then
processendkm-processstartkm
end) LenThickGr150
from processhistory;

As far as I know decode does not work with <>.
Then I repeat my question :

Will I have any performance problems if I use dynamic sql ?

What will be the best :

Braking this up in smaller queries
or using dynamic sql ?

Regards
Karen

 

[sem]

Obviously dynamic sql needs more, because the statement have to be parsed each time. But do you need to execute it 100 times per second? If not, don't even think about it, because the difference is quite insignificant. In any case, multiple queries need more resources.

As for your new query, DECODE may be used against PROCESSTHICK/25

Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

Karen,

Correct me if I'm wrong:

Given: You want to tally PROCESSTHICK counts by thickness into PL/SQL variables. You cannot upgrade to 9i.

Why not just spin through a PL/SQL CURSOR, incrementing the appropriate count variables. It will be approximately the same speed as your SELECT statement.

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:02 (25Jun04) UTC (aka "GMT" and "Zulu"), 10:02 (25Jun04) Mountain Time)