July 23, 2010 at 10:03 am
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
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 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
July 23, 2010 at 10:09 am
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.
July 23, 2010 at 10:19 am
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
July 23, 2010 at 10:24 am
no need to be snide.
July 23, 2010 at 10:29 am
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
July 23, 2010 at 10:31 am
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.
July 23, 2010 at 10:34 am
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).
July 23, 2010 at 11:01 am
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
July 23, 2010 at 12:02 pm
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