CODEWITHSUNDEEP
sqlsql-servercommon-table-expression
user2336704
user2336704

Reputation:

Syntax error on WITH clause

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 )

Upvotes: 0

Views: 3026

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

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

Upvotes: 1

Related Questions