I have changed a database from SQL to MySQL and now my usual query
doesn't work, can anyone help with how to convert it.

Thanks

Gordon

Query

SELECT 1 as Tag, NULL as Parent,
NULL as [vehiclestate.2.Alias.element],
NULL as [vehiclestate.2.Day.element],
NULL as [vehiclestate.2.Month.element],
NULL as [vehiclestate.2.Year.element],
NULL as [vehiclestate.2.Longitude.element],
NULL as [vehiclestate!2!Latitude!element],
NULL as [vehiclestate!2!Street!element],
NULL as [vehiclestate!2!City!element],
NULL as [vehiclestate!2!Zip_Code!element],
NULL as [vehiclestate!2!Speed!element],
NULL as [vehiclestate!2!PC_Time!element],
NULL as [vehiclestate!1!PC_Date!element]
>From vehiclestate
INNER JOIN (
SELECT 2 as Tag, 1 as Parent,
Alias, Max(Date_Time) As Maxreport
From vehiclestate
Group By Alias
) As A
On vehiclestate.Alias = A.Alias
And vehiclestate.Date_Time = A.Maxreport
UNION
SELECT 2 as Tag, 1 as Parent,
vehiclestate.Alias as [vehiclestate!2!Alias!element],
Day as [vehiclestate!2!Day!element],
Month as [vehiclestate!2!Month!element],
Year as [vehiclestate!2!Year!element],
Longitude as [vehiclestate!2!Longitude!element],
Latitude as [vehiclestate!2!Latitude!element],
Street as [vehiclestate!2!Street!element],
City as [vehiclestate!2!City!element],
Zip_Code as [vehiclestate!2!Zip_Code!element],
Speed as [vehiclestate!2!Speed!element],
PC_Time as [vehiclestate!2!PC_Time!element],
PC_Date as [vehiclestate!2!PC_Date!element]
from vehiclestate
INNER JOIN (
SELECT 2 as Tag, 1 as Parent,
Alias, Max(Date_Time) As Maxreport
From vehiclestate
WHERE (PC_Date >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0))
AND (PC_Date < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 1))
Group By Alias
) As A
On vehiclestate.Alias = A.Alias
And vehiclestate.Date_Time = A.Maxreport

Gordon Muir wrote:
> I have changed a database from SQL to MySQL and now my usual query
> doesn't work, can anyone help with how to convert it.
>
> Thanks
>
> Gordon
>
> Query
>
> SELECT 1 as Tag, NULL as Parent,
> NULL as [vehiclestate.2.Alias.element],
> NULL as [vehiclestate.2.Day.element],
> NULL as [vehiclestate.2.Month.element],
> NULL as [vehiclestate.2.Year.element],
> NULL as [vehiclestate.2.Longitude.element],
> NULL as [vehiclestate!2!Latitude!element],
> NULL as [vehiclestate!2!Street!element],
> NULL as [vehiclestate!2!City!element],
> NULL as [vehiclestate!2!Zip_Code!element],
> NULL as [vehiclestate!2!Speed!element],
> NULL as [vehiclestate!2!PC_Time!element],
> NULL as [vehiclestate!1!PC_Date!element]
>>From vehiclestate
> INNER JOIN (
> SELECT 2 as Tag, 1 as Parent,
> Alias, Max(Date_Time) As Maxreport
> From vehiclestate
> Group By Alias
> ) As A
> On vehiclestate.Alias = A.Alias
> And vehiclestate.Date_Time = A.Maxreport
> UNION
> SELECT 2 as Tag, 1 as Parent,
> vehiclestate.Alias as [vehiclestate!2!Alias!element],
> Day as [vehiclestate!2!Day!element],
> Month as [vehiclestate!2!Month!element],
> Year as [vehiclestate!2!Year!element],
> Longitude as [vehiclestate!2!Longitude!element],
> Latitude as [vehiclestate!2!Latitude!element],
> Street as [vehiclestate!2!Street!element],
> City as [vehiclestate!2!City!element],
> Zip_Code as [vehiclestate!2!Zip_Code!element],
> Speed as [vehiclestate!2!Speed!element],
> PC_Time as [vehiclestate!2!PC_Time!element],
> PC_Date as [vehiclestate!2!PC_Date!element]
> from vehiclestate
> INNER JOIN (
> SELECT 2 as Tag, 1 as Parent,
> Alias, Max(Date_Time) As Maxreport
> From vehiclestate
> WHERE (PC_Date >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0))
> AND (PC_Date < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 1))
> Group By Alias
> ) As A
> On vehiclestate.Alias = A.Alias
> And vehiclestate.Date_Time = A.Maxreport
>

There are several methods one described in the docs...
http://dev.mysql.com/doc/refman/5.0/...functions.html
Look at "case" and IFNULL.
Case is more ansi standard and will allow you to move this to almost
any other database engine - like oracle.


Michael.
email address is bogus.