topic Re: when i reload my code : My let List is being invisible in my sql query code when it has no value in QlikView

when i reload my code : My let List is being invisible in my sql query code when it has no value

emyemyemy — Thu, 23 Apr 2020 21:01:33 GMT

I have a let list selected from an excel file used in a where sql query :

SQL
set @MyValue= (select Mycolumn from (select column as MyColumn from `MydataBase`.`MyTable`
where column in ($(MyList)) ORDER BY ID desc limit 1 )a);

This code is correct only when i have a not null list but when i have an empty list the reloaded code showed a synthax error near from 'where column in () order by ID desc limit 1 )a);'

it shows like i wrote nothing between ' () ' !!

How can I make Mylist visible in my code even when it is null ? how can i add this ondition ?

Thank you for any help or attention !

Re: when i reload my code : My let List is being invisible in my sql query code when it has no value

marcus_sommer — Fri, 24 Apr 2020 04:47:16 GMT

I think you will need something like this:

if (len(trim($(MyList))) >= 1 then
let vWhere = 'where column in ($(MyList))';
else
let vWhere = '';
end if

SQL
set @MyValue= (select Mycolumn from (select column as MyColumn from `MydataBase`.`MyTable`
$(vWhere) ORDER BY ID desc limit 1 )a);

- Marcus

Re: when i reload my code : My let List is being invisible in my sql query code when it has no value

emyemyemy — Sun, 26 Apr 2020 22:28:42 GMT

Hello Marcus !

Thank you for your answer , I did try your suggestion i think its a great idea using the $(vWhere) either i got an error script at 'len(trim($(MyList))) '

I Think this expression is incorrect counting List items !

I am trying to figure out the problem , and grateful for any help or suggestion !

Re: when i reload my code : My let List is being invisible in my sql query code when it has no value

marcus_sommer — Mon, 27 Apr 2020 06:42:24 GMT

There was a bracket too much and the variable needs here to be set in quotes because the content is a string, therefore try it in this way:

if len(trim('$(MyList)')) >= 1 then
...

- Marcus