Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Union order
Message
 
 
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Title:
Environment versions
SQL Server:
SQL Server 2000
Miscellaneous
Thread ID:
01343299
Message ID:
01343381
Views:
21
Try to get the error using AERROR() I don't see what may be wrong from the first quick glance.

>Hate to bother, but the Query works in Query Analyzer but VFP doesn't seem to do anything with this code. No query is generated in VFP(9sp2)
m.nConnectionHandle = SQLCONNECT('TimeClockPlus')
>m.cJobNo = '11708'
>
>TEXT TO lcCmd NOSHOW PRETEXT 15
>
>	SELECT TimeHD.Empid, TimeHD.EmpName,
>		sum(regtime) as RegTime,sum(overtime) as OverTime, Sum(doubletime) as DoubleTime,
>		sum(regtime+overtime+doubletime) as TotalHours1,
>		'0' as Dist
>	FROM TimeHd INNER JOIN
>		TimeItms ON TimeHd.timecrdno = TimeItms.timecrdno
>	Where TimeItms.JobNo = ?cJobNo and
>		TimeItms.[distributed] <> 1
>
>	group by TimeHD.Empid, TimeHD.EmpName
>
>	UNION ALL
>
>	SELECT TimeHD.Empid, TimeHD.EmpName,
>		sum(regtime) as RegTime,sum(overtime) as OverTime, Sum(doubletime) as DoubleTime,
>		sum(regtime+overtime+doubletime) as TotalHours1,
>		'1' as Dist
>	FROM TimeHd INNER JOIN
>		TimeItms ON TimeHd.timecrdno = TimeItms.timecrdno
>	Where TimeItms.JobNo = ?cJobNo and
>		TimeItms.[distributed] = 1
>
>	group by TimeHD.Empid, TimeHD.EmpName
>
>	order by 1,6
>
>ENDTEXT
>
>=SQLEXEC(m.nConnectionHandle,lcCmd,'QueryName')
>
>*BROWSE LAST NOWAIT
>
>=SQLDISCONNECT(m.nConnectionHandle)
>
>>>I've got something wrong as I get "The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified."
>>>
>>>The extra field doesn't bother me (yet anyway) so for now...I think...I like the field :)
>>>
>>
>>The ORDER BY should be at the very end of your code, e.g.
>>
>>select * from (our actual select statement) derived ORDER BY 1,6
>>
>>But keep the field and be happy :)
If it's not broken, fix it until it is.


My Blog
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform