Need Help on SqL Server Script
-
hello to all ..
im new in sql script programming but i do have a background in pl/sql, i posted this thread to get some badly needed help..
i have this script that loads a table into a cursor for sequential processing, i'm quite stuck, i've been up all night n my head's splitting, the task is urgent & i'm pressured to deliver.. any help will be highly appreciated..
this is the script i'm working on.. inscos_staging and inscos_staging2 are pre-loaded with records from a dts job, both table have the same structure except that inscos_staging2 has an additional field named proctag char (1) that i'm using as a flag to generate an exception list.. i've been running the script, i get no error messages, but i also do not get the results that i want, inscos_staging2 is not updated (proctag field) .. the script is driven by a batch job that invokes osql
what am i doing wrong? pls help .. i'm really stuck
===/* select the database */
use inscosstaging
go
/* declare the variables */
declare @v_cocab char (4)
declare @v_polno char(13)
declare @v_appno char(13)
declare @v_owner char(60)
declare @v_insurd char(60)
declare @v_rmno char(10)
declare @v_rmname char(60)
declare @v_slprdt char (8)
/* declare the cursor for inscos_staging */
declare inscos_stg1 cursor
global forward_only read_only
for select * from inscos_staging
/* open the cursor */
open inscos_stg1
/* get the first record in the cursor */
fetch next from inscos_stg1
into @v_cocab, @v_polno, @v_appno, @v_owner,
@v_insurd, @v_rmno, @v_rmname, @v_slprdt
/* declare the saved_areas variables */
declare @sv_polno char(13)
declare @sv_appno char(13)
/* loop through the cursor */
while @@fetch_status = 0
begin
/* save the record keys */
@sv_polno = @v_polno
@sv_appno = @v_appno
/* update the ayala life cabinet */
if @v_cocab = 'PLLF'
begin
update [ins-p-alai].[dbo].[t_index]
set k_111 = @v_polno, k_109 = @v_rmno, k_110 = @v_rmname,
k_112 = @v_owner, k_114 = @v_insurd, k_107 = 'AL New Business Application - Approved'
where (k_107 = 'AL New Business Application') and (k_111 = @v_appno)
update inscos_staging2
set proctag = '1'
where (polno = @sv_polno) and (appno = @sv_appno)
go
end
/* update the ayala pension cabinet */
if @v_cocab = 'PLPN'
begin
update [ins-p-ap].[dbo].[t_index]
set k_126 = @v_polno, k_124 = @v_rmno, k_125 = @v_rmname,
k_127 = @v_owner, k_129 = @v_insurd, k_122 = 'AP New Business Application - Approved'
where (k_122 = 'AP New Business Application') and (k_126 = @v_appno)
update inscos_staging2
set proctag = '1'
where (polno = @sv_polno) and (appno = @sv_appno)
go
end
/* get the next record in the cursor */
fetch next from inscos_stg1
into @v_cocab, @v_polno, @v_appno, @v_owner,
@v_insurd, @v_rmno, @v_rmname, @v_slprdt
end
/* close and deallocate the cursor */
close inscos_stg1
deallocate inscos_stg1
go
===
-
Victor - I just ran your script through the Query Analyzer parser and made a few changes..removed "Go" - "The current osql utility works as if there is an implied GO at the end of any script executed, therefore all statements in the script execute. Any statements after the last GO are not executed."...to specify value of local variable you need to use "SET" which I did...I think it should work now...meanwhile am looking at the script to see how it can be done with 3 update statements and w/out a cursor...though there're many here quicker on the draw who might suggest that before I do...
//* select the database */
use inscosstaging
go
/* declare the variables */
declare @v_cocab char (4)
declare @v_polno char(13)
declare @v_appno char(13)
declare @v_owner char(60)
declare @v_insurd char(60)
declare @v_rmno char(10)
declare @v_rmname char(60)
declare @v_slprdt char (8)
/* declare the saved_areas variables */
declare @sv_polno char(13)
declare @sv_appno char(13)
/* declare the cursor for inscos_staging */
declare inscos_stg1 cursor
global forward_only read_only
for select * from inscos_staging
/* open the cursor */
open inscos_stg1
/* get the first record in the cursor */
fetch next from inscos_stg1
into @v_cocab, @v_polno, @v_appno, @v_owner,
@v_insurd, @v_rmno, @v_rmname, @v_slprdt
/* loop through the cursor */
while @@fetch_status = 0
begin
/* save the record keys */
set @sv_polno = @v_polno
set @sv_appno = @v_appno
/* update the ayala life cabinet */
if @v_cocab = 'PLLF'
begin
update [ins-p-alai].[dbo].[t_index]
set k_111 = @v_polno, k_109 = @v_rmno, k_110 = @v_rmname,
k_112 = @v_owner, k_114 = @v_insurd, k_107 = 'AL New Business Application - Approved'
where (k_107 = 'AL New Business Application') and (k_111 = @v_appno)
update inscos_staging2
set proctag = '1'
where (polno = @sv_polno) and (appno = @sv_appno)
--go
end
/* update the ayala pension cabinet */
if @v_cocab = 'PLPN'
begin
update [ins-p-ap].[dbo].[t_index]
set k_126 = @v_polno, k_124 = @v_rmno, k_125 = @v_rmname,
k_127 = @v_owner, k_129 = @v_insurd, k_122 = 'AP New Business Application - Approved'
where (k_122 = 'AP New Business Application') and (k_126 = @v_appno)
update inscos_staging2
set proctag = '1'
where (polno = @sv_polno) and (appno = @sv_appno)
--go
end
/* get the next record in the cursor */
fetch next from inscos_stg1
into @v_cocab, @v_polno, @v_appno, @v_owner,
@v_insurd, @v_rmno, @v_rmname, @v_slprdt
end
/* close and deallocate the cursor */
close inscos_stg1
deallocate inscos_stg1
go
**ASCII stupid question, get a stupid ANSI !!!** -
Here's an example of what I meant about doing this without the cursor....the first update would be something like this:
update [ins-p-ap].[dbo].[t_index]
set k_126 = v_polno,
k_124 = v_rmno,
k_125 = v_rmname,
k_127 = v_owner,
k_129 = v_insurd,
k_122 = 'AP New Business Application - Approved'
from inscos_staging join [ins-p-ap].[dbo].[t_index]
on [ins-p-ap].[dbo].[t_index].K_126 = inscos_staging.v_appno
where (k_122 = 'AP New Business Application') and
inscos_staging .v_cocab = 'PLLF'
**ASCII stupid question, get a stupid ANSI !!!** -
ok - these 3 updates should do it...(in the earlier post I got mixed up betw. PLLF & PLPN - I think I have it correctly now...
update [ins-p-ap].[dbo].[t_index]
set k_126 = v_polno,
k_124 = v_rmno,
k_125 = v_rmname,
k_127 = v_owner,
k_129 = v_insurd,
k_122 = 'AL New Business Application - Approved'
from inscos_staging join [ins-p-ap].[dbo].[t_index]
on [ins-p-ap].[dbo].[t_index].K_111 = inscos_staging.v_appno
where (k_122 = 'AL New Business Application') and
inscos_staging .v_cocab = 'PLLF'
update [ins-p-ap].[dbo].[t_index]
set k_126 = v_polno,
k_124 = v_rmno,
k_125 = v_rmname,
k_127 = v_owner,
k_129 = v_insurd,
k_122 = 'AP New Business Application - Approved'
from inscos_staging join [ins-p-ap].[dbo].[t_index]
on [ins-p-ap].[dbo].[t_index].K_126 = inscos_staging.v_appno
where (k_122 = 'AP New Business Application') and
inscos_staging .v_cocab = 'PLPN'
update inscos_staging2
set proctag = '1'
from inscos_staging
join inscos_staging2
on inscos_staging2.polno = inscos_staging.sv_polno and
inscos_staging2.appno = inscos_staging.sv_appno
**ASCII stupid question, get a stupid ANSI !!!** -
sushila,
first off, many thanx for ur time n effort in analyzing my script, had this been in pl/sql i would have completed this with much less effort.. i agree with you that this script can do w/o using cursor but i'm not yet much of a sql programmer, as my exposure to sql server leans more on the database, so the scripting techniques i know are for database/tables management..
just hav some questions, u may think these qs are elementary but i'll be happy 2b enlightened ..
== ur prev post/reply ==
update [ins-p-ap].[dbo].[t_index]
set k_126 = v_polno,
k_124 = v_rmno,
k_125 = v_rmname,
k_127 = v_owner,
k_129 = v_insurd,
k_122 = 'AL New Business Application - Approved'
from inscos_staging join [ins-p-ap].[dbo].[t_index]
on [ins-p-ap].[dbo].[t_index].K_111 = inscos_staging.v_appno
where (k_122 = 'AL New Business Application') and
inscos_staging .v_cocab = 'PLLF'
in the set portion of the update stmt, why is it that u specified "v_polno" instead of "@v_polno"? same for the rest of the vars .. is the use of @ only for declaration of variables?
u specified "inscos_staging.v_appno" as part of the join condition, i know there's a reason behind why u did so, but somehow it escapes me.. v_appno is a variable, inscos_staging does not hav v_appno but appno, should this be "inscos_staging.appno" instead?
same question goes for "inscos_staging.v_cocab" in the where clause, should this be "inscos_staging.cocab" instead?
again sushila, many many thanx for ur help.. the logic behind ur update stmts still escapes me, maybe i need som shuteye hehe
.. but for now i'll take it as the be-all just to finish the task.. -
in the set portion of the update stmt, why is it that u specified "v_polno" instead of "@v_polno"? same for the rest of the vars .. is the use of @ only for declaration of variables?
yes - @something would be the way to declare a variable!
u specified "inscos_staging.v_appno" as part of the join condition, i know there's a reason behind why u did so, but somehow it escapes me.. v_appno is a variable, inscos_staging does not hav v_appno but appno, should this be "inscos_staging.appno" instead?
yes - it should be the column name inscos_staging.appno...
same question goes for "inscos_staging.v_cocab" in the where clause, should this be "inscos_staging.cocab" instead?
same answer as above!
again sushila, many many thanx for ur help.. the logic behind ur update stmts still escapes me, maybe i need som shuteye hehe [Doze] .. but for now i'll take it as the be-all just to finish the task.
the logic may become clearer if you post the ddls of the 2 tables (staging 2 you said is different only by that one additional column right ?!) - moreover, I was giving you suggestions and hoping that you would be able to "play around" with it to get your updates working in one (sorry 3) fell swoop(s)
let me know if you have any more questions! shuteye is always a good idea - sleeping on something does wonders for perspective!
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply