Incorrect syntax near 'left'

Question

Post reply

Incorrect syntax near 'left'

slizam

SSC Eights!

Points: 846
More actions
November 28, 2011 at 8:28 pm

#248737

Date/Time : 28/11/2011 11:47:02 PM
SQL script : set dateformat 'dmy';update tbPhoneStatus set statuscd='01' from (select mf6_telmn,p.phoneno PPno,payphoneaddress,mf6_lcno,equp_sche_id MtnID,statuscd Status,TotF from payphone p left join (select telid,count(telid) TotF from faultdata where cdate is null group by telid) f on mf6_telmn=telid left join tbphonestatus s on mf6_telmn=machno where statuscd='02' and totF is null) p inner join tbphonestatus s on ppno=phoneno
Err Number : -2147217900
Err Desc : Line 1: Incorrect syntax near 'left'.
why?
how?
any solution?

Viewing 15 posts - 1 through 14 (of 14 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

First, this is your code reformatted to make it a bit more readable:

set dateformat 'dmy';

update tbPhoneStatus set

statuscd = '01'

from

(select

mf6_telmn,

p.phoneno PPno,

payphoneaddress,

mf6_lcno,

equp_sche_id MtnID,

statuscd Status,

TotF

from

payphone p left join

(select

telid,

count(telid) TotF

from

faultdata

where

cdate is null

group by

telid) f

on mf6_telmn = telid

left join tbphonestatus s

on mf6_telmn = machno

where

statuscd = '02'

and totF is null) p

inner join tbphonestatus s

on ppno = phoneno

Would you please explain what it is you are trying to accomplish?

Second, before you even answer that question, I would like you to read the first article I have referenced below in my signature block regarding asking for help. Please follow its instruction on what you need to post and how. To really help you we will need the DDL (CREATE TABLE statements) for your tables involved in the query, sample data for each of the tables (6 to 10 rows as a series of INSERT INTO statements), and most importantly we need to know what the expected results of the update query should be based on the sample data you provide.

I have looked at your code and I really can't tell what you are trying accomplish. You have nested derived tables and nothing in your original post is helpful in figuring out the code.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Try using aliases between your derived tables, nested tables and outer tables. This would help significantly for us to understand - but also will straighten out what you are trying to accomplish with regards to how the db engine interprets it.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

slizam SSC Eights! Points: 846 More actions · Answer 3

thanks for reply my topic Lynn Pettis...

the problem is, it sometimes get error when i use keyword 'LEFT' in SQL server 2000. can i know the related topic here?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

slizam (11/30/2011)
thanks for reply my topic Lynn Pettis...
the problem is, it sometimes get error when i use keyword 'LEFT' in SQL server 2000. can i know the related topic here?

No one can really help you until you help us. Please reread my earlier post and answer the questions and provide what was requested.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

slizam (11/30/2011)
thanks for reply my topic Lynn Pettis...
the problem is, it sometimes get error when i use keyword 'LEFT' in SQL server 2000. can i know the related topic here?

You will get that type of error when there is something syntactically incorrect with your query.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

slizam SSC Eights! Points: 846 More actions · Answer 6

here is the sample data from 2 tables;

Payphone

---------

PhoneNo MF6_TelMn MF6_LcNo PayphoneAddresss

019542661702408348PENANG Gsm17

tbPhoneStatus

--------------

PhoneNo MachNo StatusCd

019542661702408348 02

SQL Query

----------

SELECT p.phoneno, mf6_telmn AS TelID, mf6_lcno AS Schedule, mf6_stad1 AS Address, StatusCd

FROM payphone p LEFT JOIN tbphonestatus s ON mf6_telmn=machno

WHERE mf6_telmn='02408348'

when i was testing @sql Query Analyzer i hope this result is;

PhoneNo TelID Schedule Addresss StatusCd

0195426617 02408348 PENANG Gsm17 02

but this error occured;

Server: Msg 170, Level 15, State 1, Line 3

Line 3: Incorrect syntax near 'left'.

Why this error happen? i think its no problem before this with this query.....TQ

Phil Parkin SSC Guru Points: 246965 More actions · Answer 7

You have two WHEREs - the first one should be ON.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

slizam (12/3/2011)
here is the sample data from 2 tables;
Payphone
---------
PhoneNo MF6_TelMn MF6_LcNo PayphoneAddresss
019542661702408348PENANG Gsm17
tbPhoneStatus
--------------
PhoneNo MachNo StatusCd
019542661702408348 02
SQL Query
----------
SELECT p.phoneno, mf6_telmn AS TelID, mf6_lcno AS Schedule, mf6_stad1 AS Address, StatusCd
FROM payphone p LEFT JOIN tbphonestatus s ON mf6_telmn=machno
WHERE mf6_telmn='02408348'
when i was testing @sql Query Analyzer i hope this result is;
PhoneNo TelID Schedule Addresss StatusCd
0195426617 02408348 PENANG Gsm17 02
but this error occured;
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'left'.
Why this error happen? i think its no problem before this with this query.....TQ

First, it looks like you editted this post, so not sure what Phil was refering to about a second where clause.

Second, you obviously did not read the first article in my signature block like I asked. Nothing you posted can be cut and pasted into SSMS and run. Please read the article, it will show you how to post the information so that you get the best help possible from us. Bottom line, we are volunteers, help us help you.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

slizam (12/3/2011)
SELECT p.phoneno, mf6_telmn AS TelID, mf6_lcno AS Schedule, mf6_stad1 AS Address, StatusCd
FROM payphone p LEFT JOIN tbphonestatus s ON mf6_telmn=machno
WHERE mf6_telmn='02408348'

That query parses without error. Either one of those objects are views or that's not the entire statement that you ran.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

GilaMonster (12/3/2011)
slizam (12/3/2011)
SELECT p.phoneno, mf6_telmn AS TelID, mf6_lcno AS Schedule, mf6_stad1 AS Address, StatusCd
FROM payphone p LEFT JOIN tbphonestatus s ON mf6_telmn=machno
WHERE mf6_telmn='02408348'
That query parses without error. Either one of those objects are views or that's not the entire statement that you ran.

The query was edited. I saw the same query that phil saw with the duplicate where clause and no on statement in the join.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Phil Parkin SSC Guru Points: 246965 More actions · Answer 11

I knew I should have 'Quoted' that to prove my sanity!

The originally posted query looked like this:

SELECT p.phoneno, mf6_telmn AS TelID, mf6_lcno AS Schedule, mf6_stad1 AS Address, StatusCd

FROM payphone p LEFT JOIN tbphonestatus s WHERE mf6_telmn=machno

WHERE mf6_telmn='02408348'

Hence my comment...

--edit, thanks Jason.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Phil Parkin (12/3/2011)
I knew I should have 'Quoted' that to prove my sanity!
The originally posted query looked like this:
SELECT p.phoneno, mf6_telmn AS TelID, mf6_lcno AS Schedule, mf6_stad1 AS Address, StatusCd
FROM payphone p LEFT JOIN tbphonestatus s WHERE mf6_telmn=machno
WHERE mf6_telmn='02408348'
Hence my comment...
--edit, thanks Jason.

NP.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

slizam SSC Eights! Points: 846 More actions · Answer 13

slizam

SSC Eights!

Points: 846

December 3, 2011 at 6:18 pm

#1416363

Is it something error about reserved keyword?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

slizam (12/3/2011)
Is it something error about reserved keyword?

The modified query that you posted does not have an error at all. Not sure what you are talking about now.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events