April 21, 2008 at 7:21 am
I'm doing the following - points of note include 1) I can create the table with no trouble, 2) I can select from the table with no trouble (which of course does me no good with nothing in it) 3) cannot INSERT into the stupid table, instead I get this error: Server: Msg 208, Level 16, State 3, Line 26
Invalid object name '#temp'. <-- line 26 is first INSERT statement, to save you time.
Any help appreciated. Thanks!
Script follows ------------------------------>
IF object_id('tempdb..#temp') IS NOT NULL BEGIN DROP TABLE #temp END
CREATE TABLE #temp(
member varchar(100),
medicaidID varchar(25),
dateOfService datetime,
claimIDvarchar(15),
diag1 varchar(6),
diag2 varchar(6),
diag3 varchar(6),
diag4 varchar(6),
diag5 varchar(6),
diag6 varchar(6),
diag7 varchar(6),
diag8 varchar(6),
diag9 varchar(6),
diag10 varchar(6),
diag11 varchar(6),
diag12 varchar(6)
)
------------insert needed statements here to populate table with values
INSERT INTO #temp ('member','medicaidID','dateOfService') VALUES ('Doe, Jane ', '012345678901', '5/21/2007 00:00:00')
INSERT INTO #temp ('member','medicaidID','dateOfService') VALUES ('Doe, John', '234567890123', '10/27/2006 00:00:00')
INSERT INTO #temp ('member','medicaidID','dateOfService') VALUES ('Doe, James', '456789012345', '1/11/2006 00:00:00')
---------------end insert
-- Populate #temp with the claims history for each member during the timeframe requested
UPDATE #temp SET claimID =
(SELECT claim.claimid
FROM QNXT_PLANDATA_OH.dbo.claim claim
JOIN QNXT_PLANDATA_OH.dbo.enrollkeys enrollkeys ON enrollkeys.memid = claim.memid
WHERE #temp.medicaidID = enrollkeys.carriermemid
AND #temp.dateOfService <= claim.startdate)
-- Populate #temp diag1 - diag12 with the diagnosis code in sequence order
DECLARE @diagNumber int,
@query varchar(250)
SET @diagNumber = 0
SET @query = ''
WHILE @diagNumber < 12
BEGIN
------------- loop through the claimdiag.sequence, incrementing the @diagNumber
-- to catch all 12 possible diag codes, insert into #temp
SET @query = 'UPDATE #temp SET diag'+@diagNumber+' =
SELECT claimdiag.codeid
FROM claim
JOIN claimdiag ON claim.claimid = claimdiag.claimid
WHERE claimdiag.sequence = '+@diagNumber
EXEC @query
SET @diagNumber = @diagNumber + 1
END
SELECT * FROM #temp
-----End of Script-------->
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
April 21, 2008 at 7:30 am
just avoid using the quotes..
INSERT INTO #temp (member,medicaidID,dateOfService) VALUES ('Doe, Jane ', '012345678901', '5/21/2007 00:00:00')
this should work..
April 21, 2008 at 7:31 am
I might be missing something, but why have single-quotes around the column names in your insert statement?
I copy-and-pasted your code in Management Studio (I don't have a copy of Query Analyzer right now), and got an error because of the single-quotes. I removed those, and the insert statements worked just fine.
Maybe you have some option set that I don't, but I've never used single-quotes around column names. Like I say, maybe I'm missing something, but removing them seems to make this work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 21, 2008 at 8:22 am
Thanks guys, it's always something simple. As for why use the quotes, that's because of an ID-10-t error.
At least I'm not nuts, just sloppy.
Thanks again!
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply