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!

Question about "Partition By" clause in Oracle sql

Discussion in 'SQL PL/SQL' started by Farshad Javadi, Feb 6, 2013.

  1. Farshad Javadi

    Farshad Javadi Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    Hello and Greetings,

    I have a question about "Partition By" clause in Oracle sql. In my query below when I use the following line:

    and ABX_SCHEDULEPROFILE.ABCONTACTID IN ('265315', '692896') it works percfectly, but when I use the following line instead:

    In ( Select CONTACTCENTER.ABX_SCHEDULEPROFILE.ABCONTACTID From CONTACTCENTER.ABX_SCHEDULEPROFILE )

    I get "ORA-01722: invalid number" Error.

    Here is my query:


    Thanks Very Much,
    Farshad

    --Query : ScheduleableProfile
    select
    abtl_ext_settingtype.TYPECODE,
    ABX_SCHEDULEPROFILE.EXT_SETTINGDATA,
    Temp.CapoverTime,
    Temp.CapoverNum,
    Temp.CapoverNumber,
    Temp.CapoverSum,
    ABX_SCHEDULEPROFILE.EXT_DAYOFWEEK,
    ABX_SCHEDULEPROFILE.ABCONTACTID,
    Temp.Appointment,
    Temp.Openstat,
    Temp.Closetime,
    Temp.Starttime,
    Temp.MinutesOpen,
    Round ( To_number(Temp.slot)- To_Number(Temp.CapoverNumber) + (To_Number(Temp.CapoverNumber) * (To_Number(Temp.Cap)) ) ) As Slot,
    ABX_SCHEDULEPROFILE.EXT_EFFDATE,
    ABX_SCHEDULEPROFILE.EXT_TERMEDDATE
    from
    CONTACTCENTER.ABTL_EXT_SETTINGTYPE abtl_ext_settingtype,
    CONTACTCENTER.ABX_SCHEDULEPROFILE ABX_SCHEDULEPROFILE,
    (
    Select
    abtl_ext_settingtype.TYPECODE As Z1,
    ABX_SCHEDULEPROFILE.EXT_SETTINGDATA As Z2,
    ABX_SCHEDULEPROFILE.EXT_DAYOFWEEK As Z3,
    ABX_SCHEDULEPROFILE.ABCONTACTID As Z4,
    ABX_SCHEDULEPROFILE.EXT_EFFDATE As Z5,
    ABX_SCHEDULEPROFILE.EXT_TERMEDDATE As Z6,
    Temp2.Appointment,
    Temp2.Openstat,
    Temp2.Cap,
    SUM(Decode(abtl_ext_settingtype.TYPECODE, 'capovr', ( Case When ( (To_Number(Substr(ABX_SCHEDULEPROFILE.EXT_SETTINGDATA,1,4)) Between (temp2.Starttime) And (Temp2.Closetime) ) And To_Number(Substr(ABX_SCHEDULEPROFILE.EXT_SETTINGDATA,6,2),0)!=0 And (Temp2.Openstat='true' )) Then (1) Else 0 End ), 0))Over (Partition By ABX_SCHEDULEPROFILE.ABCONTACTID) CapoverNumber,
    SUM(Decode(abtl_ext_settingtype.TYPECODE, 'capovr', ( Case When ( To_Number(Substr(ABX_SCHEDULEPROFILE.EXT_SETTINGDATA,1,4)) Between (temp2.Starttime) And (Temp2.Closetime) And (Temp2.Openstat='true' ) ) Then (To_Number(Substr(ABX_SCHEDULEPROFILE.EXT_SETTINGDATA,6,2),0)) Else 0 End ), 0))Over (Partition By ABX_SCHEDULEPROFILE.ABCONTACTID) CapoverSum,
    Temp2.CapoverTime,
    Temp2.CapoverNum,
    Temp2.Closetime,
    Temp2.Starttime,
    Temp2.MinutesOpen,
    Case When (Temp2.Openstat='true') Then Temp2.Slot Else 0 End As Slot
    From
    CONTACTCENTER.ABTL_EXT_SETTINGTYPE abtl_ext_settingtype,
    CONTACTCENTER.ABX_SCHEDULEPROFILE ABX_SCHEDULEPROFILE,
    (
    Select
    abtl_ext_settingtype.TYPECODE As Z1,
    ABX_SCHEDULEPROFILE.EXT_SETTINGDATA As Z2,
    ABX_SCHEDULEPROFILE.EXT_DAYOFWEEK As Z3,
    ABX_SCHEDULEPROFILE.ABCONTACTID As Z4,
    ABX_SCHEDULEPROFILE.EXT_EFFDATE As Z5,
    ABX_SCHEDULEPROFILE.EXT_TERMEDDATE As Z6,
    Substr(ABX_SCHEDULEPROFILE.EXT_SETTINGDATA,1,4) As CapoverTime,
    NVL(Substr(ABX_SCHEDULEPROFILE.EXT_SETTINGDATA,6,2),0) As CapoverNum,
    Sum( Decode(abtl_ext_settingtype.TYPECODE,'slotsize', ABX_SCHEDULEPROFILE.EXT_SETTINGDATA)) Over (Partition By ABX_SCHEDULEPROFILE.ABCONTACTID) Appointment,
    Max( Decode(abtl_ext_settingtype.TYPECODE, 'openstat', Substr(ABX_SCHEDULEPROFILE.EXT_SETTINGDATA,0,4), 0)) Over (Partition By ABX_SCHEDULEPROFILE.ABCONTACTID) Openstat,
    Count(case When abtl_ext_settingtype.TYPECODE='capovr' Then 1 End) Over (Partition By ABX_SCHEDULEPROFILE.ABCONTACTID) CapoverNumber,
    Sum(NVL(Substr(ABX_SCHEDULEPROFILE.EXT_SETTINGDATA,6,2),0)) Over (Partition By ABX_SCHEDULEPROFILE.ABCONTACTID) CapoverSum,
    Sum( Decode(abtl_ext_settingtype.TYPECODE, 'closetime', Substr(ABX_SCHEDULEPROFILE.EXT_SETTINGDATA,0,4), 0)) Over (Partition By ABX_SCHEDULEPROFILE.ABCONTACTID) Closetime,
    Sum( Decode(abtl_ext_settingtype.TYPECODE, 'starttime', Substr(ABX_SCHEDULEPROFILE.EXT_SETTINGDATA,0,4), 0)) Over (Partition By ABX_SCHEDULEPROFILE.ABCONTACTID) Starttime,
    Sum( Decode(abtl_ext_settingtype.TYPECODE, 'cap', Substr(ABX_SCHEDULEPROFILE.EXT_SETTINGDATA,0,4), 0)) Over (Partition By ABX_SCHEDULEPROFILE.ABCONTACTID) Cap,
    Round(Sum( Decode(abtl_ext_settingtype.TYPECODE,'closetime', To_Number(Substr(ABX_SCHEDULEPROFILE.EXT_SETTINGDATA,1,2))*60+To_Number(Substr(ABX_SCHEDULEPROFILE.EXT_SETTINGDATA,3,2)), 'starttime', -To_Number(Substr(ABX_SCHEDULEPROFILE.EXT_SETTINGDATA,1,2))*60-To_Number(Substr(ABX_SCHEDULEPROFILE.EXT_SETTINGDATA,3,2) ))) Over (Partition By ABX_SCHEDULEPROFILE.ABCONTACTID) ) MinutesOpen,
    Round(Sum( Decode(abtl_ext_settingtype.TYPECODE,'closetime', To_Number(Substr(ABX_SCHEDULEPROFILE.EXT_SETTINGDATA,1,2))*60+To_Number(Substr(ABX_SCHEDULEPROFILE.EXT_SETTINGDATA,3,2)), 'starttime', -To_Number(Substr(ABX_SCHEDULEPROFILE.EXT_SETTINGDATA,1,2))*60-To_Number(Substr(ABX_SCHEDULEPROFILE.EXT_SETTINGDATA,3,2) ))) Over (Partition By ABX_SCHEDULEPROFILE.ABCONTACTID) ) / (Sum( Decode(abtl_ext_settingtype.TYPECODE,'slotsize', ABX_SCHEDULEPROFILE.EXT_SETTINGDATA)) Over (Partition By ABX_SCHEDULEPROFILE.ABCONTACTID)) As Slot
    From
    CONTACTCENTER.ABTL_EXT_SETTINGTYPE abtl_ext_settingtype,
    CONTACTCENTER.ABX_SCHEDULEPROFILE ABX_SCHEDULEPROFILE
    Where ABX_SCHEDULEPROFILE.EXT_TERMEDDATE >= Sysdate --$(SlotDay) + 1
    And ABX_SCHEDULEPROFILE.EXT_EFFDATE < Sysdate --$(SlotDay) + 1
    And ABX_SCHEDULEPROFILE.EXT_DAYOFWEEK = To_Char(sysdate,'D') --$(DayofWeek)
    And ABX_SCHEDULEPROFILE.RETIRED = 0
    And ABX_SCHEDULEPROFILE.EXT_SETTINGTYPE = abtl_ext_settingtype.ID
    )Temp2
    Where ABX_SCHEDULEPROFILE.EXT_TERMEDDATE >= Sysdate --$(SlotDay) + 1
    And ABX_SCHEDULEPROFILE.EXT_EFFDATE < Sysdate --$(SlotDay) + 1
    And ABX_SCHEDULEPROFILE.EXT_DAYOFWEEK = To_Char(sysdate,'D') --$(DayofWeek)
    And ABX_SCHEDULEPROFILE.RETIRED = 0
    And ABX_SCHEDULEPROFILE.EXT_SETTINGTYPE = abtl_ext_settingtype.ID
    And ABX_SCHEDULEPROFILE.EXT_SETTINGDATA=Temp2.Z2
    And ABX_SCHEDULEPROFILE.EXT_DAYOFWEEK=Temp2.Z3
    And ABX_SCHEDULEPROFILE.ABCONTACTID=Temp2.Z4
    And ABX_SCHEDULEPROFILE.EXT_EFFDATE=Temp2.Z5
    And ABX_SCHEDULEPROFILE.EXT_TERMEDDATE=Temp2.Z6
    )Temp
    where ABX_SCHEDULEPROFILE.EXT_TERMEDDATE >= Sysdate --$(SlotDay) + 1
    and ABX_SCHEDULEPROFILE.EXT_EFFDATE < Sysdate --$(SlotDay) + 1
    --and ABX_SCHEDULEPROFILE.ABCONTACTID = $(ContactID)
    and ABX_SCHEDULEPROFILE.ABCONTACTID = '265315' --Or 265315 351488 445857 692896
    and ABX_SCHEDULEPROFILE.EXT_DAYOFWEEK = To_Char(sysdate,'D') --$(DayofWeek)
    and ABX_SCHEDULEPROFILE.RETIRED = 0
    and ABX_SCHEDULEPROFILE.EXT_SETTINGTYPE = abtl_ext_settingtype.ID
    And abtl_ext_settingtype.TYPECODE=Temp.Z1
    And ABX_SCHEDULEPROFILE.EXT_SETTINGDATA=Temp.Z2
    And ABX_SCHEDULEPROFILE.EXT_DAYOFWEEK=Temp.Z3
    And ABX_SCHEDULEPROFILE.ABCONTACTID=Temp.Z4
    And ABX_SCHEDULEPROFILE.EXT_EFFDATE=Temp.Z5
    And ABX_SCHEDULEPROFILE.EXT_TERMEDDATE=Temp.Z6
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Obviously CONTACTCENTER.ABX_SCHEDULEPROFILE.ABCONTACTID is defined as a varchar2 column and it contains non-numeric data. You need to verify that all values in
    CONTACTCENTER.ABX_SCHEDULEPROFILE.ABCONTACTID are valid for the to_number() function else you get the error you're seeing. This has nothing to do with the Partition by clause.
     
  3. Farshad Javadi

    Farshad Javadi Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    Thanks Very Much Sir,

    Farshad Javadi