Unknown Value in Query Results - Warning Aberration# 102

Question

Scenario:
Using SQL 2008
A query that joins two tables where there may or may not be matching results in the second table.
One column is being requested from the 2nd table.

Observation:
When no matching results are found in the second table, the requsted column value is populated with the text 'Warning Aberration# 102'

I have not seen this before.

Question:
Any idea why this is happening and what I might change to prevent it?

Current Workaround:
The query is in a procedure - so I have placed a case statement in the sproc that replaces the bogus value with an empty string. (Workaround or Hack??? Pick your term - either fits!)

Answer 1

I can't find anything on that on Google or anywhere else.

What is the actual query you are executing?  Are you sure that 'Warning Aberration# 102' is not actual data from the second table?

---

--Brad (My Blog)

Answer 2

If you have access to the stored procedure and you can see NULL is not being replace by the warning message, I would say it's a bug.

---

Abdallah, PMP, ITIL, MCTS

Answer 3

Can you post the query producing this result? Also, are you populating a table with this result? Does this table have a Default value in that column?

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 4

This isn't a SQL Server error and must be coming from your data or via error handling of some sort.

How do you replace a "bogus" value if there isn't a match in the first place?

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 5

Yes, I arrived here after having no luck with Google also!!!!

Actual query is:

SELECT SessionId, UserName, Selected, Selection, Note, CustomerInfo, UserClicked, LoginDate,
case when dbo.table2.busname='Warning Aberration# 102' then ''
else dbo.table2.busname end as busname

FROM dbo.table1 LEFT OUTER JOIN
dbo.table2 ON dbo.table1.UserName = dbo.table2.custcode

GROUP BY SessionId, UserName, Selected, Selection, Note, CustomerInfo, UserClicked, LoginDate, dbo.table2.busname

HAVING (DATEDIFF(mi, LoginDate, GETDATE()) < (@AgeMinutes + 1))
ORDER BY LoginDate DESC

Answer 6

OK - thanks making me go check my assumptions.
I had assumed that SQL Server was producing the content. Because why would my client have saved a customer with that busname????

But, on your promptings I opened the table and found the first record with a custcode = 0 and a busname = 'Warning Aberration# 102'!

So, this means that the query is deciding to grab that row when there is no matching custcode!
Within SQL an empty string or null value in table1 is matching to a row with 0 in table2.


Not what I would have expected. But mystery solved.


Thanks for your assistance.
Richard

Answer 7

The only way for that record to show up is where there is a 0 in dbo.tabl1.UserName as well.

A LEFT OUTER JOIN won't return results from dbo.table2 unless there is a match between table1.UserName and table2.custcode.  There is no other way to return a value from table2.busname.

So check table1 and see if there is a value of 0 in the UserName column.  I suspect there is.

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 8

If Table1's UserName is a VARCHAR that's empty and Table2's CustCode is an INTEGER, then SQL will CAST the empty VARCHAR value to an INTEGER before doing the comparison... which means it will CAST it to 0, which will match the 0 CustCode in Table2.  That's probably what's happening here.

By the way, the GROUP BY is unnecessary in your query.  You can just write it as:

select SessionId
     , UserName
     , Selected
     , Selection
     , Note
     , CustomerInfo
     , UserClicked
     , LoginDate
     , dbo.table2.busname
FROM dbo.table1 
LEFT OUTER JOIN dbo.table2 ON dbo.table1.UserName = dbo.table2.custcode
WHERE LoginDate > DATEDIFF(mi,-(@AgeMinutes+1),GETDATE())
ORDER BY LoginDate DESC

Notice I also changed the WHERE clause (which was your HAVING clause) so that it will take advantage of an index on LoginDate if one exists.

---

--Brad (My Blog)