SPUFI TIPS

SQL Debugging

Finding a bad character in SQL with SPUFI

You know when you have that forty lines of code you are testing in SPUFI and you get Illegal Symbol ',' and you can't find it. Well you can, those funny numbers after the message to give you a pointer.

Here is an example:


SELECT                                                                  
'A',                                                                    
'BBB' ,                                                                 
,'C'                                                                   
FROM                                                                    
SYSIBM.SYSDUMMY1                                                        
;                                                                       
---------+---------+---------+---------+---------+---------+---------+--
---------+---------+---------+---------+---------+---------+---------+--
---------+---------+---------+---------+---------+---------+---------+-
DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL ",". SOME SYMBOLS THAT MIGH
         LEGAL ARE: ( + - ? : CASE CAST USER <FLOAT> CURRENT <DECIMAL>  
         <INTEGER>                                                      
DSNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE                        
DSNT415I SQLERRP    = DSNHPARS SQL PROCEDURE DETECTING ERROR            
DSNT416I SQLERRD    = 0  0  0  -1  217  0 SQL DIAGNOSTIC INFORMATION    
DSNT416I SQLERRD    = X'00000000'  X'00000000'  X'00000000'  X'FFFFFFFF'
         X'000000D9'  X'00000000' SQL DIAGNOSTIC INFORMATION      

Look at the first SQLERRD line.  The fifth number shows the character position within you SQL with the error.  SPUFI is limited to 72 chars so pos 217 means line 4 position 1 (217/72=3, Rem = 1).