0

I am working on a web app and there are some long winded stored procedures and just trying to figure something out, I have extracted this part of the stored proc, but cant get it to work. The guy who did this is creating alias after alias.. and I just want to get a section to work it out. Its complaining about the ending but all the curly brackets seem to match. Thanks in advance..

FInputs is another stored procedure.. the whole thing is referred to as BASE.. the result of this was being put in a temp table where its all referred to as U. I am trying to break it down into separate sections.

;WITH Base AS
(
SELECT 
    * 
FROM F_Inputs(1,1,100021)
),
U AS
(
SELECT
    ISNULL(q.CoverPK,r.CoverPK) AS CoverPK, 
    OneLine,
    InputPK,
    ISNULL(q.InputName,r.InputName) AS InputName,
    InputOrdinal,
    InputType,
    ParentPK,
    InputTriggerFK,
    ISNULL(q.InputString,r.InputString) AS InputString,
    PageNo,
    r.RatePK,
    RateName,
    Rate,
    Threshold,
    ISNULL(q.Excess,r.Excess) AS Excess,
    RateLabel,
    RateTip,
    Refer,
    DivBy,
    RateOrdinal,
    RateBW,
    ngRequired,
    ISNULL(q.RateValue,r.RateValue) AS RateValue,
    ngClass,
    ngPattern,
    UnitType,
    TableChildren,
    TableFirstColumn,
    parentRatePK,
    listRatePK,
    NewParentBW,
    NewChildBW,
    ISNULL(q.SumInsured,0) AS SumInsured,
    ISNULL(q.NoItems,0) AS NoItems,
    DisplayBW,
    ReturnBW,
    StringBW,
    r.lblSumInsured,
    lblNumber,
    SubRateHeading,
    TrigSubHeadings,
    ISNULL(q.RateTypeFK,r.RateTypeFK) AS RateTypeFK,
    0 AS ListNo,
    0 AS ListOrdinal,
    InputSelectedPK,
    InputVis,
    CASE 
        WHEN ISNULL(NewChildBW,0) = 0
        THEN 1
        WHEN q.RatePK is NOT null
        THEN 1
        ELSE RateVis
    END AS RateVis,
    RateStatus,
    DiscountFirstRate,
    DiscountSubsequentRate,
    CoverCalcFK,
    TradeFilter,
    ngDisabled,
    RateGroup,
    SectionNo
FROM BASE R
LEFT JOIN QuoteInputs Q
    ON q.RatePK = r.RatePK
    AND q.ListNo = 0
    AND q.QuoteId = 100021 )
Improve this question
8
  • I should have put in the question title "syntax error"
    – user2336704
    Commented May 13, 2017 at 14:16
  • Msg 102, Level 15, State 1, Line 75 Incorrect syntax near ')'.
    – user2336704
    Commented May 13, 2017 at 14:29
  • The code declares two views on-the-fly BASE and U. It must be followed by a query. E.g. AND q.QuoteId = 100021 ) select * from u;
    – Thorsten Kettner
    Commented May 13, 2017 at 14:29
  • Syntax error at the last line
    – user2336704
    Commented May 13, 2017 at 14:29
  • Ah wow, so the views always need to return a query? thank you!
    – user2336704
    Commented May 13, 2017 at 14:31

1 Answer 1

Reset to default
1

Well, I explained the issue in the comments section already. I'm doing it here again, so future readers find the answer more easily.

A WITH clause is part of a query. It creates a view on-the-fly, e.g.:

with toys as (select * from products where type = 'toys') select * from toys;

Without the query at the end, the statement is invalid (and would not make much sense anyhow; if one wanted a permanent view for later use, one would use CREATE VIEW instead).

Improve this answer