View previous topic :: View next topic
|
Author |
Message |
george16
New User
Joined: 08 Jul 2010 Posts: 11 Location: chennai
|
|
|
|
I have one table A. Table A having column A1 and A2.
Column A1 values example below...
A1
-----------
xsvaaaa
xsvbbbb
xsvhjhjh
xsvuyre
sssgtree
cdvcesd
cxdvvvv
drerttyrd
gfvrtyuer
bfvretytr
I need to change the value XSV replacing to YSV in colum A1.
colum A1 having 1 lak records , but XSV* records are 10 thousends records, i need to insert this 10 thousend records to table A.
This XSV value and YSV values are need to pass from out side the program. |
|
Back to top |
|
|
expat
Global Moderator
Joined: 14 Mar 2007 Posts: 8797 Location: Welsh Wales
|
|
|
|
What have you tried so far and what were the results that caused you a problem. |
|
Back to top |
|
|
george16
New User
Joined: 08 Jul 2010 Posts: 11 Location: chennai
|
|
|
|
i have tried, but i need to fix immed pls give me the logic... thru cobol |
|
Back to top |
|
|
expat
Global Moderator
Joined: 14 Mar 2007 Posts: 8797 Location: Welsh Wales
|
|
|
|
george16 wrote: |
i have tried, but i need to fix immed pls give me the logic... thru cobol |
Please do not ask for code for urgent fixes. Unfortunately the urgency is yours alone.
I assume that this is a DB2 table, but this is something that you need to state clearly. What have you tried, please post the code so that the SQL wizards can see where you are coming from and be better able to help you. Also the results of your attempt may be of value. The fact that you say that you have tried is irrelevant, because without knowing what you have tried there is always the possibility that the same suggestion could be made again which would waste everyones time.
Please give us some real detail to be able to work with to help you out. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
george16,
not for the first time, your requirements are confusing.
1. is column-a part of an index? if so, which type?
2. do you want to replace (UPDATE) the existing, or
do you want to add (INSERT) additional rows with a new column-a,
where ... has been changed to ---?
3. are there other columns? what is the disposition of these columns? |
|
Back to top |
|
|
george16
New User
Joined: 08 Jul 2010 Posts: 11 Location: chennai
|
|
|
|
1. pic X(8).
2. i want to add (INSERT)
3.A1 coulmn is the key.so we can insert new name |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
untested code
Code: |
01 parms.
05 new-val pic x(03).
05 old-val pic x(03).
read parms
exec sql
insert into tableA
select(concat(:new-val,substr(a1,4,5)),a2 from tableA)
where substr(a1,1,3) = :old-val
end-exec
|
or
Code: |
05 counter pic s9(7) comp-3.
88 time-to-commit value 1000.
88 no-work-done value zero.
start-routine section.
exec sql
declare cursor dbz with hold for select
a1,
a2
from tableA
where substr(a1,1,3) = :old-val
end-exec
exec sql
open dbz
end-exec
perform fetch-and-insert-control
until sqlcode <> 0
perform commit-routine
.
section-exit.
exit.
fetch-and-insert-control section.
exec sql
fetch dbz
into
:host-a1,
:host-a2
end-exec
if sqlcode = 0
then
compute counter = counter + 1
move new-val to hold-a1(1:3)
perform insert-routine
if time-to-commit
perform commit-routine
end-if
end-if
.
section-exit.
exit
insert-routine section.
exec sql
insert into
tablea (a1,a2)
values(:host-a1,:host-a2)
end-exec
.
section-exit.
exit.
commit-routine section.
exec sql
commit work
end-exec
set no-work-done to true
.
section-exit.
exit.
|
|
|
Back to top |
|
|
|