question

JpCoder avatar image
JpCoder asked

Writing a procedure problem

I am writing a procedure that creates a temp table, calculates the batch total by date, and then inserts total and date into another table and then drops the temp table. The query runs, creates temp table, but does not insert the data; I think the problem is with the date field but I’m not sure. I could use a fresh set of eyes…. Thanks in advance Jp ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ USE [LineProcess] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[MachineBatchTotals] AS BEGIN TRY SET NOCOUNT ON; create table Machine2TotalTemp (BatchTime datetime ,BatchLine02Total float); SELECT SUM(MTotal) AS Machine2Total FROM (SELECT Line02_JobID, MAX(Machine2_BatchCount) AS MTotal FROM MarkemLine02 WHERE (DateAndTime BETWEEN (SELECT CASE WHEN DateAndTime > '06:00:00' THEN (SELECT CONVERT(VARCHAR, getdate(), 110) + ' ' + '06:15:00') ELSE (SELECT CONVERT(VARCHAR, getdate() - 1, 110) + ' ' + '06:15:00') END AS result) AND GETDATE()) AND (Machine2_JobID <> '') GROUP BY Machine2_JobID) AS Machine2Total; merge into Machine2BatchTotal a using ( select * from Machine2TotalTemp) s on (a.DateAndTime = s.BatchTime) when matched then update set a.Machine2Total = s.BatchLine02Total when not matched then insert (DateAndTime,Machine2Total) values (s.BatchTime,s.BatchLine02Total); drop table Machine2TotalTemp; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage; END CATCH
selectinsertprocedure
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
JohnM avatar image
JohnM answered
In looking at the code, I don't see any INSERT into Machine2TotalTemp but you're using it in the MERGE statement. If it's empty, there won't be anything to merge with. Hope that helps!
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for the quick response… I can run the query down to GROUP BY Machine2_JobID) AS Machine2Total; in a separate query and it gives me the total, so Machine2Total shouldn’t be empty. I’m not sure where to add the insert into Machine2TotalTemp except after the calculation (after Group By) ─ if not in the merge, I was thinking that the date field was the problem and that maybe it is format problem in the merge… Any other suggestions? Thanks
0 Likes 0 ·
You should be able to do an "INSERT INTO Machine2TotalTemp (BatchTime, BatchLine02Total) SELECT [you'll need a date value], SUM(MTotal) AS Machine2Total..". This would put the records that you want to merge off of into the temporary table.
0 Likes 0 ·
John, Yes, I see it now ─ that works perfectly. Thanks for your insight
0 Likes 0 ·
Glad that helped to solve your problem! Make sure to mark it as an answer so that others will know your question was answered. Thanks!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.