Re: case when ... then expression

Careful other the > operators in your code...

In your examples , 'Word1' would be inserted if x is any number from 2 up..so 'Word2' would never get inserted, and 'Word3 would only get inserted if x=1;

If you reverse the order of the statements, all should be well..When using > as a test, check the largest desired number first, then go in descending order...  

OFederov_at_hds-mckhboc.com (Oleg Fedorov) wrote:

>There are many ways to do that.
>1)
>IF x>1 THEN INSERT INTO table(id, word) VALUES (ID,'Word1')
>ELSIF x>2 THEN INSERT INTO table(id, word) VALUES (ID,'Word2')
>ELSE INSERT INTO table(id, word) VALUES (ID,'Word3');
>
>2)
>DECLARE
>...
>BEGIN
>
>....
> EXECUTE IMMEDIATE 'INSERT INTO table(id, word) VALUES (ID,'||'CASE WHEN '||
> to_char(x)||'>1 THEN ''Word1'' WHEN '||
> to_char(x)||'>2 THEN ''Word2'' ELSE ''Word3'' END)';
>...
>END;
>3) using dbms_sql
>
>Regards,
>Oleg Fedorov
>OFederov_at_hds-mckhboc.com
>
>
>"Nicolas MUGNIER" <nunch_at_free.fr> wrote in message news:<IFLZ6.2240$_z5.1737748_at_nnrp5.proxad.net>...
>> Hi,
>>
>> How can I use the CASE WHEN ... THEN expression in a PL/SQL block ?
>>
>> I'v the following statement:
>> INSERT INTO table(id, word) VALUES (ID, CASE WHEN x>1 THEN 'Word1' WHEN x>2
>> THEN 'Word2' ELSE 'Word3' END);
>>
>> This statement works on command line but it doesn't in a PL/SQL block or in
>> a stored procedure.
>> Can somebody help me ?
>>
>> Thanks
>>
>> nunch

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World! -----== Over 80,000 Newsgroups - 16 Different Servers! =----- Received on Tue Jun 26 2001 - 08:44:43 CDT