DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: query

  1. 05-12-2008, 10:01 AM #1
    prodadmin
    prodadmin is offline Senior Member
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401

    query

    table order_items
    ordered_DATE ordered_HOUR ordered_DAY client_ID client ordered_timespent_MINutes ordered_items
    10-May-08 09 SATURDAY 1234 XYZ 37 9
    10-May-08 22 SATURDAY 1234 XYZ 53 11
    10-May-08 21 SATURDAY 1234 XYZ 19 3
    10-May-08 04 SATURDAY 1234 XYZ 12 1
    10-May-08 06 SATURDAY 1234 XYZ 26 5
    10-May-08 07 SATURDAY 1234 XYZ 31 6
    10-May-08 08 SATURDAY 1234 XYZ 32 7
    10-May-08 03 SATURDAY 1234 XYZ 17 1
    10-May-08 23 SATURDAY 1234 XYZ 4 3
    10-May-08 19 SATURDAY 1234 XYZ 10 2
    10-May-08 13 SATURDAY 1234 XYZ 27 5
    10-May-08 17 SATURDAY 1234 XYZ 8 2
    10-May-08 11 SATURDAY 1234 XYZ 37 8
    10-May-08 10 SATURDAY 1234 XYZ 30 6
    10-May-08 12 SATURDAY 1234 XYZ 45 9
    10-May-08 16 SATURDAY 1234 XYZ 43 8
    10-May-08 05 SATURDAY 1234 XYZ 10 1
    10-May-08 15 SATURDAY 1234 XYZ 21 4
    10-May-08 14 SATURDAY 1234 XYZ 46 11
    10-May-08 18 SATURDAY 1234 XYZ 14 2

    table processed_orders

    client_id processed_day processed_DAY processed_HOUR processed_orders timespent_in_processing_MINS
    1234 10-May-08 SATURDAY 02 12 44
    1234 10-May-08 SATURDAY 01 4 12
    1234 10-May-08 SATURDAY 09 3 16
    1234 10-May-08 SATURDAY 16 3 26
    1234 10-May-08 SATURDAY 04 6 33
    1234 10-May-08 SATURDAY 20 4 8
    1234 10-May-08 SATURDAY 14 8 47
    1234 10-May-08 SATURDAY 12 2 11
    1234 10-May-08 SATURDAY 00 12 62
    1234 10-May-08 SATURDAY 10 8 26
    1234 10-May-08 SATURDAY 11 4 20
    1234 10-May-08 SATURDAY 17 6 40
    1234 10-May-08 SATURDAY 21 2 1
    1234 10-May-08 SATURDAY 03 10 23
    1234 10-May-08 SATURDAY 19 15 41
    1234 10-May-08 SATURDAY 22 13 45
    1234 10-May-08 SATURDAY 13 4 20
    1234 10-May-08 SATURDAY 07 5 19
    1234 10-May-08 SATURDAY 23 10 30
    1234 10-May-08 SATURDAY 08 4 22
    1234 10-May-08 SATURDAY 15 4 20
    1234 10-May-08 SATURDAY 06 1 6
    1234 10-May-08 SATURDAY 05 4 34
    1234 10-May-08 SATURDAY 18 5 25
    select
    d.ordered_min as ordered_timespent_MINutes
    o.ordered_Rpt,
    o.ordered_hour,
    o.ordered_Date as ordered_Date,
    o.client,
    o.client_ID ,
    o.ordered_day,
    p.processed_time ,
    p.processed_jobs ,
    p.processed_mins as timespent_in_processing_MINS,
    p.processed_hour
    from
    processed_orders p,
    order_items O
    where
    p.processed_time =o.ordered_Date
    and p.purchaser_company_id=o.Company_ID
    and p.processed_HOUR(+)= o.ordered_HOUR

    when i join both the above tables(with outer join i am missing procssed hours data for hours 00,01,02

    how do i get that data inluded with out duplication in one query

    any hlep is much appreciated
    Reply With Quote Reply With Quote
  2. 05-12-2008, 10:06 AM #2
    tamilselvan
    tamilselvan is offline Senior Advisor
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Would it not be better to add create table statement, and insert rows statement so that others can work your query?
    Tamil
    http://www.oracleact.com
    Reply With Quote Reply With Quote
  3. 05-12-2008, 12:20 PM #3
    LKBrwn_DBA's Avatar
    LKBrwn_DBA
    LKBrwn_DBA is offline Senior Advisor
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Talking

    Quote Originally Posted by prodadmin
    ...
    when i join both the above tables(with outer join i am missing procssed hours data for hours 00,01,02

    how do i get that data inluded with out duplication in one query
    Without testing, I don't know if it will work but you could try changing the order of the outer join:

    and p.processed_HOUR = o.ordered_HOUR(+)

    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
    Reply With Quote Reply With Quote
  4. 05-12-2008, 01:38 PM #4
    prodadmin
    prodadmin is offline Senior Member
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401
    yes i tried that earlier but no difference
    Reply With Quote Reply With Quote
  5. 05-12-2008, 02:54 PM #5
    LKBrwn_DBA's Avatar
    LKBrwn_DBA
    LKBrwn_DBA is offline Senior Advisor
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool

    Could it be that maybe it's because not allways the processed_HOUR = ordered_HOUR?
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
    Reply With Quote Reply With Quote
  6. 05-19-2008, 08:01 AM #6
    prodadmin
    prodadmin is offline Senior Member
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401
    Sorry for the late reply ..attached the table scripts

    Code:
    CREATE TABLE ORDER_ITEMS
(
  ORDERED_DATE  DATE,
  ORDERED_HOUR  VARCHAR2(2 BYTE),
  ORDERED_DAY   VARCHAR2(9 BYTE),
  CLIENT_ID     NUMBER                         NOT NULL,
  CLIENT        VARCHAR2(40 BYTE)              NOT NULL,
  ORDERED_TIMESPENT_MINUTES   NUMBER,
  ORDERED_ITEMS  NUMBER
);

Insert into ORDER_ITEMS Values   '10-MAY-08', '09', 'SATURDAY ', 1234, 'XYZ', 37.35, 9);
Insert into ORDER_ITEMS Values   '10-MAY-08', '22', 'SATURDAY ', 1234, 'XYZ', 52.616667, 11);
Insert into ORDER_ITEMS Values   '10-MAY-08', '21', 'SATURDAY ', 1234, 'XYZ', 18.8, 3);
Insert into ORDER_ITEMS Values   '10-MAY-08', '04', 'SATURDAY ', 1234, 'XYZ', 11.616667, 1);
Insert into ORDER_ITEMS Values   '10-MAY-08', '06', 'SATURDAY ', 1234, 'XYZ', 25.933333, 5);
Insert into ORDER_ITEMS Values   '10-MAY-08', '07', 'SATURDAY ', 1234, 'XYZ', 30.766667, 6);
Insert into ORDER_ITEMS Values   '10-MAY-08', '08', 'SATURDAY ', 1234, 'XYZ', 32.316667, 7);
Insert into ORDER_ITEMS Values   '10-MAY-08', '03', 'SATURDAY ', 1234, 'XYZ', 16.516667, 1);
Insert into ORDER_ITEMS Values   '10-MAY-08', '23', 'SATURDAY ', 1234, 'XYZ', 3.833333, 3);
Insert into ORDER_ITEMS Values   '10-MAY-08', '19', 'SATURDAY ', 1234, 'XYZ', 10.033333, 2);
Insert into ORDER_ITEMS Values   '10-MAY-08', '13', 'SATURDAY ', 1234, 'XYZ', 27, 5);
Insert into ORDER_ITEMS Values   '10-MAY-08', '17', 'SATURDAY ', 1234, 'XYZ', 7.816667, 2);
Insert into ORDER_ITEMS Values   '10-MAY-08', '11', 'SATURDAY ', 1234, 'XYZ', 37.333333, 8);
Insert into ORDER_ITEMS Values   '10-MAY-08', '10', 'SATURDAY ', 1234, 'XYZ', 29.916667, 6);
Insert into ORDER_ITEMS Values   '10-MAY-08', '12', 'SATURDAY ', 1234, 'XYZ', 45.3, 9);
Insert into ORDER_ITEMS Values   '10-MAY-08', '16', 'SATURDAY ', 1234, 'XYZ', 42.966667, 8);
Insert into ORDER_ITEMS Values   '10-MAY-08', '05', 'SATURDAY ', 1234, 'XYZ', 9.5, 1);
Insert into ORDER_ITEMS Values   '10-MAY-08', '15', 'SATURDAY ', 1234, 'XYZ', 21.35, 4);
Insert into ORDER_ITEMS Values   '10-MAY-08', '14', 'SATURDAY ', 1234, 'XYZ', 45.7, 11);
Insert into ORDER_ITEMS  Values   '10-MAY-08', '18', 'SATURDAY ', 1234, 'XYZ', 13.933333, 2);
COMMIT;


CREATE TABLE PROCESSED_ORDERS
(
  CLIENT_ID  NUMBER                  NOT NULL,
  PROCESSED_TIME      DATE,
  PROCESSED_DAY       VARCHAR2(9 BYTE),
  PROCESSED_HOUR      VARCHAR2(2 BYTE),
  PROCESSED_JOBS      NUMBER,
  PROCESSED_TIMESPENT_MINUTES      NUMBER
);

Insert into PROCESSED_ORDERS   Values    (1234, '10-MAY-08', 'SATURDAY ', '02', 12, 43.9);
Insert into PROCESSED_ORDERS  Values    (1234, '10-MAY-08', 'SATURDAY ', '01', 4, 11.95);
Insert into PROCESSED_ORDERS  Values   (1234, '10-MAY-08', 'SATURDAY ', '09', 3, 16.3166666666667);
Insert into PROCESSED_ORDERS    Values   (1234, '10-MAY-08', 'SATURDAY ', '16', 3, 26.2166666666667);
Insert into PROCESSED_ORDERS  Values   (1234, '10-MAY-08', 'SATURDAY ', '04', 6, 32.5833333333333);
Insert into PROCESSED_ORDERS    Values   (1234, '10-MAY-08', 'SATURDAY ', '20', 4, 8.21666666666667);
Insert into PROCESSED_ORDERS   Values   (1234, '10-MAY-08', 'SATURDAY ', '14', 8, 47.1666666666667);
Insert into PROCESSED_ORDERS   Values   (1234, '10-MAY-08', 'SATURDAY ', '12', 2, 10.95);
Insert into PROCESSED_ORDERS   Values   (1234, '10-MAY-08', 'SATURDAY ', '00', 12, 61.6666666666667);
Insert into PROCESSED_ORDERS   Values   (1234, '10-MAY-08', 'SATURDAY ', '10', 8, 26.4666666666667);
Insert into PROCESSED_ORDERS   Values    (1234, '10-MAY-08', 'SATURDAY ', '11', 4, 20.2333333333333);
Insert into PROCESSED_ORDERS   Values   (1234, '10-MAY-08', 'SATURDAY ', '17', 6, 39.8833333333333);
Insert into PROCESSED_ORDERS   Values   (1234, '10-MAY-08', 'SATURDAY ', '21', 2, 1.05);
Insert into PROCESSED_ORDERS    Values   (1234, '10-MAY-08', 'SATURDAY ', '03', 10, 23.4833333333333);
Insert into PROCESSED_ORDERS    Values   (1234, '10-MAY-08', 'SATURDAY ', '19', 15, 41.1833333333333);
Insert into PROCESSED_ORDERS    Values   (1234, '10-MAY-08', 'SATURDAY ', '22', 13, 44.75);
Insert into PROCESSED_ORDERS    Values   (1234, '10-MAY-08', 'SATURDAY ', '13', 4, 20.1166666666667);
Insert into PROCESSED_ORDERS    Values   (1234, '10-MAY-08', 'SATURDAY ', '07', 5, 18.6833333333333);
Insert into PROCESSED_ORDERS    Values   (1234, '10-MAY-08', 'SATURDAY ', '23', 10, 29.9166666666667);
Insert into PROCESSED_ORDERS    Values   (1234, '10-MAY-08', 'SATURDAY ', '08', 4, 21.8166666666667);
Insert into PROCESSED_ORDERS    Values   (1234, '10-MAY-08', 'SATURDAY ', '15', 4, 20.2833333333333);
Insert into PROCESSED_ORDERS    Values   (1234, '10-MAY-08', 'SATURDAY ', '06', 1, 5.61666666666667);
Insert into PROCESSED_ORDERS    Values   (1234, '10-MAY-08', 'SATURDAY ', '05', 4, 34.0333333333333);
Insert into PROCESSED_ORDERS    Values   (1234, '10-MAY-08', 'SATURDAY ', '18', 5, 24.8);
COMMIT;

Now i join above  with this 

select
d.ordered_min as ordered_timespent_MINutes
o.ordered_Rpt,
o.ordered_hour, 
o.ordered_Date as ordered_Date,
o.client,
o.client_ID ,
o.ordered_day, 
p.processed_time ,
p.processed_jobs ,
p.processed_mins as timespent_in_processing_MINS,
p.processed_hour
from
processed_orders p, 
order_items O
where
p.processed_time =o.ordered_Date
and p.purchaser_company_id=o.Company_ID
and p.processed_HOUR(+)= o.ordered_HOUR ;

when i do with outer join i am missing procssed hours data for hours 00,01,02.Tried outer join both sides

how do i get that data inluded with out duplication in one query

any hlep is much appreciated
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

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

Forum Rules

Click Here to Expand Forum to Full Width