locked
Is it possible to assign sequences under some conditions? RRS feed

  • Question

  • Hi all of you,

    CREATE TABLE [Test](
        [Id] [bigint] NOT NULL,
        [Department] [varchar](75) NULL
    ) ON [PRIMARY]

    I know the following lines are wrong it is just for your understanding:

    ALTER TABLE TEST
    ADD DEFAULT NEXT VALUE FOR DBO.SEQUENCE_A FOR Id  Id IF Department = 'BD'

    ALTER TABLE TEST
    ADD DEFAULT NEXT VALUE FOR DBO.SEQUENCE_B FOR Id IF Department = 'HR'

    Basic idea is assign different ranges into the same column depending the value of other column.


    • Edited by Enric Vives Monday, January 18, 2016 10:13 PM clarify the thread
    Monday, January 18, 2016 10:13 PM

Answers

  • You can try:

    ALTER TABLE table ADD Field AS (CASE WHEN Field IS 'BD' THEN 1 ELSE 0

    WHEN Field IS 'HR' Then 1 ELSE 0

    END)

    Or you could use a trigger


    Andy Tauber
    Data Architect
    The Vancouver Clinic
    Website | LinkedIn

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.


    • Edited by Andy Tauber Monday, January 18, 2016 10:18 PM
    • Marked as answer by Enric Vives Monday, January 18, 2016 10:22 PM
    Monday, January 18, 2016 10:17 PM

All replies

  • You can try:

    ALTER TABLE table ADD Field AS (CASE WHEN Field IS 'BD' THEN 1 ELSE 0

    WHEN Field IS 'HR' Then 1 ELSE 0

    END)

    Or you could use a trigger


    Andy Tauber
    Data Architect
    The Vancouver Clinic
    Website | LinkedIn

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.


    • Edited by Andy Tauber Monday, January 18, 2016 10:18 PM
    • Marked as answer by Enric Vives Monday, January 18, 2016 10:22 PM
    Monday, January 18, 2016 10:17 PM
  • I don't know if your suggestion will work ok, Do CASE structure plus ADD DEFAULT NEXT and more literature in that point?

    But I think create a trigger is more polish

    Monday, January 18, 2016 10:21 PM