Error 137 (not again!)

• jere42

  Old Hand

  Points: 305

  More actions

  Follow

  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

• David Burrows

  SSC Guru

  Points: 65144

  More actions

  August 1, 2006 at 7:04 am

  #652570

  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

  Follow

  August 1, 2006 at 7:14 am

  #652576

  Bingo. You rock!

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

   

  Thanks

  G