• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

Case statement returning unexpected result

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
xbox360dp
Asked:
xbox360dp
  • 3
  • 3
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
What is the data type of AFLFLDC?

Try this:
(SELECT DISTINCT SPECIAL_CHAR(                                    
     CASE trim(AFLFLDC)
       WHEN NULL                                                  
       THEN NULL      
...
0
 
xbox360dpAuthor Commented:
varchar2
0
 
xbox360dpAuthor Commented:
Trim didn't work.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
slightwv (䄆 Netminder) Commented:
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
 
MikeOM_DBACommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
MikeOM_DBACommented:
Cool!
0
 
xbox360dpAuthor Commented:
Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now