i needed urgent help from MS-SQL GURUS

  • 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 '

  • 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

  • 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.

  • You'll need to use the SQL

    CASE

    or

    COALESCE

    to replace Oracle DECODE

  • or

    ISNULL(NULLIF(plannedminutes,-1),0)

     

    Far away is close at hand in the images of elsewhere.
    Anon.

  • 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.


    Kindest Regards,

    Gimli

    If you are a spineless creature, they can't break you

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply