Toad World® Forums

Issue with CASE statement


#1

You know when you write a CASE statement using the Calculated field feature and the results are not what you expect? Well, that is my current situation and I’m hoping for some help… See CASE statement and accompanying results.

In the accompanying results, notice the last 2 columns… The very last column is the calculated field “SAMPLE_TYPE”. It is supposed to have specific values based on the values of “LINE_TYPE”. However I am getting mixed results for the same value…

CASE.jpeg

RESULTS.jpeg


#2

What is the data type for column LINE_TYPE? Could it have extra characters in the field, for example a space after an S?
Does this query return any rows?
select line_type, length (line_type), dump (line_type)
from products
where line_type not in (‘S’, ‘S1’) ;


#3

The data as you show it seems odd but I have not seen any issues with case statements. The statement is executed by the database.

Try this, send the data to the editor and execute. The results should be the same. Save the file as a Toad Editor File (*.tef) and save the data in the file. Send this to me in private email so I can look at the issue more closely.

You might also copy and paste the SQL into SQL*Plus and see what occurs there.

Debbie
dpeabody@quest.com