June 24, 2008 at 8:36 am
Hi,
I have a stored procedure that isn't working how I want it to. The stored procedure is as follows:
ALTER procedure [dbo].[usp_WBSSelectedTEST]
@ProjectID int,
@RiskID int
AS
set nocount on
SELECT vWBSAvailable.WBSSubsetID, vWBSAvailable.[Change : WBS : WBSSubset], vWBSEntered.RiskID, vWBSEntered.WBSSubsetID
FROM vWBSAvailable LEFT OUTER JOIN
vWBSEntered ON vWBSAvailable.WBSSubsetID = vWBSEntered.WBSSubsetID
WHERE (vWBSEntered.RiskID = @RiskID) AND (vWBSEntered.ProjectID = @ProjectID)
set nocount off
The view vWBSAvailable looks like:
ALTER VIEW [dbo].[vWBSAvailable]
AS
SELECT TOP (100) PERCENT dbo.vWBSSubset.WBSSubsetID, dbo.tlbWBS.WBS, dbo.tlbWBS.WBSDescription,
'Change - ' + CAST(dbo.tlbChange.Change AS varchar(5)) + ' : ' + CAST(dbo.tlbWBS.WBS AS varchar(5))
+ ' - ' + dbo.tlbWBS.WBSDescription + ' : ' + CAST(dbo.vWBSSubset.WBSSubset AS varchar(5))
+ ' - ' + dbo.vWBSSubset.WBSSubsetDescription AS 'Change : WBS : WBSSubset', dbo.tlbChange.ProjectID
FROM dbo.tlbWBS INNER JOIN
dbo.tlbChange ON dbo.tlbWBS.ChangeID = dbo.tlbChange.ChangeID INNER JOIN
dbo.vWBSSubset ON dbo.tlbWBS.WBSID = dbo.vWBSSubset.WBSID
ORDER BY dbo.tlbChange.Change, dbo.tlbWBS.WBS, dbo.vWBSSubset.WBSSubset,
dbo.tlbWBS.WBSDescription, dbo.vWBSSubset.WBSSubsetDescription
The view vWBSEntered looks like:
ALTER VIEW [dbo].[vWBSEntered]
AS
SELECT RiskWBSSubID, RiskID, WBS, WBSSubsetID, section, WBSName, WBSNumber, ProjectID
FROM dbo.RiskWBSSubTest
The vWBSSubset is really important to see here. If you wish to know the data types for the different fields, please ask.
Basically, in Access, I have two list boxes. One list box displays all the available WBS Subset's for the given project, and the other displays all the WBS Subset's for the given risk (the views aren't the recordsource for the list boxes). At the moment, I have it so that you double click an item in the first list box, and it appears in the second list box (also being entered into the table RiskWBSSub), and if you double click an item in the second list box, it disappears from the list box and is deleted from the table RiskWBSSub. What I would like is when the item is double clicked in the first list box, it disappears from that list box (so the first list box only shows the available WBS Subset's for the given project and risk). The stored procedure usp_WBSSelectedTEST is how I intend to do this with.
Here are all the WBS Subset's that are pulled from vWBSAvailable for ProjectID of 10:
WBS Subset
322, 323, 512, 2766, 840, 344, 435, 511, 1184, 1223, 2092
Here are all the WBS Subset's that are pulled from vWBSEntered for RiskID of 284:
WBS Subset
322, 323, 512, 840
So basically, the stored procedure should bring back only the WBS Subsets:
2766, 344, 435, 511, 1184, 1223, 2092
Many thanks in advance for your help,
Andrew
June 24, 2008 at 12:23 pm
Since you're adding the value to a table when you double-click it, you could have the query for the list box have a "where not in" and select values from that table. That would remove it from the first list.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 25, 2008 at 2:18 am
Thanks.
I have tried doing that, but at the moment it doesn't return any records. Here is what I have now:
SELECT dbo.vWBSAvailable.WBSSubsetID, dbo.vWBSAvailable.[Change : WBS : WBSSubset]
FROM dbo.vWBSAvailable
WHERE dbo.vWBSAvailable.ProjectID = @ProjectID AND NOT EXISTS
(SELECT dbo.vWBSEntered.RiskID, dbo.vWBSEntered.WBSSubsetID
FROM dbo.vWBSEntered WHERE dbo.vWBSEntered.RiskID = @RiskID AND dbo.vWBSEntered.ProjectID = @ProjectID
AND dbo.vWBSEntered.WBSSubsetID <> dbo.vWBSAvailable.WBSSubsetID)
Anyone know where I have gone wrong?
June 25, 2008 at 2:29 am
No matter, fixed it. It was returning no records as I was checking that the WBSSubsetID didn't equal in the not exists, whereas I should of been checking they do equal in the not exists. So here is what it looks like now:
SELECT dbo.vWBSAvailable.WBSSubsetID, dbo.vWBSAvailable.[Change : WBS : WBSSubset]
FROM dbo.vWBSAvailable
WHERE dbo.vWBSAvailable.ProjectID = @ProjectID AND NOT EXISTS
(SELECT dbo.vWBSEntered.RiskID, dbo.vWBSEntered.WBSSubsetID
FROM dbo.vWBSEntered WHERE dbo.vWBSEntered.RiskID = @RiskID AND dbo.vWBSEntered.ProjectID = @ProjectID AND
dbo.vWBSEntered.WBSSubsetID = dbo.vWBSAvailable.WBSSubsetID)
June 25, 2008 at 9:00 am
Does that get you what you need, or is it still not quite right?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 25, 2008 at 9:59 am
Thats solved it, thanks GSquared.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply