Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: SQL Statement Help

    I'm stuck on some syntax here. Hopefully someone here can see what I'm doing wrong. I'm using query analyzer to try to get this to work, so
    that I can add it into a stored procedure that will run nightly.

    declare @strmnth char(3)
    set @strMnth = 'AUG'
    INSERT INTO EXCEPTIONS
    ([exc_desc], [exc_type], [tons_var], [pct_var])
    SELECT tmpFcstImp.product, tmpFcstImp.' + @strMnth + '_fcst as 'imp',1, 100
    FROM tmpFcstImp LEFT OUTER JOIN
    FCST_AGGREGATE ON tmpFcstImp.Product = FCST_AGGREGATE.PRODUCT
    AND tmpFcstImp.[year] = FCST_AGGREGATE.[YEAR]
    WHERE (FCST_AGGREGATE.PRODUCT IS NULL) AND (tmpFcstImp.[year] >= YEAR(GETDATE()))

    I keep getting:
    Server: Msg 170, Level 15, State 1, Line 5
    Line 5: Incorrect syntax near ' + @strMnth + '.

    Any idea what I need to change to get this to work?

    Thanks
    Inspiration Through Fermentation

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: SQL Statement Help

    You are trying to execute dynamic SQL. Try this:

    declare @strmnth char(3)
    set @strMnth = 'AUG'
    EXECUTE('INSERT INTO EXCEPTIONS
    ([exc_desc], [exc_type], [tons_var], [pct_var])
    SELECT tmpFcstImp.product, tmpFcstImp.' + @strMnth + '_fcst as [imp],1, 100
    FROM tmpFcstImp LEFT OUTER JOIN
    FCST_AGGREGATE ON tmpFcstImp.Product = FCST_AGGREGATE.PRODUCT
    AND tmpFcstImp.[year] = FCST_AGGREGATE.[YEAR]
    WHERE (FCST_AGGREGATE.PRODUCT IS NULL) AND (tmpFcstImp.[year] >= YEAR(GETDATE()))')
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    That worked! Thanks
    Inspiration Through Fermentation

  4. #4
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Thank you for confirming. Unfortunately, not everybody is that decent.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •