DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: weird SQL

  1. #11
    Join Date
    Jun 2000
    Posts
    295
    Steve,

    select * from application a
    where a.posted >= to_date('27-OCT-2000','dd-mon-yyyy') and a.posted <= to_date('22-NOV-2000','dd-mon-yyyy');

    will return a lot records.

    SQL> r
    1 select l.location_number, count(a.application_id)
    2 from application a, location l
    3 where
    4 l.location_id = 102884
    5 and l.location_id = a.location_id(+)
    6 and a.posted >= to_date('27-OCT-2000','dd-mon-yyyy')
    7 and a.posted <= to_date('22-NOV-2000','dd-mon-yyyy')
    8* group by l.location_number

    no rows selected

    SQL>


    For who comes to see the orginal question:

    SQL> r
    1 select l.location_number, count(a.application_id)
    2 from application a, location l
    3 where
    4 l.location_id = 102884
    5 and l.location_id = a.location_id(+)
    6* group by l.location_number

    LOCATION_NUMBER COUNT(A.APPLICATION_ID)
    ------------------------------ -----------------------
    2323 0

    I want to see
    LOCATION_NUMBER COUNT(A.APPLICATION_ID)
    ------------------------------ -----------------------
    2323 0

    for the following query too!
    select l.location_number, count(a.application_id)
    from application a, location l
    where
    l.location_id = 102884
    and l.location_id = a.location_id(+)
    and a.posted >= to_date('27-OCT-2000','dd-mon-yyyy')
    and a.posted <= to_date('22-NOV-2000','dd-mon-yyyy')
    group by l.location_number
    /




  2. #12
    Join Date
    Nov 2000
    Posts
    205
    Actuall my understanding is that it would work if you typed in this:

    and to_char((a.posted), 'DD-MON-YYYY') >= 27-OCT-2000
    and to_char((a.posted), 'DD-MON-YYYY') <='22-NOV-2000

    You might have to play around with the brackets, trunc as well etc.

    My understanding (and from previous usage) is that we always convert our table's field values (at least for display) before making comparisons.

    you can use to_date, but I have always had luck with to_char.

    Good luck and I hope it helps.
    Nirasha

  3. #13
    Join Date
    Nov 2000
    Posts
    440
    Are you absoletly sure? Did you tried it, cus if you did not tried it, you dont have the proof that the select:

    select * from application a
    where a.posted >= to_date('27-OCT-2000','dd-mon-yyyy') and a.posted <= to_date('22-NOV-2000','dd-mon-yyyy');

    Does actually Works.

    Do a select count(*) if you think lots of record will be return.
    But you need the proof that the where on the date works, cus that is the problem.

    if you do
    1 select l.location_number, count(a.application_id)
    2 from application a, location l
    3 where
    4 l.location_id = 102884
    5 and l.location_id = a.location_id(+)
    6* group by l.location_number

    LOCATION_NUMBER COUNT(A.APPLICATION_ID)
    ------------------------------ -----------------------
    2323 0


    and its returns something
    and the select:
    select count(*) from application a
    where a.posted >= to_date('27-OCT-2000','dd-mon-yyyy') and a.posted <= to_date('22-NOV-2000','dd-mon-yyyy');

    Also returns something then the complete seletc should works.


    Thats why a want you to do
    select count(*) from application a
    where a.posted >= to_date('27-OCT-2000','dd-mon-yyyy') and a.posted <= to_date('22-NOV-2000','dd-mon-yyyy');


    To have the proof that you have those records between those 2 dates.


  4. #14
    Join Date
    Jul 2000
    Posts
    5
    In the application table you don't have any related records as your first query tells (count=0).

    LOCATION_NUMBER COUNT(A.APPLICATION_ID)
    ------------------------------ -----------------------
    2323 0 <==== MEANS THAT THERE ARE NO RECORDS

    That's why the query where you use the date condition doesn't return rows.

    AP

  5. #15
    Join Date
    Jul 2000
    Posts
    5
    sysdba,

    I think you would get the result you expect with this query:

    select l.location_number, count(a.application_id)
    from location l,
    (select * from application
    where posted >= to_date('27-OCT-2000','dd-mon-yyyy')
    and posted <= to_date('22-NOV-2000','dd-mon-yyyy')) a
    where
    l.location_id = 102884
    and l.location_id = a.location_id(+)
    group by l.location_number
    /

    Try it!

    AP

  6. #16
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    I answered this SQL issue in the Database Programming forum.

Posting Permissions

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


Click Here to Expand Forum to Full Width