Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

SQLCODE -206 in aritmetic operations

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
cosorio

New User


Joined: 02 May 2006
Posts: 3

PostPosted: Thu May 18, 2006 11:09 pm    Post subject: SQLCODE -206 in aritmetic operations
Reply with quote

Hi ALL,

I get a SQLCODE = -206, ERROR: SUM1 IS NOT A COLUMN OF AN INSERTED TABLE

with the following sql statement:

SELECT Y.CTR_NCONTRAT_ENT,
Y.CTR_NCONTRAT_CENT,
Y.CTR_NCONTRAT_DC,
Y.CTR_NCONTRAT_CP,
Y.CTR_NCONTRAT_CTA,
Y.CTR_NCLIENTE,
Z.PRT_NCONTRIB,
Z.PRT_NOME,
Y.CTR_MOEDA,
W.BAS_MLIMRISC,
SUM(CASE X.ORD_IESTFCT WHEN 'GP' THEN X.ORD_MFACTURA
ELSE 0 END) AS SUM1,
SUM(CASE WHEN (X.ORD_IESTFCT = 'FI'
OR X.ORD_IESTFCT = 'IM')
THEN X.ORD_MFACTURA
ELSE 0 END) AS SUM2,
W.BAS_MLIMRISC - (SUM1 + SUM2)
FROM GPBT.FBDTBAS W,
GPBT.FBDTORD X,
GPBT.FBDTCTR Y,
GPBT.FBDTPRT Z
WHERE X.ORD_NCONTRAT_ENT = Y.CTR_NCONTRAT_ENT
AND X.ORD_NCONTRAT_CENT = Y.CTR_NCONTRAT_CENT
AND X.ORD_NCONTRAT_DC = Y.CTR_NCONTRAT_DC
AND X.ORD_NCONTRAT_CP = Y.CTR_NCONTRAT_CP
AND X.ORD_NCONTRAT_CTA = Y.CTR_NCONTRAT_CTA
AND X.ORD_IESTFCT IN ('FI','IM','GP')
AND Y.CTR_NCLIENTE = Z.PRT_NCLIENTE
AND Y.CTR_MOEDA = Z.PRT_MONEDA
AND Y.CTR_NCONTRAT_ENT = W.BAS_NCONTRAT_ENT
AND Y.CTR_NCONTRAT_CENT = W.BAS_NCONTRAT_CENT
AND Y.CTR_NCONTRAT_DC = W.BAS_NCONTRAT_DC
AND Y.CTR_NCONTRAT_CP = W.BAS_NCONTRAT_CP
AND Y.CTR_NCONTRAT_CTA = W.BAS_NCONTRAT_CTA
AND Z.PRT_NCONTRIB = W.BAS_NCONTRIB
AND W.BAS_CCLIENTE = 'C'
GROUP BY Y.CTR_NCONTRAT_ENT,
Y.CTR_NCONTRAT_CENT,
Y.CTR_NCONTRAT_DC,
Y.CTR_NCONTRAT_CP,
Y.CTR_NCONTRAT_CTA,
Y.CTR_NCLIENTE,
Z.PRT_NCONTRIB,
Z.PRT_NOME,
Y.CTR_MOEDA,
W.BAS_MLIMRISC;

But whitout select the last column:

W.BAS_MLIMRISC - (SUM1 + SUM2)

the sql works fine.

How is the simplest way to include the value of the last column in this stament?

Thanks & Regards

Carlos
Back to top
View user's profile Send private message

DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Fri May 19, 2006 1:42 am    Post subject: Re: SQLCODE -206 in aritmetic operations
Reply with quote

Carlos,

A couple of ways comes to mind

Both are untested, but I think should work


Code:

W.BAS_MLIMRISC                         
 - (SUM(CASE X.ORD_IESTFCT             
        WHEN 'GP'                       
        THEN X.ORD_MFACTURA             
        ELSE 0 END)                     
  - SUM(CASE                           
        WHEN (X.ORD_IESTFCT = 'FI'     
              OR X.ORD_IESTFCT = 'IM') 
        THEN X.ORD_MFACTURA             
        ELSE 0 END)                     
    )                   

or

SELECT A.CTR_NCONTRAT_ENT,           
       A.CTR_NCONTRAT_CENT,           
       A.CTR_NCONTRAT_DC,             
       A.CTR_NCONTRAT_CP,             
       A.CTR_NCONTRAT_CTA,           
       A.CTR_NCLIENTE,               
       A.PRT_NCONTRIB,               
       A.PRT_NOME,                   
       A.CTR_MOEDA,                   
       A.BAS_MLIMRISC,               
       A.SUM1,
       A.SUM2,
       A.BAS_MLIMRISC ? A.SUM1 + A.SUM2
  FROM
       (SELECT Y.CTR_NCONTRAT_ENT,                         
               Y.CTR_NCONTRAT_CENT,                       
               Y.CTR_NCONTRAT_DC,                         
               Y.CTR_NCONTRAT_CP,                         
               Y.CTR_NCONTRAT_CTA,                         
               Y.CTR_NCLIENTE,                             
               Z.PRT_NCONTRIB,                             
               Z.PRT_NOME,                                 
               Y.CTR_MOEDA,                               
               W.BAS_MLIMRISC,                             
               SUM(CASE X.ORD_IESTFCT                     
                   WHEN 'GP'                               
                   THEN X.ORD_MFACTURA                     
                   ELSE 0 END) AS SUM1,                   
               SUM(CASE                                   
                   WHEN (X.ORD_IESTFCT = 'FI'             
                         OR X.ORD_IESTFCT = 'IM')         
                   THEN X.ORD_MFACTURA                     
                   ELSE 0 END) AS SUM2
          FROM GPBT.FBDTBAS W,                               
               GPBT.FBDTORD X,                               
               GPBT.FBDTCTR Y,                               
               GPBT.FBDTPRT Z                               
         WHERE X.ORD_NCONTRAT_ENT = Y.CTR_NCONTRAT_ENT       
           AND X.ORD_NCONTRAT_CENT = Y.CTR_NCONTRAT_CENT     
           AND X.ORD_NCONTRAT_DC = Y.CTR_NCONTRAT_DC                               
           :
           :
       
         GROUP BY Y.CTR_NCONTRAT_ENT,         
                  Y.CTR_NCONTRAT_CENT,         
                  Y.CTR_NCONTRAT_DC,           
                  Y.CTR_NCONTRAT_CP,           
                  Y.CTR_NCONTRAT_CTA,         
                  Y.CTR_NCLIENTE,             
                  Z.PRT_NCONTRIB,             
                  Z.PRT_NOME,                 
                  Y.CTR_MOEDA,                 
                  W.BAS_MLIMRISC
        ) A;                                               


Dave
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts DB2 SQL Error: SQLCODE=-440, SQLSTATE... kishpra DB2 1 Sat Feb 18, 2017 2:31 am
No new posts RFE: Additional compound operators fo... prino PL/I & Assembler 1 Wed Jun 08, 2016 3:31 pm
No new posts FileAid to compare(logical operations... anand1204 Compuware & Other Tools 3 Wed Apr 06, 2016 2:55 am
No new posts SQLCODE-000000080N priya91 DB2 1 Mon Feb 01, 2016 4:35 pm
This topic is locked: you cannot edit posts or make replies. BBC is seeking a Security Operations ... Express Mainframe Jobs 0 Tue Dec 22, 2015 1:20 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us