Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2015
    Posts
    1

    Unanswered: Trying to merge 2 scripts

    Hi,

    I am brand new to SQL and am trying to write a script that will recur. It looks like:

    SELECT SUM(Count(*)) AS "TOTAL"
    FROM dcspp_order
    WHERE created_by_order IS NOT NULL
    AND BETWEEN next_day( trunc(SYSDATE) - interval '14' day, 'SUN')
    AND next_day( trunc(SYSDATE) - interval '7' day, 'SAT')
    GROUP BY Trunc(submitted_date);

    I have one that works:

    SELECT SUM(Count(*)) AS "TOTAL"
    FROM dcspp_order
    WHERE created_by_order IS NOT NULL
    AND state != 'TEMPLATE'
    AND (
    Trunc(submitted_date) >= Trunc(SYSDATE-8))
    AND (
    Trunc(submitted_date) < Trunc(SYSDATE-1))
    GROUP BY trunc(submitted_date);

    I am trying to incorporate

    WHERE <date col in question> BETWEEN next_day( trunc(sysdate) - interval '14' day, 'SUN') AND next_day( trunc(sysdate) - interval '7' day, 'SAT')

    into the new script but it fails. Can anyone help me incorporate it?

    Thanks,


    Mike

    "What if....."

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by nosrekcid
    I am trying to incorporate

    WHERE <date col in question> BETWEEN next_day( trunc(sysdate) - interval '14' day, 'SUN') AND next_day( trunc(sysdate) - interval '7' day, 'SAT')

    into the new script but it fails.
    Does it? It could help if you described how it failed. ORA-xxxxx error code would be nice, test case along with copy/paste of your SQL*Plus session would be even better.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    How will you or I know when correct SL is posted here?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    do not use interval. It is for the timestamp

    WHERE <date col in question> BETWEEN next_day( trunc(sysdate - 14), 'SUN') AND next_day( trunc(sysdate - 7), 'SAT')
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >do not use interval. It is for the timestamp

    no, not really

    http://docs.oracle.com/database/121/...htm#SQLRF30020

    DATE1 - DATE2 = INTERVAL -- both DATE1 & DATE2 are DATE datatypes.

    when 1 DATE is subtracted from another DATE the result is an INTERVAL datatype
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    May 2014
    Location
    World Wide On The Web
    Posts
    16

    Thumbs up

    Quote Originally Posted by anacedent View Post
    >do not use interval. It is for the timestamp

    no, not really

    http://docs.oracle.com/database/121/...htm#SQLRF30020

    DATE1 - DATE2 = INTERVAL -- both DATE1 & DATE2 are DATE datatypes.

    when 1 DATE is subtracted from another DATE the result is an INTERVAL datatype
    Good point. I have seen folks thinking the subtraction of two DATES is NUMBER, while it is an INTERVAL. http://stackoverflow.com/questions/9...erval-datatype
    Regards,
    Lalit

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    it does not return an interval, it returns a float which can be easily converted to an interval.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    May 2014
    Location
    World Wide On The Web
    Posts
    16
    Quote Originally Posted by beilstwh View Post
    it does not return an interval, it returns a float which can be easily converted to an interval.
    Code:
    SQL> select dump(0.8 - 0.4) from dual;
    
    DUMP(0.8-0.4)
    -------------------
    Typ=2 Len=2: 192,41
    
    SQL>
    SQL> select dump(date '2014-01-09' - date '2014-01-04') from dual;
    
    DUMP(DATE'2014-01-09'-DATE'20
    -----------------------------
    Typ=14 Len=8: 5,0,0,0,0,0,0,0
    
    SQL>
    Regards,
    Lalit

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by beilstwh View Post
    it does not return an interval, it returns a float which can be easily converted to an interval.
    please post reproducible proof that your assertion above is true.

    Code:
      1  select dump(to_number('10.1')-to_number('5.1')) contents,
      2  (to_number('10.1')-to_number('5.1')) num_val,
      3  to_char((to_number('10.1')-to_number('5.1'))) string ,
      4  dump(to_char((to_number('10.1')-to_number('5.1')))) dmp_str,
      5  dump(trunc(sysdate)-trunc(sysdate-5)) date_dmp
      6* from dual
    SQL> /
    
    CONTENTS              NUM_VAL S DMP_STR
    ------------------ ---------- - ---------------
    DATE_DMP
    --------------------------------------------------------------------------------
    Typ=2 Len=2: 193,6          5 5 Typ=1 Len=1: 53
    Typ=14 Len=8: 5,0,0,0,0,0,0,0
    Last edited by anacedent; 01-09-15 at 12:15. Reason: provided additional details
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    test>select dump(to_date('01012015','mmddyyyy') - sysdate) from dual;

    DUMP(TO_DATE('01012015','MMDDYYYY')-SYSDATE)
    --------------------------------------------------------------------------------
    Typ=14 Len=8: 255,255,255,248,255,255,101,250


    test>SELECT * FROM DBA_TYPES WHERE TYPE_OID = '0000000000000000000000000000000E';

    OWNER TYPE_NAME
    ------------------------------ ------------------------------
    TYPE_OID TYPECODE ATTRIBUTES
    -------------------------------- ------------------------------ ----------
    METHODS PRE INC
    ---------- --- ---
    FLOAT
    0000000000000000000000000000000E FLOAT 0
    0 YES NO
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    FLOAT has typ=2.
    so DATE1-DATE2 does NOT result in FLOAT datatype!

    http://docs.oracle.com/cd/B28359_01/...htm#SQLRF50950
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Type 2 is float. Type 14 is also float, just undocumented.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •