1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Sybase ASE to Oracle 11g conversion

Discussion in 'SQL PL/SQL' started by Cartwright-Jepson, Aug 25, 2011.

  1. Cartwright-Jepson

    Cartwright-Jepson Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    Can anyone please look at the following Sybase ASE to Oracle 11g conversion - the top remarked code is the original Sybase version and the two UPDATE blocks below are hopefully doing the same task... What I need to know is if there is a more simple (less code change) way of doing the same task, either as a single UPDATE or preferably even more similar to the original Sybase code...

    -- /* now apply allocations */
    -- update diary_items
    -- set id_user = ia.id_user ,
    -- tx_icon_user = 'urlcats:/user_grey.gif?method:getAllocationInfo'
    -- from diary_items di,
    -- item_allocation ia
    -- where di.id_diary_item = ia.id_object
    -- and ia.id_object_type = 'W'
    -- and ia.id_user != 'autocats'
    -- OK!
    -- ??? Need to check if second UPDATE which is the same as above is required again...
    /* now apply notes */
    UPDATE diary_items
    SET diary_items.tx_icon_user =
    ( SELECT 'urlcats:/user_grey.gif?method:getAllocationInfo'
    FROM item_allocation
    WHERE item_allocation.id_object = diary_items.id_diary_item )
    WHERE EXISTS
    ( SELECT item_allocation.id_object
    FROM item_allocation
    WHERE item_allocation.id_object = diary_items.id_diary_item
    AND item_allocation.id_object_type = 'W'
    AND item_allocation.id_user != 'autocats' )
    UPDATE diary_items
    SET diary_items.id_user =
    ( SELECT item_allocation.id_user
    FROM item_allocation
    WHERE item_allocation.id_object = diary_items.id_diary_item )
    WHERE EXISTS
    ( SELECT item_allocation.id_object
    FROM item_allocation
    WHERE item_allocation.id_object = diary_items.id_diary_item
    AND item_allocation.id_object_type = 'W'
    AND item_allocation.id_user != 'autocats' )

    Many thanks!!!​
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Code (SQL):
    UPDATE diary_items
    SET (diary_items.tx_icon_user,diary_items.id_user  ) =
    ( SELECT 'urlcats:/user_grey.gif?method:getAllocationInfo', item_allocation.id_user
    FROM item_allocation
    WHERE item_allocation.id_object = diary_items.id_diary_item )
    WHERE EXISTS
    ( SELECT item_allocation.id_object
    FROM item_allocation
    WHERE item_allocation.id_object = diary_items.id_diary_item
    AND item_allocation.id_object_type = 'W'
    AND item_allocation.id_user != 'autocats' )
     
  3. Cartwright-Jepson

    Cartwright-Jepson Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Spot on - Many thanks!!!