September 12, 2016 at 10:09 am
This statement gives me :
Msg 4104, Level 16, State 1
The multi-part identifier "dbo.EMP.ext" could not be bound.
The table EMP does exist, i am not doing any joins or using aliases.
I am simply checking is user has changed the value of ext, if they have i am inserting this row as a copy and changing the active bit to zero, then modifying(prepending) the empid with a 'tr', and i cannot figure out why it is giving this error.
IF [dbo].[EMP].ext <> @ext
BEGIN
--INSERT row OF CURRENT emp
INSERT INTO [dbo].[EMP]
([empid] ,[fname] ,[lname] ,[dept] ,[active] ,[ext] ,[date_effective]) --,[date_inserted],
[date_updated])
SELECT dbo.EMP.empid, dbo.EMP.fname, dbo.EMP.lname, dbo.EMP.dept, 0, dbo.EMP.ext,
dbo.EMP.date_effective
--,date_inserted, date_updated)
FROM [dbo].[EMP]
WHERE @lname = dbo.EMP.lname AND dbo.EMP.fname = @fname AND dbo.EMP.empnum =
@empnum
UPDATE [CAS].[dbo].[EMP] set empid = 'tr' + empid
WHERE lname = @lname AND ext = @ext AND active = 0
END
September 12, 2016 at 10:27 am
Quick thought, alias the table in the select
😎
BEGIN
--INSERT row OF CURRENT emp
INSERT INTO [dbo].[EMP]
([empid] ,[fname] ,[lname] ,[dept] ,[active] ,[ext] ,[date_effective])
SELECT
EE.empid
, EE.fname
, EE.lname
, EE.dept
, 0
, EE.ext
, EE.date_effective
FROM [dbo].[EMP] EE
WHERE @lname = EE.lname
AND EE.fname = @fname
AND EE.empnum = @empnum
September 12, 2016 at 1:00 pm
Appreciate the suggestion, but it still doesn't like it..
September 12, 2016 at 1:51 pm
Your conditional is faulty. Which row of dbo.emp do you want to check? You need a SELECT in there. Possibly something like
IF NOT EXISTS (SELECT 1 FROM dbo.Emp WHERE emp=@emp)
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply