Re: [firebird-support] Sequence
Olaf wrote 02.12.2016 10:43: >Also I can do the following: > >Priority in steps of 1 > >ID 10 Prio 1 >ID 20 Prio 2 >ID 23 Prio 3 >ID 11 Prio 4 > >Now the user can Set the ID 23 to Prio 2, how can I realize this without a loop? > >Update table set prio = newprio(1) where id = 20; >Update table set prio = prio – 1 where prio < newprio; >Update table set prio = prio + 1 where prio > newprio; > >..more simplier? UPDATE OLAF_TABLE SET PRIO = CASE WHEN ID = :ID THEN :NEW_VALUE -- The ID you explicitly want to set WHEN PRIO < :NEW_VALUE THEN PRIO - 1 -- When OldValue < NewValue, subtract from those that get lower priority WHEN PRIO > :NEW_VALUE THEN PRIO + 1 -- When OldValue > NewValue, add to those that get higher priority WHEN :OLD_VALUE < :NEW_VALUE THEN PRIO - 1 -- The record with the same value as NewValue should also be subtracted/added ELSE PRIO + 1 END WHERE PRIO BETWEEN IIF(:OLD_VALUE < :NEW_VALUE, :OLD_VALUE, :NEW_VALUE) AND IIF(:OLD_VALUE < :NEW_VALUE, :NEW_VALUE, :OLD_VALUE) I haven't tried the statement, and there may well be some errors (e.g. it wouldn't surprise med if the comparison part of the IIF complained about 'unknown type'). HTH, Set ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
RE: [firebird-support] Sequence
.. and can I order all active records completly? (without a loop) Don't manage the records during your direct update, manage the order records via a AFTER UPDATE trigger... Using a "separator" in the priority range is good, you can use it to manage the scope of the changes required. You would not need to update every row, just enough rows to establish the new sequence. Update table set prio = 1 to n where prio is not null order by prio? Von: firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com> [mailto:firebird-support@yahoogroups.com] Gesendet: Freitag, 2. Dezember 2016 10:44 An: firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com> Betreff: AW: [firebird-support] Sequence Okay, I would simply change the prio new in steps of 10. Also I can do the following: Priority in steps of 1 ID 10 Prio 1 ID 20 Prio 2 ID 23 Prio 3 ID 11 Prio 4 Now the user can Set the ID 23 to Prio 2, how can I realize this without a loop? Update table set prio = newprio(1) where id = 20; Update table set prio = prio - 1 where prio < newprio; Update table set prio = prio + 1 where prio > newprio; ..more simplier? Von: firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com> [mailto:firebird-support@yahoogroups.com] Gesendet: Donnerstag, 1. Dezember 2016 18:47 An: firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com> Betreff: RE: [firebird-support] Sequence Olag, > Before > Record 1 prio 3 > Record 2 prio 10 > Record 3 prio 18 > Record 4 prio 20 > Record 5 prio 30 > > The user set the record 4 to prio 15, I would like to do this: > > Record 1 from 3 to 10 > Record 2 from 10 to 20 > Record 3 from 20 (should 15, Destination between record 2 and record 3)) to > 30 > Record 4 to 40 > Record 5 to 50 You example is confusing. If a user can never change the position of Record 4 ahead of Record 3 (as is the case in your example) what purpose does priority serve. If you had said that the outcome you wanted was: Record 1 from 3 to 10 Record 2 from 10 to 20 Record 4 to 30 <-* Record 3 from 20 (should 15, Destination between record 2 and record 3)) to 40 <-* Record 5 to 50 Then that would have made sense. Please clarify, the problem domain does matter to the solution. Sean
RE: [firebird-support] Sequence
Olag, > Before > Record 1 prio 3 > Record 2 prio 10 > Record 3 prio 18 > Record 4 prio 20 > Record 5 prio 30 > > The user set the record 4 to prio 15, I would like to do this: > > Record 1 from 3 to 10 > Record 2 from 10 to 20 > Record 3 from 20 (should 15, Destination between record 2 and record 3)) to > 30 > Record 4 to 40 > Record 5 to 50 You example is confusing. If a user can never change the position of Record 4 ahead of Record 3 (as is the case in your example) what purpose does priority serve. If you had said that the outcome you wanted was: Record 1 from 3 to 10 Record 2 from 10 to 20 Record 4 to 30 <-* Record 3 from 20 (should 15, Destination between record 2 and record 3)) to 40 <-* Record 5 to 50 Then that would have made sense. Please clarify, the problem domain does matter to the solution. Sean
