asked on row that should be excluded still shows up
disclaimer: I am a sql beginner working on concepts a bit beyond my comfort level. Stored Proc was not written by me.
I do not see why a row is showing up that should be hidden (based upon my where clause conditions)
Here is the Stored Proc:
(note: final result is a select on a temporary table, if that matters)
(note 2: The "ShowForm" column and the table AgentFormOverride were recently created by me and this is my attempt at integration into the existing Stored Proc below)
Here is the where clause (at the bottom of the stored proc) that (in my mind) should be excluding the last row:
WHERE
t.ShowForm = 1 and
t.DuplicatesAllowed = 1 AND NOT EXISTS
(SELECT * FROM @tblForms WHERE Sequence = t.Sequence AND DocumentID IS NULL)
The reason is that ShowForm is set to False (set to "0"). ShowForm type is bit.
Essentially, the last row in the result set should toggle (show or be hidden) as ShowForm is set to "show" or "not show".
Here is the result set:
![toggle show hide]()
I do not see why a row is showing up that should be hidden (based upon my where clause conditions)
Here is the Stored Proc:
(note: final result is a select on a temporary table, if that matters)
(note 2: The "ShowForm" column and the table AgentFormOverride were recently created by me and this is my attempt at integration into the existing Stored Proc below)
USE [PP]
GO
/****** Object: StoredProcedure [dbo].[AgentFormList_Get] Script Date: 12/31/2012 09:32:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[AgentFormList_Get]
@AssetID int,
@AssetSID int
AS
/*
////////////////////////////////////////////////////////////////////////////////////////////////////////////
Name: AgentFormList_Get
Date:
Purpose: Returns list of active forms for specified asset
Requested by: Madhav
Author: Paul Peterson
History
Date Modified by Requested by Modification/Description
--------------------------------------------------------------------------------------------------------------
11-30-2010 Paul Kade Don't return Reimbursement Policy for Saxon & Litton
3/3/2011 Bethb Madhav A part of the Document and Image Archive Process, changed proc to look at vDocument
////////////////////////////////////////////////////////////////////////////////////////////////////////////
*/
DECLARE @Colist bit,
@ExcludeValue int,
@DocExpReim int,
@Abbr varchar(10)
SELECT @Colist = dbo.ColistRequired(@AssetID, @AssetSID)
SELECT @ExcludeValue = CASE WHEN @Colist = 1 THEN 2 ELSE 1 END
SELECT @DocExpReim = dbo.GetLookupID('DocExpReim')
SELECT @Abbr = RTRIM(Merchant.Abbr)
FROM Asset
INNER JOIN Pool ON Asset.PoolID = Pool.PoolID AND Asset.PoolSID = Pool.PoolSID
INNER JOIN Merchant on Pool.MerchantID = Merchant.MerchantID AND Pool.MerchantSID = Merchant.MerchantSID
WHERE Asset.AssetID = @AssetID AND Asset.AssetSID = @AssetSID
DECLARE @tblForms table (
AgentFormListID int,
AgentFormListSID int,
FormTypeID int,
Sequence int,
FormName varchar(50),
AcceptanceReqd bit,
DuplicatesAllowed bit,
ExternalLink varchar(50),
CreateDt datetime,
ActionDate datetime,
AcceptDecline int,
AcceptedBy varchar(50),
DocumentID int,
DocumentSID int,
AssetTaskID int,
AssetTaskSID int,
AgentFormID int,
AgentFormSID int,
TaskCode varchar(50),
Active bit,
ShowForm bit
)
INSERT INTO @tblForms
SELECT AgentFormList.AgentFormListID, AgentFormList.AgentFormListSID,
FormTypeID, Sequence, FormName, AcceptanceReqd, DuplicatesAllowed, ExternalLink,
CreateDt, ActionDate, AcceptDecline, dbo.getUserName(AgentForm.AcceptUserID,AgentForm.AcceptUserSID) AS AcceptedBy, Document.DocumentID, Document.DocumentSID, AssetTaskID, AssetTaskSID,
AgentForm.AgentFormID, AgentForm.AgentFormSID, TaskCode, AgentForm.Active, afo.ShowForm
FROM AgentFormList
LEFT JOIN AgentForm on
AgentFormList.AgentFormListID = AgentForm.AgentFormListID
AND AgentFormList.AgentFormListSID = AgentForm.AgentFormListSID
AND AgentForm.AssetID = @AssetID AND AgentForm.AssetSID = @AssetSID
LEFT JOIN AgentFormOverride afo on
AgentFormList.AgentFormListID = afo.AgentFormListID
LEFT JOIN vDocument Document ON
AgentForm.DocumentID = Document.DocumentID
AND AgentForm.DocumentSID = Document.DocumentSID
WHERE ColistFlag <> @ExcludeValue
AND (FormTypeID <> @DocExpReim OR (FormTypeID = @DocExpReim AND @Abbr NOT IN ('LIT','SXN')))
INSERT INTO @tblForms
SELECT
AgentFormListID,
AgentFormListSID,
FormTypeID,
Sequence,
FormName,
AcceptanceReqd,
DuplicatesAllowed,
ExternalLink,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
TaskCode,
1,
ShowForm
FROM @tblForms t
WHERE
t.ShowForm = 1 and
t.DuplicatesAllowed = 1 AND NOT EXISTS
(SELECT * FROM @tblForms WHERE Sequence = t.Sequence AND DocumentID IS NULL)
SELECT * FROM @tblForms
order by sequence
Here is the where clause (at the bottom of the stored proc) that (in my mind) should be excluding the last row:
WHERE
t.ShowForm = 1 and
t.DuplicatesAllowed = 1 AND NOT EXISTS
(SELECT * FROM @tblForms WHERE Sequence = t.Sequence AND DocumentID IS NULL)
The reason is that ShowForm is set to False (set to "0"). ShowForm type is bit.
Essentially, the last row in the result set should toggle (show or be hidden) as ShowForm is set to "show" or "not show".
Here is the result set:
Microsoft DevelopmentMicrosoft SQL Server
Last Comment
Tom Knowlton
There are at least two INSERT statements in your stored procedure. Is it possible the record is being added by the other?
ASKER
I see what you mean.
Yes, I believe it is possible.
Assuming the record is being added by the FIRST insert, how do I prevent that?
Yes, I believe it is possible.
Assuming the record is being added by the FIRST insert, how do I prevent that?
The obvious answer is to duplicate your criteria:
...
WHERE ColistFlag <> @ExcludeValue
AND (FormTypeID <> @DocExpReim OR (FormTypeID = @DocExpReim AND @Abbr NOT IN ('LIT','SXN'))) AND t.ShowForm = 1
...
It's not possible for me to say whether or not that would have other repercussions, but it's easy enough to try and undo if it doesn't work the way you want.
...
WHERE ColistFlag <> @ExcludeValue
AND (FormTypeID <> @DocExpReim OR (FormTypeID = @DocExpReim AND @Abbr NOT IN ('LIT','SXN'))) AND t.ShowForm = 1
...
It's not possible for me to say whether or not that would have other repercussions, but it's easy enough to try and undo if it doesn't work the way you want.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
I did try that.
But since none of the other rows returned will have a value of 0 for "ShowForm" (since they don't exist in AgentFormOverride) the only row that shows up is the one in AgentFormOverride.
Does that make sense?
But since none of the other rows returned will have a value of 0 for "ShowForm" (since they don't exist in AgentFormOverride) the only row that shows up is the one in AgentFormOverride.
Does that make sense?
How about:
...
WHERE ColistFlag <> @ExcludeValue
AND (FormTypeID <> @DocExpReim OR (FormTypeID = @DocExpReim AND @Abbr NOT IN ('LIT','SXN'))) AND t.ShowForm <> 0
...
...
WHERE ColistFlag <> @ExcludeValue
AND (FormTypeID <> @DocExpReim OR (FormTypeID = @DocExpReim AND @Abbr NOT IN ('LIT','SXN'))) AND t.ShowForm <> 0
...
ASKER
It still shows up in the results with this change...but there is only ONE row returned instead of 21 rows.
When I make this change is it ONLY in the top WHERE clause and NOT in the bottom WHERE clause?
When I make this change is it ONLY in the top WHERE clause and NOT in the bottom WHERE clause?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Maybe this can help:
This version actually returns 0 rows:
Whereas. . .
This version returns 21 rows, and the last row still shows up:
This version actually returns 0 rows:
USE [PP]
GO
/****** Object: StoredProcedure [dbo].[AgentFormList_Get] Script Date: 12/31/2012 12:54:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[AgentFormList_Get]
@AssetID int,
@AssetSID int
AS
/*
////////////////////////////////////////////////////////////////////////////////////////////////////////////
Name: AgentFormList_Get
Date:
Purpose: Returns list of active forms for specified asset
Requested by: Madhav
Author: Paul Peterson
History
Date Modified by Requested by Modification/Description
--------------------------------------------------------------------------------------------------------------
11-30-2010 Paul Kade Don't return Reimbursement Policy for Saxon & Litton
3/3/2011 Bethb Madhav A part of the Document and Image Archive Process, changed proc to look at vDocument
////////////////////////////////////////////////////////////////////////////////////////////////////////////
*/
DECLARE @Colist bit,
@ExcludeValue int,
@DocExpReim int,
@Abbr varchar(10)
SELECT @Colist = dbo.ColistRequired(@AssetID, @AssetSID)
SELECT @ExcludeValue = CASE WHEN @Colist = 1 THEN 2 ELSE 1 END
SELECT @DocExpReim = dbo.GetLookupID('DocExpReim')
SELECT @Abbr = RTRIM(Merchant.Abbr)
FROM Asset
INNER JOIN Pool ON Asset.PoolID = Pool.PoolID AND Asset.PoolSID = Pool.PoolSID
INNER JOIN Merchant on Pool.MerchantID = Merchant.MerchantID AND Pool.MerchantSID = Merchant.MerchantSID
WHERE Asset.AssetID = @AssetID AND Asset.AssetSID = @AssetSID
DECLARE @tblForms table (
AgentFormListID int,
AgentFormListSID int,
FormTypeID int,
Sequence int,
FormName varchar(50),
AcceptanceReqd bit,
DuplicatesAllowed bit,
ExternalLink varchar(50),
CreateDt datetime,
ActionDate datetime,
AcceptDecline int,
AcceptedBy varchar(50),
DocumentID int,
DocumentSID int,
AssetTaskID int,
AssetTaskSID int,
AgentFormID int,
AgentFormSID int,
TaskCode varchar(50),
Active bit,
ShowForm bit
)
INSERT INTO @tblForms y
SELECT AgentFormList.AgentFormListID, AgentFormList.AgentFormListSID,
FormTypeID, Sequence, FormName, AcceptanceReqd, DuplicatesAllowed, ExternalLink,
CreateDt, ActionDate, AcceptDecline, dbo.getUserName(AgentForm.AcceptUserID,AgentForm.AcceptUserSID) AS AcceptedBy, Document.DocumentID, Document.DocumentSID, AssetTaskID, AssetTaskSID,
AgentForm.AgentFormID, AgentForm.AgentFormSID, TaskCode, AgentForm.Active, afo.ShowForm
FROM AgentFormList
LEFT JOIN AgentForm on
AgentFormList.AgentFormListID = AgentForm.AgentFormListID
AND AgentFormList.AgentFormListSID = AgentForm.AgentFormListSID
AND AgentForm.AssetID = @AssetID AND AgentForm.AssetSID = @AssetSID
LEFT JOIN AgentFormOverride afo on
AgentFormList.AgentFormListID = afo.AgentFormListID
LEFT JOIN vDocument Document ON
AgentForm.DocumentID = Document.DocumentID
AND AgentForm.DocumentSID = Document.DocumentSID
WHERE ColistFlag <> @ExcludeValue
AND (FormTypeID <> @DocExpReim OR (FormTypeID = @DocExpReim AND @Abbr NOT IN ('LIT','SXN')))
AND y.ShowForm <> 0
INSERT INTO @tblForms
SELECT
AgentFormListID,
AgentFormListSID,
FormTypeID,
Sequence,
FormName,
AcceptanceReqd,
DuplicatesAllowed,
ExternalLink,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
TaskCode,
1,
ShowForm
FROM @tblForms t
WHERE
t.DuplicatesAllowed = 1 AND NOT EXISTS
(SELECT * FROM @tblForms WHERE Sequence = t.Sequence AND DocumentID IS NULL)
SELECT * FROM @tblForms
order by sequence
Whereas. . .
This version returns 21 rows, and the last row still shows up:
USE [PP]
GO
/****** Object: StoredProcedure [dbo].[AgentFormList_Get] Script Date: 12/31/2012 12:54:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[AgentFormList_Get]
@AssetID int,
@AssetSID int
AS
/*
////////////////////////////////////////////////////////////////////////////////////////////////////////////
Name: AgentFormList_Get
Date:
Purpose: Returns list of active forms for specified asset
Requested by: Madhav
Author: Paul Peterson
History
Date Modified by Requested by Modification/Description
--------------------------------------------------------------------------------------------------------------
11-30-2010 Paul Kade Don't return Reimbursement Policy for Saxon & Litton
3/3/2011 Bethb Madhav A part of the Document and Image Archive Process, changed proc to look at vDocument
////////////////////////////////////////////////////////////////////////////////////////////////////////////
*/
DECLARE @Colist bit,
@ExcludeValue int,
@DocExpReim int,
@Abbr varchar(10)
SELECT @Colist = dbo.ColistRequired(@AssetID, @AssetSID)
SELECT @ExcludeValue = CASE WHEN @Colist = 1 THEN 2 ELSE 1 END
SELECT @DocExpReim = dbo.GetLookupID('DocExpReim')
SELECT @Abbr = RTRIM(Merchant.Abbr)
FROM Asset
INNER JOIN Pool ON Asset.PoolID = Pool.PoolID AND Asset.PoolSID = Pool.PoolSID
INNER JOIN Merchant on Pool.MerchantID = Merchant.MerchantID AND Pool.MerchantSID = Merchant.MerchantSID
WHERE Asset.AssetID = @AssetID AND Asset.AssetSID = @AssetSID
DECLARE @tblForms table (
AgentFormListID int,
AgentFormListSID int,
FormTypeID int,
Sequence int,
FormName varchar(50),
AcceptanceReqd bit,
DuplicatesAllowed bit,
ExternalLink varchar(50),
CreateDt datetime,
ActionDate datetime,
AcceptDecline int,
AcceptedBy varchar(50),
DocumentID int,
DocumentSID int,
AssetTaskID int,
AssetTaskSID int,
AgentFormID int,
AgentFormSID int,
TaskCode varchar(50),
Active bit,
ShowForm bit
)
INSERT INTO @tblForms y
SELECT AgentFormList.AgentFormListID, AgentFormList.AgentFormListSID,
FormTypeID, Sequence, FormName, AcceptanceReqd, DuplicatesAllowed, ExternalLink,
CreateDt, ActionDate, AcceptDecline, dbo.getUserName(AgentForm.AcceptUserID,AgentForm.AcceptUserSID) AS AcceptedBy, Document.DocumentID, Document.DocumentSID, AssetTaskID, AssetTaskSID,
AgentForm.AgentFormID, AgentForm.AgentFormSID, TaskCode, AgentForm.Active, afo.ShowForm
FROM AgentFormList
LEFT JOIN AgentForm on
AgentFormList.AgentFormListID = AgentForm.AgentFormListID
AND AgentFormList.AgentFormListSID = AgentForm.AgentFormListSID
AND AgentForm.AssetID = @AssetID AND AgentForm.AssetSID = @AssetSID
LEFT JOIN AgentFormOverride afo on
AgentFormList.AgentFormListID = afo.AgentFormListID
LEFT JOIN vDocument Document ON
AgentForm.DocumentID = Document.DocumentID
AND AgentForm.DocumentSID = Document.DocumentSID
WHERE ColistFlag <> @ExcludeValue
AND (FormTypeID <> @DocExpReim OR (FormTypeID = @DocExpReim AND @Abbr NOT IN ('LIT','SXN')))
INSERT INTO @tblForms
SELECT
AgentFormListID,
AgentFormListSID,
FormTypeID,
Sequence,
FormName,
AcceptanceReqd,
DuplicatesAllowed,
ExternalLink,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
TaskCode,
1,
ShowForm
FROM @tblForms t
WHERE
t.DuplicatesAllowed = 1 AND NOT EXISTS
(SELECT * FROM @tblForms WHERE Sequence = t.Sequence AND DocumentID IS NULL)
AND t.ShowForm <> 0
SELECT * FROM @tblForms
order by sequence
How about this change to the first query. Note you're checking the ShowForm field of AgentFormOverride this time. Whether you should be checking for "<> 0" or " =1" I can't say, but this is probably the better syntax. Leave the second query as it was originally.
...
WHERE ColistFlag <> @ExcludeValue
AND (FormTypeID <> @DocExpReim OR (FormTypeID = @DocExpReim AND @Abbr NOT IN ('LIT','SXN')))
AND afo.ShowForm <> 0
...
...
WHERE ColistFlag <> @ExcludeValue
AND (FormTypeID <> @DocExpReim OR (FormTypeID = @DocExpReim AND @Abbr NOT IN ('LIT','SXN')))
AND afo.ShowForm <> 0
...
ASKER
Now no rows return. Did I do it correctly?
Screenshot of what was returned in Management Studio query window:
![no rows return]()
USE [PP]
GO
/****** Object: StoredProcedure [dbo].[AgentFormList_Get] Script Date: 12/31/2012 12:54:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[AgentFormList_Get]
@AssetID int,
@AssetSID int
AS
/*
////////////////////////////////////////////////////////////////////////////////////////////////////////////
Name: AgentFormList_Get
Date:
Purpose: Returns list of active forms for specified asset
Requested by: Madhav
Author: Paul Peterson
History
Date Modified by Requested by Modification/Description
--------------------------------------------------------------------------------------------------------------
11-30-2010 Paul Kade Don't return Reimbursement Policy for Saxon & Litton
3/3/2011 Bethb Madhav A part of the Document and Image Archive Process, changed proc to look at vDocument
////////////////////////////////////////////////////////////////////////////////////////////////////////////
*/
DECLARE @Colist bit,
@ExcludeValue int,
@DocExpReim int,
@Abbr varchar(10)
SELECT @Colist = dbo.ColistRequired(@AssetID, @AssetSID)
SELECT @ExcludeValue = CASE WHEN @Colist = 1 THEN 2 ELSE 1 END
SELECT @DocExpReim = dbo.GetLookupID('DocExpReim')
SELECT @Abbr = RTRIM(Merchant.Abbr)
FROM Asset
INNER JOIN Pool ON Asset.PoolID = Pool.PoolID AND Asset.PoolSID = Pool.PoolSID
INNER JOIN Merchant on Pool.MerchantID = Merchant.MerchantID AND Pool.MerchantSID = Merchant.MerchantSID
WHERE Asset.AssetID = @AssetID AND Asset.AssetSID = @AssetSID
DECLARE @tblForms table (
AgentFormListID int,
AgentFormListSID int,
FormTypeID int,
Sequence int,
FormName varchar(50),
AcceptanceReqd bit,
DuplicatesAllowed bit,
ExternalLink varchar(50),
CreateDt datetime,
ActionDate datetime,
AcceptDecline int,
AcceptedBy varchar(50),
DocumentID int,
DocumentSID int,
AssetTaskID int,
AssetTaskSID int,
AgentFormID int,
AgentFormSID int,
TaskCode varchar(50),
Active bit,
ShowForm bit
)
INSERT INTO @tblForms
SELECT AgentFormList.AgentFormListID, AgentFormList.AgentFormListSID,
FormTypeID, Sequence, FormName, AcceptanceReqd, DuplicatesAllowed, ExternalLink,
CreateDt, ActionDate, AcceptDecline, dbo.getUserName(AgentForm.AcceptUserID,AgentForm.AcceptUserSID) AS AcceptedBy, Document.DocumentID, Document.DocumentSID, AssetTaskID, AssetTaskSID,
AgentForm.AgentFormID, AgentForm.AgentFormSID, TaskCode, AgentForm.Active, afo.ShowForm
FROM AgentFormList
LEFT JOIN AgentForm on
AgentFormList.AgentFormListID = AgentForm.AgentFormListID
AND AgentFormList.AgentFormListSID = AgentForm.AgentFormListSID
AND AgentForm.AssetID = @AssetID AND AgentForm.AssetSID = @AssetSID
LEFT JOIN AgentFormOverride afo on
AgentFormList.AgentFormListID = afo.AgentFormListID
LEFT JOIN vDocument Document ON
AgentForm.DocumentID = Document.DocumentID
AND AgentForm.DocumentSID = Document.DocumentSID
WHERE ColistFlag <> @ExcludeValue
AND (FormTypeID <> @DocExpReim OR (FormTypeID = @DocExpReim AND @Abbr NOT IN ('LIT','SXN')))
AND afo.ShowForm <> 0
SELECT * FROM @tblForms
order by sequence
INSERT INTO @tblForms
SELECT
AgentFormListID,
AgentFormListSID,
FormTypeID,
Sequence,
FormName,
AcceptanceReqd,
DuplicatesAllowed,
ExternalLink,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
TaskCode,
1,
ShowForm
FROM @tblForms t
WHERE
t.DuplicatesAllowed = 1 AND NOT EXISTS
(SELECT * FROM @tblForms WHERE Sequence = t.Sequence AND DocumentID IS NULL)
SELECT * FROM @tblForms
order by sequence
Screenshot of what was returned in Management Studio query window:
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
It looks as if you did what I asked. What if you try the corollary:
...
WHERE ColistFlag <> @ExcludeValue
AND (FormTypeID <> @DocExpReim OR (FormTypeID = @DocExpReim AND @Abbr NOT IN ('LIT','SXN')))
AND afo.ShowForm = 1
...
...
WHERE ColistFlag <> @ExcludeValue
AND (FormTypeID <> @DocExpReim OR (FormTypeID = @DocExpReim AND @Abbr NOT IN ('LIT','SXN')))
AND afo.ShowForm = 1
...
ASKER
Same result. It's as if the WHERE clause addition is being ignored.
I checked AgentFormOverride table and it has ONE entry and ShowForm is set to 0 (zero). I was beginning to wonder if the row had been deleted! But no, it's still there:
![agent form override]()
I checked AgentFormOverride table and it has ONE entry and ShowForm is set to 0 (zero). I was beginning to wonder if the row had been deleted! But no, it's still there:
ASKER
I just want to mention that if I REMOVE the WHERE clause restriction, the row shows up (the bottom row)
Here is a screenshot:
![removed where]()
Here is the T-SQL that generates the screen above:
Here is a screenshot:
Here is the T-SQL that generates the screen above:
USE [PP]
GO
/****** Object: StoredProcedure [dbo].[TomTest] Script Date: 01/02/2013 11:14:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[TomTest]
@AssetID int,
@AssetSID int
AS
/*
////////////////////////////////////////////////////////////////////////////////////////////////////////////
Name: AgentFormList_Get
Date:
Purpose: Returns list of active forms for specified asset
Requested by: Madhav
Author: Paul Peterson
History
Date Modified by Requested by Modification/Description
--------------------------------------------------------------------------------------------------------------
11-30-2010 Paul Kade Don't return Reimbursement Policy for Saxon & Litton
3/3/2011 Bethb Madhav A part of the Document and Image Archive Process, changed proc to look at vDocument
////////////////////////////////////////////////////////////////////////////////////////////////////////////
*/
DECLARE @Colist bit,
@ExcludeValue int,
@DocExpReim int,
@Abbr varchar(10)
SELECT @Colist = dbo.ColistRequired(@AssetID, @AssetSID)
SELECT @ExcludeValue = CASE WHEN @Colist = 1 THEN 2 ELSE 1 END
SELECT @DocExpReim = dbo.GetLookupID('DocExpReim')
SELECT @Abbr = RTRIM(Merchant.Abbr)
FROM Asset
INNER JOIN Pool ON Asset.PoolID = Pool.PoolID AND Asset.PoolSID = Pool.PoolSID
INNER JOIN Merchant on Pool.MerchantID = Merchant.MerchantID AND Pool.MerchantSID = Merchant.MerchantSID
WHERE Asset.AssetID = @AssetID AND Asset.AssetSID = @AssetSID
DECLARE @tblForms table (
AgentFormListID int,
AgentFormListSID int,
FormTypeID int,
Sequence int,
FormName varchar(50),
AcceptanceReqd bit,
DuplicatesAllowed bit,
ExternalLink varchar(50),
CreateDt datetime,
ActionDate datetime,
AcceptDecline int,
AcceptedBy varchar(50),
DocumentID int,
DocumentSID int,
AssetTaskID int,
AssetTaskSID int,
AgentFormID int,
AgentFormSID int,
TaskCode varchar(50),
Active bit,
ShowForm bit
)
INSERT INTO @tblForms
SELECT AgentFormList.AgentFormListID, AgentFormList.AgentFormListSID,
FormTypeID, Sequence, FormName, AcceptanceReqd, DuplicatesAllowed, ExternalLink,
CreateDt, ActionDate, AcceptDecline, dbo.getUserName(AgentForm.AcceptUserID,AgentForm.AcceptUserSID) AS AcceptedBy, Document.DocumentID, Document.DocumentSID, AssetTaskID, AssetTaskSID,
AgentForm.AgentFormID, AgentForm.AgentFormSID, TaskCode, AgentForm.Active, afo.ShowForm
FROM AgentFormList
LEFT JOIN AgentForm on
AgentFormList.AgentFormListID = AgentForm.AgentFormListID
AND AgentFormList.AgentFormListSID = AgentForm.AgentFormListSID
AND AgentForm.AssetID = @AssetID AND AgentForm.AssetSID = @AssetSID
LEFT JOIN AgentFormOverride afo on
AgentFormList.AgentFormListID = afo.AgentFormListID
LEFT JOIN vDocument Document ON
AgentForm.DocumentID = Document.DocumentID
AND AgentForm.DocumentSID = Document.DocumentSID
WHERE ColistFlag <> @ExcludeValue
AND (FormTypeID <> @DocExpReim OR (FormTypeID = @DocExpReim AND @Abbr NOT IN ('LIT','SXN')))
SELECT * FROM @tblForms
order by sequence
INSERT INTO @tblForms
SELECT
AgentFormListID,
AgentFormListSID,
FormTypeID,
Sequence,
FormName,
AcceptanceReqd,
DuplicatesAllowed,
ExternalLink,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
TaskCode,
1,
ShowForm
FROM @tblForms t
WHERE
t.DuplicatesAllowed = 1 AND NOT EXISTS
(SELECT * FROM @tblForms WHERE Sequence = t.Sequence AND DocumentID IS NULL)
SELECT * FROM @tblForms
order by sequence
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a questionASKER
We ended-up going in another direction.
I think this troubleshooting was useful for others.
I think this troubleshooting was useful for others.
Thank you for the points. Sorry I couldn't be of any help.
ASKER
No worries.
You did great.
You did great.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck