Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2005
    Posts
    92

    Unanswered: sub query question

    hello.,

    i have a query which is like this:

    select A.TIME_ID,A.VENUE_ID,COUNT(*),B.FILE_NM,max(B.EFF_ TIME_ID) from file_venue A, file_control B
    where A.FILE_CONTROL_ID = B.FILE_CONTROL_ID and A.STATUS_CD != 'D' group by A.VENUE_ID,A.TIME_ID,B.FILE_NM;


    the output of this is:

    TIME_ID, VENUE_ID, COUNT(*) FILE_NM MAX(EFF_TIME_ID)
    1120953600 31561 1 XYZ 1121203862
    1120953600 31565 1 WYZ1 1121203862
    1120953600 31597 1 ABC 1121115660
    1120953600 31597 1 ABC1 1121203862


    the output must contain all the time_id, venue_id, count(*) and file_n for the max(eff_time_Id) and there should not be any duplicate venue_id.there can be only 1 venue_id for any max(eff_time_id). above result is shwing duplicates for the venue_id (31597). it should not be the case.

    i have to write a sub query..can anyone help me in this pls

    thanks

  2. #2
    Join Date
    Jan 2004
    Posts
    492
    I would have to ask this - given that data, what do you expect the result to be? You have more than one file name per a given venue (31597). If you did not really care which file name you selected, you could just do max(file_nm).

    What are the results, and give a quick explanation of why you expect those results.

    Off the top of my head, maybe something like this:
    Code:
    select c.time_id,
           c.venue_id,
           count(ven.venue_id),
           d.file_nm,
           ven.eff_time_id
    from file_venue c,
           file_control d,
    (select a.venue_id, 
            max(b.eff_time_id) eff_time_id
      from file_venue a,
           file_control b
     where a.file_control_id = b.file_control_id) ven
    where c.file_control_id = b.file_control_id and
          c.venue_id = ven.venue_id
    group by c.time_id, c.venue_id, d.file_nm, ven.eff_time_id
    Last edited by ss659; 10-18-05 at 15:19.
    Oracle OCPI (Certified Practicing Idiot)

  3. #3
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    I agree. Wverything looks to be distinct.

  4. #4
    Join Date
    Oct 2005
    Posts
    92

    sub query question

    Quote Originally Posted by ss659
    I would have to ask this - given that data, what do you expect the result to be? You have more than one file name per a given venue (31597). If you did not really care which file name you selected, you could just do max(file_nm).

    What are the results, and give a quick explanation of why you expect those results.

    Off the top of my head, maybe something like this:
    Code:
    select c.time_id,
           c.venue_id,
           count(ven.venue_id),
           d.file_nm,
           ven.eff_time_id
    from file_venue c,
           file_control d,
    (select a.venue_id, 
            max(b.eff_time_id) eff_time_id
      from file_venue a,
           file_control b
     where a.file_control_id = b.file_control_id) ven
    where c.file_control_id = b.file_control_id and
          c.venue_id = ven.venue_id
    group by c.time_id, c.venue_id, d.file_nm, ven.eff_time_id


    the result should be like this:

    TIME_ID VENUE_ID COUNT(*) FILE_NM MAX(EFF_TIME_ID)
    1120953600 31561 1 XYZ 1121203862
    1120953600 31565 1 WYZ1 1121203862
    1120953600 31597 1 ABC1 1121203862

    BUT RIGHT NOW THE RESULT IS SHOWING LIKE THIS:

    TIME_ID VENUE_ID COUNT(*) FILE_NM MAX(EFF_TIME_ID)
    1120953600 31561 1 XYZ 1121203862
    1120953600 31565 1 WYZ1 1121203862
    1120953600 31597 1 ABC 1121115660
    1120953600 31597 1 ABC1 1121203862

    THERE ARE 2 TABLES:
    FILE_VENUE ---> I NEED VENUE_ID, TIME_ID
    FILE_CONTROL ---> FILE_NM, MAX(EFF_TIME_ID)

    WHERE FILE_VENUE.FILE_CONTROL_ID = FILE_CONTROL.FILE_CONTROL_ID,
    FILE_VENUE.STATUS_CD <> 'D' AND FILE_CONTROL.DATATYPE_CD ='RAW'


    thanks

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    select A.TIME_ID,A.VENUE_ID,COUNT(*),max(B.FILE_NM),max(B .EFF_ TIME_ID) from file_venue A, file_control B
    where A.FILE_CONTROL_ID = B.FILE_CONTROL_ID and A.STATUS_CD != 'D' group by A.VENUE_ID,A.TIME_ID;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Jan 2004
    Posts
    492
    If you do max(b.file_nm) AND max(b.eff_time_id) in the same select, I would assume its possible to have a file that has a "larger" name, but an earlier timestamp right?

    You could have:
    Code:
    Venue ID   File Name    Timestamp
    1             ABC            17-OCT-2005 12:45:21 pm
    1             DEF            16-OCT-2005 10:03:00 am
    In this case, your query would pull the DEF file, but the wrong timestamp. I'm not saying my query was correct, but I think the filename needs to be dependent on the most current timestamp for that venue.
    Oracle OCPI (Certified Practicing Idiot)

  7. #7
    Join Date
    Oct 2005
    Posts
    92

    sub query question

    Thanks A Lot Buddy

Posting Permissions

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