Post reply

upgrading from Access to TSQL

  • 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!

  • December 29, 2003 at 9:30 am

    #487992

    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://qa.sqlservercentral.com/columnists/sjones

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

    http://www.dkranch.net

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • December 29, 2003 at 9:36 am

    #487994

    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

  • December 29, 2003 at 11:49 am

    #488028

    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

  • December 29, 2003 at 11:51 am

    #488029

    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.

  • December 30, 2003 at 7:29 am

    #488174

    For SHDate:

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

    Russel Loski

    Russel Loski

    Russel Loski, MCSE Business Intelligence, Data Platform

  • December 30, 2003 at 7:59 am

    #488181

    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

    Norm Johnson

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

  • December 30, 2003 at 9:41 am

    #488212

    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

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

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