Page 1 of 1

Carriage Return in Spufi not working

PostPosted: Mon Oct 24, 2011 3:22 pm
by dhamija_sahil
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 ?

Re: Carriage Return in Spufi not working

PostPosted: Mon Oct 24, 2011 10:29 pm
by dick scherrer
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.

Re: Carriage Return in Spufi not working

PostPosted: Wed Oct 26, 2011 12:51 pm
by GuyC
select CMD from
(
SELECT acct_num, 1 as srt, ''UPDATE table1 SET col1 = "1" WHERE ACCT_NUM = "'
||ACCT_NUM||'"' as CMD
FROM table2 WHERE
ACCT_NUM BETWEEN '0582111083238' AND '0650626000939'
union all
SELECT acct_num, 2 as srt, ';'  as CMD FROM table2 WHERE
ACCT_NUM BETWEEN '0582111083238' AND '0650626000939'
union all
SELECT acct_num, 3 as srt, 'COMMIT;' as CMD FROM table2 WHERE
ACCT_NUM BETWEEN '0582111083238' AND '0650626000939'
) X
order by acct_num,srt

Re: Carriage Return in Spufi not working

PostPosted: Fri Oct 28, 2011 5:42 pm
by dhamija_sahil
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.

Re: Carriage Return in Spufi not working

PostPosted: Wed Nov 02, 2011 4:19 pm
by GuyC
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