topic Using variable in from clause, numeric constant not valid in QlikView

Using variable in from clause, numeric constant not valid

marcusbohman — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am using a FOR EACH loop to get the same fields from different tables in a DB2 database.

_________

FOR EACH vCountry IN '1A','AT'

LOAD

FIELD1,

FIELD2,

FIELD3,

FIELD4

SQL SELECT FIELD1,

FIELD2,

FIELD3,

FIELD4

FROM $(vCountry)XXXXX.XXXX;

NEXT vCountry;

___________

'AT' runs just fine but '1A' raises "Numeric constant 1AXXXXX.XXXX is not valid" error. I don't understand why a variable with non-numerical characters can be interpreted as a number. Is there a way to force the variable to be a string? Or any other solution to make all elements in my list be treated as strings?

Kind Regards,

Marcus

Re: Using variable in from clause, numeric constant not valid

Clever_Anjos — Fri, 26 Jan 2018 12:26:09 GMT

It´s a DB2 error message, no Qlik

Re: Using variable in from clause, numeric constant not valid

marcusbohman — Fri, 26 Jan 2018 15:52:16 GMT

Thank you cleveranjos‌. I recognise that the error comes from DB2 but I think that a change in my QV script could avoid it. It seems to pass 'AT' as string but '1A' as numerical. Would you know any way of passing '1A' as string as well?

Or do you mean that I am posting in the wrong forum?

/Marcus

Re: Using variable in from clause, numeric constant not valid

Clever_Anjos — Mon, 29 Jan 2018 10:02:13 GMT

As long as I can see (maybe I wrong) that SQL below would return error even into your DB2 client

SELECT FIELD1,

FIELD2,

FIELD3,

FIELD4

FROM 1AXXXXX.XXXX;

Re: Using variable in from clause, numeric constant not valid

tamilarasu — Mon, 29 Jan 2018 10:10:26 GMT

Hi Marcus,

I am not sure but try adding single quotes around the variable name like below,

FOR EACH vCountry IN '1A','AT'

LOAD

FIELD1,

FIELD2,

FIELD3,

FIELD4;

SQL SELECT FIELD1,

FIELD2,

FIELD3,

FIELD4

FROM '$(vCountry)'XXXXX.XXXX;

NEXT vCountry;