So I was running this query and keep on the getting the above error of Msg 4145, Level 15, State 1, Procedure GetQN8, Line 1592 An expression of non-boolean type specified in a context where a condition is expected, near 'END'.. Can someone please help me out where am I going wrong?
This is the code -
USE [MailActivity]
GO
IF OBJECT_ID('GetQN8') IS NULL
EXEC('CREATE PROCEDURE GetQN8 AS SET NOCOUNT ON;')
GO
ALTER PROCEDURE GetQN8 (@CampaignName VARCHAR(200),@EngagedMonths INT, @Active VARCHAR(10), @NotInGroup INT, @CampaignNameForDupCheck VARCHAR(200))
AS
BEGIN
SET NOCOUNT ON
-- CREATE TEMP TABLES DROP TABLE #tempParticipantDataQN8
CREATE TABLE #tempParticipantDataQN8
(
ParticipantId INT PRIMARY KEY,
GroupId INT,
EmailAddress VARCHAR(200),
FirstName VARCHAR(100),
ActiveYN CHAR(1),
NewsLetterStatus CHAR(1),
ExpirationDate DATETIME
)
-- CREATE INDEX
CREATE NONCLUSTERED INDEX [IX_TempParticipantDataEmail] ON #tempParticipantDataQN8
(
[EmailAddress] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
-- DROP TABLE #tempParticipantAuditDataQN8
CREATE TABLE #tempParticipantAuditDataQN8
(
ParticipantId INT,
ColumnName VARCHAR(100),
AfterChangeValue VARCHAR(100),
LastChangedDate DATETIME,
LastChangedUser VARCHAR(100)
)
-- CREATE INDEX
CREATE NONCLUSTERED INDEX [IX_TempParticipantAuditDataColumnName] ON #tempParticipantAuditDataQN8
(
[ColumnName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
CREATE NONCLUSTERED INDEX [IX_TempParticipantAuditDataPartId] ON #tempParticipantAuditDataQN8
(
[ParticipantId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
INSERT INTO #tempParticipantDataQN8 (ParticipantId,EmailAddress,FirstName,ActiveYN,NewsLetterStatus,GroupId,ExpirationDate)
SELECT p.ParticipantId,
p.[Preferred Email],
p.[First Name],
p.ActiveYN,
p.NewsLetterStatus,
p.GroupId,
p.expirationdate
FROM ids_reporting.ids_reporting.dbo.Participant p WITH (NOLOCK)
JOIN ids_reporting.ids_reporting.dbo.[Group] gr WITH (NOLOCK)
ON p.groupId = gr.groupId
WHERE 1=1
AND ((p.[Preferred Email] NOT LIKE '%@none.com') AND (p.[Preferred Email] IS NOT NULL)) -- Has an email address
AND ISNULL(gr.parentGroupId,'') <> 1269
-- PULL AUDIT
INSERT INTO #tempParticipantAuditDataQN8 ( ParticipantId, ColumnName, AfterChangeValue, LastChangedDate, LastChangedUser)
SELECT a.ParticipantId, ColumnName, AfterChangeValue, LastChangedDate, LastChangedUser
FROM ids_reporting.ids_reporting.dbo.Participant_Audit a
JOIN #tempParticipantDataQN8 p
ON p.ParticipantId = a.ParticipantId
AND a.ColumnName = 'NewsLetterStatus'
AND a.AfterChangeValue = 'U'
--Query
SELECT DISTINCT(p.[EmailAddress]) AS [EmailAddress],
p.FirstName AS [FirstName],
p.GroupId AS [GroupId]
FROM [CampaignLists],#tempParticipantDataQN8 p
LEFT OUTER JOIN MailActivity.dbo.SupressionList08012016 sup
ON p.[EmailAddress] = sup.EmailAddress
JOIN MailActivity.dbo.MailActivities maEng WITH (NOLOCK)
ON (p.[EmailAddress] = maEng.EmailAddress
AND maEng.EventType = 'MessageGears.Model.Generated.OpenActivity'
AND maEng.EventTimestamp >= DATEADD(month, @EngagedMonths, GETDATE())
)
LEFT OUTER JOIN
(
SELECT DISTINCT(EmailAddress) AS [EmailAddress]
FROM MailActivity.dbo.MailActivities WITH (NOLOCK)
WHERE EventType = 'MessageGears.Model.Generated.BouncedActivity'
AND CategoryCode NOT IN ('10', '30')
GROUP BY EmailAddress
HAVING COUNT(*) >= 3
) AS SoftBounce
ON p.[EmailAddress] = SoftBounce.EmailAddress
LEFT OUTER JOIN MailActivity.dbo.MailActivities spam WITH (NOLOCK)
ON (p.[EmailAddress] = spam.EmailAddress
AND spam.EventType IN ('MessageGears.Model.Generated.UnsubActivity', 'MessageGears.Model.Generated.SpamComplaintActivity') )
WHERE 1=1
AND SoftBounce.EmailAddress IS NULL -- No soft bounces >= 3
AND spam.EmailAddress IS NULL -- No spam complaints
AND sup.EmailAddress IS NULL -- No suppression list
AND p.ActiveYN = @Active
AND [CampaignLists].CampaignName = @CampaignName
AND ((p.[EmailAddress] NOT LIKE '%@none.com') AND (p.[EmailAddress] IS NOT NULL)) -- Has an email address
AND p.GroupId NOT IN (@NotInGroup)
AND NOT EXISTS (SELECT 1 FROM MailActivity.dbo.MailActivities
WHERE EmailAddress = p.[EmailAddress]
AND RequestId IN
(SELECT DISTINCT(RequestId) FROM [CampaignLists]
WHERE CampaignName = @CampaignNameForDupCheck
)
)
END
Update: So I moved the [CampaignLists].CampaignName back to the WHERE clause as I need to grab the records that are in @CampaignName while also ignoring duplicate records in another campaigns/tables that is being checked at the very end by @CampaignNameForDupCheck. Executing the updated query gives me 0 records now.
ENDbe the culprit here? – Tim Biegeleisen