Solved

row that should be excluded still shows up

Posted on 2012-12-31
16
288 Views
Last Modified: 2013-01-03
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)


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

Open in new window



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
0
Comment
Question by:knowlton
  • 9
  • 7
16 Comments
 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 38733156
There are at least two INSERT statements in your stored procedure.  Is it possible the record is being added by the other?
0
 
LVL 5

Author Comment

by:knowlton
ID: 38733170
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?
0
 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 38733289
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.
0
 
LVL 5

Author Comment

by:knowlton
ID: 38733297
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?
0
 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 38733330
How about:

...
WHERE ColistFlag <> @ExcludeValue
AND (FormTypeID <> @DocExpReim OR (FormTypeID = @DocExpReim AND @Abbr NOT IN ('LIT','SXN'))) AND t.ShowForm <> 0
...
0
 
LVL 5

Author Comment

by:knowlton
ID: 38733349
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?
0
 
LVL 5

Author Comment

by:knowlton
ID: 38733365
Maybe this can help:



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

Open in new window





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

Open in new window

0
 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 38733526
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
...
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 5

Author Comment

by:knowlton
ID: 38736871
Now no rows return.  Did I do it correctly?

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

Open in new window



Screenshot of what was returned in Management Studio query window:


no rows return
0
 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 38736925
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
...
0
 
LVL 5

Author Comment

by:knowlton
ID: 38736940
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
0
 
LVL 5

Author Comment

by:knowlton
ID: 38737363
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:

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

Open in new window

0
 
LVL 34

Accepted Solution

by:
Paul MacDonald earned 500 total points
ID: 38738138
What happens if you skip the second SELECT and INSERT statements altogether?  You can just wrap them in

/*
...
*/


to comment them out.
0
 
LVL 5

Author Closing Comment

by:knowlton
ID: 38740729
We ended-up going in another direction.

I think this troubleshooting was useful for others.
0
 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 38740830
Thank you for the points.  Sorry I couldn't be of any help.
0
 
LVL 5

Author Comment

by:knowlton
ID: 38740874
No worries.

You did great.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now