April 8, 2009 at 10:31 am
In our processes, we validate years and create an error row if a year is not valid. On through our process, we do arithmetic operations on the year, but I only want to do these on the years that do not have errors.
I create a temp table (I've also tried Table Variables) of the certificates that the year fails and try to exclude these from my select. Instead, I get an error trying to convert a varchar to int. It seems that it is trying to add 1 to my years to ALL certificates, then it would pull these if they were not in my error temp table.
I have included code so you can see what I mean. I have commented out some different variations.
I want to avoid the ISNUMERIC check again in my select because it was done in an earlier process to produce an error row. With that said, I may have to do that to get it to work.
I just wondered if there was a way to change the optimizer to make it evaluate EXCLUSION or JOINs first, then do the arithmetic. Here's the code and thanks in advance with any ideas.
SET NOCOUNT ON
-- Create and declare error tables
DECLARE @ErrorTbl TABLE (CertNo INT)
CREATE TABLE #ErrorTbl (CertNo INT)
DECLARE @NOTErrorTbl TABLE (CertNo INT)
CREATE TABLE #NOTErrorTbl (CertNo INT)
CREATE TABLE #tblData ( CertNo INT
, StringYear CHAR(4))
INSERT #tblData (CertNo, StringYear) VALUES (1,'2001')
INSERT #tblData (CertNo, StringYear) VALUES (2,'2002')
INSERT #tblData (CertNo, StringYear) VALUES (3,'2003')
INSERT #tblData (CertNo, StringYear) VALUES (4,'2004')
INSERT #tblData (CertNo, StringYear) VALUES (5,'2005')
INSERT #tblData (CertNo, StringYear) VALUES (6,'20/1')
INSERT #tblData (CertNo, StringYear) VALUES (7,'2006')
INSERT #tblData (CertNo, StringYear) VALUES (8,'2007')
INSERT #tblData (CertNo, StringYear) VALUES (9,'2008')
INSERT #tblData (CertNo, StringYear) VALUES (10,'20/p')
INSERT #tblData (CertNo, StringYear) VALUES (11,'2009')
INSERT #tblData (CertNo, StringYear) VALUES (12,'2010')
--Put the CertNo into @ErrorTbl that are not numeric
INSERT @ErrorTbl (CertNo) SELECT CertNo FROM #tblData WHERE ISNUMERIC(StringYear) = 0
--Put the CertNo into #ErrorTbl that are not numeric
INSERT #ErrorTbl (CertNo) SELECT CertNo FROM #tblData WHERE ISNUMERIC(StringYear) = 0
-- Put the CertNo into @NOTErrorTbl for good years
INSERT @NOTErrorTbl (CertNo) SELECT CertNo FROM #tblData WHERE ISNUMERIC(StringYear) = 1
-- Put the CertNo into #NOTErrorTbl for good years
INSERT #NOTErrorTbl (CertNo) SELECT CertNo FROM #tblData WHERE ISNUMERIC(StringYear) = 1
--SELECT CertNo, StringYear
--FROM #tblData
--WHERE CertNo NOT IN (SELECT CertNo FROM #ErrorTbl)
-- AND StringYear + 1 < 2012
--SELECT a.CertNo, StringYear
--FROM #tblData a
-- INNER JOIN @NOTErrorTbl b
-- ON a.CertNo = b.CertNo
--WHERE StringYear + 1 < 2012
SELECT CertNo, StringYear
FROM #tblData
WHERE CertNo IN (SELECT CertNo FROM #NOTErrorTbl)
AND StringYear + 1 < 2012
DROP TABLE #tblData
DROP TABLE #ErrorTbl
DROP TABLE #NOTErrorTbl
SET NOCOUNT OFF
-- Al
April 8, 2009 at 10:52 am
Give this a try:
with GoodCerts (
CertNo,
StringYear
) as (
select
CertNo,
StringYear
from
#tblData
WHERE
ISNUMERIC(StringYear) = 1
)
select
*
from
#tblData td
inner join GoodCerts gc
on (td.CertNo = gc.CertNo)
where
gc.StringYear + 1 < 2012
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply