Post reply

Problems to transcript a Oracle SQL Script

  • September 3, 2009 at 1:59 am

    #214710

    Hello together, i have problems to build a T-SQL script for MS SQL 2005, which should do the same like the following Oracle script:

    MERGE INTO unknown_ip ip1

    USING (SELECT ip_address, volume_ib, volume_ob, device_id, remark

    FROM ip_temp) ip

    ON (ip1.ip_address = ip.ip_address

    AND ip1.device_id = ip.device_id

    AND TRUNC(ip1.log_date) = TRUNC(SYSDATE))

    WHEN MATCHED THEN

    UPDATE SET ip1.volume_ib = ip1.volume_ib + ip.volume_ib,

    ip1.volume_ob = ip1.volume_ob + ip.volume_ob

    WHEN NOT MATCHED THEN

    INSERT (ip1.log_date, ip1.ip_address, ip1.volume_ib, ip1.volume_ob

    , ip1.device_id, ip1.remark)

    VALUES (TRUNC(SYSDATE), ip.ip_address, ip.volume_ib, ip.volume_ob

    , IP.device_id, ip.remark);

    The function TRUNC i have successfully build, the value SYSDATE i have replaced with GETDATE(), but i have problems to map the MERGE and CASE functions.

    Please help, Best Regards

    Franz Ladda

  • September 3, 2009 at 2:48 am

    #1048586

    MERGE is a new feature of SQL Server 2008, there's no such feature in SQL Server 2005.

    I don't see any CASE in your query: am I missing something?

    -- Gianluca Sartori

  • September 3, 2009 at 2:53 am

    #1048588

    Hello,

    i mean the WHEN MATCHED an WHEN NOT MATCHED STATEMENT s, it is logical like the CASE funktion.

    Best regards

    Franz Ladda

  • September 3, 2009 at 3:52 am

    #1048612

    Thanks for clarifying.

    I'm afraid you will have to do it the old way:

    1) update ... from src inner join dest

    2) insert into .... where not exists....

    -- Gianluca Sartori

  • September 3, 2009 at 4:05 am

    #1048622

    Sorry,

    but i have no other possibility to doe it the old way

    Best Regards

    Franz Ladda

  • September 3, 2009 at 11:27 am

    #1048946

    As a rule of thumbs there is no way of reusing PL/SQL code as Transact-SQL - provided code does something more complex than a "select column from table" or similar statement.

    The "translation" process usually involves reading and understanding what the original Oracle code does then, write it from scratch on SQL Server side.

    The same applies when going from SQL Server to Oracle - lots of fun! 😀

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • September 3, 2009 at 10:59 pm

    #1049214

    franz.ladda (9/3/2009)

    Hello together, i have problems to build a T-SQL script for MS SQL 2005, which should do the same like the following Oracle script:

    MERGE INTO unknown_ip ip1

    USING (SELECT ip_address, volume_ib, volume_ob, device_id, remark

    FROM ip_temp) ip

    ON (ip1.ip_address = ip.ip_address

    AND ip1.device_id = ip.device_id

    AND TRUNC(ip1.log_date) = TRUNC(SYSDATE))

    WHEN MATCHED THEN

    UPDATE SET ip1.volume_ib = ip1.volume_ib + ip.volume_ib,

    ip1.volume_ob = ip1.volume_ob + ip.volume_ob

    WHEN NOT MATCHED THEN

    INSERT (ip1.log_date, ip1.ip_address, ip1.volume_ib, ip1.volume_ob

    , ip1.device_id, ip1.remark)

    VALUES (TRUNC(SYSDATE), ip.ip_address, ip.volume_ib, ip.volume_ob

    , IP.device_id, ip.remark);

    The function TRUNC i have successfully build, the value SYSDATE i have replaced with GETDATE(), but i have problems to map the MERGE and CASE functions.

    Please help, Best Regards

    Franz Ladda

    SQL Server 2005 doesn't have a MERGE statement. You will need to write two separate queries. For the WHEN MATCHED portion, you will need to write an UPDATE. For the WHEN NOT Matched portion, you will need to write an insert.

    Also, putting a function on a datetime column isn't a good idea for performance... not even in Oracle.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • September 9, 2009 at 12:51 am

    #1050920

    I must remember this thread next time someone tries to sell SQL code portability to me :laugh:

    I can't help that mention that putting a function on a datetime column is fine for performance - so long the calculation also exists as a computed column, which is persisted or appropriately indexed. The SQL Server optimizer is very clever like that. Oracle provides indexes on functions directly I think...?

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • September 9, 2009 at 7:00 am

    #1051042

    Paul White (9/9/2009)Oracle provides indexes on functions directly I think...?

    You are correct once again. Oracle do supports "function based indexes".

    Last but not least, in the Oracle world applying a function to a predicate column indexed by a normal index would prevent Oracle optimizer to take advantage of such an index.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • September 9, 2009 at 7:16 am

    #1051059

    PaulB (9/9/2009)You are correct once again.

    It can't last 😀

    PaulB (9/9/2009)Last but not least, in the Oracle world applying a function to a predicate column indexed by a normal index would prevent Oracle optimizer to take advantage of such an index.

    Same in SQL Server. I just like surprising people with the 'indexed function' trick in SQL Server...

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • August 16, 2011 at 4:22 am

    #1369000

    - deleted -

  • August 16, 2011 at 7:13 am

    #1369057

    Please don't hijack a thread.

    The issue you describe is not even related to the subject discussed in the original thread.

    Ask your question using a new thread in the SSIS section.

    Or you might even want to perform an internet search before. The question you asked may already be answered somewhere. 😉


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • August 17, 2011 at 4:28 am

    #1369493

    LutzM (8/16/2011)

    Please don't hijack a thread.

    The issue you describe is not even related to the subject discussed in the original thread.

    Ask your question using a new thread in the SSIS section.

    Or you might even want to perform an internet search before. The question you asked may already be answered somewhere. 😉

    ok. sorry then ...

Viewing 13 posts - 1 through 12 (of 12 total)

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