February 10, 2018 at 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
February 10, 2018 at 6:28 am
anujkumar.mca - Saturday, February 10, 2018 5:44 AMHi 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 aRETURN @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).
February 10, 2018 at 6:51 am
george-178499 - Saturday, February 10, 2018 6:28 AManujkumar.mca - Saturday, February 10, 2018 5:44 AMHi 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 aRETURN @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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 10, 2018 at 8:35 am
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?
February 10, 2018 at 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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 15, 2018 at 1:13 pm
Sounds like you're getting a dirty read on the database. Are you using WITH(NOLOCK)?
February 15, 2018 at 1:31 pm
Phil Parkin - Saturday, February 10, 2018 11:03 AManujkumar.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.
February 27, 2018 at 8:53 am
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