?
Solved

Case statement returning unexpected result

Posted on 2015-01-07
8
Medium Priority
?
228 Views
Last Modified: 2015-01-07
Gurus,

I'm using the "case" function in one of my queries and it's returning an unexpected result.

Statement:
(SELECT DISTINCT SPECIAL_CHAR(                                    
    CASE AFLFLDC                                                
      WHEN NULL                                                  
      THEN NULL                                                  
      ELSE '$'                                                  
        ||aflfldc/100                                            
    END,'fm99999.00')                                            
  FROM AFL , ART                                                      
  WHERE AFLFTYKEYI = 52                                          
  AND AFLARTKEYI   = ARTKEYI                                    
  AND aflkavkeyi   = artkavkeyi                                  
  )) AS CALCULATED_WAS;

Returns: $
Should Return: null

What am I doing wrong?
0
Comment
Question by:xbox360dp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40536372
What is the data type of AFLFLDC?

Try this:
(SELECT DISTINCT SPECIAL_CHAR(                                    
     CASE trim(AFLFLDC)
       WHEN NULL                                                  
       THEN NULL      
...
0
 

Author Comment

by:xbox360dp
ID: 40536377
varchar2
0
 

Author Comment

by:xbox360dp
ID: 40536380
Trim didn't work.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 40536388
Found that out when I set up a test case with a varchar2 field.

Try this:
(SELECT DISTINCT SPECIAL_CHAR(                                    
     CASE WHEN AFLFLDC IS NULL                                                  
       THEN NULL                                                  
       ELSE '$'                                                   
         ||aflfldc/100                                            
     END,'fm99999.00')                                            
   FROM AFL , ART                                                      
   WHERE AFLFTYKEYI = 52                                          
   AND AFLARTKEYI   = ARTKEYI                                     
   AND aflkavkeyi   = artkavkeyi                                  
   )) AS CALCULATED_WAS;

Open in new window

0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40536404
You could try decode;
( SELECT DISTINCT
         Special_Char ( DECODE ( Aflfldc,  NULL, NULL
                               , '$' || TO_NUMBER( Aflfldc ) / 100, 'fm99999.00' ) )
    FROM Afl, Art
   WHERE Aflftykeyi = 52 
     AND Aflartkeyi = Artkeyi 
     AND Aflkavkeyi = Artkavkeyi 
)) AS CALCULATED_WAS;

Open in new window

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40536420
If you want alternatives, NVL2:

 (SELECT DISTINCT SPECIAL_CHAR(                                    
    NVL2(AFLFLDC,'$'||aflfldc/100,null)'fm99999.00')                                            
  FROM AFL , ART                                                      
  WHERE AFLFTYKEYI = 52                                          
  AND AFLARTKEYI   = ARTKEYI                                    
  AND aflkavkeyi   = artkavkeyi                                  
  )) AS CALCULATED_WAS;

Open in new window

0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40536430
Cool!
0
 

Author Closing Comment

by:xbox360dp
ID: 40536491
Thank you!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question