February 14, 2018 at 11:28 am
Hello,
I have a tracking table (Bankruptcy_Tracking) that tracks when steps are completed in a process. The completion of one step will then trigger the creation of a follow up date for the next step(s). The template used for setting the follow up dates in a another table (Bankruptcy_Tracking_Config). My code which is normally in a stored procedure works great when there is only one follow up date to set. However, when there are multiple I receive the follow error:
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
I know the problem is when I am setting the value of @Step_ID, and subsequently @Days. I'm looking for the best way to set this up with a loop. My knowledge and experience with loops is rather lacking.
** I set the @var variables as an easy way to convert the INT parameters to varchar. I like to only accept INT parameters as a way to limit exposure to SQL injections.
Thank you!!
David92595
CREATE TABLE Bankruptcy_Tracking_Config
([Step_ID] int, [Step] varchar(26), [Process_ID] int, [Process] varchar(11), [Process_Order] int, [Trigger_ID] int, [Days] int)
;
INSERT INTO Bankruptcy_Tracking_Config
([Step_ID], [Step], [Process_ID], [Process], [Process_Order], [Trigger_ID], [Days])
VALUES
(134, 'Objection Deadline Date', 16, 'BK Advesary', 1, 0, 0),
(135, 'Obtain BK Docs', 16, 'BK Advesary', 2, 0, 0),
(136, 'Obtain Loan Docs', 16, 'BK Advesary', 3, 0, 0),
(137, 'Response Drafted', 16, 'BK Advesary', 4, 2, 0),
(138, 'Response Approved by Attorney', 16, 'BK Advesary', 5, 4, 0),
(139, 'Response Filed', 16, 'BK Advesary', 6, 5, 0),
(140, 'Response Served', 16, 'BK Advesary', 7, 6, 0),
(141, 'Hearing Prep', 16, 'BK Advesary', 8, 9, -7),
(142, 'Hearing Date', 16, 'BK Advesary', 9, 7, 5),
(143, 'Hearing Results to Client', 16, 'BK Advesary', 10, 9, 1),
(144, 'Prepare and File Order', 16, 'BK Advesary', 11, 10, 1),
(145, 'Order Entered', 16, 'BK Advesary', 12, 11, 1),
(146, 'Order Provided to Client', 16, 'BK Advesary', 13, 12, 0)
;
CREATE TABLE Bankruptcy_Tracking_Config
([ID] int, [TS] int, [Step] varchar(26), [Completed] datetime, [Follow_Up] datetime, [Process] varchar(11), [Process_ID] int, [Process_Order] int, [Instance] int)
;
INSERT INTO Bankruptcy_Tracking_Config
([ID], [TS], [Step], [Completed], [Follow_Up], [Process], [Process_ID], [Process_Order], [Instance], [Timestamp])
VALUES
(28, 111111, 'Objection Deadline Date', NULL, NULL, 'BK Advesary', 16, 1, 1),
(29, 111111, 'Obtain BK Docs', NULL, NULL, 'BK Advesary', 16, 2, 1),
(30, 111111, 'Obtain Loan Docs', NULL, NULL, 'BK Advesary', 16, 3, 1),
(31, 111111, 'Response Drafted', NULL, NULL, 'BK Advesary', 16, 4, 1),
(32, 111111, 'Response Approved Attorney', NULL, NULL, 'BK Advesary', 16, 5, 1),
(33, 111111, 'Response Filed', NULL, NULL, 'BK Advesary', 16, 6, 1),
(34, 111111, 'Response Served', NULL, NULL, 'BK Advesary', 16, 7, 1),
(35, 111111, 'Hearing Prep', NULL, '2018-02-15 00:00:00.000', 'BK Advesary', 16, 8, 1),
(36, 111111, 'Hearing Date', '2018-02-14 00:00:00.000', NULL, 'BK Advesary', 16, 9, 1),
(37, 111111, 'Hearing Results to Client', NULL, '2018-02-15 00:00:00.000', 'BK Advesary', 16, 10, 1),
(38, 111111, 'Prepare and File Order', NULL, NULL, 'BK Advesary', 16, 11, 1),
(39, 111111, 'Order Entered', NULL, NULL, 'BK Advesary', 16, 12, 1),
(40, 111111, 'Order Provided to Client', NULL, NULL, 'BK Advesary', 16, 13, 1)
;
Declare @TS NUMERIC = 111111
,@Process_ID INT = 16
,@Process_Order INT = 9
,@Completed DATE = Convert(Date,Getdate())
Declare @Step_ID INT = (Select Step_ID FROM Bankruptcy_Tracking_Config WHERE Trigger_ID = @Process_Order AND Process_ID = @Process_ID)
Declare @Days as varchar(3) = (Select [Days] from Bankruptcy_Tracking_Config where Step_ID = @Step_ID),
@varProcess_Order as varchar(2) = CONVERT(varchar(2),@Process_Order),
@varProcess_ID as varchar(2) = CONVERT(varchar(2),@Process_ID),
@varTS as varchar(10) = Convert(varchar(10), @TS)
Declare @Function as varchar(30) = Case WHEN @Days < 0 THEN 'dbo.fn_SubtractBusinessDays' Else 'dbo.fn_AddBusinessDays' END
DECLARE @Run nvarchar(1000),
@ParamDefCompleted nvarchar(25)
Set @Run = 'update Bankruptcy_Tracking
SET Follow_Up = CONVERT(DATE,' + @Function + '(CONVERT(nvarchar(10),@Completed),' + @Days + ')) FROM Bankruptcy_Tracking as BT LEFT OUTER JOIN Bankruptcy_Tracking_Config as BTC on BT.Process_ID = BTC.Process_ID and BTC.Process_Order = BT.Process_Order
WHERE BTC.Process_ID = ' + @varProcess_ID + ' AND BTC.Trigger_ID = ' + @varProcess_Order + ' AND TS = ' + @varTS
--For Testing Purposes. Print the change before you make it.
--Print @Run
SET @ParamDefCompleted = N'@Completed nvarchar(10)'
Execute sp_executesql @Run, @ParamDefCompleted, @Completed
February 14, 2018 at 12:04 pm
You say you want to create records, but your code is updating records.
Also, I see absolutely no reason to use dynamic SQL here. Because you are using dynamic SQL, you are forcing an iterative approach, which causes problems. If you just start out with standard SQL, you can use a set-based approach and won't have an issue when there are multiple records to update.
Finally, why do you have two separate functions that presumably do the same thing? Subtracting a number is exactly the same as adding the negative of that same number.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 14, 2018 at 12:24 pm
Apologies, the template is created via another action. I need to update the data.
The two separate function are used in conjunction with our holidays table. They are also used in determining the roll of the date set when a follow up date lands on a weekend or holiday. should the date be set for the Friday before the weekend or the Monday after...
I've taken your advise and removed the Dynamic SQL, however, I'm still having a problem using the AddBusinessDays and SubtractBusinessDays functions. Any idea's?
--@Function wont run as @days is not longer declared
Declare @Function as varchar(30) = Case WHEN @Days < 0 THEN 'dbo.fn_SubtractBusinessDays' Else 'dbo.fn_AddBusinessDays' END
update Bankruptcy_Tracking
SET Follow_Up = CONVERT(DATE,@Function(@Completed,BTC.[Days])) FROM Bankruptcy_Tracking as BT LEFT OUTER JOIN Bankruptcy_Tracking_Config as BTC on BT.Process_ID = BTC.Process_ID and BTC.Process_Order = BT.Process_Order
WHERE BTC.Process_ID = @Process_ID AND BTC.Trigger_ID = @Process_Order AND TS = @TS
February 14, 2018 at 12:59 pm
David92595 - Wednesday, February 14, 2018 12:24 PMApologies, the template is created via another action. I need to update the data.The two separate function are used in conjunction with our holidays table. They are also used in determining the roll of the date set when a follow up date lands on a weekend or holiday. should the date be set for the Friday before the weekend or the Monday after...
I didn't ask what they did, I asked why you had two that presumably did exactly the same thing.
You also didn't mention anything about the dynamic SQL. I've rewritten your query using standard SQL.
UPDATE BT
SET Follow_Up = CASE WHEN [Days] < 0 THEN dbo.fn_SubtractBusinessDays(@Completed, [Days]) ELSE dbo.fn_AddBusinessDays(@Completed, [Days]) END
FROM #Bankruptcy_Tracking BT
LEFT OUTER JOIN #Bankruptcy_Tracking_Config AS BTC
ON BT.Process_ID = BTC.Process_ID
AND bt.Process_Order = BTC.Process_Order
WHERE BTC.Process_ID = @Process_ID
AND BTC.Trigger_ID = @Process_Order
AND BT.TS = @TS
Drew
PS: There were several issues with your sample data.
1) You had two different tables with exactly the same name.
2) Your insert clause named a column that wasn't in your table.
3) The number of columns in your insert clause didn't match the number of columns in your values clause
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 14, 2018 at 1:14 pm
Thank you for your assistance.
Sorry about the bad data. I took out the timestamp thinking it would simplify my example, obviously forgot to take it out of the table.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply