Sigh ... weird issue

• vick.ram79

  SSCommitted

  Points: 1637

  More actions

  January 22, 2010 at 3:45 pm

  #142006

  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 !!

  How To Post[/url]

• edmund chan

  Say Hey Kid

  Points: 689

  More actions

  January 22, 2010 at 3:51 pm

  #1107629

  What are the data type for those two columns?

  Can you also post your "check" statements?

• LutzM

  SSC Guru

  Points: 107049

  More actions

  January 22, 2010 at 4:15 pm

  #1107635

  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

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• vick.ram79

  SSCommitted

  Points: 1637

  More actions

  January 22, 2010 at 4:55 pm

  #1107646

  Yeah, you did the right test.

  This makes it even more wierd.... I am getting the same result as yours when i run in query analyzer.

  but instead of table object i use a temp table and somehow i am not getting the same result..

  anyways . i will look into it tomorrow..

  thanks

  How To Post[/url]

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  January 22, 2010 at 5:24 pm

  #1107659

  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);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]