Help with a Cursor... yes..

  • 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

     

  • 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.

  • 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.

  • 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

     

     

     

     

  • >>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.

     

     

  • 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.

  • I'm puzzled because I don't know as much about TSQL as you do.. quite obviously.

  • 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!

  • 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.

  • 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! 

  • 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.

  •  

    Why you are using cursors?

    Can you use temp tables ? in place of cursors..

    it will increase the performance of your query?

     

  • Because I'm a newbie .. and thought that would be easier than trying to nuke out a set based method...

  • 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