June 19, 2008 at 4:41 am
Hi,
I haven't done many case statements in SQL, and I am stuck with the below code:
ALTER PROCEDURE [dbo].[usp_UAP]
@WBSID int,
@UAP int output
AS
set nocount on
SELECT dbo.tlbUAP.UAPID, dbo.tlbUAP.ChangeID, dbo.tlbUAP.ReportStructureID,
dbo.tlbUAP.[Percent], dbo.tlbUAP.UAPValue,
dbo.tlbUAP.Justification, dbo.tlbUAP.Section, dbo.tlbUAP.upsize_ts
FROM dbo.tlbUAP INNER JOIN
dbo.tlbChange ON dbo.tlbUAP.ChangeID = dbo.tlbChange.ChangeID INNER JOIN
dbo.tlbWBS ON dbo.tlbChange.ChangeID = dbo.tlbWBS.ChangeID INNER JOIN
dbo.ThreePointEstimate ON dbo.tlbWBS.WBSID = dbo.ThreePointEstimate.WBSID
WHERE dbo.tlbWBS.WBSID = @WBSID
group by dbo.tlbUAP.UAPID, dbo.tlbUAP.ChangeID, dbo.tlbUAP.ReportStructureID,
dbo.tlbUAP.[Percent], dbo.tlbUAP.UAPValue,
dbo.tlbUAP.Justification,
dbo.tlbUAP.Section, dbo.tlbUAP.upsize_ts
set @UAP = select case when dbo.tlbUAP.[Percent] is not null then dbo.tlbUAP.[Percent] else dbo.tlbUAP.UAPValue
fromdbo.tlbUAP INNER JOIN
dbo.tlbChange ON dbo.tlbUAP.ChangeID = dbo.tlbChange.ChangeID INNER JOIN
dbo.tlbWBS ON dbo.tlbChange.ChangeID = dbo.tlbWBS.ChangeID INNER JOIN
dbo.ThreePointEstimate ON dbo.tlbWBS.WBSID = dbo.ThreePointEstimate.WBSID
WHERE dbo.tlbWBS.WBSID = @WBSID
set nocount off
There is only one record returned for any entered WBSID (which is correct). On any given record, either the Percent field in tlbUAP is null, or the UAPValue is null. Want I am trying to do is check which of these is null, and set the field that isn't null to @UAP as an output parameter so it can be used in Access.
At the moment, I am getting a couple of errors being on the set @UAP = ... line:
Msg 156, Level 15, State 1, Procedure usp_UAP, Line 22
Incorrect syntax near the keyword 'select'.
Msg 156, Level 15, State 1, Procedure usp_UAP, Line 23
Incorrect syntax near the keyword 'from'.
What am I doing wrong?
Many thanks,
Andrew
June 19, 2008 at 4:47 am
I solved the "Incorrect syntax near the keyword 'from'." error, I had missed of the word 'end' at the end of the set @UAP = ... line.
I need help with the other error though.
June 19, 2008 at 5:02 am
I solved the other error as well. I put brackets around the select from where statement, and I also missed out the group by which I put in. The group by was necessary as without it, more than one row is returned, and that is not allowed with a sub query. Here is what I have now:
set @UAP = (select case when dbo.tlbUAP.[Percent] is not null then dbo.tlbUAP.[Percent] else dbo.tlbUAP.UAPValue end
fromdbo.tlbUAP INNER JOIN
dbo.tlbChange ON dbo.tlbUAP.ChangeID = dbo.tlbChange.ChangeID INNER JOIN
dbo.tlbWBS ON dbo.tlbChange.ChangeID = dbo.tlbWBS.ChangeID INNER JOIN
dbo.ThreePointEstimate ON dbo.tlbWBS.WBSID = dbo.ThreePointEstimate.WBSID
WHERE dbo.tlbWBS.WBSID = @WBSID
group by dbo.tlbUAP.UAPID, dbo.tlbUAP.ChangeID, dbo.tlbUAP.ReportStructureID,
dbo.tlbUAP.[Percent], dbo.tlbUAP.UAPValue,
dbo.tlbUAP.Justification,
dbo.tlbUAP.Section, dbo.tlbUAP.upsize_ts)
June 19, 2008 at 5:36 am
SELECT@UAP = COALESCE(dbo.tlbUAP.[Percent], dbo.tlbUAP.UAPValue)
FROMdbo.tlbUAP
INNER JOINdbo.tlbChange ON dbo.tlbChange.ChangeID = dbo.tlbUAP.ChangeID
INNER JOINdbo.tlbWBS ON dbo.tlbWBS.ChangeID = dbo.tlbChange.ChangeID
INNER JOINdbo.ThreePointEstimate ON dbo.ThreePointEstimate.WBSID = dbo.tlbWBS.WBSID
WHEREdbo.tlbWBS.WBSID = @WBSID
N 56°04'39.16"
E 12°55'05.25"
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply