case statement

[FALCONSEYE]

I have the following ( part of my function):

...
tempSalesPrice      varchar2  := '0';  
tempCode            number    := NULL;  
tempVariance        number    := 0;
...
 select cllc.code, variance_from_commissions, 
    ( case when tempSalesPrice != '0' then tempSalesPrice
           else ru.calc_sale_price 
           end) 
    into tempCod, tempVariance, tempSalesPrice
    from  mytable1 ru
    inner join mytable2 cllc 
    on cllc.pid = ru.pid
    where ... ;

when i compile this, i get

Warning: compiled but with compilation errors

How do i run a case statement and assign the returning value to a variable ?
thanks in advance.

http://www.fuzzysiberians.com

 

[FALCONSEYE]

sale price was stored as a varchar2. when i convert it to a number, it works.
thanks

to_number(ru.calc_sale_price )

http://www.fuzzysiberians.com

 

[Dagon]

I assume you are aware of how to look for details of the error using either "show errors" or select * from user_errors. In future, you should post the output of that rather than the compilation message.

 

[Beilstwh]

Also, unless there is an OVERWELMING reason, store numbers in a number, strings in a varchar2, and dates in a DATE. There is no reason that I can think of to store your sales price in a varchar!

Bill
Lead Application Developer
New York State, USA