topic Re: break and combine statements in script in QlikView

break and combine statements in script

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

I am trying to find an answer on how to break and combine statements in script. I am passing more than 600 numbers as one parameter to a stored procedure. Because this is too long and has been cut into two lines when I paste into the script window automatically. In the middle of the data loading, an error message stated:Error converting data type varchar to numeric. However, testing with 500 numbers in one line, it works just fine.

Your help will be much appreciated.

Re: break and combine statements in script

adamdavi3s — Fri, 09 Dec 2016 13:53:00 GMT

We would need to see how the string is created but you should be able to manipulate it and do the first 300 then the second 300 etc.... I am guessing the string is simply going over the max length for SQL (4000,8000 etc depending on version)

Is there nothing better you can do to avoid this situation though? sounds kind of awful to deal with!

Re: break and combine statements in script

addpharm — Fri, 09 Dec 2016 14:01:52 GMT

script:

exec SP '111,222,333,444, .......................9999999.'

the 600 numbers as one parameter are to be passed to the stored procedure. it couldn't be shortened unfortunately.

many thanks

Re: break and combine statements in script

marcus_sommer — Fri, 09 Dec 2016 14:23:56 GMT

I think you could use a variable for this like:

let v = '111,222,';

let v = '$(v)' & '333';

let v = '$(v)' & '444';

...

just simplified here. Another method could be to load all these parameters into a table and then concatenating them to one value and again assign it to a variable.

- Marcus

Re: break and combine statements in script

addpharm — Fri, 09 Dec 2016 14:49:09 GMT

in VBA, it is easy to use like "-" and "&" to break the line:

exec SP '111,222,333 ' _

& '444 .....................99999999'

just wonder does the qlikview script provide the similar approach?

many thanks

Re: break and combine statements in script

marcus_sommer — Fri, 09 Dec 2016 14:55:21 GMT

AFAIK no - but both above mentioned variable-approaches will work.

- Marcus

Re: break and combine statements in script

addpharm — Fri, 09 Dec 2016 14:59:43 GMT

Thank you, Marcus