Validating Staging Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Validating Staging Table

Author

Topic

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-12-07 : 12:25:32

What is wrong? (the dumb question of the day)
I have tried this query as it is and placing a not( just after the Where and a ) at the end
in both instances there are 0 rows affected no errors

UPDATE   tblEEfromTXT
SET      tblEEfromTXT.errDesc = 'Passed format validation'
FROM	 tblEEfromTXT INNER JOIN dbo.tblGroupContract GRP ON 
	 tblEEfromTXT.groupNum = GRP.groupNum AND tblEEfromTXT.location = '0' + GRP.location
WHERE    (RTRIM(tblEEfromTXT.lastname1) <> '') AND
	 (RTRIM(tblEEfromTXT.firstname) <> '') AND 
         (isnumeric(tblEEfromTXT.subscss) = 1) AND
	 (LEN(RTRIM(tblEEfromTXT.subscss)) = 9) AND
	 (ISDATE(tblEEfromTXT.birthdate) = 1) AND
	 (tblEEfromTXT.birthdate > '1/1/1880') AND 
	 (tblEEfromTXT.sex IN ('F', 'M')) AND
	 (CAST(tblEEfromTXT.relation1 AS TINYINT) BETWEEN 1 AND 4) AND
    	 (tblEEfromTXT.relation2 IN ('E', 'S', 'D')) AND
	 (ISNUMERIC(tblEEfromTXT.relExt) = 1) AND
	 (tblEEfromTXT.coverage IN ('I', 'P', 'T', 'D', 'F')) AND
	 (RTRIM(tblEEfromTXT.address1) <> '') AND 
	 (RTRIM(tblEEfromTXT.city) <> '') AND 
	 (RTRIM(tblEEfromTXT.state) <> '') AND
	 (ISNUMERIC (LEFT(tblEEfromTXT.zipCode, 5)) = 1) AND
    	 (RIGHT(tblEEfromTXT.zipCode, 4) = '    ') OR (ISNUMERIC(RIGHT(tblEEfromTXT.zipCode, 4)) = 1)) AND 
    	 ((ISDATE(tblEEfromTXT.dateHired)=1 AND 
             CAST(tblEEfromTXT.dateHired AS DATETIME) > '1/1/1880') OR 
             RTRIM(tblEEfromTXT.dateHired)='') AND 
	 (tblEEfromTXT.status IN ('S', 'D', 'M', 'L')) AND 
	 (tblEEfromTXT.otherDental IN ('Y', 'N')) AND
	 ((ISDATE(tblEEfromTXT.certExpirationCurrent) = 1 AND 
             tblEEfromTXT.certExpirationCurrent > '1/1/1880')
             OR RTRIM(tblEEfromTXT.certExpirationCurrent)='') AND 	 
         (tblEEfromTXT.handicap IN ('Y', 'N')) AND	 
	 (ISNUMERIC(tblEEfromTXT.cobraMths) = 1) AND 
         (CAST(tblEEfromTXT.cobraMths AS TINYINT) < 36) AND 
	 ((ISDATE(tblEEfromTXT.cobraExp) = 1 AND tblEEfromTXT.cobraExp > '1/1/1880') OR 
             RTRIM(tblEEfromTXT.cobraExp)='') AND 
	 ((ISDATE(tblEEfromTXT.cobraTermDate) = 1 AND tblEEfromTXT.cobraTermDate > '1/1/1880') OR 
             RTRIM(tblEEfromTXT.cobraTermDate)='') AND 
	 (tblEEfromTXT.actionCode IN ('A', 'C', 'T')) AND 
	 (ABS(DATEDIFF(day, CAST(tblEEfromTXT.actEffecDate AS DATETIME), GETDATE())) < 93) AND
	     (GRP.contractTo > GETDATE())

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*
A candle loses nothing by lighting another candle

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-07 : 13:15:58

You haven't given us enough information. We would need to see sample data that represents your problem and the expected result set. We can't just look at your query and know what it is supposed to do.

Tara

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-12-07 : 21:38:44

probably there shouldn't be any rows affected? check each criteria you have after the where clause and see if you can do a select using that criteria.

--------------------
keeping it simple...

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-12-13 : 14:40:57

Thanx for your reply