Subject Re: [firebird-support] In() Question
Author setysvar

Den 31.01.2018 10:08, skrev 'Check_Mail' check_mail@... [firebird-support]:

Hello @ll,


I have a configuration table with stations, who can be only one order active and stations, where it isn’t so. The list contains all stations, who only one order can be active. For example, if there an order in station 2, the user cannot start an order on station 1, 2 and 3. On stations 4 it isn’t so, on station 5 only station 5 will be checked.


Please not with a sql statement.


Station, onlyone, list

1                 1        1,2,3

2                 1        1,2,3

3                 1        1,2,3

4                 0

5                 1        5

6                 0

The sql where station in(1,2,3) works, but not if there an variable where station in(:varname). An cursor message occurs.

  How can I solve this? Perhaps per sql where station, the current station is 2 for example (..where station in (select station from table where onlyone …ends to 1 from 2 to 1 and ..onlyone ends while 1 is ending (station 4)..?

Of course you sql doesn't work, :varname should contain one value, not a set of values! You may circumvent your problem by doing
where station in (:varname1, :varname2, :varname3, :varname4...) and have as many varnames as the maximum number of values that could be of interest. However, I would rather recommend a fix: Don't use any onlyone column, but make a new table, let's call it SingleGroup:

MyGroup Station
1       1
1       2
1       3
2       5

Then your SQL could be modified to something like
FROM Station s
JOIN SingleGroup sg1 on s.Station = sg1.Station
JOIN SingleGroup sg2 on sg1.MyGroup = sg2.MyGroup
WHERE s.Station = :varname

This also allows for somewhat more complex situations, suppose things changed and you allowed station 1 and 3 to be independant of each other, whereas station 2 still couldn't be concurrent with either of them:

MyGroup Station
1       1
1       2
2       5
3       2
3       3