Post reply

Common Table Expression (CTE) assistance needed...

  • January 21, 2008 at 8:02 am

    #185941

    I have the code below to explode a BOM table. The syntax looks correct to me, but I get this error:

    Msg 156, Level 15, State 1, Line 1

    Incorrect syntax near the keyword 'WITH'.

    WITH BomExpl(ASSYPARTNO, COMPPARTNO, BOMQTY, ComponentLevel) AS

    (

    SELECT b.ASSYPARTNO

    ,b.COMPPARTNO

    ,b.BOMQTY

    ,0 AS ComponentLevel

    FROM vis_matrl.dbo.source_boms AS b

    WHERE b.ASSYPARTNO = 'A01481-02'

    UNION ALL

    SELECT bom.ASSYPARTNO

    ,bom.COMPPARTNO

    ,bom.BOMQTY

    ,ComponentLevel + 1

    FROM vis_matrl.dbo.source_boms AS bom

    INNER JOIN BomExpl AS p

    ON bom.ASSYPARTNO = p.COMPPARTNO

    )

    SELECT d.ASSYPARTNO

    ,d.COMPPARTNO

    ,d.BOMQTY

    ,ComponentLevel

    FROM BomExpl AS d

    ORDER BY ComponentLevel, ASSYPARTNO, COMPPARTNO, ITEMNO

  • January 21, 2008 at 8:08 am

    #770156

    Ensure that the statement immediately preceeding the WITH statement ends in a semicolon.

    Try to get into the habit of using the semicolon as a statement terminator in all T-SQL code. Microsoft is starting to enforce this with all new stuff. IMO, long overdue.

    [font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

  • January 21, 2008 at 8:16 am

    #770160

    Thank you...this is the only statement I have (other than comments) in my query window.

  • January 21, 2008 at 9:21 am

    #770183

    I figured it out. It didn't work because I was in SQL2000. When I ran it in SQL2005, it worked. Thank you for your help anyway.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply