Why no result from TypeOf?
(1) By RB Smissaert (RBSmissaert) on 2020-10-11 08:36:36 [source]
The following SQL produces no errors, but no rows either. select typeof(cast((cast(p.age as real) / 5) as integer) * 5) as AgeG, typeof(avg(( select numeric_value from num_values as V where P.id = V.id and P.Sex = 'Female' and V.read_code = '42R4' ))) as avg_Ferritin_Male, typeof(avg(( select numeric_value from num_values as V where P.id = V.id and P.Sex = 'Male' and V.read_code = '42R4' ))) as avg_Ferritin_Female from Patients as P group by AgeG having AgeG between 19 and 97 order by AgeG asc limit 1 Why is this? age is an integer column and numeric_value is a real column, so I expect integer, real, real RBS
(2) By Ulrich Telle (utelle) on 2020-10-11 10:13:36 in reply to 1 [link] [source]
Your HAVING clause
"having AgeG between 19 and 97" causes this behaviour:
AgeG is not an integer, but a string as a result from the
typeof function. Therefore the HAVING clause is not fulfilled for any selected row, and you get an empty result set. Simply leaving out the HAVING clause should give you the expected result.
(3) By RB Smissaert (RBSmissaert) on 2020-10-11 11:38:30 in reply to 2 [link] [source]
Thanks for that. Indeed leaving out the having clause produces the expected result.