Invalid Column Names?

Here is my code:
DECLARE curCAED1 CURSOR FAST_FORWARD FOR
SELECT * FROM okc_contr_license
WHERE CONTR_TYPE = 'ME'
ORDER BY CONTRACTOR_NUMBER
OPEN curCAED1
FETCH NEXT FROM curCAED1 INTO
@.pContrType, @.pContractorNumber,
@.pCityLicType, @.pCityLicNumber, @.pCityLicRenDate,
@.pFirstName, @.pMiddleInitial, @.pLastName
WHILE @.@.FETCH_STATUS = 0
BEGIN
UPDATE PermitsSystemNew.dbo.CAED_CONVERSION --Error generated here--
SET CAE_FName = @.pFirstName
WHERE CONTR_TYPE = @.pContrType AND CONTRACTOR_NUMBER = @.pContractorNumber
FETCH NEXT FROM curCAED1 INTO
@.pContrType, @.pContractorNumber,
@.pCityLicType, @.pCityLicNumber, @.pCityLicRenDate,
@.pFirstName, @.pMiddleInitial, @.pLastName
END
CLOSE curCAED1
DEALLOCATE curCAED1
Here is my error:
Server: Msg 207, Level 16, State 3, Line 134
Invalid column name 'CONTR_TYPE'.
Server: Msg 207, Level 16, State 1, Line 134
Invalid column name 'CONTRACTOR_NUMBER'.
What is going on? Shouldn't this work?
Thanks y'all!
DarrellI can't be too sure without the DDL, but I'll ask the question of why would
you do this update in a cursor? Also, bad idea to do SELECT * in a cursor.
"Darrell" <Darrell.Wright.nospam@.okc.gov> wrote in message
news:eQyhwdlWGHA.1220@.TK2MSFTNGP02.phx.gbl...
> Here is my code:
> DECLARE curCAED1 CURSOR FAST_FORWARD FOR
> SELECT * FROM okc_contr_license
> WHERE CONTR_TYPE = 'ME'
> ORDER BY CONTRACTOR_NUMBER
> OPEN curCAED1
> FETCH NEXT FROM curCAED1 INTO
> @.pContrType, @.pContractorNumber,
> @.pCityLicType, @.pCityLicNumber, @.pCityLicRenDate,
> @.pFirstName, @.pMiddleInitial, @.pLastName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> UPDATE PermitsSystemNew.dbo.CAED_CONVERSION --Error generated here--
> SET CAE_FName = @.pFirstName
> WHERE CONTR_TYPE = @.pContrType AND CONTRACTOR_NUMBER =
> @.pContractorNumber
> FETCH NEXT FROM curCAED1 INTO
> @.pContrType, @.pContractorNumber,
> @.pCityLicType, @.pCityLicNumber, @.pCityLicRenDate,
> @.pFirstName, @.pMiddleInitial, @.pLastName
> END
> CLOSE curCAED1
> DEALLOCATE curCAED1
> Here is my error:
> Server: Msg 207, Level 16, State 3, Line 134
> Invalid column name 'CONTR_TYPE'.
> Server: Msg 207, Level 16, State 1, Line 134
> Invalid column name 'CONTRACTOR_NUMBER'.
> What is going on? Shouldn't this work?
> Thanks y'all!
> Darrell|||Those columns don't exist, as you've read the results of the select
query into (presumably) @.pContrType and @.pContractorNumber.
What is the WHERE clause trying to achieve?
*** Sent via Developersdex http://www.examnotes.net ***|||First. Found the problem. The fields don't exist in the table being updated
(as
noted by Anthony Brown). Doh! My mistake.
Second. There is a one-to-many relationship. One record in the table being
updated to many records in the cursor table.
Third. Normally I would agree with your SELECT * principal. However, there a
re
only 8 columns in the table I am declaring the cursor on. So, it is under co
ntrol.
Thanks for your help and insight.
David D Webb said the following on 04/07/2006 11:09 AM:
> I can't be too sure without the DDL, but I'll ask the question of why woul
d
> you do this update in a cursor? Also, bad idea to do SELECT * in a cursor
.
>
> "Darrell" <Darrell.Wright.nospam@.okc.gov> wrote in message
> news:eQyhwdlWGHA.1220@.TK2MSFTNGP02.phx.gbl...
>|||> Second. There is a one-to-many relationship. One record in the table being
> updated to many records in the cursor table.
You are effectively making the relationship 1 to 1. The only update that
"counts" will the be last one for a given contractor_number. Unfortunately,
you have made it a somewhat random 1 to 1 relationship since the cursor is
not ordered by anything other than contractor_number. You could just have
easily used an aggregate (min, max - ly, there is no
chose_any_one_i_don't_care() aggregate function), performing the update much
more quickly and efficiently with a single statement.|||HAve you tried to run the update with some values to see if it works by
itself?
E.g.
begin tran
UPDATE PermitsSystemNew.dbo.CAED_CONVERSION --Error generated here--
SET CAE_FName = [some value]
WHERE CONTR_TYPE = [some value]
AND
CONTRACTOR_NUMBER = [some value]
rollback
It looks like the table PermitsSystemNew.dbo.CAED_CONVERSION does not have
those columns...
Let em kow how it goes...
"Darrell" wrote:

> Here is my code:
> DECLARE curCAED1 CURSOR FAST_FORWARD FOR
> SELECT * FROM okc_contr_license
> WHERE CONTR_TYPE = 'ME'
> ORDER BY CONTRACTOR_NUMBER
> OPEN curCAED1
> FETCH NEXT FROM curCAED1 INTO
> @.pContrType, @.pContractorNumber,
> @.pCityLicType, @.pCityLicNumber, @.pCityLicRenDate,
> @.pFirstName, @.pMiddleInitial, @.pLastName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> UPDATE PermitsSystemNew.dbo.CAED_CONVERSION --Error generated here--
> SET CAE_FName = @.pFirstName
> WHERE CONTR_TYPE = @.pContrType AND CONTRACTOR_NUMBER = @.pContractorNumb
er
> FETCH NEXT FROM curCAED1 INTO
> @.pContrType, @.pContractorNumber,
> @.pCityLicType, @.pCityLicNumber, @.pCityLicRenDate,
> @.pFirstName, @.pMiddleInitial, @.pLastName
> END
> CLOSE curCAED1
> DEALLOCATE curCAED1
> Here is my error:
> Server: Msg 207, Level 16, State 3, Line 134
> Invalid column name 'CONTR_TYPE'.
> Server: Msg 207, Level 16, State 1, Line 134
> Invalid column name 'CONTRACTOR_NUMBER'.
> What is going on? Shouldn't this work?
> Thanks y'all!
> Darrell
>