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.
| Author |
Topic |
|
charmada
Starting Member
2 Posts |
 Posted - 2011-01-24 : 10:35:30
|
| Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting the varchar value 'Active' to data type int.SELECT "vEbase"."EbClock" AS "Clock Number", (CASE WHEN Len((Substring("vEbase"."EbMiddleName",1,1))) = 1 THEN "vEbase"."EbLastName" + ', ' + "vEbase"."EbFirstName" + ' ' + (Substring("vEbase"."EbMiddleName",1,1)) + '.' ELSE "vEbase"."EbLastName" + ', ' + "vEbase"."EbFirstName" END) AS "LFull Name", "vEEmploy"."EeStatus" AS "Employment Status", (CASE WHEN "vEEmploy"."EeStatus" = 'Active' AND ("vEemploy"."EeDateBeg" <= '01/01/2006' AND ("vEemploy"."EeDateEnd" >= '12/31/2006' OR "vEemploy"."EeDateEnd" IS NULL)) THEN "vEEmploy"."EeStatus" ELSE 0 end) AS "Active 2006", (CASE WHEN "vEEmploy"."EeStatus" = 'Active' AND ("vEemploy"."EeDateBeg" <= '01/01/2007' AND ("vEemploy"."EeDateEnd" >= '12/31/2007' OR "vEemploy"."EeDateEnd" IS NULL)) THEN "vEEmploy"."EeStatus" ELSE 0 END) AS "Active 2007", (CASE WHEN "vEEmploy"."EeStatus" = 'Active' AND ("vEemploy"."EeDateBeg" <= '01/01/2008' AND ("vEemploy"."EeDateEnd" >= '12/31/2008' OR "vEemploy"."EeDateEnd" IS NULL)) THEN "vEEmploy"."EeStatus" ELSE 0 END) AS "Active 2008", (CASE WHEN "vEEmploy"."EeStatus" = 'Active' AND ("vEemploy"."EeDateBeg" <= '01/01/2009' AND ("vEemploy"."EeDateEnd" >= '12/31/2009' OR "vEemploy"."EeDateEnd" IS NULL)) THEN "vEEmploy"."EeStatus" ELSE 0 END) AS "Active 2009"FROM "vEbase" INNER JOIN "vEEmploy" ON "vEbase"."EbFlxID" = "vEEmploy"."EeFlxIDEb"WHERE ("vEbase"."EbArchive" <> 'Y' AND "vEbase"."EbFlagEmp" = 'Y')AND ("vEbase"."EbArchive" <> 'Y' AND ("vEEmploy"."EeDateBeg" <= '#enddate#' AND ("vEEmploy"."EeDateEnd" >= '#enddate#' OR "vEEmploy"."EeDateEnd" IS NULL))) |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-01-24 : 10:39:20
|
| CASEWHEN "vEEmploy"."EeStatus" = 'Active'AND ("vEemploy"."EeDateBeg" <= '01/01/2006'AND ("vEemploy"."EeDateEnd" >= '12/31/2006'OR "vEemploy"."EeDateEnd" IS NULL)) THEN "vEEmploy"."EeStatus"ELSE 0 end) AS "Active 2006","vEEmploy"."EeStatus" (which should be [vEEmploy].[EeStatus])is chanracter and returned from the case statement - therefore everything return must be character.The else clause cannot return 0 - it can return '0' if needed.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
charmada
Starting Member
2 Posts |
Posted - 2011-01-24 : 10:54:27
|
THANKS nigerlrivett That worked have a great day!!!quote:
Originally posted by nigelrivett
CASEWHEN "vEEmploy"."EeStatus" = 'Active'AND ("vEemploy"."EeDateBeg" <= '01/01/2006'AND ("vEemploy"."EeDateEnd" >= '12/31/2006'OR "vEemploy"."EeDateEnd" IS NULL)) THEN "vEEmploy"."EeStatus"ELSE 0 end) AS "Active 2006","vEEmploy"."EeStatus" (which should be [vEEmploy].[EeStatus])is chanracter and returned from the case statement - therefore everything return must be character.The else clause cannot return 0 - it can return '0' if needed.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
|
|
|
|
|
|
|
|
|
Getting conversion failed error see used script