CODEWITHSUNDEEP
mysqlcoalesce
sqlnewb
sqlnewb

Reputation: 121

Coalesce pulling zero as value

this is driving me crazy!

I have three tables. The first table has a list of all records along with other data (region, dates, etc). The 2nd and 3rd tables have all the hours/cost data, but the 2nd table contains only historical records, and the 3rd table contains newer records. I want my coalesce to try to find a value in the newer records first, but if no record is found, to look in the historic table. For some reason, even though i KNOW there is a value in the historic table, the result of my coalesce is coming in as 0. 

Table1

ID     Region 
1        US 
2        US 
3        Europe 
4        US 
5        Europe 
6        US

Table2

ID     Hours
1        10
2        15
3        20


Table3

ID     Hours
4        3
5        7
6        4

So, my statement is written like this:

SELECT t1.ID, COALESCE(t3.hours, t2.hours) AS HOURS FROM table1 t1 LEFT JOIN table2 t2 ON t1.ID=t2.ID LEFT JOIN table3 t3 ON t1.ID=t3.ID

Now, for some reason, if the value is found in t3 (the newer records) it pulls in the correct value, but if it does not find a value and has to pull in a value from t2, it is pulling in a 0 instead of the actual value. Result looks like this:

ID     HOURS
1        0
2        0
3        0
4        3
5        7
6        4

My guess is that it has something to do with the column type in table 2 (I have all column settings as VARCHAR(55), but I can't find any rules around data types in coalesce function about having to use only a certain column type with coalesce.

Appreciate any guidance!

edited to add results for Spencer's inquiry:

ID      t2.hours + 0   t2.hours   hex(t2.hours)   length(t2.hours)
413190    240          240            F0                 3

Upvotes: 0

Views: 958

Answers (2)

spencer7593
spencer7593

Reputation: 108400

It looks like the evaluation of t2.hours in the COALESCE function is being done in numeric context, and the values stored in the hours column are evaluating to a numeric value of 0.

One quick way to check what numeric that evaluates to is to add a zero, as in the first expression in this query: 

 SELECT t2.hours + 0 
      , t2.hours
      , HEX(t2.hours)
      , LENGTH(t2.hours)
   FROM table2 t2

I'm curious what that query shows for one of the rows that's returning a 0 from the COALESCE expression, whether the numeric evaluation is returning 0, and whether there's any leading wonky characters in the column value.

Upvotes: 0

Marc B
Marc B

Reputation: 360642

Incorrect joins:

FROM table1 t1
LEFT JOIN table2 t2 ON t1.ID=t2.ID
                              ^
LEFT JOIN table3 t3 ON t1.ID=t2.ID
                              ^

you're joining table 3 using values from table 2

Upvotes: 2

Related Questions