CODEWITHSUNDEEP
sql-server
Walhalla
Walhalla

Reputation: 396

IS NOT NULL AS ... - Convert Query from MySQL to MS-SQL

I have an application that is working fine using a MySQL/MariaDB-Database.
I did make it more flexible and now I am basically able to use a Microsoft SQL-Server database.

I found out, that some SQL-queries do NOT work anymore.
I don't have experience with MS-SQL and I am looking for support to convert the following query to make it work with MS-SQL. It would be great, if the query could be converted to work in both MS-SQL and MySQL ...

I have created an SQL-Fiddle with some example-data.
Link: http://sqlfiddle.com/#!18/5fb718/2

The Query itself looks like this:

SELECT computermapping.PrinterGUID, computerdefaultprinter.PrinterGUID IS NOT NULL AS isDefaultPrinter
FROM computermapping 
LEFT JOIN computerdefaultprinter ON computerdefaultprinter.ComputerGUID = computermapping.ComputerGUID 
AND computerdefaultprinter.PrinterGUID = computermapping.PrinterGUID
WHERE computermapping.ComputerGUID = "5bec3779-b002-46ba-97c4-19158c13001f"

When I run this on SQL-Fiddle I get the following error:

Incorrect syntax near the keyword 'IS'.

When I run this Query in Microsoft SQL Server Management Studio I get the same Error. I have an German-Installation ...

Meldung 156, Ebene 15, Status 1, Zeile 1
Falsche Syntax in der Nähe des IS-Schlüsselworts.

I was looking on the Internet to find information on how to use the IS NOT NULL AS in MS-SQL. Maybe I was using the wrong keywords, but I was not able to find a solution myself.

If it does matter, I am using "SQL-Server 2014 SP3" at the moment.

Thank you

Upvotes: 0

Views: 101

Answers (3)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131492

The exact question was already answered, but you should really, really try using the SQL Server Migration Assistant for MySQL. No database supports the SQL standard beyond a basic compatibility level and MySQL is one of the quirkiest databases when it comes to SQL compatibility.

The SQL standard process is sloooow so all vendors implement features long before they're standardized. Different databases have different priorities too, and MySQL's priority for the first decade at least wasn't enterprise applications, so SQL compatibility wasn't a high priority.

Some common features were added only in MySQL 8. Some hacks allowed (but discouraged) in MySQL, like non-aggregate columns in a grouping query, or quirky updates to calculate row numbers, don't work in any other databases because logically, they lead to unpredictable results.

Even in MySQL, non-aggregate columns can cause serious performance degradation when upgrading from one 5.x version to the next. There's at least one SO question about this.

The Migration assistant will convert tables and types where possible and flag any stored procedure or view queries that can't be translated. It can also copy data from an existing MySQL database to a SQL Server database

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

Convert

computerdefaultprinter.PrinterGUID IS NOT NULL AS isDefaultPrinter

to

CASE 
   WHEN computerdefaultprinter.PrinterGUID IS NOT NULL THEN 1 
   ELSE 0 
END AS isDefaultPrinter

Demo here

Also bear in mind that there is no BOOLEAN type in SQL Server. BIT type is used instead.

Finally

WHERE computermapping.ComputerGUID = "5bec3779-b002-46ba-97c4-19158c13001f"

should be converted to

WHERE computermapping.ComputerGUID = '5bec3779-b002-46ba-97c4-19158c13001f'

since the single quote character is used to delimit strings in SQL Server

Upvotes: 3

forpas
forpas

Reputation: 164099

MySql evaluates boolean expressions like:

computerdefaultprinter.PrinterGUID IS NOT NULL

as 1 for TRUE or 0 for FALSE.
SQL Server does not do such an evaluation, so you need a CASE expression:

CASE WHEN computerdefaultprinter.PrinterGUID IS NOT NULL THEN 1 ELSE 0 END

Upvotes: 2

Related Questions