snytax - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

snytax

Author

Topic

notsosuper
Posting Yak Master

190 Posts

Posted - 2005-07-28 : 19:31:33

what is the problem with this snytax? error messages Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'select'.
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'AS'.

select a.contactname,a.contacttitle,
b.contmethodtype, b.contmethodvalue,
c.clientname, c.billingaddress1, c.billingcity, c.billingstate, c.billingzip
(select top 1 contmethodvalue from contactmethods where [b].contactid = [a].[contactid] and contmethodtype = 'Phone') AS Phone,
(select top 1 contmethodvalue from contactmethods where [b].contactid = [a].[contactid] and contmethodtype = 'Email') AS Email From contacts as a , contactmethods as b, clients as c where a.contactid = b.contactid and a.clientid = c.clientid and c.groupid = 'NRSIG' order by a.contactname,c.clientname

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2005-07-28 : 19:53:32

You're missing a comma after c.billingzip.....

notsosuper
Posting Yak Master

190 Posts

Posted - 2005-07-28 : 20:27:03

thank you

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-07-28 : 20:33:15

Also, your subquery doesn't have an order by in it, which should be used anytime you use the TOP keyword.

-ec

notsosuper
Posting Yak Master

190 Posts

Posted - 2005-07-29 : 11:29:59

I have order by at the end, do I need somewhere else?

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-07-29 : 11:53:35

quote:
Originally posted by notsosuper

I have order by at the end, do I need somewhere else?

Yes, here:
select top 1 contmethodvalue from contactmethods where .contactid = [a].[contactid] and contmethodtype = 'Phone' ORDER BY contmethodvalue

and here

select top 1 contmethodvalue from contactmethods where [b].contactid = [a].[contactid] and contmethodtype = 'Email' [b]ORDER BY contmethodvalue

notsosuper
Posting Yak Master

190 Posts

Posted - 2005-07-29 : 11:59:48

I did tried to put those it is giving me snytax error, can you put all the code with these order by if you don't mind please. thanks

notsosuper
Posting Yak Master

190 Posts

Posted - 2005-07-29 : 12:23:40

here the all code, it is giving me error Incorrect syntax near the keyword 'AS'.

select a.contactname,a.contacttitle,(select top 1 contmethodvalue from contactmethods where contactid = [a].[contactid] and contmethodtype = 'Phone') AS Phone ORDER BY contmethodvalue,
(select top 1 contmethodvalue from contactmethods where contactid = [a].[contactid] and contmethodtype = 'Email') AS Email ORDER BY contmethodvalue,
(select top 1 contmethodvalue from contactmethods where contactid = [a].[contactid] and contmethodtype = 'fax') AS Fax ORDER BY contmethodvalue,
(select top 1 contmethodvalue from contactmethods where contactid = [a].[contactid] and contmethodtype = 'Home Phone') AS HomePHONE ORDER BY contmethodvalue,
(select top 1 contmethodvalue from contactmethods where contactid = [a].[contactid] and contmethodtype = 'Cell Phone') AS Cell ORDER BY contmethodvalue,
(select top 1 contmethodvalue from contactmethods where contactid = [a].[contactid] and contmethodtype = 'Home Email') AS HomeEmail ORDER BY contmethodvalue,
(select top 1 contmethodvalue from contactmethods where contactid = [a].[contactid] and contmethodtype = 'Home Fax') AS HomeFax ORDER BY contmethodvalue,
(select top 1 contmethodvalue from contactmethods where contactid = [a].[contactid] and contmethodtype = 'Other Fax') AS OtherFax ORDER BY contmethodvalue,
(select top 1 contmethodvalue from contactmethods where contactid = [a].[contactid] and contmethodtype = 'Other Phone') AS OtherPhone ORDER BY contmethodvalue,
c.clientid, c.clientname, c.billingaddress1, c.billingcity, c.billingstate, c.billingzip
From contacts as a , clients as c
where a.clientid = c.clientid and c.groupid = 'ERER'
AND a.status IS NULL OR a.status <> 'inactive'
order by a.contactname,c.clientname

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-07-29 : 12:32:23

like this:

(select top 1 contmethodvalue from contactmethods where contactid = [a].[contactid] and contmethodtype = 'Email' ORDER BY contmethodvalue) AS Email ,

-ec

notsosuper
Posting Yak Master

190 Posts

Posted - 2005-07-29 : 12:42:27

thank you so much, what is purpose of order by? also if I want to join this query with another one table query like select clientname, clientaddress from clienttable where clientname='something', can I do that or I can't becuase it has not appear same columns in it?

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-07-29 : 13:57:42

quote:
Originally posted by notsosuper

thank you so much, what is purpose of order by? also if I want to join this query with another one table query like select clientname, clientaddress from clienttable where clientname='something', can I do that or I can't becuase it has not appear same columns in it?

you need to use the ORDER BY in conjunction with TOP to ensure that you are actually get the top item.

-ec

notsosuper
Posting Yak Master

190 Posts

Posted - 2005-07-29 : 14:35:42

if I want to add a bcp to this code above how can I do that?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-29 : 15:00:51

notsosuper, didn't we help you with that already in another thread?

EDIT: Yep, we did. Now apply your knowledge from that thread to your new query.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52899

Tara

notsosuper
Posting Yak Master

190 Posts

Posted - 2005-07-29 : 15:21:29

thank you I couldn't remember

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources