vvin_virtual('vCountry', 'NL', 'char', 2, NULL, 'true') doesn't put quotes around

[Theo Kusters]

Hi,
I am using the function vvin_virtual('vCountry', 'NL', 'char', 2, NULL, 'true') in my SQL statement. Showing the data with the default works fine, when filtering on the same default value (NL in this case) it returns SQL error
failure (SQLSTT=42703) - SQL0206 - Column or globale variabel NL not found.

In the logging I see that there are nog quotes around the filter value:
SQL snippet:

'&maxrecords=100&format=json&country=' concat NL concat '&ff=' concat 'Tilburg'

I am using the latest update: 6.0.20210421.0
6.0.20210322.0 doesn't have this error.
Regards,
Theo

[sean.lanktree]

Hi Theo -

Can you please post your SQL statement?

[Theo Kusters]

Yes sure: I removed our API key:

SELECT
t.*
FROM
json_table(
systools.httpgetclob(
'https://expressentry.melissadata.net/web/GlobalExpressFreeForm?id=' concat systools.urlencode(
'APIKEY',
''
) concat '&maxrecords=100&format=json&country=' concat vvin_virtual('vCountry', 'NL', 'char', 2, NULL, 'true') concat '&ff=' concat vvin_virtual('vSearch', '6862VB', 'char', 200, NULL, 'true'),
NULL
),
'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 

[sean.lanktree]

Hi Theo -

Can you locate the ID for this data source (this is a column in the Data Sources & Widgets grid).
Then check file VVDTA300 where VVID = YOUR_DATA_SOURCE_ID and VVFLD = 'vCountry'. We want to know what the value of VVVIRT is for this record.

thanks

[Theo Kusters]

SELECT
*
FROM
valence6.VVDTA300
WHERE
VVID = 1159
AND VVFLD = 'vCountry'; 

Name |Value |
-------+------------------------------------------------------------+
VVID |1159 |
VVFSEQ |0 |
VVSEQ |13 |
VVFLD |vCountry |
VVASFLD|vCountry |
VVLEN |2 |
VVDEC |0 |
VVLIT |0 |
VVLBL |vCountry |
VVFNC | |
VVGBY | |
VVCALC | |
VVTYPE |A |
VVKEYF | |
VVCCSID|37 |
VVVIRT |1 |
VVDFLT |NL | 

[sean.lanktree]

Thanks Theo, how are you providing the vCountry value to your data source when running the application? From an app variable, a filter field, or a session variable?

[Johnny Major]

Theo,

Thanks, so to be clear you have a filter defined on your widget for "vCountry" and when you enter NL on that filter it breaks?

Thanks

[sean.lanktree]

Hi Theo -

We have replicated your issue and will have it fixed on the next update.