CODEWITHSUNDEEP
sqlpostgresqlcastinginner-join
Rawhi
Rawhi

Reputation: 6413

Nested SQL query searching the wrong table (postgreSQL)

I have two tables:

The type is a number between 1 and 10, the data column should be a number between 1 and 100 ("1", ..., "100") or "emptyX" (the X represents any character from "a" to "z")

The device which has the type 9 always has a number in the data column.

I need to update the data field for all the "type 9" devices which have a data bigger than 50 so that ==> data = data /2.

I've started with an INNER JOIN : 

select l.id 
from devices_log l 
inner join (select id from devices where type = 9) d on (l.device_id = d.id)

This statement returns all the logs for "type 9" devices, but when I add the where condition : 

select l.id 
from devices_log l 
inner join (select id from devices where type = 9) d on (l.device_id = d.id) 
where cast(data as INTEGER) > 50

I got this error :

ERROR: invalid input syntax for integer: "emptyG"

I've also tried so many statements that lead to the same error : 

select id
from devices_log 
where device_id in (select id from devices where type = 9) 
and cast(data as integer) > 50

select id from
(
  select id, device_id, cast (devices_log.data as integer) as int_data 
  from devices_log 
  join devices on (devices_log.device_id = devices.id
) and type = 9) ccs where ccs.int_data > 50

Any suggestions?

Thanks in advance

Upvotes: 1

Views: 71

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

SQL queries describe the result set, not the specific steps being taken. I actually thought this problem didn't appear in Postgres (I've seen it in other databases). I would start with this version:

select l.id
from devices_log l inner join
     devices d 
     on l.device_id = d.id
where d.type = 9 and cast(l.data as INTEGER) > 50 ;

If this doesn't fix the problem, then you can fix this with a case in the where:

select l.id
from devices_log l inner join
     devices d 
     on l.device_id = d.id
where d.type = 9 and
      (case when d.type = 9 then cast(l.data as INTEGER) end) > 50 ;

The case should not evaluate the then unless the condition is true.

Upvotes: 2

Related Questions