Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2009
    Posts
    38

    Unanswered: divide and multiply

    can i ask something before giving the tables ? (not that i dont want to but maybe it isn't necessary.)

    i have these commands

    SELECT DISTINCT ((SELECT SUM (m.p_s)
    FROM m JOIN work
    on work.code_m=m.code_m AND work.code_p=m.code_p AND work.code_ep=m.code_ep)) /

    ((SELECT COUNT (m.p_s)
    FROM m JOIN work
    on work.code_m=m.code_m AND work.code_p=m.code_p AND work.code_ep=m.code_ep))FROM work *

    ((SELECT SUM (DISTINCT ep.total)
    FROM ep
    JOIN dp
    ON dp.code_d=ep.code_d AND dp.code_e=ep.code_e
    AND dp.date BETWEEN '01/APR/2008' AND '01/JUL/2008' ))FROM ep

    The division works properly. The 3rd command works properly when it's on its own. But when trying all three together (divide and multiply) it says ORA-00933: SQL command not properly ended. Has anyone some idea whats wrong with it?

    I can put on sample data but i figured that maybe its something very simple that i can' t find out.


    and something else. I have table with date (01/02/2008) but when trying to run an sql command with date if i put 01/02/2008 it says EXPECTED DATE FOUND NUMBER. it only reads 01/feb/2008. how can i make it read 01/02/2008 ??

    thank you.

  2. #2
    Join Date
    Jan 2009
    Posts
    17
    for the date problem,
    in where clause try like this
    AND dp.date BETWEEN to_date('01/04/2008','dd/mm/yyyy') AND to_date('01/06/2008','dd/mm/yyyy' )

  3. #3
    Join Date
    Feb 2009
    Posts
    38
    Quote Originally Posted by sridhar.dbe
    for the date problem,
    in where clause try like this
    AND dp.date BETWEEN to_date('01/04/2008','dd/mm/yyyy') AND to_date('01/06/2008','dd/mm/yyyy' )
    thank you a lot. and i solved the other. i sould have FROM at the end not in the middle.

  4. #4
    Join Date
    Feb 2009
    Posts
    38
    Quote Originally Posted by sridhar.dbe
    for the date problem,
    in where clause try like this
    AND dp.date BETWEEN to_date('01/04/2008','dd/mm/yyyy') AND to_date('01/06/2008','dd/mm/yyyy' )

    Is 01/04/2008 included in this condition BETWEEN to_date('01/04/2008','dd/mm/yyyy') ?

    thanks again

  5. #5
    Join Date
    Jan 2009
    Posts
    17
    Quote Originally Posted by misty1976
    if i put 01/02/2008 it says EXPECTED DATE FOUND NUMBER. it only reads 01/feb/2008. how can i make it read 01/02/2008 ??
    to read 01/02/2008 you need to use to_date function
    otherwise you can specify as 01/FEB/2008

  6. #6
    Join Date
    Feb 2009
    Posts
    38
    Thank you. I did it your way and it worked fine. but when we say between 1/4/08 and 3/5/08 is 1/4/08 included? Or must i say between 31/3/2008 and 4/5/2008 ??

  7. #7
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by misty1976
    but when we say between 1/4/08 and 3/5/08 is 1/4/08 included? Or must i say between 31/3/2008 and 4/5/2008 ??
    DATE data type contains both date and time. When no time is specified, the start of that day is assigned.
    So the first date represents 01-Apr-2008 00:00:00 (format DD-Mon-YYYY HH24:MI:SS, midnight).
    The second one represents 04-May-2008 00:00:00 (midnight).
    So whole 01-Apr-2008 is included (which you asked) within the first second of 04-May-2008 (which is probably not what you want if it contains time portion).

    If you want to include whole days within time part, you may:
    - specify time portion in the end date (03-May-2008 23:59:59) or
    - use two >= and < comparisons instead of BETWEEN AND

  8. #8
    Join Date
    Feb 2009
    Posts
    38
    Quote Originally Posted by flyboy
    DATE data type contains both date and time. When no time is specified, the start of that day is assigned.
    So the first date represents 01-Apr-2008 00:00:00 (format DD-Mon-YYYY HH24:MIS, midnight).
    The second one represents 04-May-2008 00:00:00 (midnight).
    So whole 01-Apr-2008 is included (which you asked) within the first second of 04-May-2008 (which is probably not what you want if it contains time portion).

    If you want to include whole days within time part, you may:
    - specify time portion in the end date (03-May-2008 23:59:59) or
    - use two >= and < comparisons instead of BETWEEN AND
    Thank you a lot.

Posting Permissions

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