SQLite Forum

Why no result from TypeOf?
Login

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.

RBS