Help regarding this query !!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Help regarding this query !!

  1. #1
    Join Date
    Dec 2001
    Posts
    96

    Help regarding this query !!

    Hello Friends,

    I have a query here.

    select to_char(start_date,'mm/dd/yyyy hhAM') stdt,to_char(end_date,'mm/dd/yyyy hhAM')enddt,SHOWTYPE, DESCTV from SHOWS
    where Start_date between
    to_date('07242003000000','MMDDYYYYHH24MISS')
    and to_date('07242003235959','MMDDYYYYHH24MISS')
    group by to_char(start_date,'mm/dd/yyyy hhAM'),to_char(end_date,'mm/dd/yyyy hhAM'),SHOWTYPE, DESCTV
    SQL> /

    STDT ENDDT SHOWTYPE DESCTV
    --------------- --------------- -------------------------------- -------------------------------
    07/24/2003 05PM 07/24/2003 10PM JEWELRY Jewelry with Misty
    07/24/2003 07AM 07/24/2003 12PM JEWELRY Jewelry with Angie
    07/24/2003 03AM 07/24/2003 07AM GEMSTONES Gemstones with Geoff and Cheryl
    07/24/2003 10PM 07/25/2003 03AM GEMSTONES Gemstones with Tommy
    07/24/2003 12PM 07/24/2003 05PM JEWELRY Jewelry with Heidi

    I need all the records from those days, but I get only 1,i dont get all the above mentioned above if i run the same select with link to another table for income. can somebody suggest?

    Here is the second query
    select to_char(a.start_date,'mm/dd/yyyy hhAM')stdt,to_char(a.end_date,'mm/dd/yyyy hhAM')
    enddt,a.SHOWTYPE, a.DESCTV ,b.income_sales
    from acntv.SHOWS a,intra.pbb_batch_sum b
    where a.Start_date between
    to_date('07242003000000','MMDDYYYYHH24MISS')
    and to_date('07242003235959','MMDDYYYYHH24MISS')
    and a.batch=b.batch
    SQL> /

    TO_CHAR(A.START TO_CHAR(A.END_D SHOWTYPE DESCTV INCOME_SALES
    --------------- --------------- -------------------------------- ---------------------------------------------------------------- ------------
    07/24/2003 07AM 07/24/2003 12PM JEWELRY Jewelry with Angie 158204.16


    Thanks

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Presumably there is the only one record that matches the a.batch=b.batch condition.

    If you want all five records to be returned regardless of whether they meet the condition, use an outer-join ...

    a.batch=b.batch(+)
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Dec 2001
    Posts
    96
    It works !

    Thanks Slimdave.

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