February 13, 2006 at 11:48 am
I've got a cursor I've adapted to pick up course history from a table and insert specific records by term, etc. into a new table, but am getting errors in the "IF ...THEN' part which don't make sense to me...
I'd appreciate any thoughts you cursor experts might have...
Thanks!
Server: Msg 170, Level 15, State 1, Line 33
Line 33: Incorrect syntax near '='.
Server: Msg 137, Level 15, State 1, Line 36
Must declare the variable '@SCHOOLNUM2'.
Server: Msg 156, Level 15, State 1, Line 45
Incorrect syntax near the keyword 'DECLARE'.
Server: Msg 134, Level 15, State 1, Line 46
The variable name '@GRADE' has already been declared. Variable names must be unique within a query batch or stored procedure.
Server: Msg 134, Level 15, State 1, Line 47
The variable name '@SCHOOLNUM' has already been declared. Variable names must be unique within a query batch or stored procedure.
Server: Msg 170, Level 15, State 1, Line 61
Line 61: Incorrect syntax near ','.
Server: Msg 137, Level 15, State 1, Line 66
Must declare the variable '@DISTNO'.
Server: Msg 137, Level 15, State 1, Line 67
Must declare the variable '@FLSCHLNO'.
Server: Msg 137, Level 15, State 1, Line 68
Must declare the variable '@COUNTRY'.
Server: Msg 137, Level 15, State 1, Line 69
Must declare the variable '@BYAUTOPROC'.
Server: Msg 137, Level 15, State 1, Line 70
Must declare the variable '@EOYSTATUS'.
Server: Msg 137, Level 15, State 1, Line 71
Must declare the variable '@RESERVED'.
Server: Msg 137, Level 15, State 1, Line 72
Must declare the variable '@FILLER'.
Server: Msg 137, Level 15, State 1, Line 73
Must declare the variable '@SCHOOLFLAG'.
Server: Msg 137, Level 15, State 1, Line 76
Must declare the variable '@U$DATE'.
Server: Msg 156, Level 15, State 1, Line 79
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 81
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 83
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 85
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 87
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 89
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 91
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 93
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 95
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 97
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 99
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 101
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 103
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 105
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 107
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 109
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 111
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 113
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 122
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 124
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 126
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 128
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 130
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 132
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 134
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 136
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 138
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 140
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 142
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 144
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 146
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 148
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 150
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 152
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 154
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 156
Incorrect syntax near the keyword 'THEN'.
Server: Msg 137, Level 15, State 1, Line 160
Must declare the variable '@SCHOOLNUM2'.
Server: Msg 170, Level 15, State 1, Line 169
Line 169: Incorrect syntax near ','.
Server: Msg 137, Level 15, State 1, Line 182
Must declare the variable '@USERFIELD1'.
here's the PROC
BEGIN /*FIND ALL STUDENTS*/
DECLARE @STULINK NUMERIC
DECLARE STULINK_CURSOR CURSOR FOR
SELECT DISTINCT STULINK FROM SASI.ASTU5031
OPEN STULINK_CURSOR
FETCH NEXT FROM STULINK_CURSOR INTO @STULINK
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN /*GET PREVIOUS SCHOOLS THIS STUDENT ATTENDED LESS THAN 10TH GRADE*/
DECLARE @GRADE VARCHAR(2)
DECLARE @SCHOOLNUM VARCHAR(3)
SET @GRADE = (SELECT GRADE FROM SASI.ASTU5031 WHERE STULINK = @STULINK)
DECLARE SCHOOLNUM_CURSOR CURSOR FOR
SELECT DISTINCT SCHOOLATTN FROM SASI.ACHS5031
WHERE
GRADE < 10 AND
STULINK = @STULINK AND
SCHOOLNUM IN ('003', '021', '022', '023', '030', '031', '032', '034', '035')
OPEN SCHOOLNUM_CURSOR
FETCH NEXT FROM SCHOOLNUM_CURSOR INTO @SCHOOLNUM
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN /*GET ALL ACHS RECORDS ASSOCIATED WITH CURRENT SCHOOLNUM CURSOR*/
DECLARE SCHOOLNUM2_CURSOR CURSOR FOR
SELECT DISTINCT GRADE, TERM, SCHLYEAR, USERSTAMP FROM SASI.ACHS5031
WHERE
GRADE < 10 AND
STULINK = @STULINK AND
SCHOOLNUM IN ('003', '021', '022', '023', '030', '031', '032', '034', '035')
DECLARE SCHOOLNUM = @SCHOOLNUM2
OPEN SCHOOLNUM2_CURSOR
FETCH NEXT FROM SCHOOLNUM2_CURSOR INTO @SCHOOLNUM2
BEGIN /*SET ASAH INSERT VARIABLES AND DETERMINE STARTDATE*/
DECLARE @STARTDATE smalldatetime
DECLARE @ENDDATE smalldatetime
DECLARE @status VARCHAR(6)
DECLARE @SCHLATTNM VARCHAR(40)
DECLARE @CITY VARCHAR(24)
DECLARE @STATE VARCHAR (2
DECLARE @GRADE VARCHAR(6)
DECLARE @SCHOOLNUM VARCHAR(3)
DECLARE @SEQUENCE NUMERIC
END
BEGIN
SET @SCHLATTNM =
case substring(SCHLATTNUM,1,3)
when '021' then 'Ce Junior High'
when '022' then 'Fe Junior High'
when '023' then 'Re Junior High'
when '030' then 'Ke Secondary School'
when '031' then 'Ce High School'
when '032' then 'Ol High School'
when '034' then 'Al'
when '035' then 'Eive'
end,
END
BEGIN
SET @DISTNO = ''
SET @FLSCHLNO = ''
SET @COUNTRY =''
SET @BYAUTOPROC = ''
SET @EOYSTATUS = ''
SET @RESERVED =''
SET @FILLER = ''
SET @SCHOOLFLAG = ''
SET @U$DATE =''
END
BEGIN
IF ((@GRADE = '12') AND ((GRADE = '09') AND (TERM = '2'))) THEN @ENDDATE = '06/01/03'
ELSE
IF ((@GRADE = '12') AND ((GRADE = '09') AND (TERM = '1'))) THEN @ENDDATE = '02/01/03'
ELSE
IF ((@GRADE = '12') AND ((GRADE = '08') AND (TERM = '2'))) THEN @ENDDATE = '06/01/03'
ELSE
IF ((@GRADE = '12') AND ((GRADE = '08') AND (TERM = '1'))) THEN @ENDDATE = '02/01/03'
ELSE
IF ((@GRADE = '12') AND ((GRADE = '07') AND (TERM = '2'))) THEN @ENDDATE = '06/01/03'
ELSE
IF ((@GRADE = '12') AND ((GRADE = '07') AND (TERM = '1'))) THEN @ENDDATE = '02/01/03'
ELSE
IF ((@GRADE = '11') AND ((GRADE = '09') AND (TERM = '2'))) THEN @ENDDATE = '06/01/03'
ELSE
IF ((@GRADE = '11') AND ((GRADE = '09') AND (TERM = '1'))) THEN @ENDDATE = '02/01/03'
ELSE
IF ((@GRADE = '11') AND ((GRADE = '08') AND (TERM = '2'))) THEN @ENDDATE = '06/01/03'
ELSE
IF ((@GRADE = '11') AND ((GRADE = '08') AND (TERM = '1'))) THEN @ENDDATE = '02/01/03'
ELSE
IF ((@GRADE = '11') AND ((GRADE = '07') AND (TERM = '2'))) THEN @ENDDATE = '06/01/03'
ELSE
IF ((@GRADE = '11') AND ((GRADE = '07') AND (TERM = '1'))) THEN @ENDDATE = '02/01/03'
ELSE
IF ((@GRADE = '10') AND ((GRADE = '09') AND (TERM = '2'))) THEN @ENDDATE = '06/01/03'
ELSE
IF ((@GRADE = '10') AND ((GRADE = '09') AND (TERM = '1'))) THEN @ENDDATE = '02/01/03'
ELSE
IF ((@GRADE = '10') AND ((GRADE = '08') AND (TERM = '2'))) THEN @ENDDATE = '06/01/03'
ELSE
IF ((@GRADE = '10') AND ((GRADE = '08') AND (TERM = '1'))) THEN @ENDDATE = '02/01/03'
ELSE
IF ((@GRADE = '10') AND ((GRADE = '07') AND (TERM = '2'))) THEN @ENDDATE = '06/01/03'
ELSE
IF ((@GRADE = '10') AND ((GRADE = '07') AND (TERM = '1'))) THEN @ENDDATE = '02/01/03'
ELSE
END
WHILE @@FETCH_STATUS = 0
BEGIN
/*WRITE CODE TO DETERMINE START DATE HERE */
BEGIN
IF ((@GRADE = '12') AND ((GRADE = '09') AND (TERM = '2'))) THEN @STARTDATE = '02/01/03'
ELSE
IF ((@GRADE = '12') AND ((GRADE = '09') AND (TERM = '1'))) THEN @STARTDATE = '09/01/02'
ELSE
IF ((@GRADE = '12') AND ((GRADE = '08') AND (TERM = '2'))) THEN @STARTDATE = '02/01/02'
ELSE
IF ((@GRADE = '12') AND ((GRADE = '08') AND (TERM = '1'))) THEN @STARTDATE = '09/01/01'
ELSE
IF ((@GRADE = '12') AND ((GRADE = '07') AND (TERM = '2'))) THEN @STARTDATE = '06/01/03'
ELSE
IF ((@GRADE = '12') AND ((GRADE = '07') AND (TERM = '1'))) THEN @STARTDATE = '02/01/03'
ELSE
IF ((@GRADE = '11') AND ((GRADE = '09') AND (TERM = '2'))) THEN @STARTDATE = '06/01/03'
ELSE
IF ((@GRADE = '11') AND ((GRADE = '09') AND (TERM = '1'))) THEN @STARTDATE = '02/01/03'
ELSE
IF ((@GRADE = '11') AND ((GRADE = '08') AND (TERM = '2'))) THEN @STARTDATE = '06/01/03'
ELSE
IF ((@GRADE = '11') AND ((GRADE = '08') AND (TERM = '1'))) THEN @STARTDATE = '02/01/03'
ELSE
IF ((@GRADE = '11') AND ((GRADE = '07') AND (TERM = '2'))) THEN @STARTDATE = '06/01/03'
ELSE
IF ((@GRADE = '11') AND ((GRADE = '07') AND (TERM = '1'))) THEN @STARTDATE = '02/01/03'
ELSE
IF ((@GRADE = '10') AND ((GRADE = '09') AND (TERM = '2'))) THEN @STARTDATE = '06/01/03'
ELSE
IF ((@GRADE = '10') AND ((GRADE = '09') AND (TERM = '1'))) THEN @STARTDATE = '02/01/03'
ELSE
IF ((@GRADE = '10') AND ((GRADE = '08') AND (TERM = '2'))) THEN @STARTDATE = '06/01/03'
ELSE
IF ((@GRADE = '10') AND ((GRADE = '08') AND (TERM = '1'))) THEN @STARTDATE = '02/01/03'
ELSE
IF ((@GRADE = '10') AND ((GRADE = '07') AND (TERM = '2'))) THEN @STARTDATE = '06/01/03'
ELSE
IF ((@GRADE = '10') AND ((GRADE = '07') AND (TERM = '1'))) THEN @STARTDATE = '02/01/03'
ELSE
END
FETCH NEXT FROM SCHOOLNUM2_CURSOR INTO @SCHOOLNUM2
END
CLOSE SCHOOLNUM2_CURSOR
DEALLOCATE SCHOOLNUM2_CURSOR
END
BEGIN
/* INSERT RECORD FOR THIS SCHOOL INTO THE ASAH */
DECLARE @RETURNEDROWS INT
SELECT (STATUS, SCHOOLNUM, STULINK, SEQUENCE, USERFIELD1, USERFIELD2,
USERSTAMP, DATESTAMP, TIMESTAMP, SCHLYEAR,FROMDATE,TODATE,SCHLATTNUM,SCHLATTNM,CITY,STATE,GRADELEVEL,
COUNTRY, BYAUTOPROC, EOYSTATUS, RESERVED,FILLER, SCHOOLFLAG, DISTNO, FLSCHLNO, U$DATE, PROVINCE)
FROM @SCHOOLNUM2 AS @RETURNEDROWS
SET @RETURNEDROWS = @@ROWCOUNT
IF @RETURNEDROWS = '0'
INSERT INTO SASI.ASAH5031
(STATUS, SCHOOLNUM, STULINK, SEQUENCE, USERFIELD1, USERFIELD2,
USERSTAMP, DATESTAMP, TIMESTAMP, SCHLYEAR,FROMDATE,TODATE,SCHLATTNUM,SCHLATTNM,CITY,STATE,GRADELEVEL,
COUNTRY, BYAUTOPROC, EOYSTATUS, RESERVED,FILLER, SCHOOLFLAG, DISTNO, FLSCHLNO, U$DATE, PROVINCE)
VALUES
(@STATUS, @SCHOOLNUM, @STULINK, @SEQUENCE, @USERFIELD1, @USERFIELD2,
@USERSTAMP, @DATESTAMP, @TIMESTAMP, @SCHLYEAR,@FROMDATE,@TODATE,S@CHLATTNUM,@SCHLATTNM,@CITY,@STATE,@GRADELEVEL,
@COUNTRY, @BYAUTOPROC, @EOYSTATUS, @RESERVED,@FILLER, @SCHOOLFLAG, @DISTNO, @FLSCHLNO, @U$DATE, @PROVINCE)
END
FETCH NEXT FROM SCHOOLNUM_CURSOR INTO @SCHOOLNUM
END
CLOSE SCHOOLNUM_CURSOR
DEALLOCATE SCHOOLNUM_CURSOR
END
FETCH NEXT FROM STULINK_CURSOR INTO @STULINK
END
CLOSE STULINK_CURSOR
DEALLOCATE STULINK_CURSOR
END
GO
February 13, 2006 at 11:55 am
DECLARE SCHOOLNUM = @SCHOOLNUM2
OPEN SCHOOLNUM2_CURSOR
You use the variable '@SCHOOLNUM2' before you declare it, for starters.
Try fixing that, and then see what errors you get.
February 13, 2006 at 12:02 pm
First off you have many syntax errors including a ) missing for a declare statement. But as for you IF statement you are using the wrong logic method this is what you do in SQL
IF ((@GRADE = '12') AND ((GRADE = '09') AND (TERM = '2')))
SET @ENDDATE = '06/01/03'
ELSE
IF ....
SET
However I don't think your logic is accurate because all your results end up simplified to
SET @ENDDATE = '06/01/03'
or
SET @ENDDATE = '02/01/03'
So are you sure you want the end date to be
06/01/03 no matter if @Grade is 12, 11 or 10 as long as Grade is 9, 8, 7 when Term is equal to 2?
and
02/01/03 no matter if @Grade is 12, 11 or 10 as long as Grade is 9, 8, 7 when Term is equal to 1?
Also, I am pretty sure with a cursory glance over this that you could do a set based solution but I haven't the time right now to break your logic down to provide you what you are looking for.
February 13, 2006 at 12:46 pm
Yes... I had not declared all of the variables. Once I did that, I am left with all the "if" errors...
I'm puzzled.
Server: Msg 156, Level 15, State 1, Line 101
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 103
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 105
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 107
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 109
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 111
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 113
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 115
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 117
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 119
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 121
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 123
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 125
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 127
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 129
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 131
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 133
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 135
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 144
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 146
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 148
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 150
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 152
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 154
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 156
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 158
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 160
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 162
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 164
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 166
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 168
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 170
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 172
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 174
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 176
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 178
Incorrect syntax near the keyword 'THEN'.
Server: Msg 170, Level 15, State 1, Line 195
February 13, 2006 at 12:50 pm
>>Incorrect syntax near the keyword 'THEN'.
Why are you puzzled ? The error tells you exactly what is wrong. Incorrect syntax.
Look up IF in books online, View the example code. The keyword THEN does not exist in T-SQL. Hence the error.
February 13, 2006 at 1:10 pm
Looking at the code, there are several things that I would do differently.
One, the first cursor, SCHOOLNUM2_CURSOR, loops through the table and resets the variable @STARTDATE to one of two values.
Second, 3 cursors seems way to much to do an insert into one table based on the values in some other tables. I would try to do a set based operation in one insert statement. What is the logic that you are trying to implement here?
Third, instead of hardcoding the values when resolving a code to a name, use a table to lookup the value. An even better way would be to store only the ID in the details table so that the name isn't repeated.
Finally, you don't need so many BEGIN-END statements bracketing every DECLARE or SET operation.
If you respond with the logic that you are trying to implement, I'll try and help with a insert statement.
February 13, 2006 at 1:38 pm
I'm puzzled because I don't know as much about TSQL as you do.. quite obviously.
February 13, 2006 at 1:55 pm
I would definitely suggest you take JeffB's advice here.
I am also having some trouble figuring out your logic, but it seems like a few insert/update statements should be able to handle it.
For instance, setting the date to one of two values seems like it could be done in one update statement using a CASE comparison in the SELECT clause.
Just play around with different options until you get it, b/c I think you are doing way too much for this process.
Good luck!
February 15, 2006 at 6:09 am
SASI.ASTU5031 -- Previous School Attended?
SASI.ACHS5031 -- Current School
-- Looks like a list of the schools based on a portion of a filed but not sure which table came from.
BEGIN
SET @SCHLATTNM =
case substring(SCHLATTNUM,1,3)
when '021' then 'Ce Junior High'
when '022' then 'Fe Junior High'
when '023' then 'Re Junior High'
when '030' then 'Ke Secondary School'
when '031' then 'Ce High School'
when '032' then 'Ol High School'
when '034' then 'Al'
when '035' then 'Eive'
end,
END
-- Looks like logic to determine the the start and end dates for a given year of school and term base on current grade now?? However this assumes they completed every year once an in order which doesn't seem like a good idea on the off chance someone had to repeat a year for any reason. Also your yar is the same all the way down and your dates are fixed 2/1 and 6/1 in the first but here the school year tends to slide aroun a bit.
BEGIN
IF ((@GRADE = '12') AND ((GRADE = '09') AND (TERM = '2'))) THEN @ENDDATE = '06/01/03'
ELSE
IF ((@GRADE = '12') AND ((GRADE = '09') AND (TERM = '1'))) THEN @ENDDATE = '02/01/03'
ELSE
IF ((@GRADE = '12') AND ((GRADE = '08') AND (TERM = '2'))) THEN @ENDDATE = '06/01/03'
ELSE
IF ((@GRADE = '12') AND ((GRADE = '08') AND (TERM = '1'))) THEN @ENDDATE = '02/01/03'
ELSE
IF ((@GRADE = '12') AND ((GRADE = '07') AND (TERM = '2'))) THEN @ENDDATE = '06/01/03'
ELSE
... --Code truncated for space
SASI.ASAH5031 -- Not sure what this table represents nor how the data going is is created in th process.
INSERT INTO SASI.ASAH5031
(STATUS, SCHOOLNUM, STULINK, SEQUENCE, USERFIELD1, USERFIELD2,
USERSTAMP, DATESTAMP, TIMESTAMP, SCHLYEAR,FROMDATE,TODATE,SCHLATTNUM,SCHLATTNM,CITY,STATE,GRADELEVEL,
COUNTRY, BYAUTOPROC, EOYSTATUS, RESERVED,FILLER, SCHOOLFLAG, DISTNO, FLSCHLNO, U$DATE, PROVINCE)
VALUES
(@STATUS, @SCHOOLNUM, @STULINK, @SEQUENCE, @USERFIELD1, @USERFIELD2,
@USERSTAMP, @DATESTAMP, @TIMESTAMP, @SCHLYEAR,@FROMDATE,@TODATE,S@CHLATTNUM,@SCHLATTNM,@CITY,@STATE,@GRADELEVEL,
@COUNTRY, @BYAUTOPROC, @EOYSTATUS, @RESERVED,@FILLER, @SCHOOLFLAG, @DISTNO, @FLSCHLNO, @U$DATE, @PROVINCE)
END
Oevrall I hav the feeling you are making a mountain out of a mole-hill. However, as you said, you are new to T-SQL and have a large learning curve to deal with. You should be able to simply write a single query to
INSERT INTO SASI.ASAH5031
SELECT
....COlumns and data manipulation Logic....
FROM
SASI.ACHS5031
INNER JOIN
SASI.ASTU5031
ON
....Conditions that make records from 1 table the counterpart in the second...
[WHERE ....Any special conditions that must be met....]
A cursor is a lot of logic to have to work thru especially if an issue occurrs. You have a lot o issues in your current code which cannot be addressed without a better understanin of what you hav and what you want. Can you
1) Post the DDL (Data Definition) of the Tables (In Enterprise Manager you can righ click and copy the table and paste in notepad to get the DDL).
2) Tell us what is stored in each table and the columns where column name may be vague.
3) Provide an example of the data (please alter sensitive information before posting) from SASI.ASTU5031, what the related data looks like in SASI.ACHS5031 and the resulting data you expect from those in SASI.ASAH5031.
4) Explain any specialized logic you need to apply to the data.
February 15, 2006 at 8:05 am
The basic problem here is this. We have a table (SASI.ACHS5031) containing course history records from previous grades and schools attended for currently enrolled students. The records are of student's courses for either 1st and/or 2nd term for each school and school year they were previously enrolled. The goal is to use the information contained in this table to populate a new table (ASAH5031). This new table will be referenced by the vendor provided GUI to print a transcript showing the student's schools attended, including the enter date, leave date, school name, and the city and state of each school the student attended. There are some records already in the ASAH, so if the record produced by this process already exists in the ASAH, the process should note this and do nothing.
I started out working with a set based query, but got stuck with it. My DBA recommended using a cursor approach, which you see. It's a bag of monkeys to my eye, but I'm not conversant enough with the set based approach to get the job done.
I
The logic is this.
In the current school a student is a current 12th grader and has ACHS5031 records with
Schoolyear = 2002 and Grade = 09 and term = 1 and term = 2 (he attended two terms and has a record for each term attended at the same school
then...
he gets a record in ASAH with
Fromdate = '9/1/02' the beginning of the 1st term for that school year
ToDate = '6/1/03' the end of the 2nd term for that school year
SchAttNum = whatever school he did these in ...contained in table ACHS5031
SchAttName = 'school text name'
City = 'school city'
State = 'school state'
Grade level = '09'
Notice that the from and todate here span both terms. He needs to have records for both terms to get one record like this in ASAH
OR
If Schoolyear = 2002 and Grade = 09 and school attended = X and term = 1 ONLY (he attended on one course in 1st term)
he gets a record in ASAH with
From date -= '9/1/02'
ToDate = '2/1/03' due to only being in first term
SchAttNum =
SchAttName =
City =
State =
Grade level = '09'
Here the end date here shows he only attended for 1st term
and finally....
If Schoolyear = 2002 and Grade = 09 and school attended = X and term = 2 ONLY (he attended only one course and it was during the second term)
he gets a record in with
From date -= '2/1/03' this is the beginning of the 2nd term
ToDate = '6/1/03' the end of the second term
SchAttNum =
SchAttName =
City =
State =
Grade level = '09'
Here the end date here shows he only attended for 1st term
And so on.
Here are the DDL for the two tables...
The source table:
CREATE TABLE [dbf].[ACHS5031] (
[STATUS] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SCHOOLNUM] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[STULINK] [decimal](5, 0) NOT NULL ,
[SEQUENCE] [decimal](3, 0) NOT NULL ,
[ACTDATE] [datetime] NULL ,
[ACTENDDATE] [datetime] NULL ,
[ACTCODE] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ACTDESC] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ADVISOR] [decimal](3, 0) NULL ,
[ADVNAME] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SEASONMO] [decimal](2, 0) NULL ,
[SEASONYR] [decimal](4, 0) NULL ,
[PARTSCHOOL] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NOTATION] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
The target table
CREATE TABLE [dbf].[ASAH5031] (
[STATUS] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SCHOOLNUM] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[STULINK] [decimal](5, 0) NOT NULL ,
[SEQUENCE] [decimal](3, 0) NOT NULL ,
[FROMDATE] [datetime] NULL ,
[TODATE] [datetime] NULL ,
[SCHLATTNUM] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SCHLATTNM] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CITY] [varchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[STATE] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PROVINCE] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[COUNTRY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[GRADELEVEL] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MEMBERSHIP] [decimal](5, 1) NULL ,
[ABSENT] [decimal](5, 1) NULL ,
[PRESENT] [decimal](5, 1) NULL ,
[BYAUTOPROC] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[USERFIELD1] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[USERFIELD2] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[EOYSTATUS] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RESERVED] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FILLER] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SCHOOLFLAG] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SCHLYEAR] [decimal](4, 0) NULL ,
[DISTNO] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FLSCHLNO] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[USERSTAMP] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DATESTAMP] [datetime] NULL ,
[TIMESTAMP] [decimal](6, 0) NULL ,
[U$DATE] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
Thanks for your help!
February 17, 2006 at 6:38 am
Haven't had a chance to see what I can do with it but will look at over the weekend while I have some time and try to give you a response unless someone else decides to beat me to it.
February 17, 2006 at 7:06 am
Why you are using cursors?
Can you use temp tables ? in place of cursors..
it will increase the performance of your query?
February 17, 2006 at 11:38 am
Because I'm a newbie .. and thought that would be easier than trying to nuke out a set based method...
February 17, 2006 at 11:39 am
I appreciate your effort. Thanks!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply