Incorrect syntax for SQL query

Question

So I have this SQL Server query:

declare @symbol varchar(8) = '180460';
declare @sdate DATE = '20170630';
declare @edate DATE = '20170704';
IF @symbol = 'TSE00081'
    BEGIN
        SELECT * from dbo.IndexNames
    END
ELSE
BEGIN
    SELECT t1.qcode, t4.Name,t1.effectivedate, t1.publishdate, EventMap.Description, t1.SharesChanged, t1.FFW, t1.OldFFW,
    IIF(t1.EventCode='SK',t6.IndexName,''), IIF(t1.EventCode='SK',t7.Indexname,''), IIF(t1.Market<>'',t5.IndexName,''), IIF(t3.NameEn<>'',t3.NameEn,t2.NameEn), 
    IIF(t1.OldSector<>'',t8.NameEn,''),  IIF(t1.Allotment1<>0 AND t1.EventCode<>'05', t1.Allotment2/t1.Allotment1,''), t1.SharesUnit
    FROM ( dbo.AdvNoticeData as t1 , dbo.IdTable as t4 )
    LEFT JOIN dbo.EventMap on t1.EventCode = dbo.EventMap.EventCode
    LEFT JOIN dbo.Industries as t2 on t1.SectorAsOf = t2.IndNum
    LEFT JOIN dbo.Industries as t3 on t1.Sector = t3.IndNum
    LEFT JOIN dbo.Industries as t8 on t1.OldSector = t8.IndNum
    LEFT JOIN dbo.IndexNames as t5 on t1.OldMarket = t5.Market
    LEFT JOIN dbo.IndexNames as t6 on t1.Scale = t6.Scale
    LEFT JOIN dbo.IndexNames as t7 on t1.OldScale = t7.Scale 
    WHERE (t1.Market=1 or t1.MarketAsOf=1 or (t1.OldMarket=1 and t1.MarketAsOf=''))
    and t1.EffectiveDate >= @sdate and t1.EffectiveDate <= @edate and t1.Qcode = t4.Qcode 
    ORDER BY t1.EffectiveDate, t1.EventCode, t1.Qcode
END

the problem is after the FROM clause. I keep on getting incorrect syntax at ','

When I tried this command (with few modifications) on sqlyog, it works with that comma after the FROM clause. what seems to be the problem? here's the mysql counter part that works:

SELECT t1.qcode, t4.Name,t1.effectivedate, t1.publishdate, 
EventMap.Description, t1.SharesChanged, t1.FFW, t1.OldFFW, 
IF(t1.EventCode="SK",t6.IndexName,""),
IF(t1.EventCode="SK",t7.Indexname,""),
IF(t1.market<>"",t5.IndexName,""),
IF(t3.NameEn<>"",t3.NameEn,t2.NameEn),
IF(t1.oldSector<>"",t8.NameEn,""),
IF(t1.allotment1<>0 AND t1.eventcode<>"05",t1.allotment2/t1.allotment1,""),
t1.sharesunit
FROM (AdvNoticeData AS t1, IdTable AS t4)
LEFT JOIN EventMap ON t1.eventcode = EventMap.eventcode
LEFT JOIN Industries AS t2 ON t1.sectorasof = t2.IndNum
LEFT JOIN Industries AS t3 ON t1.sector = t3.IndNum
LEFT JOIN Industries AS t8 ON t1.oldsector = t8.IndNum
LEFT JOIN IndexNames AS t5 ON t1.oldmarket = t5.Market
LEFT JOIN IndexNames AS t6 ON t1.scale = t6.Scale
LEFT JOIN IndexNames AS t7 ON t1.oldscale = t7.Scale
WHERE (t1.market=1 OR t1.marketasof=1 OR (t1.oldmarket=1 AND t1.marketasof=""))
AND t1.effectivedate >= '20170630'
AND t1.effectivedate <= '20170703'
AND t1.Qcode = t4.Qcode
ORDER BY t1.effectivedate, t1.eventcode,t1.qcode

juergen d · Accepted Answer · 2017-07-04 10:42:40Z

3

Don't mix legacy and new join syntax.

...
FROM dbo.AdvNoticeData as t1
JOIN dbo.IdTable as t4 ON t1.Qcode = t4.Qcode
LEFT JOIN dbo.EventMap on t1.EventCode = dbo.EventMap.EventCode
...

answered Jul 4, 2017 at 10:42

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Woootiness Over a year ago

that seemed to work, now I have "Incorrect syntax near the keyword 'Where'

juergen d Over a year ago

And how does the code look like right around the where?

Woootiness Over a year ago

@juergend here's the code: WHERE (t1.Market=1 or t1.MarketAsOf=1 or (t1.OldMarket=1 and t1.MarketAsOf='')) and t1.EffectiveDate >= sdate and t1.EffectiveDate <= edate and t1.Qcode = t4.Qcode ORDER BY t1.EffectiveDate, t1.EventCode, t1.Qcode

juergen d Over a year ago

And before the where? Exactly like in your question?

Woootiness Over a year ago

yes yes, I replaced ',' with JOIN and removed the parentheses

|

Hybris95 · Accepted Answer · 2017-07-04 11:05:49Z

Modify your old JOIN syntax with an INNER JOIN

declare @symbol varchar(8) = '180460';
declare @sdate DATE = '20170630';
declare @edate DATE = '20170704';
IF @symbol = 'TSE00081'
    BEGIN
        SELECT * from dbo.IndexNames
    END
ELSE
BEGIN
    SELECT t1.qcode, t4.Name,t1.effectivedate, t1.publishdate, EventMap.Description, t1.SharesChanged, t1.FFW, t1.OldFFW,
    IIF(t1.EventCode='SK',t6.IndexName,''), IIF(t1.EventCode='SK',t7.Indexname,''), IIF(t1.Market<>'',t5.IndexName,''), IIF(t3.NameEn<>'',t3.NameEn,t2.NameEn), 
    IIF(t1.OldSector<>'',t8.NameEn,''),  IIF(t1.Allotment1<>0 AND t1.EventCode<>'05', t1.Allotment2/t1.Allotment1,''), t1.SharesUnit
    FROM dbo.AdvNoticeData as t1
    INNER JOIN dbo.IdTable as t4 ON t1.Qcode = t4.Qcode
    LEFT JOIN dbo.EventMap on t1.EventCode = dbo.EventMap.EventCode
    LEFT JOIN dbo.Industries as t2 on t1.SectorAsOf = t2.IndNum
    LEFT JOIN dbo.Industries as t3 on t1.Sector = t3.IndNum
    LEFT JOIN dbo.Industries as t8 on t1.OldSector = t8.IndNum
    LEFT JOIN dbo.IndexNames as t5 on t1.OldMarket = t5.Market
    LEFT JOIN dbo.IndexNames as t6 on t1.Scale = t6.Scale
    LEFT JOIN dbo.IndexNames as t7 on t1.OldScale = t7.Scale 
    WHERE (t1.Market=1 or t1.MarketAsOf=1 or (t1.OldMarket=1 and t1.MarketAsOf=''))
    and t1.EffectiveDate >= @sdate and t1.EffectiveDate <= @edate 
    ORDER BY t1.EffectiveDate, t1.EventCode, t1.Qcode
END

Collectives™ on Stack Overflow

Incorrect syntax for SQL query

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related