Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • 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
Industry Leaders: 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!

 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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