| Need help in Stored Procedure SQL Statement [message #40709] |
Fri, 01 November 2002 11:43  |
Srikanth Mannem
Messages: 6
Registered: October 2002
|
Junior Member |
|
|
Hi,
I have a requirement to update table that has 138 columns. The columns are named as CommitedHours1, CommitedHours2, ... CommitedHours138.
I wrote the following 138 statements and it worked.
UPDATE FORECASTOUTPUT SET CommitedHours2 = 0 WHERE HolidayScheduleOID IN (Select HolidayScheduleOID FROM EDS_HolidaysView WHERE to_char(HolidayDate, 'DD-MON-YY') = to_char(tmpDataDate + 1, 'DD-MON-YY')) AND FLAG IN (tmpWorkweek, tmpCommitments, tmpInactivity) AND
SESSIONID = tmpSessionID;
But, when I put them in the For Loop and it's not working. As I am running the Stored Procedure with ASP, it gives me that the SQL is invalid. Can anyone please help me.
FOR I IN 1 .. 138
LOOP
tmpString := 'UPDATE FORECASTOUTPUT SET CommitedHours' || I;
tmpString := tmpString || ' = 0 WHERE HolidayScheduleOID IN (Select HolidayScheduleOID FROM EDS_HolidaysView';
tmpString := tmpString || ' WHERE to_char(HolidayDate, ''DD-MON-YY'') = to_char(tmpDataDate + ' || tmpDummy1;
tmpString := tmpString || ', ''DD-MON-YY'')) AND FLAG IN (' || tmpWorkweek || ', ' || tmpCommitments;
tmpString := tmpString || ', ' || tmpInactivity || ') AND SESSIONID = ' || tmpSessionID;
EXECUTE IMMEDIATE tmpString;
tmpDummy1 := tmpDummy1 + 1;
END LOOP;
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
