Jump to content
Sign in to follow this  
Henry Olive

Firebird3 - WHERE MYFIELD IN : MYVALUE

Recommended Posts

I wish everyone a healthy day

I have a S.Proc and it's WHERE part like below

.....

WHERE MY FIELD IN  : MYVALUE 

I'm getting Token Unknown err. msg Line 12 Char 20  which is  :     ( myvalue='AA', 'BB' )

what am i doing wrong ?

Thank You
 

Share this post


Link to post
Guest

I do not think you can do that at all. (I should say that i read somewhere that this is a "popular request" and might be possible in the future.)

 

You get the error because the parser expects a (, not a :. The colon saying that MYVALUE is a parameter.

If we could do that, then we would have to be decided how a list of values should be represented in one and only one parameter. So we cannot.

Should it be a string? How to separate integers? What if it's a float field to the left. And so on.

 

1. You can use a subselect instead of the IN.

2. You could set up another table, fill it with the values and JOIN to that other table.

3. You could express it with OR.

 

Solution #2 is good if you potentially have a lot of values. Also you can use a GTT in order to make sure it happens on a per attachment or transaction basis (yes you would have to fill the table before each execution of your select, but that goes for the other methods in some way too).

There are other solutions out there.

 

Read the following chapter in the FB 3.0 docs, 4.2.3 Existential Predicates. It will help!

Share this post


Link to post
Guest

The OR way, i do not consider it as "flush" as a JOIN.

I do not think this should be a recommendation at all, it may blow up in other SQL Engines.

Also i think FB2.5 and later has a special syntax for this. It is documented, not used below.

 

PAR1 = null;

PAR2 = null;

PAR....

 

.... set the current values of PARn ...

 

select *

from table

where (((:PAR1 is null) or (field1 = :PAR1)) and

            ((:PAR2 is null) or (field2 = :PAR2)) ... )

Share this post


Link to post
5 hours ago, Dany Marmur said:

I do not think you can do that at all

Yes, you can't pass a list value as a parameter.

 

@Henry Olive try to be more precise in your question

If you use Firedac you can use a macro. 

if you use another connector with no macro implementation you can use replacestr or replacetext
 

SQL : String;

myvalue : String;

begin

SQL:='SELECT * WHERE MYFIELD IN (MYVALUE)';

myValue:=QuotedStr('AA')+','+QuotedStr('BB'); // you can use a stringlist to get the same result

S.SQL.Text:=ReplaceText(SQL,'MYVALUE',myvalue);

 

 

If you speak about a PSQL you can use a SQL STATEMENT
 

procedure whatever(myvalue : VARCHAR(160))

as

Declare variable STMT varchar(1024);

begin

STMT='SELECT * WHERE MY FIELD IN ('|| : MYVALUE||')';

FOR EXECUTE STATEMENT STMT INTO <list of output field>

  DO SUSPEND;

end;

But, in this case, it's to you to check SQL is correct and myvalue parameter good

Edited by Serge_G

Share this post


Link to post

select <field list> from aTable where aField in (a1,a2,a3,a4)
 

works well on my laptop. aField is integer column, a1 .. an = integer value.
WI-V2.5.9.27139 Firebird 2.5

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
Sign in to follow this  

×