Informix

 View Only

 Why does DECODE a json column in view does not work?

Emanuel Reisinger's profile image
Emanuel Reisinger posted 10/13/26 09:54 AM

Hi,

Why does not work DECODE on a json column in a view?

drop table if exists decode_test;
create table decode_test (
  id int
 ,data_json json
 ,data_vchar varchar(16)
);
insert into decode_test (id) values (1);

drop view if exists decode_test_view;
create view decode_test_view(decode_json, decode_json_as_lvchar, decode_vchar, id, data_json, data_vchar) as
select 
  DECODE(data_json, null, 0, 1)
 ,DECODE(data_json::lvarchar, null, 0, 1)
 ,DECODE(data_vchar, null, 0, 1)
 ,id, data_json, data_vchar 
from decode_test
;

select 
  DECODE(data_json, null, 0, 1)
 ,DECODE(data_json::lvarchar, null, 0, 1)
 ,DECODE(data_vchar, null, 0, 1)
 ,id, data_json, data_vchar 
from decode_test
;

|decode_json|decode_json_as_lvchar|decode_vchar|id |data_json|data_vchar|
|-----------|---------------------|------------|---|---------|----------|
|0          |0                    |0           |1  |null     |null      |

select * from decode_test_view;

|decode_json|decode_json_as_lvchar|decode_vchar|id |data_json|data_vchar|
|-----------|---------------------|------------|---|---------|----------|
|1          |1                    |0           |1  |null     |null      |

Using

Informix 14.10FC11

Informix 15.0.1.7

BR

Emanuel

Emanuel Reisinger's profile image
Emanuel Reisinger posted 10/13/26 10:04 AM

Using a case instead of decode in the view works.

DECODE(data_json, null, 0, 1)

case when data_json is null then 0 else 1 end

But why does decode not work?

BR

Andreas Legner's profile image
Andreas Legner IBM Champions posted 10/14/26 06:14 AM

Good question ... looking like a defect (can you report this in a support case?)

There's also this:

select id from decode_test where data_json is null;

         id

          1

1 row(s) retrieved.

vs.

select NVL(data_json, 'NULL') from decode_test;


(U0001) - JSON Syntax error at character 1
Error in line 34
Near character position 45

while this does work:

select NVL(data_json::lvarchar, 'NULL') from decode_test;


(expression)  NULL

HTH,

 Andreas

Emanuel Reisinger's profile image
Emanuel Reisinger posted 10/14/26 06:36 AM

Case opened.

Related Content