The multi-part identifier "n.note_id" could not be

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

The multi-part identifier "n.note_id" could not be

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2007-12-07 : 14:16:48

Where am I going wrong???? I can not get this error message to go away.

CREATE PROCEDURE [dbo].[p_ConcatenateNotesForHCSReturnFileUPDATE]

AS
BEGIN

IF OBJECT_ID('IMPACT_prod..Notes2') IS NOT NULL
DROP TABLE Notes2

SELECT DISTINCT eve_id1 INTO Notes2 from EVE
ORDER BY eve_id1
ALTER TABLE Notes2 ADD NoteData VARCHAR(8000)

DECLARE @id varchar(8), @lineid int, @lastlineid int, @notes VARCHAR(8000)
DECLARE cur CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT eve_id1 FROM Notes2

OPEN cur
FETCH NEXT FROM cur INTO @id

WHILE @@FETCH_STATUS = 0
BEGIN --Begin outer while loop
SET @lastlineid = 0
SELECT @lineid = MIN(CAST(n.note_line AS int)) FROM notes n WHERE note_id = @id AND note_sys = 'EVE'
SET @notes = (SELECT note_data from notes where note_id = @id AND note_line = @lineid AND note_sys = 'EVE')
WHILE EXISTS (SELECT note_id FROM NOTES WHERE note_id = @id AND note_line > @lineid AND note_sys = 'EVE')
BEGIN
SET @lastlineid = @lineid
SELECT @lineid = MIN(CAST(n.note_line AS int)) FROM notes n
WHERE n.note_id = @id AND n.note_line > @lastlineid AND note_sys = 'EVE'
PRINT @id + ' -- Line ID: ' + cast(@lineid as varchar(8)) + ' -- Last Line ID: ' + cast(@lastlineid as varchar(8))
SET @notes = @notes + '||' + LTRIM(RTRIM((SELECT note_data from notes where note_id = @id AND note_line = @lineid AND note_sys = 'EVE')))
END --END INNER While loop
PRINT 'event ID: ' + cast(@id AS varchar(8)) + ':'
PRINT @notes
UPDATE Notes2
SET NoteData = @notes
WHERE eve_id1 = @id
FETCH NEXT FROM cur INTO @id
END --End Outer While Loop!
CLOSE cur
DEALLOCATE cur

SELECT Distinct
--n.note_sys,
--c.CLM_id1 AS 'ClaimNumber',
e.EVE_id1 AS 'EventNumber',
--e.EVE_clm As 'EventEventTable',
--c.CLM_elrc AS 'EventClaimTable',
e.EVE_STAT AS 'STATUS',
e.EVE_SW01 AS 'NEGOYESORNO',
e.EVE_EXNO AS 'HCSAuthorizationID',
--p.EVEP_LNAME AS 'PROVIDERLASTNAME',
--p.EVEP_FNAME AS 'PROVIDERFIRSTNAME',
p.EVEP_OFFIC AS 'PROVIDEROFFICE',
--c.CLM_EDID AS 'LOCKDATE',
--c.CLM_MCHG AS 'TOTALBILLEDAMT',
--c.CLM_SPPO AS 'SAVINGS',
--c.CLM_55d AS 'OONNEGO',
--c.CLM_ATT1,
--c.CLM_ATT2,
--c.CLM_ATT3,
--c.CLM_ATT4,
--c.CLM_ATT5,
NOTEDATA AS 'NOTES'
--left(n.NOTE_DATA, 10) AS 'NOTEStotakeoutprompt'

FROM dbo.EVE e
Right Join dbo.clm c
ON e.EVE_id1 = c.clm_id1
right Join dbo.EVEP p
ON e.eve_id1 = p.EVEP_id1
right Join Notes2
ON n.note_id = e.EVE_id1

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2007-12-07 : 14:21:28

FROM dbo.EVE e
Right Join dbo.clm c
ON e.EVE_id1 = c.clm_id1
right Join dbo.EVEP p
ON e.eve_id1 = p.EVEP_id1
right Join Notes2 n
ON n.note_id = e.EVE_id1

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2007-12-07 : 14:27:12

Thanks, it took that message away. Now I have this message.

Msg 207, Level 16, State 1, Procedure p_ConcatenateNotesForHCSReturnFileUPDATE, Line 78
Invalid column name 'note_id'.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2007-12-07 : 14:35:34

Notes2 does not have note_id it only contains eveid and NoteData

ON n.note_id = e.EVE_id1
should be
ON n.eve_id = e.EVE_id1

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-12-07 : 14:37:36

That would seem pretty easy to track down. is it note_id or noteid?

Make sure the column exists in the table
"notes" and that it is spelled properly. I didn't see anything in the posted code that referred to note_id as being in another table aside from notes (and it seems the alias n is always preceding it properly where needed)

Poor planning on your part does not constitute an emergency on my part.

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2007-12-07 : 14:39:14

Thank you sooo much!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources