about SQLCODE -104 (error) during select in DB2

0
votes

I have been trying to adapt a working "INSERT" into a SELECT statement and keep finding a error -104

a)Can someone please help me understand the reason why the "." on "where" clause (below) works when I run with an "insert" but gives me an error when using on a "select" clause? b)How should the "INSERT" criteria be written correctly when using a SELECT?

Many thanks in advance for any assistance on this question

Insert:

 INSERT INTO  SISPD2.SIS_DAILY_RECALC                            
         (CIN_NUM,PGM_DIV_FG,NON_CA_FG,PGM_EXCPT_FG,WTW_FG)   
 (SELECT C.CIN_NUM, C.PGM_DIV_FG, C.NON_CA_FG, C.PGM_EXCPT_FG,
         C.WTW_FG                                             
    FROM  SISPD2.SIS_INDV C                                  
   WHERE C.CIN_NUM NOT IN (SELECT CIN_NUM FROM  SISPD2.SIS_DAILY_RECALC)

------------------------------------------------------------------------

Select :

    SELECT C.CIN_NUM  ||','||                                          
         C.PGM_DIV_FG ||','||                                        
         C.NON_CA_FG  ||','||                                        
         C.PGM_EXCPT_FG ||','||                                     
         C.WTW_FG   ||','||                                        
  WHERE (C.CIN_NUM NOT IN (SELECT A.CIN_NUM                        
                            FROM  SISPD2.SIS_DAILY_RECALC A));

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL ".". SOME SYMBOLS THAT MIGHT
BE LEGAL ARE: , FROM INTO
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE

1
db2db2-zos
Be careful when tagging. You should only tag the DBMS relevant to your problem. - Sean Lange
Not familiar with db2, and having a hard time figuring out exactly what you are asking, but I just came across the (rarely supported) || concatenation operator the other day; it looks to me like your second query is trying to concatenate the WHERE onto the single result field, which could confuse the query parser. - Uueerdo
You're missing the FROM clause -- you must SELECT FROM something. - mustaccio
thank you so much mustaccio :) - Wanda Larangeira
thank you so much Uueerdo :) - Wanda Larangeira

1 Answers

0
votes

You shouldn't be concatenating a statement together unless you're building a dynamic statement you intend to PREPARE and EXECUTE

 SELECT C.CIN_NUM  
         , C.PGM_DIV_FG
         , C.NON_CA_FG
         , C.PGM_EXCPT_FG
         , C.WTW_FG
  WHERE (C.CIN_NUM NOT IN (SELECT A.CIN_NUM
                            FROM  SISPD2.SIS_DAILY_RECALC A));