IBM Mainframe Forum

Hi,

I am trying to run the below querry in spufi :

SELECT 'UPDATE table1 SET col1 = "1" WHERE ACCT_NUM = "'
||ACCT_NUM||'"'||X'0D'||';'||X'0D'||'COMMIT ;'
FROM table2 WHERE
ACCT_NUM BETWEEN '0582111083238' AND '0650626000939'

to get the output result as :

UPDATE table1 SET col1 = "1" WHERE ACCT_NUM = "0582111083238"
;
COMMIT ;
UPDATE table1 SET col1 = "1" WHERE ACCT_NUM = "0585111082775"
;
COMMIT ;

But instead of displaying the semi-colon and 'Commit' statement in next line (Carriage return - X'0D') I am getting the output result as :

UPDATE table1 SET col1 = "1" WHERE ACCT_NUM = "0582111083238".;.COMMIT;
UPDATE table1 SET col1= "1" WHERE ACCT_NUM = "0585111082775".;.COMMIT;

Can anyone suggest me how to fix this so that I get the desired output ?

Hello and welcome to the forum,

Spufi is possibly not the best way to do this. X'0D' is just another byte of data on the mainframe - it is not "carriage control".

One way to get what you want is to put the query in a bit of code and control the WRITEs programatically.

Hey Dick,

Thanks for the greetings !!!

GuyC,

Thanks for the response. The querry is working perfectly fine .
I analysed the query but had couple of doubts in this. Can you please explain the query whenever its feasible for you ?
Thanks for your help.

for each acct_num the query generates 3 output rows :
suppose two acct_num : x and y
x,1, 'UPDATE ...'
y,1, 'UPDATE ...'
x,2, ';'
y,2, ';'
x,3, 'COMMIT';'
y,3, 'COMMIT';'
then you sort on the first two columns and select only the third column