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
 General SQL Server Forums
 New to SQL Server Programming
 Syntax errors when moving to SQL from Access

Author  Topic 

prettyjenny
Yak Posting Veteran

57 Posts
Posted - 2009-12-30 : 11:19:23
Hello,

I got the Syntax errors when moving to SQL from Access. I have not changed anything from the code since I moved from MS Access db to SQL Server db. I generates the syntax errors.
Can you help please?
Here is the query in ASP:

strSQL =" SELECT tbl_S.Sname, tbl_S.Nname, tbl_S.ST, tbl_S.Co, " & _
"('" & Request.Form("Date") & "'-tbl_S.PStart)/30 AS TMonths, " & _
"('" & Request.Form("Date") & "'-tbl_S.PStart)/365.25 AS TYears, " & _
"Count(SubQry.AID) AS Assigns, " & _
"Count(SubQry.AID)/(('" & Request.Form("Date") & "'-tbl_S.PStart)/365.25) AS TripsPerYear " & _
"FROM tbl_S LEFT JOIN ( SELECT tbl_S.Sname, tbl_S.Nname, tbl_A.SID, " & _
"tbl_A.AID, tbl_A.AEDate-tbl_A.ASDate+1 AS Days, " & _
"tbl_A.TID, tbl_P.Post FROM tbl_P INNER JOIN ((tbl_A INNER JOIN tbl_S ON " & _
"tbl_A.SID = tbl_S.SID) INNER JOIN tbl_T ON tbl_A.TID = tbl_T.TID) ON " & _
"tbl_P.PID = tbl_T.PID WHERE (tbl_P.PID<>262) AND " & _
"tbl_A.AEDate<='" & Request.Form("Date") & "' ORDER BY tbl_S.Sname; ) AS SubQry ON " & _
"tbl_S.SID = SubQry.SID " & _
"GROUP BY tbl_S.Sname, tbl_S.Nname, tbl_S.ST, tbl_S.Co, tbl_S.SID, " & _
"tbl_S.PStart, tbl_S.PEnd " & _
"HAVING (((tbl_S.ST) Like '" & strST & "') AND " & _
"((tbl_S.PStart)<='" & Request.Form("Date") & "') AND " & _
"((tbl_S.PEnd)>='" & Request.Form("Date") & "' Or (tbl_S.PEnd) Is Null) AND (('%')<>False))" & _
""& strSort &";"
Response.Write "<p>" & strSQL & "</p>"


Here are the string and errors:

SELECT tbl_S.Sname, tbl_S.Nname, tbl_S.ST, tbl_S.Co, ('12/30/2009'-tbl_S.PStart)/30 AS TMonths, 
('12/30/2009'-tbl_S.PStart)/365.25 AS TYears, Count(SubQry.AID) AS Assigns,
Count(SubQry.AID)/(('12/30/2009'-tbl_S.PStart)/365.25) AS TripsPerYear 
FROM tbl_S LEFT JOIN ( SELECT tbl_S.Sname, tbl_S.Nname, tbl_A.SID, tbl_A.AID, tbl_A.AEDate-tbl_A.ASDate+1 AS Days, tbl_A.TID, tbl_P.Post 
FROM tbl_P INNER JOIN ((tbl_A INNER JOIN tbl_S ON tbl_A.SID = tbl_S.SID) INNER JOIN tbl_T 
ON tbl_A.TID = tbl_T.TID) ON tbl_P.PID = tbl_T.PID 
WHERE (tbl_P.PID<>262) AND tbl_A.AEDate<='12/30/2009' 
ORDER BY tbl_S.Sname; ) AS SubQry ON tbl_S.SID = SubQry.SID 
GROUP BY tbl_S.Sname, tbl_S.Nname, tbl_S.ST, tbl_S.Co, tbl_S.SID, tbl_S.PStart, tbl_S.PEnd 
HAVING (((tbl_S.ST) Like '%') 
AND ((tbl_S.PStart)<='12/30/2009') 
AND ((tbl_S.PEnd)>='12/30/2009' 
Or (tbl_S.PEnd) Is Null) AND (('%')<>False))


Microsoft OLE DB Provider for SQL Server error '80040e14'
Line 1: Incorrect syntax near ';'.
ReportT.asp, line 115

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2009-12-30 : 11:29:01
First we will see the formatted code (without that ; inside).
Will this work for you?
I have doubts with the %-signs in the HAVING clause...
SELECT   tbl_s.sname, 
         tbl_s.nname, 
         tbl_s.st, 
         tbl_s.co, 
         ('12/30/2009' - tbl_s.pstart) / 30                           AS tmonths, 
         ('12/30/2009' - tbl_s.pstart) / 365.25                       AS tyears, 
         Count(subqry.aid)                                            AS assigns, 
         Count(subqry.aid) / (('12/30/2009' - tbl_s.pstart) / 365.25) AS tripsperyear 
FROM     tbl_s 
         LEFT JOIN (SELECT   tbl_s.sname, 
                             tbl_s.nname, 
                             tbl_a.sid, 
                             tbl_a.aid, 
                             tbl_a.aedate - tbl_a.asdate + 1 AS days, 
                             tbl_a.tid, 
                             tbl_p.post 
                    FROM     tbl_p 
                             INNER JOIN ((tbl_a 
                                          INNER JOIN tbl_s 
                                            ON tbl_a.sid = tbl_s.sid) 
                                         INNER JOIN tbl_t 
                                           ON tbl_a.tid = tbl_t.tid) 
                               ON tbl_p.pid = tbl_t.pid 
                    WHERE    (tbl_p.pid <> 262) 
                             AND tbl_a.aedate <= '12/30/2009' 
                    ORDER BY tbl_s.sname) AS subqry 
           ON tbl_s.sid = subqry.sid 
GROUP BY tbl_s.sname, 
         tbl_s.nname, 
         tbl_s.st, 
         tbl_s.co, 
         tbl_s.sid, 
         tbl_s.pstart, 
         tbl_s.pend 
HAVING   (((tbl_s.st) LIKE '%') 
          AND ((tbl_s.pstart) <= '12/30/2009') 
          AND ((tbl_s.pend) >= '12/30/2009' 
                OR (tbl_s.pend) IS NULL) 
          AND (('%') <> false)) 
ORDER BY tbl_s.sname, 
         tbl_s.nname


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

prettyjenny
Yak Posting Veteran

57 Posts
Posted - 2009-12-30 : 12:54:09
It does not work.

% is fine in Access. But the 'false' does not work also.

thanks.
Go to Top of Page

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts
Posted - 2009-12-30 : 12:57:04
Can you explain the meaning of these 2 conditions in your HAVING clause.(what you are trying to do)
(((tbl_s.st) LIKE '%')

and
AND (('%') <> false))
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2009-12-30 : 14:26:37
ALso, use SQL Server's date functions

('12/30/2009' - tbl_s.pstart) / 30 AS tmonths,
('12/30/2009' - tbl_s.pstart) / 365.25 AS tyears
[/code]

becomes
datediff(month,tbl_s.pstart,'12/30/2009') as tmonths
datediff(year,tbl_s.pstart,'12/30/2009') as tyears

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2009-12-30 : 14:29:10
Oops, the datediffs disappeared



('12/30/2009' - tbl_s.pstart) / 30 AS tmonths, 
('12/30/2009' - tbl_s.pstart) / 365.25 AS tyears



becomes

datediff(month,tbl_s.part,'12/30/2009') as tmonths
datediff(year,tbl_s.part,'12/30/2009') as tyears

Everyday I learn something that somebody else already knew
Go to Top of Page

prettyjenny
Yak Posting Veteran

57 Posts
Posted - 2009-12-31 : 08:51:01
(((tbl_s.st) LIKE '%')
>>This is a drop-down list (A, B, C) that user select to run a report. '%' means user select all.

and

AND (('%') <> false))
>>I didn't write this script so I still don't understand this. It works fine in MS Access database.

thanks.
Go to Top of Page

prettyjenny
Yak Posting Veteran

57 Posts
Posted - 2010-01-05 : 09:49:06
After removing order by, I got this new error:
80040e14
Invalid operator for data type. Operator equals devide, type equals datetime.

Can anyone help?

thanks.
Go to Top of Page

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts
Posted - 2010-01-05 : 10:01:51
Did you replace your lines
('12/30/2009' - tbl_s.pstart) / 30                           AS tmonths, 
('12/30/2009' - tbl_s.pstart) / 365.25                       AS tyears,

as per Jim's suggestion
datediff(month,tbl_s.part,'12/30/2009') as tmonths
datediff(year,tbl_s.part,'12/30/2009') as tyears
Go to Top of Page

prettyjenny
Yak Posting Veteran

57 Posts
Posted - 2010-01-05 : 11:06:23
Yes. I did.
I still got the same error
Invalid operator for data type. Operator equals devide, type equals datetime.

thanks.
Go to Top of Page

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts
Posted - 2010-01-05 : 11:07:10
Please post your new code.
Go to Top of Page

prettyjenny
Yak Posting Veteran

57 Posts
Posted - 2010-01-05 : 12:50:17
I removed 'order by', 'false' because they worked in Access but not in SQL server.

Here is my code:

thanks.


SELECT tbl_s.sname,
tbl_s.nname,
tbl_s.st,
tbl_s.co,
datediff(month,tbl_s.part,'12/30/2009') as tmonths,
datediff(year,tbl_s.part,'12/30/2009') as tyears,
Count(subqry.aid) AS assigns,
Count(subqry.aid) / (('12/30/2009' - tbl_s.pstart) / 365.25) AS tripsperyear
FROM tbl_s
LEFT JOIN (SELECT tbl_s.sname,
tbl_s.nname,
tbl_a.sid,
tbl_a.aid,
tbl_a.aedate - tbl_a.asdate + 1 AS days,
tbl_a.tid,
tbl_p.post
FROM tbl_p
INNER JOIN ((tbl_a
INNER JOIN tbl_s
ON tbl_a.sid = tbl_s.sid)
INNER JOIN tbl_t
ON tbl_a.tid = tbl_t.tid)
ON tbl_p.pid = tbl_t.pid
WHERE (tbl_p.pid <> 262)
AND tbl_a.aedate <= '12/30/2009'
ORDER BY tbl_s.sname) AS subqry
ON tbl_s.sid = subqry.sid
GROUP BY tbl_s.sname,
tbl_s.nname,
tbl_s.st,
tbl_s.co,
tbl_s.sid,
tbl_s.pstart,
tbl_s.pend
HAVING (((tbl_s.st) LIKE '%')
AND ((tbl_s.pstart) <= '12/30/2009')
AND ((tbl_s.pend) >= '12/30/2009'
OR (tbl_s.pend) IS NULL))
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site