August 1, 2006 at 6:45 am
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
August 1, 2006 at 7:04 am
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.
August 1, 2006 at 7:14 am
Bingo. You rock!
I wish Microsoft would have made that clear in the docs.
Thanks
G
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply