Changing Query from Oracle to SQL server

Transact-SQL
1

The SQL query needed to change from for Oracle to for SQL Server is as
below:

SELECT FACILITY_ID, ACTIVITY, DVIAN, ADDRESS, CITY, STATE_ID, ZIP_CODE,
COM_PHONE, DSN_PHONE, DATE_CREAT, DATE_MOD, NVL(COUNTRY_ID, ' ') as
COUNTRY_ID FROM FACILITY_INFO WHERE upper(CITY) like 'Eastover' and STATE_ID
like 'SC ' and ZIP_CODE like '29044-5015' and COUNTRY_ID like 'US' order
by STATE_ID, ACTIVITY;

Thanks for the help

2

Try this:

Blockquote

SELECT
FACILITY_ID,
ACTIVITY,
DVIAN, ADDRESS, CITY, STATE_ID, ZIP_CODE,
COM_PHONE, DSN_PHONE, DATE_CREAT, DATE_MOD,
--NVL(COUNTRY_ID, ' ') as COUNTRY_ID
ISNULL((COUNTRY_ID, ' ') as COUNTRY_ID
FROM FACILITY_INFO WHERE upper(CITY) like 'Eastover' and STATE_ID
like 'SC ' and ZIP_CODE like '29044-5015' and COUNTRY_ID like 'US'
order by STATE_ID, ACTIVITY;

3

That worked...thank you very much!

4

Great YW.

5

I imagine this is working because the database is case-INsensitive.

In which case get rid of the UPPER() function, it will stop the query being SARGable.

I am guessing that that test actually uses a parameter, rather than the string given, but if not then LIKE is not needed here (and will be much inefficient than = ). Same for the other LIKE tests

6

Correct but I thought he needs it in the statement since I dont know see the remaining of the code.
Pasi.

7

Sorry, meant to reply to the O/P code rather than yours, it was clearly the Oracle NVL() function that was causing the migration problem which you fixed :slight_smile:

1 Like
8

Still need to get rid of all the LIKEs on this one. They don't hurt as written except to confuse someone during troubleshooting.

1 Like