Firebird3 - WHERE MYFIELD IN : MYVALUE

[Henry Olive 5]

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
 

[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!

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

[Serge_G 87]

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 December 13, 2021 by Serge_G

[irawan 2]

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

[Henry Olive 5]

Thank you SO MUCH  Dany, Serge, Irawan