Jump to content
bazzer747

How to Code SQL IN Statement correctly

Recommended Posts

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.

Share this post


Link to post

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)"

Share this post


Link to post

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 ...

Share this post


Link to post

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

Share this post


Link to post

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 ...

  • Like 1

Share this post


Link to post

Use the macro! 😉

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

 

  • Like 1

Share this post


Link to post
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, ...) )

  • Like 1

Share this post


Link to post
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 by aehimself

Share this post


Link to post
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
 

Share this post


Link to post
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.

Share this post


Link to post
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:

dfSelectToInSQL.thumb.gif.7e99cf5f46a69a0818807653bd61bf57.gif

 

And where in SQL looks like this:

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

Simply clever :P

Share this post


Link to post

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

 

 

Share this post


Link to post
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.

Share this post


Link to post

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.

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×