upgrading from Access to TSQL

Question

Post reply

upgrading from Access to TSQL

poger67

SSC Enthusiast

Points: 146
More actions
December 29, 2003 at 9:24 am

#85205

I'm converting some online Access based databases to MSSQL2000 server and have hit a few snags in some of my existing queries.
The first one has to do with a nested query that's bombing for some reason:
<cfquery name="SongCountOpener" datasource="#Session.DSN#" maxrows="10">
select * from (
select so_title,count(*) as cnt
from songs,setlist
where so_soid=sl_soid and
sl_trueseq = 1 and
so_soid <> 139 and
so_soid <> 31 and
so_soid <> 26 and
so_soid <> 142 and
so_soid <> 140
group by so_title)
where cnt > 1
order by cnt desc
</cfquery>
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'where'.
The 2nd problem is using format to format a date:
<cfquery name="SelSetlists" datasource="#Session.DSN#">
select *,format(sh_date,'MM/DD/YYYY') as shdate
from setlist,shows,songs,venues
where datepart('YYYY',sh_date) = '#yeid#' and
(sh_shid = sl_shid) and
(sl_soid = so_soid) and
(sh_veid = ve_veid)
order by sh_date, sl_set, sl_seq
</cfquery>
What's the proper way in tsql to format a date in the query - I read tons of online forums this weekend and couldn't find any that would work.
I know this is probably basic stuff, but we all gotta start somewhere!

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

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

Steve Jones - SSC Editor SSC Guru Points: 736458 More actions · Answer 1

for the first one, you need a table alias, so

select a.* from (select....) a

where a.cnt > 1

For the 2nd, look up CAST in BOL.

Steve Jones

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/

http://www.dkranch.net

noeld SSC Guru Points: 96590 More actions · Answer 2

The First is that instead of

where cnt > 1

Should be deleted and after "group by"

just write having count(*)>1

The Second is treaky but, why are you selecting * and the a field that is supposed to be taken from the select statement ?

And by the way Your query is formated in ACCESS SQL not in TSQL

Dates in TSQL are not surrounded by '#'

HTH

* Noel

poger67 SSC Enthusiast Points: 146 More actions · Answer 3

Thanks Steve & noeld.

Steve - I'll add the table aliases and check out CAST (do you have a good URL for that handy?)

Noeld - yes - the app is running now fine in Access, and I will keep this one there. I'm just trying to convert it into tsql to see what changes I'd need to make in future projects.

Steve

poger67 SSC Enthusiast Points: 146 More actions · Answer 4

quote:
And by the way Your query is formated in ACCESS SQL not in TSQL
Dates in TSQL are not surrounded by '#'

The # on that yeid is for Cold Fusion - but yes, it is in Access now.

Russel Loski SSCertifiable Points: 7059 More actions · Answer 5

For SHDate:

Convert (varchar(10), sh_date,101) as shdate

Russel Loski

Russel Loski, MCSE Business Intelligence, Data Platform

N.Johnson SSC Veteran Points: 271 More actions · Answer 6

Dear poger67, in the first reply from Steve Jones, he referred to BOL. For a long time I wondered what BOL was until I asked one of my fellow programmers and, lo and behold, BOL is Books Online ... which is the HELP in SQL Query Analyzer and Enterprise Manager (it's really on your computer ... not necessarily OnLine on the internet).

If I've given you any untruths, I'm sure the veteran SQL folks will point us newbies in the right direction.

NJJ

Norm Johnson

"Keep smiling ... it gives your face something happy to do
... and it makes people wonder what you're up to!"

GDG_DBA SSC Journeyman Points: 82 More actions · Answer 7

For more current ANSI syntax, you might try rewriting the first query as follows:

SELECT so_title, count(*) as cnt

--Notice the new join syntax below

FROM songs JOIN setlist ON so_soid = sl_soid

--This leaves the WHERE clause to simply logically filter the results.

--The nice thing about this (once you get used to it) is that it is simple to read.

--Join logic and filter logic are separated.

WHERE sl_trueseq = 1 AND

so_soid <> 139 AND

so_soid <> 31 AND

so_soid <> 26 AND

so_soid <> 142 AND

so_soid <> 140

GROUP BY so_title

HAVING count(*) > 1

ORDER BY cnt DESC

This query also eliminates the need to select * from your inner result set. This is done by using the HAVING clause which works as a filter on the agregate results.

For date formatting try the convert function.

Per BOL…

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Yours might look like…

Convert(char(10), sh_date, 101)

-Greg