October 16, 2017 at 10:11 am
hi,
Here is the delete syntax but it is not working even though all the conditions satisfy. What is the issue?
DELETE [MPNM]
FROM [NolMatch] AS [MPNM]
JOIN [Devices] AS [lastload]
ON [MPNM].[TableID] = [lastload].[TableID]
WHERE [lastload].[ODSLoadStatus] IS NULL
OR [ODSLoadStatus] = 'Excluded Model'
October 16, 2017 at 10:56 am
komal145 - Monday, October 16, 2017 10:11 AMhi,
Here is the delete syntax but it is not working even though all the conditions satisfy. What is the issue?DELETE [MPNM]
FROM [NolMatch] AS [MPNM]
JOIN [Devices] AS [lastload]
ON [MPNM].[TableID] = [lastload].[TableID]
WHERE [lastload].[ODSLoadStatus] IS NULL
OR [ODSLoadStatus] = 'Excluded Model'
What happens if you change it to a SELECT?
SELECT [MPNM].*
FROM [NolMatch] AS [MPNM]
JOIN [Devices] AS [lastload]
ON [MPNM].[TableID] = [lastload].[TableID]
WHERE [lastload].[ODSLoadStatus] IS NULL
OR [ODSLoadStatus] = 'Excluded Model'
October 16, 2017 at 11:04 am
komal145 - Monday, October 16, 2017 10:11 AMhi,
Here is the delete syntax but it is not working even though all the conditions satisfy. What is the issue?DELETE [MPNM]
FROM [NolMatch] AS [MPNM]
JOIN [Devices] AS [lastload]
ON [MPNM].[TableID] = [lastload].[TableID]
WHERE [lastload].[ODSLoadStatus] IS NULL
OR [ODSLoadStatus] = 'Excluded Model'
Have you tried this:
DELETE FROM [NolMatch]
FROM
[NolMatch] AS [MPNM]
JOIN [Devices] AS [lastload]
ON [MPNM].[TableID] = [lastload].[TableID]
WHERE
[lastload].[ODSLoadStatus] IS NULL
OR [ODSLoadStatus] = 'Excluded Model';
October 16, 2017 at 11:34 am
Luis Cazares - Monday, October 16, 2017 10:56 AMkomal145 - Monday, October 16, 2017 10:11 AMhi,
Here is the delete syntax but it is not working even though all the conditions satisfy. What is the issue?DELETE [MPNM]
FROM [NolMatch] AS [MPNM]
JOIN [Devices] AS [lastload]
ON [MPNM].[TableID] = [lastload].[TableID]
WHERE [lastload].[ODSLoadStatus] IS NULL
OR [ODSLoadStatus] = 'Excluded Model'What happens if you change it to a SELECT?
SELECT [MPNM].*
FROM [NolMatch] AS [MPNM]
JOIN [Devices] AS [lastload]
ON [MPNM].[TableID] = [lastload].[TableID]
WHERE [lastload].[ODSLoadStatus] IS NULL
OR [ODSLoadStatus] = 'Excluded Model'
Actually the code works indivdually ran...i mean just code part. But if i place the code in storedproc and in trnsaction , it doe snot work.
CREATE PROCEDURE [PrepareStageTables]
(
@TruncateStage bit
)
AS
IF ( @TruncateStage = 0)
BEGIN
RETURN 0;
END
ELSE
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
DELETE [MPNM]
FROM [NolMatch] AS [MPNM]
JOIN [Devices] AS [lastload]
ON [MPNM].[TableID] = [lastload].[TableID]
WHERE [lastload].[ODSLoadStatus] IS NULL
OR [ODSLoadStatus] = 'Excluded Model'
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
DECLARE @ErrorNumber INT = ERROR_NUMBER();
DECLARE @ErrorLine INT = ERROR_LINE();
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
DECLARE @ErrorState INT = ERROR_STATE();
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
END;
October 16, 2017 at 11:37 am
komal145 - Monday, October 16, 2017 11:34 AMLuis Cazares - Monday, October 16, 2017 10:56 AMkomal145 - Monday, October 16, 2017 10:11 AMhi,
Here is the delete syntax but it is not working even though all the conditions satisfy. What is the issue?DELETE [MPNM]
FROM [NolMatch] AS [MPNM]
JOIN [Devices] AS [lastload]
ON [MPNM].[TableID] = [lastload].[TableID]
WHERE [lastload].[ODSLoadStatus] IS NULL
OR [ODSLoadStatus] = 'Excluded Model'What happens if you change it to a SELECT?
SELECT [MPNM].*
FROM [NolMatch] AS [MPNM]
JOIN [Devices] AS [lastload]
ON [MPNM].[TableID] = [lastload].[TableID]
WHERE [lastload].[ODSLoadStatus] IS NULL
OR [ODSLoadStatus] = 'Excluded Model'Actually the code works indivdually ran...i mean just code part. But if i place the code in storedproc and in trnsaction , it doe snot work.
CREATE PROCEDURE [PrepareStageTables]
(
@TruncateStage bit
)
ASIF ( @TruncateStage = 0)
BEGIN
RETURN 0;
END
ELSEBEGIN
BEGIN TRY
BEGIN TRANSACTION;
DELETE [MPNM]
FROM [NolMatch] AS [MPNM]
JOIN [Devices] AS [lastload]
ON [MPNM].[TableID] = [lastload].[TableID]
WHERE [lastload].[ODSLoadStatus] IS NULL
OR [ODSLoadStatus] = 'Excluded Model'COMMIT TRANSACTION;
END TRYBEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;DECLARE @ErrorNumber INT = ERROR_NUMBER();
DECLARE @ErrorLine INT = ERROR_LINE();
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
DECLARE @ErrorState INT = ERROR_STATE();
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
END;
Okay, and what is the full and complete error message you are getting?
October 16, 2017 at 11:37 am
komal145 - Monday, October 16, 2017 11:34 AMLuis Cazares - Monday, October 16, 2017 10:56 AMkomal145 - Monday, October 16, 2017 10:11 AMhi,
Here is the delete syntax but it is not working even though all the conditions satisfy. What is the issue?DELETE [MPNM]
FROM [NolMatch] AS [MPNM]
JOIN [Devices] AS [lastload]
ON [MPNM].[TableID] = [lastload].[TableID]
WHERE [lastload].[ODSLoadStatus] IS NULL
OR [ODSLoadStatus] = 'Excluded Model'What happens if you change it to a SELECT?
SELECT [MPNM].*
FROM [NolMatch] AS [MPNM]
JOIN [Devices] AS [lastload]
ON [MPNM].[TableID] = [lastload].[TableID]
WHERE [lastload].[ODSLoadStatus] IS NULL
OR [ODSLoadStatus] = 'Excluded Model'Actually the code works indivdually ran...i mean just code part. But if i place the code in storedproc and in trnsaction , it doe snot work.
Code either works or not. It doesn't depends on where you put it or how you call it. You need to give a better description than "does not work".
Need an Answer? Actually, No ... You Need a Question
October 16, 2017 at 3:39 pm
Lynn Pettis - Monday, October 16, 2017 11:37 AMkomal145 - Monday, October 16, 2017 11:34 AMLuis Cazares - Monday, October 16, 2017 10:56 AMkomal145 - Monday, October 16, 2017 10:11 AMhi,
Here is the delete syntax but it is not working even though all the conditions satisfy. What is the issue?DELETE [MPNM]
FROM [NolMatch] AS [MPNM]
JOIN [Devices] AS [lastload]
ON [MPNM].[TableID] = [lastload].[TableID]
WHERE [lastload].[ODSLoadStatus] IS NULL
OR [ODSLoadStatus] = 'Excluded Model'What happens if you change it to a SELECT?
SELECT [MPNM].*
FROM [NolMatch] AS [MPNM]
JOIN [Devices] AS [lastload]
ON [MPNM].[TableID] = [lastload].[TableID]
WHERE [lastload].[ODSLoadStatus] IS NULL
OR [ODSLoadStatus] = 'Excluded Model'Actually the code works indivdually ran...i mean just code part. But if i place the code in storedproc and in trnsaction , it doe snot work.
CREATE PROCEDURE [PrepareStageTables]
(
@TruncateStage bit
)
ASIF ( @TruncateStage = 0)
BEGIN
RETURN 0;
END
ELSEBEGIN
BEGIN TRY
BEGIN TRANSACTION;
DELETE [MPNM]
FROM [NolMatch] AS [MPNM]
JOIN [Devices] AS [lastload]
ON [MPNM].[TableID] = [lastload].[TableID]
WHERE [lastload].[ODSLoadStatus] IS NULL
OR [ODSLoadStatus] = 'Excluded Model'COMMIT TRANSACTION;
END TRYBEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;DECLARE @ErrorNumber INT = ERROR_NUMBER();
DECLARE @ErrorLine INT = ERROR_LINE();
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
DECLARE @ErrorState INT = ERROR_STATE();
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
END;Okay, and what is the full and complete error message you are getting?
no error message but delete is not working 🙁
October 16, 2017 at 5:12 pm
Unless you can reproduce the issue in an empty sandbox data and provide us with DDL and sample data that recreates the problem, not sure what more we can do.
October 17, 2017 at 1:34 am
komal145 - Monday, October 16, 2017 11:34 AMActually the code works indivdually ran...i mean just code part. But if i place the code in storedproc and in trnsaction , it doe snot work.CREATE PROCEDURE [PrepareStageTables]
(
@TruncateStage bit
)
ASIF ( @TruncateStage = 0)
BEGIN
RETURN 0;
END
ELSEBEGIN
BEGIN TRY
BEGIN TRANSACTION;
DELETE [MPNM]
FROM [NolMatch] AS [MPNM]
JOIN [Devices] AS [lastload]
ON [MPNM].[TableID] = [lastload].[TableID]
WHERE [lastload].[ODSLoadStatus] IS NULL
OR [ODSLoadStatus] = 'Excluded Model'COMMIT TRANSACTION;
END TRYBEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;DECLARE @ErrorNumber INT = ERROR_NUMBER();
DECLARE @ErrorLine INT = ERROR_LINE();
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
DECLARE @ErrorState INT = ERROR_STATE();
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
END;
How exactly are you calling your 'PrepareStageTables' stored proc?
November 3, 2017 at 4:57 pm
komal145 - Monday, October 16, 2017 10:11 AMhi,
Here is the delete syntax but it is not working even though all the conditions satisfy. What is the issue?DELETE [MPNM]
FROM [NolMatch] AS [MPNM]
JOIN [Devices] AS [lastload]
ON [MPNM].[TableID] = [lastload].[TableID]
WHERE [lastload].[ODSLoadStatus] IS NULL
OR [ODSLoadStatus] = 'Excluded Model'
You're trying to use a syntax that is proprietary to Microsoft that makes absolutely no sense. This means it can pretty much behave any way it wants to. Let's take a look at your join; what does that mean in ANSI/ISO standard SQL? We do the join and we get a working table. This working table is supposed to only exist for the duration of the statement in which it appears. So you go ahead and take rows out of the on working table, then it ceases to exist in the ANSI ISO standard model of the language. This goes back to the original Sybase product and is totally screwed up mess.
DELETE FROM NolMatch
WHERE EXISTS
(SELECT *
FROM LastLoads AS L
WHERE (L.ods_load_status IS NULL
OR L.ods_load_status = 'Excluded Model')
AND NolMatch.table_id = L.table_id;
This is my guess for ANSI/ISO standard version of what you're trying to do. But since you failed to post any DDL or anything, it's just a guess.
Please post DDL and follow ANSI/ISO standards when asking for help.
November 5, 2017 at 7:25 pm
jcelko212 32090 - Friday, November 3, 2017 4:57 PMYou're trying to use a syntax that is proprietary to Microsoft that makes absolutely no sense. This means it can pretty much behave any way it wants to.
The proprietary version of code works just fine especially since it's being used in a Microsoft product. And, no... it doesn't mean that it can behave any way it wants to.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 5, 2017 at 7:29 pm
komal145 - Monday, October 16, 2017 3:39 PMLynn Pettis - Monday, October 16, 2017 11:37 AMkomal145 - Monday, October 16, 2017 11:34 AMLuis Cazares - Monday, October 16, 2017 10:56 AMkomal145 - Monday, October 16, 2017 10:11 AMhi,
Here is the delete syntax but it is not working even though all the conditions satisfy. What is the issue?DELETE [MPNM]
FROM [NolMatch] AS [MPNM]
JOIN [Devices] AS [lastload]
ON [MPNM].[TableID] = [lastload].[TableID]
WHERE [lastload].[ODSLoadStatus] IS NULL
OR [ODSLoadStatus] = 'Excluded Model'What happens if you change it to a SELECT?
SELECT [MPNM].*
FROM [NolMatch] AS [MPNM]
JOIN [Devices] AS [lastload]
ON [MPNM].[TableID] = [lastload].[TableID]
WHERE [lastload].[ODSLoadStatus] IS NULL
OR [ODSLoadStatus] = 'Excluded Model'Actually the code works indivdually ran...i mean just code part. But if i place the code in storedproc and in trnsaction , it doe snot work.
CREATE PROCEDURE [PrepareStageTables]
(
@TruncateStage bit
)
ASIF ( @TruncateStage = 0)
BEGIN
RETURN 0;
END
ELSEBEGIN
BEGIN TRY
BEGIN TRANSACTION;
DELETE [MPNM]
FROM [NolMatch] AS [MPNM]
JOIN [Devices] AS [lastload]
ON [MPNM].[TableID] = [lastload].[TableID]
WHERE [lastload].[ODSLoadStatus] IS NULL
OR [ODSLoadStatus] = 'Excluded Model'COMMIT TRANSACTION;
END TRYBEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;DECLARE @ErrorNumber INT = ERROR_NUMBER();
DECLARE @ErrorLine INT = ERROR_LINE();
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
DECLARE @ErrorState INT = ERROR_STATE();
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
END;Okay, and what is the full and complete error message you are getting?
no error message but delete is not working 🙁
Are you sure that it's actually getting to the delete? Add a couple of strategically located PRINT statements and find out what the execution path is. OR, take a gander at the actual execution plan. I think it because of the trick that you're trying to do with the IF ELSE and it's not actually ever executing the DELETE. Either that or, as Nigel implies in his post, when you call this proc, are you also passing a "1" to the parameter that the proc has and that the IF relies on to get to the DELETE?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 11, 2017 at 1:29 pm
Really, you only program in Microsoft products today? No major corporation uses Oracle, DB2, Postgres or any other form of SQL? At an early point in my career. I decided I'd be the guy that taught very generic, very portable SQL and could get you past any of the FIPS requirements, if you were a federal contractor; I grew up during the Cold War. It stood me pretty well for about four decades now.
Actually, proprietary code can pretty much behave anyway which it wants to. I had an article on the outer join syntax various companies had before the Standards. The extended equality had an Oracle version, and Microsoft version, and then there was the Informix version which went into the from clause rather than the where clause, they all behave slightly differently. Then of course within Microsoft. We had the definition of the BIT data type. Originally, it was what a computer science major with think of is a bit, namely having only the values {0, 1} as bit flags. Then suddenly it switched over to a numeric data type. But all numeric datatypes in SQL have to be nullable so now those columns became{0,1,NULL} I made a bundle on this one!
Please post DDL and follow ANSI/ISO standards when asking for help.
November 11, 2017 at 4:09 pm
jcelko212 32090 - Friday, November 3, 2017 4:57 PM>> The proprietary version of code works just fine especially since it's being used in a Microsoft product. And, no... it doesn't mean that it can behave any way it wants to. <<
Really, you only program in Microsoft products today? No major corporation uses Oracle, DB2, Postgres or any other form of SQL? At an early point in my career. I decided I'd be the guy that taught very generic, very portable SQL and could get you past any of the FIPS requirements, if you were a federal contractor; I grew up during the Cold War. It stood me pretty well for about four decades now.
Actually, proprietary code can pretty much behave anyway which it wants to. I had an article on the outer join syntax various companies had before the Standards. The extended equality had an Oracle version, and Microsoft version, and then there was the Informix version which went into the from clause rather than the where clause, they all behave slightly differently. Then of course within Microsoft. We had the definition of the BIT data type. Originally, it was what a computer science major with think of is a bit, namely having only the values {0, 1} as bit flags. Then suddenly it switched over to a numeric data type. But all numeric datatypes in SQL have to be nullable so now those columns became{0,1,NULL} I made a bundle on this one!
If you read what Jeff actually wrote, you can see the quote "it's being used in a Microsoft product." He said nothing about anyone else writing code in any other environment for any other product at any other company, but referred to the code the OP was asking about. It looks like you completely missed the point or are just trying to troll people.
And no, a bit column does not have to be nullable. Does it support nulls - yes. I don't have any idea who told you that numerics have to be nullable, but try the following simple test and observe the results.
CREATE TABLE dbo.joe (
ID Integer,
BitColumnA bit not null);
INSERT INTO dbo.Joe(ID, BitColumnA) VALUES(1, 1);
INSERT INTO dbo.Joe(ID, BitColumnA) VALUES(1, NULL);
This isn't about ISO or anything else other than pure, simple observation.
Now, go ahead and tell me how wrong I am because some ISO standard written in 1971 defines something a particular way and I have misinterpreted everything in my entire life up to this point.
November 11, 2017 at 7:59 pm
jcelko212 32090 - Friday, November 3, 2017 4:57 PM>> The proprietary version of code works just fine especially since it's being used in a Microsoft product. And, no... it doesn't mean that it can behave any way it wants to. <<
Really, you only program in Microsoft products today? No major corporation uses Oracle, DB2, Postgres or any other form of SQL? At an early point in my career. I decided I'd be the guy that taught very generic, very portable SQL and could get you past any of the FIPS requirements, if you were a federal contractor; I grew up during the Cold War. It stood me pretty well for about four decades now.
Actually, proprietary code can pretty much behave anyway which it wants to. I had an article on the outer join syntax various companies had before the Standards. The extended equality had an Oracle version, and Microsoft version, and then there was the Informix version which went into the from clause rather than the where clause, they all behave slightly differently. Then of course within Microsoft. We had the definition of the BIT data type. Originally, it was what a computer science major with think of is a bit, namely having only the values {0, 1} as bit flags. Then suddenly it switched over to a numeric data type. But all numeric datatypes in SQL have to be nullable so now those columns became{0,1,NULL} I made a bundle on this one!
Heh... Lots of major corporations use Oracle, DB2, Postgress, etc... I've worked for a couple of them, didn't like it (especially hated all 3 years working with Oracle even though I was good at it) and, unlike you, decided to get good at something instead of going generic.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply