How to Code SQL IN Statement correctly

[bazzer747 25]

Hi

I'm having great difficulty in getting the syntax correct on a query I need to make via a Firedac Q!uery.

 

Basically I want to set a WHERE clause that picks up all records that have a 'S' or 'R' in a certain field.

In 'normal' SQL this codes like this:

 

 SELECT  * FROM  tTableName WHERE  Status IN ('S', 'R') ORDER BY EventDate

 

... which works fine on the SQL Server, but how do I do this via a Firedac query?

It may look something like this:

 

qQueryName.Open('SELECT * FROM tTableName WHERE Status IN  :pSR ORDER BY EventDate',[ ('S','R') ] );

 

The issue is how do I format the parameter - the above doesn't work, nor any of the different ways I can think of of setting the parameter to 'S','R'.

 

Any help would be appreciated.

[Alexander Sviridenkov 356]

Parameters cannot be used for IN operator. In some cases you can use LIKE instead of IN.

[Attila Kovacs 629]

you have to pass it one by one, like:

 

SELECT * FROM tTableName WHERE Status IN (:ST0,:ST1)

 

thus, you have to build your query with

 

there is no "IN (:ONEPARAM)"

[bazzer747 25]

Many thanks,

 

I have this code working fine now:

 

qQuery.Open( 'SELECT * FROM tTableName WHERE Status  IN ( :pS, :pR  ) ORDER BY EventDate', [ 'S', 'R' ] );

 

Good to know there is a way ...

[mausmb 13]

Hi,

 

This code "qQuery.Open( 'SELECT * FROM tTableName WHERE Status  IN ( :pS, :pR  ) ORDER BY EventDate', [ 'S', 'R' ] );" can't work for a single or more than 2 parameters (just in case)

 

Param1:='( 'S','R' )'

Param2:='['S','R']'

 

qQuery.Open( 'SELECT * FROM tTableName WHERE Status  IN :Param1 ORDER BY EventDate, :Param2 );

 

I'm using that approach without Param2 (that part you should test it)

 

regards,

M

[Dmitry Arefiev 101]

For unlimited number of items for "IN" clause, may be possible to use SQL Server Table Valued Parameter. For details see "Object Pascal\Database\FireDAC\Samples\DBMS Specific\MSSQL\TVP" demo.

PS: I am not tested that ...

[Wloochacz 2]

Use the macro! 😉

fQ.SQL.Text := 'SELECT  * FROM  tTableName WHERE  Status IN (!InValues) ORDER BY EventDate';
fQ.MacroByName('InValues').AsRaw := '''S'', ''R''';
fQ.Open;

 

[aehimself 396]

On 1/21/2020 at 11:38 PM, Attila Kovacs said:

SELECT * FROM tTableName WHERE Status IN (:ST0,:ST1)

Be careful with DB engine limitations. I can not count how many times my code failed, because Oracle can't handle more than 1000 entries in a single IN operator.

I wrote a method to create the IN statement for me, which breaks the array up to chunks of 900:

( IDFIELD IN (:pVal1, :pVal2, [...], :pVal900) OR IDFIELD IN (:pVar901, :pVar902, ...) )

[Fr0sT.Brutal 900]

I saw multiple opinions that such big IN's are bad design. Where they come from?

[Alexander Sviridenkov 356]

28 minutes ago, Fr0sT.Brutal said:

I saw multiple opinions that such big IN's are bad design. Where they come from?

Such queries may be slow, After some number join  is faster,

[aehimself 396]

2 hours ago, Fr0sT.Brutal said:

I saw multiple opinions that such big IN's are bad design. Where they come from?

Client - Server architecture, mostly. User opens up the Client and selects which records to process / manipulate. We do have filtering capabilities (so instead of an array of IDs we could send a filter) but it requires critical thinking so users are afraid of it.

So we simply send 1-2-5-10k IDs to the Server. An other case would be if you need to fix data corruption in the database.

Edited February 21, 2020 by aehimself

[Wloochacz 2]

On 2/21/2020 at 9:20 AM, aehimself said:

Be careful with DB engine limitations. I can not count how many times my code failed, because Oracle can't handle more than 1000 entries in a single IN operator.

Indeed, but OP work with SQL Server.

And this database has no such limits
 

[Wloochacz 2]

On 2/21/2020 at 4:57 PM, Alexander Sviridenkov said:

Such queries may be slow, After some number join  is faster,

In e.g Firebird database, yes.

In MS SQL it does not matter.

[Wloochacz 2]

On 2/21/2020 at 4:28 PM, Fr0sT.Brutal said:

I saw multiple opinions that such big IN's are bad design. Where they come from?

In general, this is true, especially in SQL executed on the server side, such as stored procedures, triggers, etc.

And there, it's better to write like this:

select
   A.*
from foo1 A
inner join foo2 B on (A.ID = B.ParentID)

instead of this:

select
   A.*
from foo1 A 
where A.ID in (select B.ParentID from foo2 B)

Although the result will be identical.

However, in some cases it is simply convenient.
For example, in my application I pass an array of PrimaryKey values to ReportManager so that he can ask the server for the data he has selected on the list.

It works like this:

 

And where in SQL looks like this:

where TD.IdDevice in (10,12,16,23)

Simply clever :P

[Attila Kovacs 629]

you can also go with join if you want

;WITH t AS(
SELECT * FROM ( VALUES ('a'),('b'),('c') ) AS _(x)
)
,t2 AS(
SELECT * FROM ( VALUES ('a'),('b'),('d') ) AS _(y)
)

SELECT * FROM t
LEFT JOIN t2 ON t2.y=t.x

 

 

[Fr0sT.Brutal 900]

On 2/21/2020 at 8:55 PM, aehimself said:

Client - Server architecture, mostly. User opens up the Client and selects which records to process / manipulate. We do have filtering capabilities (so instead of an array of IDs we could send a filter) but it requires critical thinking so users are afraid of it.

So we simply send 1-2-5-10k IDs to the Server. An other case would be if you need to fix data corruption in the database.

Just as I suspected. Depending on DB capabilities, this could be solved using temporary tables.

[aehimself 396]

As time progressed, more and more new possible solutions were introduced, but since it's 20+ year old legacy code which was designed to have their filters this way... changing the frameworks filtering would require us to check and modify ~1000 frames on the Windows client and I guess it would break the web app too. There's close to no chance on changing this anymore 😞

 

I'm not a DB expert, I personally never used temporary tables; but I have a slight memory having issues / limitations with temporary tables on some engines. I could be wrong on this one, though.