Carriage Return in Spufi not working



IBM's flagship relational database management system

Carriage Return in Spufi not working

Postby dhamija_sahil » Mon Oct 24, 2011 3:22 pm

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 ?
dhamija_sahil
 
Posts: 2
Joined: Mon Oct 24, 2011 2:43 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Carriage Return in Spufi not working

Postby dick scherrer » Mon Oct 24, 2011 10:29 pm

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.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Carriage Return in Spufi not working

Postby GuyC » Wed Oct 26, 2011 12:51 pm

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
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Carriage Return in Spufi not working

Postby dhamija_sahil » Fri Oct 28, 2011 5:42 pm

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.
dhamija_sahil
 
Posts: 2
Joined: Mon Oct 24, 2011 2:43 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Carriage Return in Spufi not working

Postby GuyC » Wed Nov 02, 2011 4:19 pm

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
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post