cast problem 8.3.1

<snip>
|>
|> postgres error log:
|> ERROR: operator does not exist: text = integer
|> HINT: No operator matches the given name and argument type(s). You
|> might need to add explicit type casts.
|> STATEMENT: update T_MON_DEVICE_STATUS set device_ip = $1 :: inet
|> , status = $2 :: integer , status_date = now () ,
|> last_event_log_no = case $3 when 0 then null else $4 :: integer
|> end where unit_serial_no = $5 :: text and device_name =
|> $6 :: text
|>
</snip>

You're not casting your $3.

Steve Clark escribió:

Hello List,

I can't seem to figure out what is this code that worked on 7.4.x.
I've added cast to everything but still
get:

postgres error log:
ERROR: operator does not exist: text = integer
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.
STATEMENT: update T_MON_DEVICE_STATUS set device_ip = $1 :: inet
, status = $2 :: integer , status_date = now () ,
last_event_log_no = case $3 when 0 then null else $4 :: integer
end where unit_serial_no = $5 :: text and device_name = $6 :: text

pgc code:
EXEC SQL UPDATE T_MON_DEVICE_STATUS
SET device_ip = :h_device_ip::inet,
status = :h_status::integer,
status_date = now(),
last_event_log_no =
case :h_event_log_no when 0 then null
else
:h_event_log_no::integer end
WHERE unit_serial_no = :h_unit_serial_no::text
AND device_name = :h_device_name::text;

table:
\d t_mon_device_status
Table "public.t_mon_device_status"
Column | Type | Modifiers
-------------------+--------------------------+-----------
unit_serial_no | character varying(15) | not null
device_name | character varying(64) | not null
device_ip | inet | not null
status | integer | not null
status_date | timestamp with time zone | not null
last_event_log_no | integer |
Indexes:
"pk_tmds_usn_dn" PRIMARY KEY, btree (unit_serial_no, device_name)
Foreign-key constraints:
"fk_tmds_usn" FOREIGN KEY (unit_serial_no) REFERENCES
t_unit_status_log(unit_serial_no) ON DELETE CASCADE

maybe someone else can see where I am going wrong.

Thanks in advance,
Steve

h_event_log_no is not casted to integer....maybe it's getting a string
and the problem is comparing to 0?

Charles Simard wrote:

<snip>
|>
|> postgres error log:
|> ERROR: operator does not exist: text = integer
|> HINT: No operator matches the given name and argument type(s). You
|> might need to add explicit type casts.
|> STATEMENT: update T_MON_DEVICE_STATUS set device_ip = $1 :: inet
|> , status = $2 :: integer , status_date = now () ,
|> last_event_log_no = case $3 when 0 then null else $4 :: integer
|> end where unit_serial_no = $5 :: text and device_name =
|> $6 :: text
|>
</snip>

You're not casting your $3.

thanks Charles and Rodrigo - that fixed it.

Steve