[firebird-support] Sequence

['Check_Mail' [e-mail] [firebird-support]]

Hello @ll,



in a table there a many records with prioritys 1, 4, ..



Now the user can set one record to 5 and after this, I would set all other
records before in steps of 10 and after this again:



For Example:



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



How can I realize this without a loop? Update table set prio = .? Or with
cte?



Thank you.



Best regards.



Olaf

['Leyne, Sean' [e-mail] [firebird-support]]

Olag,

Post by 'Check_Mail' ***@satron.de [firebird-support]
Before
Record 1 prio 3
Record 2 prio 10
Record 3 prio 18
Record 4 prio 20
Record 5 prio 30
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

['Check_Mail' [e-mail] [firebird-support]]

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-***@yahoogroups.com
[mailto:firebird-***@yahoogroups.com]
Gesendet: Donnerstag, 1. Dezember 2016 18:47
An: firebird-***@yahoogroups.com
Betreff: RE: [firebird-support] Sequence





Olag,

Post by 'Check_Mail' ***@satron.de [firebird-support]
Before
Record 1 prio 3
Record 2 prio 10
Record 3 prio 18
Record 4 prio 20
Record 5 prio 30
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

['Check_Mail' [e-mail] [firebird-support]]

.. and can I order all active records completly?



(without a loop)

Update table set prio = 1 to n where prio is not null order by prio?



Von: firebird-***@yahoogroups.com
[mailto:firebird-***@yahoogroups.com]
Gesendet: Freitag, 2. Dezember 2016 10:44
An: firebird-***@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-***@yahoogroups.com
<mailto:firebird-***@yahoogroups.com>
[mailto:firebird-***@yahoogroups.com]
Gesendet: Donnerstag, 1. Dezember 2016 18:47
An: firebird-***@yahoogroups.com
<mailto:firebird-***@yahoogroups.com>
Betreff: RE: [firebird-support] Sequence





Olag,

Post by 'Check_Mail' ***@satron.de [firebird-support]
Before
Record 1 prio 3
Record 2 prio 10
Record 3 prio 18
Record 4 prio 20
Record 5 prio 30
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

['Leyne, Sean' [e-mail] [firebird-support]]

.. and can I order all active records completly?

(without a loop)

<SL> Don't manage the records during your direct update, manage the order records via a AFTER UPDATE trigger...

<SL> 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-***@yahoogroups.com<mailto:firebird-***@yahoogroups.com> [mailto:firebird-***@yahoogroups.com]
Gesendet: Freitag, 2. Dezember 2016 10:44
An: firebird-***@yahoogroups.com<mailto:firebird-***@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-***@yahoogroups.com<mailto:firebird-***@yahoogroups.com> [mailto:firebird-***@yahoogroups.com]
Gesendet: Donnerstag, 1. Dezember 2016 18:47
An: firebird-***@yahoogroups.com<mailto:firebird-***@yahoogroups.com>
Betreff: RE: [firebird-support] Sequence



Olag,

Post by 'Check_Mail' ***@satron.de [firebird-support]
Before
Record 1 prio 3
Record 2 prio 10
Record 3 prio 18
Record 4 prio 20
Record 5 prio 30
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

[setysvar [e-mail] [firebird-support]]

Post by 'Check_Mail' ***@satron.de [firebird-support]
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?

Post by 'Check_Mail' ***@satron.de [firebird-support]
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-***@yahoogroups.com
firebird-support-***@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-***@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

[liviuslivius [e-mail] [firebird-support]]

Hi 
 
i do not read your whole info but maybe you need to use case statement
 
e.g your 3 update statements can be changed fro:
 
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;
 
to one:
 Update table set prio = CASE WHEN id = 20 THEN newprio(1) ELSE WHEN prio < newprio THEN prio – 1 ELSE WHEN prio > newprio THEN rio + 1 END ;
 
regards,
Karol Bieniaszewski
W dniu 2016-12-02 10:54:57 uÅŒytkownik 'Check_Mail' ***@satron.de [firebird-support] <firebird-***@yahoogroups.com> napisał:
 
.. and can I order all active records completly?
 
(without a loop)
Update table set prio = 1 to n where prio is not null order by prio?
 
Von: firebird-***@yahoog roups.com [mailto:firebird-***@yahoogroups.com]
Gesendet: Freitag, 2. Dezember 2016 10:44
An: firebird-***@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:< /p>
 
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-***@yahoogroups.com [mailto:firebird-***@yahoogroups.com]
Gesendet: Donnerstag, 1. Dezember 2016 18:47
An: firebird-***@yahoogroups.com
Betreff: RE: [firebird-support] Sequence
 
 
Olag,

Post by 'Check_Mail' ***@satron.de [firebird-support]
Before
Record 1 prio 3
Record 2 prio 10
Record 3 prio 18
Record 4 prio 20
Record 5 prio 30
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
 

 

['Check_Mail' [e-mail] [firebird-support]]

Thank you



Von: firebird-***@yahoogroups.com [mailto:firebird-***@yahoogroups.com]
Gesendet: Freitag, 2. Dezember 2016 15:16
An: firebird-***@yahoogroups.com
Betreff: Re: AW: [firebird-support] Sequence





Hi



i do not read your whole info but maybe you need to use case statement



e.g your 3 update statements can be changed fro:



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;



to one:

Update table set prio = CASE WHEN id = 20 THEN newprio(1) ELSE WHEN prio < newprio THEN prio – 1 ELSE WHEN prio > newprio THEN rio + 1 END ;



regards,

Karol Bieniaszewski



W dniu 2016-12-02 10:54:57 uÅŒytkownik 'Check_Mail' ***@satron.de <mailto:***@satron.de> [firebird-support] <firebird-***@yahoogroups.com <mailto:firebird-***@yahoogroups.com> > napisał:



.. and can I order all active records completly?



(without a loop)

Update table set prio = 1 to n where prio is not null order by prio?



Von: firebird-***@yahoog roups.com [mailto:firebird-***@yahoogroups.com]
Gesendet: Freitag, 2. Dezember 2016 10:44
An: firebird-***@yahoogroups.com <mailto:firebird-***@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:< /p>



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-***@yahoogroups.com <mailto:firebird-***@yahoogroups.com> [mailto:firebird-***@yahoogroups.com]
Gesendet: Donnerstag, 1. Dezember 2016 18:47
An: firebird-***@yahoogroups.com <mailto:firebird-***@yahoogroups.com>
Betreff: RE: [firebird-support] Sequence





Olag,

Post by 'Check_Mail' ***@satron.de [firebird-support]
Before
Record 1 prio 3
Record 2 prio 10
Record 3 prio 18
Record 4 prio 20
Record 5 prio 30
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