0

Global Variable used correctly?

I have a very long string (469 characters comma-separated quoted list) that I have set in a global variable.

The global variable content is in a similar form to this: 'a','b','c','d'

I am attempting to use this in the full query for a table in the WHERE clause using the IN operator like so: 

select
    col1,
    col2
from
    sqlTable
where
    col1 in ($$gv_list)

When I save and try to run this, no rows are loaded. I am using version 4.9.7.

4replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi Marc, 
    A global variable does not support an array of values. Only a scalar value is supported. Thus, the value you entered for the global variable will be considered as a string with all its quotes and commas. That's why the query won't return any rows.

    Like
    • Emma Mohamed The expectation was that the quotes and commas would be put in the gv's place and the normal IN() would work.

      Like
  • Marc,

    This may be possible depending on which database you are using.

    I tried something similar using MySQL database as a source and was able to do your use case.

    This 3 minute video shows how to do this use case:

    Like 2
    • Anurag Malik Thanks. I tried using the STRING_SPLIT from Microsoft SQL but it was not recognized by the engine. Your video explains the exact use case for me. I use this list in dozens of SQL statements.

      Like
Like Follow
  • 3 wk agoLast active
  • 4Replies
  • 24Views
  • 3 Following

Product Announcement

Incorta 5 is now Generally Available