Help with this Query Pls
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Help with this Query Pls

  1. #1
    Join Date
    Dec 2001
    Posts
    96

    Help with this Query Pls

    Hello All,

    I need little help with this query. I want the output similar to the First output shown here and which is correct .

    select to_char(a.start_date,'mm/dd/yyyy hhAM') Stdt,to_char(a.end_date,'mm/dd/yyyy hhAM')endt,
    a.SHOWTYPE, a.DESCTV
    from acntv.SHOWS a,intra.pbb_batch_sum b
    where a.Start_date between
    to_date('07242003070000','MMDDYYYYHH24MISS')
    and to_date('07252003035959','MMDDYYYYHH24MISS')
    and a.batch=b.batch(+)
    group by to_char(a.start_date,'mm/dd/yyyy hhAM'),to_char(a.end_date,'mm/dd/yyyy hhAM'),
    a.SHOWTYPE, a.DESCTV ;
    stdt endt SHOWTYPE DESCTV
    --------------- --------------- -------------------------------- --------------------
    07/24/2003 07AM 07/24/2003 12PM JEWELRY Jewelry with Angie
    07/24/2003 10PM 07/25/2003 03AM GEMSTONES Gemstones with Tommy
    07/24/2003 12PM 07/24/2003 05PM JEWELRY Jewelry with Heidi
    07/24/2003 05PM 07/24/2003 10PM JEWELRY Jewelry with Misty
    07/25/2003 03AM 07/25/2003 07AM JEWELRY Jewelry with Robert

    If I happen to pass parameter as start date and end date, I get different result,since our date of operation is different and I need the same output as the first result shown above. Can somebody help me?

    select to_char(a.start_date,'mm/dd/yyyy hhAM')stdt,to_char(a.end_date,'mm/dd/yyyy hhAM')endt,
    a.SHOWTYPE, a.DESCTV
    from acntv.SHOWS a,intra.pbb_batch_sum b
    where a.Start_date between
    to_date('&st','MMDDYYYYHH24MISS')
    and to_date('&en','MMDDYYYYHH24MISS')
    and a.batch=b.batch(+)
    group by to_char(a.start_date,'mm/dd/yyyy hhAM'),to_char(a.end_date,'mm/dd/yyyy hhAM'),
    a.SHOWTYPE, a.DESCTV ;
    Enter value for st: 07242003
    old 7: to_date('&st','MMDDYYYYHH24MISS')
    new 7: to_date('07242003','MMDDYYYYHH24MISS')
    Enter value for en: 07252003
    old 8: and to_date('&en','MMDDYYYYHH24MISS')
    new 8: and to_date('07252003','MMDDYYYYHH24MISS')

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

    Pls suggest how I should do it.

    Thanks

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    Have you tried passing in the complete date/time group? The second case - looks like all you are passing in is the start date with no (explicit) time component.

  3. #3
    Join Date
    Dec 2001
    Posts
    96
    No, I am not passing the complete date/time group. I just need the user to input the date. Is this possible ?
    If not, can we he just pass the date and we concatenate the time string ? if so, how to do this ?

    Thanks

  4. #4
    Join Date
    May 2002
    Posts
    2,645
    A reason why your output could/would be different is because of the difference between

    where a.Start_date between
    to_date('07242003070000','MMDDYYYYHH24MISS')
    and to_date('07252003035959','MMDDYYYYHH24MISS')


    and

    Enter value for st: 07242003[no time here]
    old 7: to_date('&st','MMDDYYYYHH24MISS')
    new 7: to_date('07242003','MMDDYYYYHH24MISS')
    Enter value for en: 07252003[no time here]
    old 8: and to_date('&en','MMDDYYYYHH24MISS')
    new 8: and to_date('07252003','MMDDYYYYHH24MISS')

    So your a.start_date between range is going to be different

  5. #5
    Join Date
    Dec 2001
    Posts
    96
    Yes, if I pass 07242003070000 and 07252003035959 in the sql,
    where a.Start_date between to_date('07242003070000','MMDDYYYYHH24MISS')
    and to_date('07252003035959','MMDDYYYYHH24MISS') , I will get the desired output.

    I want to know if there is any way,the user just enters 07252003 and 07252003 & if I can hard-code 070000 and 035959 or concatenate the time(hard-code) with the user entered date? I need this time constant,he can enter the dates.

    Pls advise.
    Thanks

  6. #6
    Join Date
    Dec 2001
    Posts
    96
    Problem solved, thanks for your inputs

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