November 2, 2005 at 5:38 am
Hi Here iam sending sample Query
My working Project suppose to work on both Oracle and Ms-Sql server but it is working fine with Oracle
in case of MS-SQL SERVER it is not working here two thing are wrong 1.DECODE function whcih is not work in MS-SQL SERVER
2.The parameters which iam passing thru frant end those comming in ''XXXXX'' double single cots why
i traced this by using PROFILER why it is taking '' xxx'' double single cots but is taking 'xxxx' in oracle
SELECT BUID, quotaarea, QuotaGroup , TimeSlotCode, ScheduleDate , sum(decode(plannedminutes,''-1'',0,plannedminutes)) AS plannedminutes, sum(plannedminutes) AS isnegative, sum(ActualMinutes) AS ActualMinutes , sum(AllocatedMinutes) AS AllocatedMinutes from QuotaByGroup WHERE BUID IN (''BANG'') AND SCHEDULEDATE >=''2005-11-02'' AND SCHEDULEDATE <=''2005-11-02'' and ((PlannedMinutes = 0 and ActualMinutes > 0) or PlannedMinutes <> 0) GROUP BY scheduledate, BUID,QuotaArea, QuotaGroup , TimeSlotCode ORDER BY scheduledate, BUID,QuotaArea, QuotaGroup , TimeSlotCode '
November 2, 2005 at 6:51 am
1. There is no DECODE in SQL Server.
2. SQL Server uses single quotes. I believe Oracle uses double quotes. So for it to work in SQL Server, you need to use single quotes. For example: SCHEDULEDATE >='2005-11-02'
-SQLBill
November 3, 2005 at 5:09 am
Use
SET QUOTED_IDENTIFIER OFF
to allow using double quotes and single quotes for string delineation in SQL Server. THis can be set at a DB level I think.
November 3, 2005 at 5:51 am
You'll need to use the SQL
CASE
or
COALESCE
to replace Oracle DECODE
November 3, 2005 at 7:22 am
or
ISNULL(NULLIF(plannedminutes,-1),0)
Far away is close at hand in the images of elsewhere.
Anon.
November 7, 2005 at 6:32 am
I'm hesitating to answer the question, bacause i'm only a MS-SQL Guru wannabe.
SQL Server 2000 should use the ANSI SQL-92 standard. As of 9i oracle should use also the ANSI SQL-92. But they both implemented it quite different. I once did some research on migrating a SQL Server database to Oracle and found the following paper: http://www.oracle.com/technology/tech/migration/ama/docs/ama_wp.pdf.
Maybe you find it usefull if you ever want to write another select on both DBMS'ses.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply