[CLOSED]string stmt SQL

[speedy67]

I have a program generated with Auto Code (SQL)
and I have this SQL statement that does not work :

stmt='SELECT ATRQRY,DCONTO AS CONTO,ARAG1 AS NOME, SU'+
'M(DVALOR) AS SALDO , MAX((SUBSTR(DIGITS(DDTREG), 7 ,'+
' 2) || SUBSTR(DIGITS(DDTREG), 5 , 2) || SUBSTR(DIGIT'+
'S(DDTREG), 1 , 4))) AS DTULMOV FROM DBCONL04 LEFT OU'+
'TER JOIN ANACOL01 ON DCONTO=ACONTO WHERE DFLMST<>'+SQ+' '+SQ+
' AND DFLCEK<>'+SQ+'A'+SQ+' AND DDTREG<=20100101 AND AT'+
'RQRY='+SQ+'F'+SQ+' AND DCAUSA<>'+SQ+'96'+SQ+' AND DTRQRY'+
'<>'+SQ+'A'+SQ+' GROUP BY ATRQRY,DCONTO ,ARAG1 HAVING S'+
'UM(DVALOR) <>0';

If you replace '| |' with the instruction "CONCAT" works well.
What's wrong?

Pierpaolo

[robert.swanson]

That statement should work fine, as best I can tell from your post... If you're getting nothing back, it's possible that the 5th line in that statement build is running into the comments column of the RPG code. Try splitting that line and recompiling, see if that fixes the problem. If so, we'll have a permanent fix in place on the upcoming maintenance release that addresses this.

[speedy67]

I have split :

stmt='SELECT ATRQRY,'+
' DCONTO AS CONTO ,'+
' ARAG1 AS NOME ,'+
' SUM(DVALOR) AS SALDO , '+
' MAX((SUBSTR(DIGITS(DDTREG), 7 ,2) || '+
' SUBSTR(DIGITS(DDTREG), 5 , 2) || '+
' SUBSTR(DIGITS(DDTREG), 1 , 4) )) '+
' AS DTULMOV '+
' FROM DBCONL04 '+
' LEFT OUTER JOIN ANACOL01 ON DCONTO=ACONTO '+
' WHERE DFLMST<>'+SQ+' '+SQ+
' AND DFLCEK<>'+SQ+'A'+SQ+
' AND DDTREG<=20100101 '+
' AND ATRQRY= '+SQ+'F'+SQ+
' AND DCAUSA<>'+SQ+'96'+SQ+
' AND DTRQRY<>'+SQ+'A'+SQ+
' GROUP BY ATRQRY,DCONTO ,ARAG1 '+
' HAVING SUM(DVALOR) <>0' ;

but not work.

if I replace the string :
MAX((SUBSTR(DIGITS(DDTREG), 7 ,2) || '+
' SUBSTR(DIGITS(DDTREG), 5 , 2) || '+
' SUBSTR(DIGITS(DDTREG), 1 , 4) )) '+
with :
MAX(digits(DDTREG)) '+
' AS DTULMOV '+
works.
Ciao Pierpaolo

[robert.swanson]

Must be some sort of SQL error. Try running the back-end in debug and see what SQLCOD is set to after the statement executes. You can do this by simply doing STRDBG over the back-end RPG program and putting a breakpoint after the post_Action variable is retrieved. Then call the program interactively, set post_Action to 'getRecords' at your breakpoint, then step into the procedure that executes the SQL statement. Then you can see both what's inside the stmt field and the value of SQLCOD, which is probably some sort of negative number.

[speedy67]

Now it works.
The problem was the CCSID of the file qrpglesrc set at 37 but my A400 is set to 280.
At the runtime , stmt field , "|" character was turned into "!".
Thank you.
Pierpaolo