0

I have a firebird database and a query from excel. In the begining I have 

WHERE (table.myID IN (1,2,3))

that works to pass the parameter in SQL to firebird. My table.myID is with integers. Then I tried to pass parameters from excel with 

WHERE (table.myID IN (?))

where I point ? to one cell. If in the cell there is only one number that works. But if I ?=1, 2, 3 from one cell the query doesn't work. I tried to quote the context, format the cell but no success.

Other posts like this or this or this solve this puzzel with INSTR and VBA in examples with a list of text. I am new in firebird and doesn't know the similar INSTR command or another way to pass this list of integers.

So how can I pass a list of integers from one cell?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Luiz Carlos Stevanatto
  • 1
  • 2
  • 1
    [Parameterize an SQL IN clause](https://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause) – Lukasz Szozda Nov 28 '17 at 20:49
  • The problem happens with numbers. It is obscure for me what exactly excel pass from cell to ?. – Luiz Carlos Stevanatto Nov 29 '17 at 10:06
  • @LuizCarlosStevanatto you should make excel construct and pass the string, consisting of numbers delimited by and wrapped into the agreed separator symbols, that your SQL query would use in the LIKE clause – Arioch 'The Nov 29 '17 at 10:11
  • LIKE clause is basic ANSI SQL. It is weird to use non-standard visual basic functions when standard SQL offers the same. @lad2025 gave you MS SQL example of it - though frankly it should not matter as it is universal standard SQL, which every SQL server should implement. Firebird example is Route#2 at https://stackoverflow.com/a/43997801/976391 – Arioch 'The Nov 29 '17 at 10:21
  • `?=1, 2, 3 ` - would not do, the separators should be one characters (u have two - comma and space) and be added before and after list, so should be like `?=,1,2,3,` instead. And the `LIKE` clause must be reversed – Arioch 'The Nov 29 '17 at 10:24
  • [SOLVED] The query is **(?) CONTAINING '~' || table.myID || '~'** and in the cell is **'~1~2~3~'** . – Luiz Carlos Stevanatto Nov 29 '17 at 11:39
  • 1
    Please do not add things like '[SOLVED]' to question titles, instead accept the answer that solved your question (which for self-answered question has a timeout before you can accept) – Mark Rotteveel Nov 29 '17 at 15:34

1 Answers1

0

As Arioch 'The mentioned in the comments, the query is

(?) CONTAINING '~' || table.myID || '~'

and in the cell it is

'~1~2~3~'

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Luiz Carlos Stevanatto
  • 1
  • 2
  • Something not understood yet. There are something unexpected when I use one list with {111,112,113,114}. If I replace (?) by ~111~112~113~114~ in the SQL tool it works. But if I use ~111~112~113~114~ from one cell in excel the query is only the first three ~111~112~113~ and exclude ~114~. Why? – Luiz Carlos Stevanatto Jan 18 '19 at 16:09