Error 137 (not again!)

Question

Post reply

Error 137 (not again!)

jere42

Old Hand

Points: 305
More actions
August 1, 2006 at 6:45 am

#90349

Hi,
I can't figure out what is happening here... actually I do and don't. With the following code I get an "Error 137: Must declare variable '@base'..." message but, I declared it and it parses fine if I comment out the IF block of code (at the end).
I didn't make the basic mistake of using a variable name in an EXEC string. Saw that before but, I am definitely missing something here
Tnx in advance
Gerald
INSERT INTO @base
SELECT DISTINCT
dbo.tbl0150ClaimWorksheet.CSR_USER_ID_NUM, dbo.tbl0150ClaimWorksheet.CORP_RECD_DATE, dbo.tbl0150ClaimWorksheet.CORP_RECD_TIME,
dbo.tbl0150ClaimWorksheet.CLM_RSN_CODE AS WSR, dbo.tbl0170ProfWorksheet.NPMT_CODE AS NP, 1 AS mark
FROM   dbo.tbl0150ClaimWorksheet INNER JOIN
dbo.tbl0010CallDetail ON dbo.tbl0150ClaimWorksheet.CSR_USER_ID_NUM = dbo.tbl0010CallDetail.CSR_USER_ID_NUM AND
dbo.tbl0150ClaimWorksheet.CORP_RECD_DATE = dbo.tbl0010CallDetail.CORP_RECD_DATE AND
dbo.tbl0150ClaimWorksheet.CORP_RECD_TIME = dbo.tbl0010CallDetail.CORP_RECD_TIME
INNER JOIN dbo.tbl0170ProfWorksheet ON
dbo.tbl0170ProfWorksheet.CSR_USER_ID_NUM = dbo.tbl0150ClaimWorksheet.CSR_USER_ID_NUM AND
dbo.tbl0170ProfWorksheet.CORP_RECD_DATE = dbo.tbl0150ClaimWorksheet.CORP_RECD_DATE AND
dbo.tbl0170ProfWorksheet.CORP_RECD_TIME = dbo.tbl0150ClaimWorksheet.CORP_RECD_TIME
WHERE (dbo.tbl0150ClaimWorksheet.CORP_RECD_DATE BETWEEN @sop AND @eop) AND
(dbo.tbl0010CallDetail.COST_CTR_NUM LIKE @ccid) AND
(dbo.tbl0150ClaimWorksheet.LOB_CODE = 'S') AND
(dbo.tbl0150ClaimWorksheet.PROV_NUM LIKE @provid)
IF (@grp != '%' OR @sfx != '%')      -- We are filtering on grp/sfx so lose the records in the temp set that doesn't work. All the other filters were able to be done in the base query!
begin
DELETE FROM @base WHERE NOT EXISTS
(SELECT GRP_NUM, SFX_NUM FROM dbo.tbl0020Subscriber AS S
WHERE (@base.CSR_USER_ID_NUM = S.CSR_USER_ID_NUM) AND (@base.CORP_RECD_DATE = S.CORP_RECD_DATE) AND
(@base.CORP_RECD_TIME = S.CORP_RECD_TIME) AND (S.GRP_NUM LIKE @grp) AND (S.SFX_NUM LIKE @sfx))
end

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65141 More actions · Answer 1

You cannot use table variable names to specify columns you have to use an alias, like this

DELETE b

FROM @base b

WHERE NOT EXISTS

(SELECT GRP_NUM, SFX_NUM

FROM tbl0020Subscriber AS S

WHERE (b.CSR_USER_ID_NUM = S.CSR_USER_ID_NUM)

AND (b.CORP_RECD_DATE = S.CORP_RECD_DATE)

AND (b.CORP_RECD_TIME = S.CORP_RECD_TIME)

AND (S.GRP_NUM LIKE @grp) AND (S.SFX_NUM LIKE @sfx))

Far away is close at hand in the images of elsewhere.
Anon.

jere42 Old Hand Points: 305 More actions · Answer 2

Bingo. You rock!

I wish Microsoft would have made that clear in the docs.

Thanks

G