Q
Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

SQL statement help needed

This user had a few questions regarding SQL statements. DB2 UDB expert Kent Milligan was on hand to help him out.

I need help with an SQL statement. We're using JD Edwards. The statement is as follows:

 
  
CREATE VIEW SOPWORK03 AS 
(SELECT 
 SDLITM, 
 SDIVD, 
 CAST(DIGITS(DECIMAL(SUM(SDSOQS),14,0)) AS CHARACTER(14)) AS SUMSOQSDATEDIG, CASE WHEN SDFEA=0 THEN(SUM(SDAEXP) AS SUMEXTPRICE) ELSE(SUM(SDFEA) AS SUMEXTPRICE) END CASE, 
FROM F42119   JOIN F56SOPSTAT ON STPART# = SDLITM 
              JOIN F00365SOP ON ONDTEJ <= SDIVD 
WHERE SDMCU = '         B50' 
  AND SDLTTR = '600' 
GROUP BY SDLITM, SDIVD          ) ;   
The problem I'm having is the CASE statement. I've tried many iterations of this and they all error out. I also tried using an IF statement and had no luck. The CASE statement is replacing one that was:

UM(SDAEXP) AS SUMEXTPRICE

I simply need to have this value be the sum of either the SDAEXP or the SDFEA field.

I apologize if this is a bit confusing, but I took this SQL over from someone with a lot more SQL knowledge than I have.

Your case statement needs a little restructuring. Try this:

 

CREATE VIEW SOPWORK03 AS                                         
( 
SELECT                                                         
 SDLITM,                                                         
 SDIVD,                                                         
 CAST(DIGITS(DECIMAL(SUM(SDSOQS),14,0)) AS CHARACTER(14)) AS SUMSOQSDATEDIG,                                                 
 CASE SDFEA                                                       
   WHEN 0 THEN SUM(SDAEXP)                                         
   ELSE SUM(SDFEA)                                                 
 END AS SUMEXTPRICE                                               
FROM F42119   JOIN F56SOPSTAT ON STPART# = SDLITM               
              JOIN F00365SOP ON ONDTEJ <= SDIVD                 
WHERE SDMCU = '         B50'                                     
  AND SDLTTR = '600'                                             
GROUP BY SDLITM, SDIVD          )    

==================================
MORE INFORMATION ON THIS TOPIC
==================================

Visit the ITKnowledge Exchange and get answers to your DB2 questions fast.

Check out this Search400.com Featured Topic: Expert advice on DB2

Search400.com's targeted search engine: Get relevant information on DB2/400.

The Best Web Links: Tips, tutorials and more.

Dig Deeper on DB2 UDB (universal databases)

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataCenter

Close