Valid SQL statement rejected

[Theo Kusters]

Hi,

Please make it possible to use the SQL statement below. Currently it fails on the json_table(

Kind regards,
Theo

SELECT

t.*

FROM

json_table(

systools.httpgetclob( URL => CAST('https://expressentry.melissadata.net/web/GlobalExpressFreeForm?id='

CONCAT SYSTOOLS.URLENCODE('API_KEY','')

CONCAT '&maxrecords=100&format=json&country=NL&ff='

CONCAT SYSTOOLS.URLENCODE('xxx','') AS varchar(500)),

HTTPHEADER => CAST(NULL AS CLOB(5K)))

,

'lax $.Results'

columns (

Address varchar(100) PATH 'lax $.Address.Address' ,

Address1 varchar(100) PATH 'lax $.Address.Address1' ,

Address2 varchar(100) PATH 'lax $.Address.Address2' ,

CountryName varchar(100) PATH 'lax $.Address.CountryName' ,

ISO3166_2 varchar(100) PATH 'lax $.Address.ISO3166_2' ,

SuperAdministrativeArea varchar(100) PATH 'lax $.Address.SuperAdministrativeArea' ,

AdministrativeArea varchar(100) PATH 'lax $.Address.AdministrativeArea' ,

SubAdministrativeArea varchar(100) PATH 'lax $.Address.SubAdministrativeArea' ,

Locality varchar(100) PATH 'lax $.Address.Locality' ,

Thoroughfare varchar(100) PATH 'lax $.Address.Thoroughfare' ,

Building varchar(100) PATH 'lax $.Address.Building' ,

PostalCode varchar(100) PATH 'lax $.Address.PostalCode'

)) AS t; 

[Theo Kusters]

I found that the problem was with:
URL =>
HTTPHEADER =>
I could remove them from the statement and now it works.

[Johnny Major]

Hi Theo,

It appears after testing your statement the SQL parser doesn't support the number literal with size "CLOB(5K)".

We will add support for this in our next Valence 6.0 update. Any version after 6.0.20210421 will support this

Thanks