January 22, 2010 at 3:45 pm
So I have a table like following:
CodeName SARatio
---------- -------
AB 1
I want to check if the row is anything other then 1, then I should log an error into another table.
So I write:
INSERT INTO #tblSpecialAllocError([LegalName], ErrDesc)
SELECT '','Please provide SA Ratio for all consumers which sums to 100% for ' + CodeName
FROM #tblSARatioCode
WHERE SARatio <> 1
For some reason, the row AB is being entered as an error row.... I can't figure out what could be so wierd about this query. Its pretty straightforward.... or is it my eyes playing some games !!
January 22, 2010 at 3:51 pm
What are the data type for those two columns?
Can you also post your "check" statements?
January 22, 2010 at 4:15 pm
I cannot confirm what you describe. Please provide sample data that allow us to verify the scenario. The following works just as expected (no output):
DECLARE @t TABLE (CodeName CHAR(2), SARatio INT)
INSERT INTO @t VALUES('AB',1)
SELECT 'Please provide SA Ratio for all consumers which sums to 100% for ' + CodeName
FROM @t
WHERE SARatio <> 1
January 22, 2010 at 4:55 pm
January 22, 2010 at 5:24 pm
Your field SARatio is a percentage held as a number between 0 and 1?
Try ...
WHERE ABS(SARatio - 1)>=0.01
just to see if you are getting the line selected due to it being a float column. (Remember FLOAT data is not (ironically here) 100% accurate and the SARatio may not be exactly 1 even when you think it should be)
ABS(SARatio -1) >= 0.01 is testing to see if SARatio is at least 0.01 away from 1 (anything less than 0.01 away from 1 being treated as equal to 1)
If that works, you may want to consider either holding your SARatio in another data type or adding a 100% flag which is on or off because that WHERE clause will cause table / index scans.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
January 22, 2010 at 9:06 pm
vick.ram79 (1/22/2010)
So I have a table like following:
CodeName SARatio
---------- -------
AB 1
it looks like you have a great link in your signature and you forgot to follow that 😛
Anyways, are you hiding the business logic and giving us a simplified version? this works too
CREATE TABLE #t (CodeName CHAR(2), SARatio INT)
INSERT INTO #t VALUES('AB',1)
SELECT 'Please provide SA Ratio for all consumers which sums to 100% for ' + CodeName
FROM #t
WHERE SARatio <> 1
when you say..Query Analyzer.. is it SSMS you are referring?
---------------------------------------------------------------------------------
January 22, 2010 at 10:14 pm
Please provide the table structure. It seems like there might be a datatype mismatch between the query and the table.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 22, 2010 at 10:57 pm
Mr. Magoo,
It works when I use your check, ABS(SARatio - 1)>=0.01.
There are no errors reported (which is correct). And yes, the column is a float.
But I swear I was doing a Cast(SARatio As Int) = 1, and it was failing !
Would not a cast take care of this float mismatch??
Thanks again ...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply