#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    3
    Rep Power
    0

    is both are same ??


    UPDATE GuestStayStatistics
    SET
    AvgLOS = ISNULL(gss.SumLOS*1.00/gss.TotalStays,0),
    AvgGuestCount = ISNULL(gss.SumGuestCount*1.00/gss.TotalStays,0),
    AvgRevPerStay = ISNULL(gss.SumTotalActualRevenue/gss.TotalStays,0),

    FROM (
    Select
    ProfileId, PropertyCode, IsActualRecord,
    sum(case
    when gss.ReservationStatus not in ('CANCELED', 'NOSHOW','WAITLIST') then Nights
    else NULL
    end) SumLOS,

    sum(case
    when gss.ReservationStatus not in ('CANCELED', 'NOSHOW','WAITLIST') then GuestCount
    else NULL
    end) SumGuestCount,

    can i use the following code in oracle for the same function

    AS

    sql_status number;
    col1 gueststaystatistics.AVGLOS%TYPE;
    col2 gueststaystatistics.AVGGUESTCOUNT%TYPE;
    col3 gueststaystatistics.AVGREVPERSTAY%TYPE;

    CURSOR c1 IS
    SELECT gstat.ROWID,
    gss.AVGLOS,gss.AVGGUESTCOUNT,gss.AVGREVPERSTAY FROM (SELECT
    profileid,
    propertycode,
    isactualrecord,
    AVG(case
    when gss.ReservationStatus not in ( 'CANCELED' , 'NOSHOW' , 'WAITLIST' ) then Nights * 1.00
    else 0
    end) AVGLOS,
    AVG(case
    when gss.ReservationStatus not in ( 'CANCELED' , 'NOSHOW' , 'WAITLIST' ) then GuestCount * 1.00
    else 0
    end) AVGGUESTCOUNT,
    AVG(case
    when gss.ReservationStatus not in ( 'CANCELED' , 'NOSHOW' , 'WAITLIST' ) then
    case
    when gss.TotalActualRevenue = 0 THEN NVL(gss.TotalRoomRevenue, 0)
    ELSE NVL(gss.TotalActualRevenue, 0)
    END
    else 0
    end) AVGREVPERSTAY,
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    5
    Rep Power
    0
    Please could you clarify what you want to achieve here. Are you trying to convert SQL server stored procedure into Oracle stored procedure? I don't understand what exactly your procedure is trying to do.

IMN logo majestic logo threadwatch logo seochat tools logo