Den 31.01.2018 10:08, skrev 'Check_Mail' [email protected]
[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
HTH,
Set
