Solved

Can not convert a mssql query to a mysql query

Posted on 2010-08-20
5
323 Views
Last Modified: 2012-05-10
Hi I am converting a asp website on a windows server to php on a linux and am having trouble converting one of the mssqls to mysql.

Here is the query as a mssql:

SELECT DISTINCT(v2_dealers.ID), name, town, County, CAST(SQRT(SQUARE(" & x & " - ISNULL(tbl_Postcodes.xCoord, 2000000)) + SQUARE(" & y & " - ISNULL(tbl_Postcodes.yCoord, 200000))) / 1000 * 0.62 AS Int) AS Dist " & _
                  "FROM v2_dealers INNER JOIN v2_dealerbrands ON v2_dealers.ID=v2_dealerbrands.dealer LEFT OUTER JOIN tbl_Postcodes ON v2_dealers.Postcode=tbl_Postcodes.PC "& _
                  "left join tbl_rvlibrary on v2_dealerbrands.make=tbl_rvlibrary.make " & _
                  "WHERE v2_dealers.status='OK' and v2_dealers.id in (select ds.dealer from v2_dealersites ds,v2_sites s where s.id=ds.site and url='"&thissite&"') " & _
                  "and tbl_rvlibrary.rvid='" & rvID & "' " & _
                  "AND (SQRT(SQUARE(" & x & "- ISNULL(tbl_Postcodes.xCoord, 2000000)) + SQUARE(" & y & " - ISNULL(tbl_Postcodes.yCoord, 200000))) / 1000 * 0.62 <=150)" & _
                  " ORDER BY Dist

And my attempt of the mysql:

SELECT DISTINCT(v2_dealers.ID), name, town, County, CAST(SQUARE('$x' IS NULL(tbl_postcodes.xCoord, 2000000)) + SQUARE('$y' IS NULL(tbl_postcodes.yCoord, 200000)) / 1000 * 0.62 AS Int) AS Dist
                  FROM v2_dealers INNER JOIN v2_dealerbrands ON v2_dealers.ID=v2_dealerbrands.dealer LEFT OUTER JOIN tbl_Postcodes ON v2_dealers.Postcode=tbl_Postcodes.PC
                  left join tbl_rvlibrary on v2_dealerbrands.make=tbl_rvlibrary.make
                  WHERE v2_dealers.status='OK' and v2_dealers.id in (select ds.dealer from v2_dealersites ds,v2_sites s where s.id=ds.site and url='$thissite')
                  and tbl_rvlibrary.rvid='$rvID'
                  AND (SQRT(SQUARE('$x' IS NULL(tbl_postcodes.xCoord, 2000000)) + SQUARE('$y' IS NULL(tbl_postcodes.yCoord, 200000))) / 1000 * 0.62 <=150)
                  ORDER BY Dist


This is the error I receive:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IS NULL(tbl_postcodes.xCoord, 2000000)) + SQUARE(792660 - IS NULL(tbl_postcodes.' at line 1

I have tried using IF NULL but still receive the same error.
0
Comment
Question by:cyberswannie
  • 3
  • 2
5 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
ID: 33483737
in MySQL, the function IsNull is not doing the same thing as in MS SQL Server.
use COALESCE instead

SELECT DISTINCT(v2_dealers.ID), name, town, County, CAST(SQUARE('$x' - COALESCE(tbl_postcodes.xCoord, 2000000)) + SQUARE('$y'   - COALESCE(tbl_postcodes.yCoord, 200000)) / 1000 * 0.62 AS Int) AS Dist
                  FROM v2_dealers INNER JOIN v2_dealerbrands ON v2_dealers.ID=v2_dealerbrands.dealer LEFT OUTER JOIN tbl_Postcodes ON v2_dealers.Postcode=tbl_Postcodes.PC
                  left join tbl_rvlibrary on v2_dealerbrands.make=tbl_rvlibrary.make
                  WHERE v2_dealers.status='OK' and v2_dealers.id in (select ds.dealer from v2_dealersites ds,v2_sites s where s.id=ds.site and url='$thissite')
                  and tbl_rvlibrary.rvid='$rvID'
                  AND (SQRT(SQUARE('$x' - COALESCE(tbl_postcodes.xCoord, 2000000)) + SQUARE('$y' - COALESCE(tbl_postcodes.yCoord, 200000))) / 1000 * 0.62 <=150)
                  ORDER BY Dist

Open in new window

0
 

Author Comment

by:cyberswannie
ID: 33483803
Thanks AngelIII, 1 for the quick reply and 2 for sorting the IS NULL problem, Unfortuneatly now I receice this error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Int) AS Dist FROM v2_dealers INNER JOIN v2_dealerbrands ON v2_dealers.ID=v2_d' at line 1

Thanks again for the help, I have never even seen the function COALESCE.
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 125 total points
ID: 33483927
it must be INTEGER and not INT:
http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html#function_cast
SELECT DISTINCT(v2_dealers.ID), name, town, County
, CAST( SQUARE('$x' - COALESCE(tbl_postcodes.xCoord, 2000000))  
        + SQUARE('$y'   - COALESCE(tbl_postcodes.yCoord, 200000)) / 1000 * 0.62 AS Integer) AS Dist

FROM v2_dealers 
INNER JOIN v2_dealerbrands ON v2_dealers.ID=v2_dealerbrands.dealer 
LEFT OUTER JOIN tbl_Postcodes ON v2_dealers.Postcode=tbl_Postcodes.PC
left join tbl_rvlibrary on v2_dealerbrands.make=tbl_rvlibrary.make

WHERE v2_dealers.status='OK' 
  and v2_dealers.id in (select ds.dealer from v2_dealersites ds,v2_sites s where s.id=ds.site and url='$thissite')
  and tbl_rvlibrary.rvid='$rvID'
  AND (SQRT(SQUARE('$x' - COALESCE(tbl_postcodes.xCoord, 2000000)) + SQUARE('$y' - COALESCE(tbl_postcodes.yCoord, 200000))) / 1000 * 0.62 <=150)
ORDER BY Dist

Open in new window

0
 

Author Closing Comment

by:cyberswannie
ID: 33483993
Quick and correct couldn't of asked for any more.
0
 

Author Comment

by:cyberswannie
ID: 33483996
Thanks Angellll
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MySQL Memory Keeps Increasing 4 35
TSQL - How to declare table name 26 30
Show Results for Latest DateTime in a View 27 25
Sql server get data from a usp to use in a usp 5 16
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question