syntax error

  • thanks in advance. appreciate your help

    receive error

    Msg 102, Level 15, State 1, Line 111

    Incorrect syntax near 'SEASON_2'. end of sp.

    what am i forgetting?

    SELECT

    S1.STYLE,

    S1. COLOR,

    S1.replication_date,

    S1.SEASON,

    S1.ON_ORD_1,

    S1.ON_ORD_2,

    S1.ON_ORD_3,

    S1.ON_ORD_4,

    S1.ON_ORD_5,

    S1.ON_ORD_6,

    S1.ON_ORD_7,

    S1.ON_ORD_8,

    S1.ON_ORD_9,

    S1.ON_HND_1,

    S1.ON_HND_2,

    S1.ON_HND_3,

    S1.ON_HND_4,

    S1.N_HND_5,

    S1.ON_HND_6,

    S1.ON_HND_7,

    S1.ON_HND_8,

    S1.ON_HND_9,

    S2.STYLE,

    S2. COLOR,

    S2.replication_date,

    S2.SEASON,

    S2.ON_ORD_1,

    S2.ON_ORD_2,

    S2.ON_ORD_3,

    S2.ON_ORD_4,

    S2.ON_ORD_5,

    S2.ON_ORD_6,

    S2.ON_ORD_7,

    S2.ON_ORD_8,

    S2.ON_ORD_9,

    S2.ON_HND_1,

    S2.ON_HND_2,

    S2.ON_HND_3,

    S2.ON_HND_4,

    S2.N_HND_5,

    S2.ON_HND_6,

    S2.ON_HND_7,

    S2.ON_HND_8,

    S2.ON_HND_9

    FROM dbo.STYLEMST_HISTORY

    LEFT JOIN

    (/*FOR CURRENT DAY*/

    SELECT

    SYSTYLAS STYLE,

    SYCLNMAS COLOR,

    CONVERT(datetime,CONVERT(char(10),REPLICATION_DATE,101)) as replication_date,

    SYSEAAS SEASON,

    SYASZ1as ON_ORD_1,

    SYASZ2as ON_ORD_2,

    SYASZ3as ON_ORD_3,

    SYASZ4as ON_ORD_4,

    SYASZ5as ON_ORD_5,

    SYASZ6as ON_ORD_6,

    SYASZ7as ON_ORD_7,

    SYASZ8as ON_ORD_8,

    SYASZ9as ON_ORD_9,

    SYFSZ1AS ON_HND_1,

    SYFSZ2AS ON_HND_2,

    SYFSZ3AS ON_HND_3,

    SYFSZ4AS ON_HND_4,

    SYFSZ5AS ON_HND_5,

    SYFSZ6AS ON_HND_6,

    SYFSZ7AS ON_HND_7,

    SYFSZ8AS ON_HND_8,

    SYFSZ9AS ON_HND_9

    FROM

    dbo.STYLEMST_HISTORY

    where CONVERT(datetime,CONVERT(char(10),S1.REPLICATION_DATE,101))=CONVERT(datetime,CONVERT(char(10),getdate(),101))

    )S1

    LEFT JOIN

    (

    /* For previous day */

    SELECT

    SYSTYLAS STYLE_2,

    SYCLNMAS COLOR_2,

    CONVERT(datetime,CONVERT(char(10),REPLICATION_DATE,101)) as replication_date_2,

    SYSEAAS SEASON_2,

    SYASZ1as ON_ORD_1_2,

    SYASZ2as ON_ORD_2_2,

    SYASZ3as ON_ORD_3_2,

    SYASZ4as ON_ORD_4_2,

    SYASZ5as ON_ORD_5_2,

    SYASZ6as ON_ORD_6_2,

    SYASZ7as ON_ORD_7_2,

    SYASZ8as ON_ORD_8_2,

    SYASZ9as ON_ORD_9_2,

    SYFSZ1AS ON_HND_1_2,

    SYFSZ2AS ON_HND_2_2,

    SYFSZ3AS ON_HND_3_2,

    SYFSZ4AS ON_HND_4_2,

    SYFSZ5AS ON_HND_5_2,

    SYFSZ6AS ON_HND_6_2,

    SYFSZ7AS ON_HND_7_2,

    SYFSZ8AS ON_HND_8_2,

    SYFSZ9AS ON_HND_9_2,

    CONVERT(datetime,CONVERT(char(10),getdate()-1,101)) as previous_day_2

    FROM

    dbo.STYLEMST_HISTORY

    WHERE CONVERT(datetime,CONVERT(char(10),REPLICATION_DATE,101))=CONVERT(datetime,CONVERT(char(10),getdate()-1,101))

    )S2

    ONS1.SYSTYL=S2.STYLE_2

    AND S1.SYCLNM = S2.COLOR_2

    AND S1.SYSEA = S2.SEASON_2

  • It would help if you could provide the DDL (CREATE TABLE) statement(s) for the table(s) involved. Can't really help based on what you have posted.

    Did you try double-clicking on the error message in the message tab? That will take you directly yo the line with the error.

  • Lynn Pettis (7/23/2010)


    It would help if you could provide the DDL (CREATE TABLE) statement(s) for the table(s) involved. Can't really help based on what you have posted.

    Did you try double-clicking on the error message in the message tab? That will take you directly yo the line with the error.

    Apparently she doesn't have time

  • no need to be snide.

  • i did double click the error message and it brings me to the end of the procedure

    Incorrect syntax near 'SEASON_2'.

    the procedure posted before is using just the one table based upon the replication date which is the timestamp.

    the table creation is basic it is just copying and existing table every day to compare records(below)

    Select

    SYSEA,

    SYSTYL,

    SYCOLR,

    SYCLNM,

    SYSYNM,

    SYCLAS,

    SYSHOP,

    SYCOOR,

    SYPRC1,

    SYUOM,

    SYSCAL,

    SYVSIZ,

    SYDZCS,

    SYHFCS,

    SYHFAS,

    SYPRJU,

    SYCMCD,

    SYWLCD,

    SYUIWF,

    SYSTAT,

    SY2NDG,

    SYUB1,

    SYUB2,

    SYUB3,

    SYUB4,

    SYUB5,

    SYBSZ1,

    SYBSZ2,

    SYBSZ3,

    SYBSZ4,

    SYBSZ5,

    SYBSZ6,

    SYBSZ7,

    SYBSZ8,

    SYBSZ9,

    SYCSZ1,

    SYCSZ2,

    SYCSZ3,

    SYCSZ4,

    SYCSZ5,

    SYCSZ6,

    SYCSZ7,

    SYCSZ8,

    SYCSZ9,

    SYFSZ1,

    SYFSZ2,

    SYFSZ3,

    SYFSZ4,

    SYFSZ5,

    SYFSZ6,

    SYFSZ7,

    SYFSZ8,

    SYFSZ9,

    SYSSZ1,

    SYSSZ2,

    SYSSZ3,

    SYSSZ4,

    SYSSZ5,

    SYSSZ6,

    SYSSZ7,

    SYSSZ8,

    SYSSZ9,

    SYASZ1,

    SYASZ2,

    SYASZ3,

    SYASZ4,

    SYASZ5,

    SYASZ6,

    SYASZ7,

    SYASZ8,

    SYASZ9,

    SYCPUM,

    SYIDP,

    SYCDP,

    SYDLBK,

    SYLRGQ,

    SYLRGU,

    SYOFFP,

    SYJCPP,

    YEAR(GETDATE()) * 10000 + MONTH(GETDATE()) * 100 + DAY(GETDATE()) AS REPLICATION_DATE

    into dbo.STYLEMST_HISTORY

    FROM

    dbo.STYLEMST

  • sharonmtowler (7/23/2010)


    no need to be snide.

    I didn't take it as snide.

    You really should read the first article I reference below in my signature block regarding asking for help. Follow those guidelines, include sample data, you will get much better responses from many people. The fact that you'd get tested code in return for your helping us help you is a bonus.

    Please remember, we are volunteers offering our experience and time to provide help when we can. The more you provide up front makes it easier for us to provide that help.

  • Sorry, I just found that comment by you funny considering you were asking people to spend their time to help with your problem, and doing what he asked would have made it easier for them to help.

    Anyway, without DDL its pretty tough to figure out exactly what you're trying to get, but based on my best guess, try this:

    SELECT

    S1.STYLE,

    S1. COLOR,

    S1.replication_date,

    S1.SEASON,

    S1.ON_ORD_1,

    S1.ON_ORD_2,

    S1.ON_ORD_3,

    S1.ON_ORD_4,

    S1.ON_ORD_5,

    S1.ON_ORD_6,

    S1.ON_ORD_7,

    S1.ON_ORD_8,

    S1.ON_ORD_9,

    S1.ON_HND_1,

    S1.ON_HND_2,

    S1.ON_HND_3,

    S1.ON_HND_4,

    S1.N_HND_5,

    S1.ON_HND_6,

    S1.ON_HND_7,

    S1.ON_HND_8,

    S1.ON_HND_9,

    S2.STYLE,

    S2. COLOR,

    S2.replication_date,

    S2.SEASON,

    S2.ON_ORD_1,

    S2.ON_ORD_2,

    S2.ON_ORD_3,

    S2.ON_ORD_4,

    S2.ON_ORD_5,

    S2.ON_ORD_6,

    S2.ON_ORD_7,

    S2.ON_ORD_8,

    S2.ON_ORD_9,

    S2.ON_HND_1,

    S2.ON_HND_2,

    S2.ON_HND_3,

    S2.ON_HND_4,

    S2.N_HND_5,

    S2.ON_HND_6,

    S2.ON_HND_7,

    S2.ON_HND_8,

    S2.ON_HND_9

    FROM (/*FOR CURRENT DAY*/

    SELECT

    SYSTYL AS STYLE,

    SYCLNM AS COLOR,

    CONVERT(datetime,CONVERT(char(10),REPLICATION_DATE,101)) as replication_date,

    SYSEA AS SEASON,

    SYASZ1 as ON_ORD_1,

    SYASZ2 as ON_ORD_2,

    SYASZ3 as ON_ORD_3,

    SYASZ4 as ON_ORD_4,

    SYASZ5 as ON_ORD_5,

    SYASZ6 as ON_ORD_6,

    SYASZ7 as ON_ORD_7,

    SYASZ8 as ON_ORD_8,

    SYASZ9 as ON_ORD_9,

    SYFSZ1 AS ON_HND_1,

    SYFSZ2 AS ON_HND_2,

    SYFSZ3 AS ON_HND_3,

    SYFSZ4 AS ON_HND_4,

    SYFSZ5 AS ON_HND_5,

    SYFSZ6 AS ON_HND_6,

    SYFSZ7 AS ON_HND_7,

    SYFSZ8 AS ON_HND_8,

    SYFSZ9 AS ON_HND_9

    FROM

    dbo.STYLEMST_HISTORY

    where CONVERT(datetime,CONVERT(char(10),S1.REPLICATION_DATE,101))=CONVERT(datetime,CONVERT(char(10),getdate(),101))

    )S1

    LEFT OUTER JOIN

    (

    /* For previous day */

    SELECT

    SYSTYL AS STYLE_2,

    SYCLNM AS COLOR_2,

    CONVERT(datetime,CONVERT(char(10),REPLICATION_DATE,101)) as replication_date_2,

    SYSEA AS SEASON_2,

    SYASZ1 as ON_ORD_1_2,

    SYASZ2 as ON_ORD_2_2,

    SYASZ3 as ON_ORD_3_2,

    SYASZ4 as ON_ORD_4_2,

    SYASZ5 as ON_ORD_5_2,

    SYASZ6 as ON_ORD_6_2,

    SYASZ7 as ON_ORD_7_2,

    SYASZ8 as ON_ORD_8_2,

    SYASZ9 as ON_ORD_9_2,

    SYFSZ1 AS ON_HND_1_2,

    SYFSZ2 AS ON_HND_2_2,

    SYFSZ3 AS ON_HND_3_2,

    SYFSZ4 AS ON_HND_4_2,

    SYFSZ5 AS ON_HND_5_2,

    SYFSZ6 AS ON_HND_6_2,

    SYFSZ7 AS ON_HND_7_2,

    SYFSZ8 AS ON_HND_8_2,

    SYFSZ9 AS ON_HND_9_2,

    CONVERT(datetime,CONVERT(char(10),getdate()-1,101)) as previous_day_2

    FROM

    dbo.STYLEMST_HISTORY

    WHERE CONVERT(datetime,CONVERT(char(10),REPLICATION_DATE,101))=CONVERT(datetime,CONVERT(char(10),getdate()-1,101))

    )S2

    ON S1.SYSTYL =S2.STYLE_2

    AND S1.SYCLNM = S2.COLOR_2

    AND S1.SYSEA = S2.SEASON_2

    Basically, looks like you just want to outer join the two derived tables. If thats what you want, the above should be closer to the right format (I cant test since you provided no ddl)

    What tripped you up (assuming I guessed your intent right) was FROM dbo.STYLEMST_HISTORY

    LEFT JOIN. Instead of deriving 2 tables and joining them together, you table scan a table, try to outer join it to the first derived table (with no joining criteria). then also try to outer join it to the second (with all your joining criteria linking the 2 derived tables instead).

  • The only thing u r missing is the join condition...

    SELECT

    S1.STYLE,

    S1. COLOR,

    S1.replication_date,

    S1.SEASON,

    S1.ON_ORD_1,

    S1.ON_ORD_2,

    S1.ON_ORD_3,

    S1.ON_ORD_4,

    S1.ON_ORD_5,

    S1.ON_ORD_6,

    S1.ON_ORD_7,

    S1.ON_ORD_8,

    S1.ON_ORD_9,

    S1.ON_HND_1,

    S1.ON_HND_2,

    S1.ON_HND_3,

    S1.ON_HND_4,

    S1.N_HND_5,

    S1.ON_HND_6,

    S1.ON_HND_7,

    S1.ON_HND_8,

    S1.ON_HND_9,

    S2.STYLE,

    S2. COLOR,

    S2.replication_date,

    S2.SEASON,

    S2.ON_ORD_1,

    S2.ON_ORD_2,

    S2.ON_ORD_3,

    S2.ON_ORD_4,

    S2.ON_ORD_5,

    S2.ON_ORD_6,

    S2.ON_ORD_7,

    S2.ON_ORD_8,

    S2.ON_ORD_9,

    S2.ON_HND_1,

    S2.ON_HND_2,

    S2.ON_HND_3,

    S2.ON_HND_4,

    S2.N_HND_5,

    S2.ON_HND_6,

    S2.ON_HND_7,

    S2.ON_HND_8,

    S2.ON_HND_9

    FROM dbo.STYLEMST_HISTORY

    LEFT JOIN

    (/*FOR CURRENT DAY*/

    SELECT

    SYSTYL AS STYLE,

    SYCLNM AS COLOR,

    CONVERT(datetime,CONVERT(char(10),REPLICATION_DATE,101)) as replication_date,

    SYSEA AS SEASON,

    SYASZ1 as ON_ORD_1,

    SYASZ2 as ON_ORD_2,

    SYASZ3 as ON_ORD_3,

    SYASZ4 as ON_ORD_4,

    SYASZ5 as ON_ORD_5,

    SYASZ6 as ON_ORD_6,

    SYASZ7 as ON_ORD_7,

    SYASZ8 as ON_ORD_8,

    SYASZ9 as ON_ORD_9,

    SYFSZ1 AS ON_HND_1,

    SYFSZ2 AS ON_HND_2,

    SYFSZ3 AS ON_HND_3,

    SYFSZ4 AS ON_HND_4,

    SYFSZ5 AS ON_HND_5,

    SYFSZ6 AS ON_HND_6,

    SYFSZ7 AS ON_HND_7,

    SYFSZ8 AS ON_HND_8,

    SYFSZ9 AS ON_HND_9

    FROM

    dbo.STYLEMST_HISTORY

    where CONVERT(datetime,CONVERT(char(10),S1.REPLICATION_DATE,101))=CONVERT(datetime,CONVERT(char(10),getdate(),101))

    )S1

    ------------------ ADD JOIN CONDITION HERE.............

    LEFT JOIN

    (

    /* For previous day */

    SELECT

    SYSTYL AS STYLE_2,

    SYCLNM AS COLOR_2,

    CONVERT(datetime,CONVERT(char(10),REPLICATION_DATE,101)) as replication_date_2,

    SYSEA AS SEASON_2,

    SYASZ1 as ON_ORD_1_2,

    SYASZ2 as ON_ORD_2_2,

    SYASZ3 as ON_ORD_3_2,

    SYASZ4 as ON_ORD_4_2,

    SYASZ5 as ON_ORD_5_2,

    SYASZ6 as ON_ORD_6_2,

    SYASZ7 as ON_ORD_7_2,

    SYASZ8 as ON_ORD_8_2,

    SYASZ9 as ON_ORD_9_2,

    SYFSZ1 AS ON_HND_1_2,

    SYFSZ2 AS ON_HND_2_2,

    SYFSZ3 AS ON_HND_3_2,

    SYFSZ4 AS ON_HND_4_2,

    SYFSZ5 AS ON_HND_5_2,

    SYFSZ6 AS ON_HND_6_2,

    SYFSZ7 AS ON_HND_7_2,

    SYFSZ8 AS ON_HND_8_2,

    SYFSZ9 AS ON_HND_9_2,

    CONVERT(datetime,CONVERT(char(10),getdate()-1,101)) as previous_day_2

    FROM

    dbo.STYLEMST_HISTORY

    WHERE CONVERT(datetime,CONVERT(char(10),REPLICATION_DATE,101))=CONVERT(datetime,CONVERT(char(10),getdate()-1,101))

    )S2

    ON S1.SYSTYL =S2.STYLE_2

    AND S1.SYCLNM = S2.COLOR_2

    AND S1.SYSEA = S2.SEASON_2

  • thank you thank you thank you

Viewing 9 posts - 1 through 8 (of 8 total)

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