set @.. = select case when help

  • 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

  • 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.

  • 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)

  • 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