Enter maximum value on the basis of 2 independent tables.

  • Hi Folks, 
    I stuck in a puzzle, please suggest a way to resolve this. 

    I have 2 below Tables in my database.
    Tab_A (OrderNo, Col1, Col2.......Coln),
    Tab_B (OrderHeaderNo, Col1,Col2, Col3........Coln)

    Case: At the time of data insertion in both tables, orderNo/OrderHeaderNo should be a max of existing both columns. (New number should not exist in any table's columns ). We have a procedure to insert the data into tables, which is triggered by the application

    I created below function to get maximum value from both tables. but if 2 different users create order then the duplicate value has been inserted. I want a solution which can avoid duplicate values.
    ALTER FUNCTION [dbo].[GetMaxOrderNo]
    (
        
    )
    RETURNS int
    AS
    BEGIN
      DECLARE @MaxOrderNo AS INT
        SELECT @MaxOrderNo = max(cast(a.OrderNo as int)) from
        ( SELECT OrderNo as OrderNo from OM_SalesOrderHeader
         UNION
         SELECT InterBranchOpenOrderNo from InterBranchOpenOrderHeader
        ) AS a

        RETURN @MaxOrderNo

    END

  • anujkumar.mca - Saturday, February 10, 2018 5:44 AM

    Hi Folks, 
    I stuck in a puzzle, please suggest a way to resolve this. 

    I have 2 below Tables in my database.
    Tab_A (OrderNo, Col1, Col2.......Coln),
    Tab_B (OrderHeaderNo, Col1,Col2, Col3........Coln)

    Case: At the time of data insertion in both tables, orderNo/OrderHeaderNo should be a max of existing both columns. (New number should not exist in any table's columns ). We have a procedure to insert the data into tables, which is triggered by the application

    I created below function to get maximum value from both tables. but if 2 different users create order then the duplicate value has been inserted. I want a solution which can avoid duplicate values.
    ALTER FUNCTION [dbo].[GetMaxOrderNo]
    (
        
    )
    RETURNS int
    AS
    BEGIN
      DECLARE @MaxOrderNo AS INT
        SELECT @MaxOrderNo = max(cast(a.OrderNo as int)) from
        ( SELECT OrderNo as OrderNo from OM_SalesOrderHeader
         UNION
         SELECT InterBranchOpenOrderNo from InterBranchOpenOrderHeader
        ) AS a

        RETURN @MaxOrderNo

    END

    Why not use an identity column?. Triggering the max + 1 logic via the application/calling would make the process to follow a queue(ie make the user1 transaction complete in the proc. followed by user 2).

  • george-178499 - Saturday, February 10, 2018 6:28 AM

    anujkumar.mca - Saturday, February 10, 2018 5:44 AM

    Hi Folks, 
    I stuck in a puzzle, please suggest a way to resolve this. 

    I have 2 below Tables in my database.
    Tab_A (OrderNo, Col1, Col2.......Coln),
    Tab_B (OrderHeaderNo, Col1,Col2, Col3........Coln)

    Case: At the time of data insertion in both tables, orderNo/OrderHeaderNo should be a max of existing both columns. (New number should not exist in any table's columns ). We have a procedure to insert the data into tables, which is triggered by the application

    I created below function to get maximum value from both tables. but if 2 different users create order then the duplicate value has been inserted. I want a solution which can avoid duplicate values.
    ALTER FUNCTION [dbo].[GetMaxOrderNo]
    (
        
    )
    RETURNS int
    AS
    BEGIN
      DECLARE @MaxOrderNo AS INT
        SELECT @MaxOrderNo = max(cast(a.OrderNo as int)) from
        ( SELECT OrderNo as OrderNo from OM_SalesOrderHeader
         UNION
         SELECT InterBranchOpenOrderNo from InterBranchOpenOrderHeader
        ) AS a

        RETURN @MaxOrderNo

    END

    Why not use an identity column?. Triggering the max + 1 logic via the application/calling would make the process to follow a queue(ie make the user1 transaction complete in the proc. followed by user 2).

    Agreed. Identity in the OrderHeader table seems easiest solution.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • @Phil \ @george - OrderNo and OrderHeadrNo are two different columns of different tables. Value of both columns must be different that's why identity at a column will not help.

  • How about an identity column for each of the tables then?. 
    Or do you have a need that the value of the orderno being used in TAB_A should NOT be the one used in OrderHeaderNo of TAB_B
    TAB_A        
    OrderNo    Col1    Col2
    1    Text_1    Text_2
    2    Text_1    Text_2
    3    Text_1    Text_2
            
    TAB_B        
    OrderHeaderNo    Col1    Col2
    4    Text_A    Text_2_B
    5    Text_A    Text_2_B
    6    Text_A    Text_2_B

    If so then the question would arise as to what is your goal that you wish to accomplish?

  • anujkumar.mca - Saturday, February 10, 2018 8:01 AM

    @Phil \ @george - OrderNo and OrderHeadrNo are two different columns of different tables. Value of both columns must be different that's why identity at a column will not help.

    How is an order linked between the tables, if these numbers are different?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Sounds like you're getting a dirty read on the database.  Are you using WITH(NOLOCK)?

  • Phil Parkin - Saturday, February 10, 2018 11:03 AM

    anujkumar.mca - Saturday, February 10, 2018 8:01 AM

    @Phil \ @george - OrderNo and OrderHeadrNo are two different columns of different tables. Value of both columns must be different that's why identity at a column will not help.

    How is an order linked between the tables, if these numbers are different?

    This, in my opinion, is the key question.  If the tables are related, I'd create an identity column on the parent and then the child table could reference the parent table by way of a foreign key.

    If the tables aren't related, then they wouldn't necessarily (from a database perspective) need to have sequential numbering across both of them.  If there's a business need that they have unique numbers across both of them, you might want to look into a sequence object. See https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql. Just be sure to start with the minimum value you can accept when you create it.  There's a section on that page on caching you'll want to understand as well.  This might do what you need.

    I've not had to use sequences in SQL Server, but I have in Oracle years ago because Oracle didn't have an identity property for a column back then.  I don't know about now.

  • You have to serialize the access to both tables at the same time, then get your max, do what you need and then commit the transaction letting others access it. There is no other way. If you don't serialize access, then you'll end up with duplicates if many people access the table at the same time performing the same action. Simple as that.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply