Solved

oracle query

Posted on 2013-06-05
17
390 Views
Last Modified: 2013-06-06
CREATE TABLE TAB1
(
  CONSIGNEE_ID  VARCHAR2(10 BYTE)  ,
  WEEK_DAY      VARCHAR2(3 BYTE)  ,
  ITEM_NO       NUMBER(2) ,
  ATTRIB_GROUP  VARCHAR2(5 BYTE) ,
  ATTRIB_CODE   VARCHAR2(4 BYTE) )



Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'FRI', 1, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'FRI', 3, 'ALERT', 'CMV');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'FRI', 3, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'FRI', 4, 'ALERT', 'CMV');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'FRI', 4, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'FRI', 7, 'ALERT', 'CMV');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'FRI', 7, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'FRI', 8, 'ALERT', 'CMV');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'FRI', 8, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'MON', 1, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'MON', 3, 'ALERT', 'CMV');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'MON', 3, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'MON', 4, 'ALERT', 'CMV');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'MON', 4, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'MON', 7, 'ALERT', 'CMV');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'MON', 7, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'SAT', 3, 'ALERT', 'CMV');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'SAT', 3, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'SAT', 4, 'ALERT', 'CMV');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'SAT', 4, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'SUN', 3, 'ALERT', 'CMV');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'SUN', 3, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'SUN', 4, 'ALERT', 'CMV');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'SUN', 4, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'SUN', 5, 'ALERT', 'CMV');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'SUN', 5, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'THU', 1, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'THU', 2, 'ALERT', 'CMV');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'THU', 2, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'THU', 3, 'ALERT', 'CMV');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'THU', 3, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'THU', 6, 'ALERT', 'CMV');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'THU', 6, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'THU', 7, 'ALERT', 'CMV');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'THU', 7, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'TUE', 1, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'TUE', 4, 'ALERT', 'CMV');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'TUE', 4, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'TUE', 5, 'ALERT', 'CMV');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'TUE', 5, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'TUE', 8, 'ALERT', 'CMV');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'TUE', 8, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'TUE', 9, 'ALERT', 'CMV');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'TUE', 9, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'TUE', 10, 'ALERT', 'CMV');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'TUE', 10, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'WED', 1, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'WED', 3, 'ALERT', 'CMV');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'WED', 3, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'WED', 4, 'ALERT', 'CMV');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'WED', 4, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'WED', 7, 'ALERT', 'CMV');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'WED', 7, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'WED', 8, 'ALERT', 'CMV');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('RUS-CS0001', 'WED', 8, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('CEN-CS0001', 'FRI', 1, 'ALERT', 'CMV');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('CEN-CS0001', 'FRI', 1, 'SP', 'N4');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('CEN-CS0001', 'FRI', 1, 'SP', 'NEO');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('CEN-CS0001', 'TUE', 1, 'ALERT', 'CMV');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('CEN-CS0001', 'TUE', 1, 'SP', 'N4');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('CEN-CS0001', 'TUE', 1, 'SP', 'NEO');
COMMIT;

----------------------------------------------


CREATE TABLE TAB2
(
  CONSIGNEE_ID  VARCHAR2(10 BYTE)    ,
  WEEK_DAY      VARCHAR2(3 BYTE)        ,
  ITEM_NO       NUMBER(2)     ,
  PRODUCT_TYPE  VARCHAR2(5 BYTE)   )



Insert into TAB2
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, PRODUCT_TYPE)
 Values
   ('CEN-CS0001', 'TUE', 1, 'LRBC');
Insert into TAB2
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, PRODUCT_TYPE)
 Values
   ('CEN-CS0001', 'FRI', 1, 'LRBC');
Insert into TAB2
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, PRODUCT_TYPE)
 Values
   ('RUS-CS0001', 'FRI', 9, 'LPHER');
Insert into TAB2
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, PRODUCT_TYPE)
 Values
   ('RUS-CS0001', 'MON', 9, 'LPHER');
Insert into TAB2
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, PRODUCT_TYPE)
 Values
   ('RUS-CS0001', 'SAT', 6, 'LPHER');
Insert into TAB2
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, PRODUCT_TYPE)
 Values
   ('RUS-CS0001', 'SUN', 7, 'LPHER');
Insert into TAB2
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, PRODUCT_TYPE)
 Values
   ('RUS-CS0001', 'THU', 9, 'LPHER');
Insert into TAB2
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, PRODUCT_TYPE)
 Values
   ('RUS-CS0001', 'TUE', 12, 'LPHER');
Insert into TAB2
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, PRODUCT_TYPE)
 Values
   ('RUS-CS0001', 'WED', 10, 'LPHER');
COMMIT;

--------------------------

select distinct a.consignee_id,
          b.week_day,
          a.item_no,
          a.attrib_group,
          a.attrib_code,
          b.product_type
  from tab1 a,
       tab2 b
where a.consignee_id = b.consignee_id
   and a.item_no = b.item_no

CONSIGNEE_ID|WEEK_DAY|ITEM_NO|ATTRIB_GROUP|ATTRIB_CODE|PRODUCT_TYPE
CEN-CS0001|FRI|1|ALERT|CMV|LRBC
CEN-CS0001|FRI|1|SP|N4|LRBC
CEN-CS0001|FRI|1|SP|NEO|LRBC
CEN-CS0001|TUE|1|ALERT|CMV|LRBC
CEN-CS0001|TUE|1|SP|N4|LRBC
CEN-CS0001|TUE|1|SP|NEO|LRBC
RUS-CS0001|SAT|6|ALERT|CMV|LPHER
RUS-CS0001|SAT|6|SP|IR|LPHER
RUS-CS0001|SUN|7|ALERT|CMV|LPHER
RUS-CS0001|SUN|7|SP|IR|LPHER
RUS-CS0001|FRI|9|ALERT|CMV|LPHER
RUS-CS0001|FRI|9|SP|IR|LPHER
RUS-CS0001|MON|9|ALERT|CMV|LPHER
RUS-CS0001|MON|9|SP|IR|LPHER
RUS-CS0001|THU|9|ALERT|CMV|LPHER
RUS-CS0001|THU|9|SP|IR|LPHER
RUS-CS0001|WED|10|ALERT|CMV|LPHER
RUS-CS0001|WED|10|SP|IR|LPHER


But my requirement is
see attached excel data.
When I join item_no I get all the data. Hope this information helps.
sample-data-required.xls
0
Comment
Question by:anumoses
  • 7
  • 5
  • 5
17 Comments
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
SELECT b.consignee_id name,
       b.week_day wkday,
       b.product_type prd_typ,
       a.attrib_group attrib_grp,
       a.attrib_code atrrib_cod
  FROM tab1 a
       RIGHT JOIN tab2 b
           ON a.consignee_id = b.consignee_id AND a.item_no = b.item_no AND a.week_day = b.week_day
           order by name,wkday;
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
to get the nulls try this...

SELECT NULLIF(name, prev_name) name,
       CASE WHEN name = prev_name AND wkday = prev_wkday THEN NULL ELSE wkday END wkday,
       CASE
           WHEN name = prev_name AND wkday = prev_wkday AND prd_typ = prev_prdtyp THEN NULL
           ELSE prd_typ
       END
           prd_typ,
       attrib_grp,
       attrib_cod
  FROM (SELECT   b.consignee_id name,
                 b.week_day wkday,
                 b.product_type prd_typ,
                 a.attrib_group attrib_grp,
                 a.attrib_code attrib_cod,
                 LAG(b.consignee_id) OVER (ORDER BY b.consignee_id, b.week_day) prev_name,
                 LAG(b.week_day) OVER (ORDER BY b.consignee_id, b.week_day) prev_wkday,
                 LAG(b.product_type) OVER (ORDER BY b.consignee_id, b.week_day, b.product_type)
                     prev_prdtyp
            FROM tab1 a
                 RIGHT JOIN
                 tab2 b
                     ON     a.consignee_id = b.consignee_id
                        AND a.item_no = b.item_no
                        AND a.week_day = b.week_day
        ORDER BY name, wkday);
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
Both the queries work fast and very perfectly. I like the 2nd solution. But I have a question
if I want to join the consignee table to get the consignee name
 where will I join

select name from consignee c
where a.consignee_id = c.consignee_id
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
Comment Utility
SELECT CASE
           WHEN id = prev_id
           THEN
               NULL
           ELSE
               (SELECT name
                  FROM consignee_id c
                 WHERE c.consignee_id = id)
       END
           name,
       CASE WHEN id = prev_id AND wkday = prev_wkday THEN NULL ELSE wkday END wkday,
       CASE
           WHEN id = prev_id AND wkday = prev_wkday AND prd_typ = prev_prdtyp THEN NULL
           ELSE prd_typ
       END
           prd_typ,
       attrib_grp,
       attrib_cod
  FROM (SELECT   b.consignee_id id,
                 b.week_day wkday,
                 b.product_type prd_typ,
                 a.attrib_group attrib_grp,
                 a.attrib_code attrib_cod,
                 LAG(b.consignee_id) OVER (ORDER BY b.consignee_id, b.week_day) prev_id,
                 LAG(b.week_day) OVER (ORDER BY b.consignee_id, b.week_day) prev_wkday,
                 LAG(b.product_type) OVER (ORDER BY b.consignee_id, b.week_day, b.product_type)
                     prev_prdtyp
            FROM tab1 a
                 RIGHT JOIN
                 tab2 b
                     ON     a.consignee_id = b.consignee_id
                        AND a.item_no = b.item_no
                        AND a.week_day = b.week_day
        ORDER BY id, wkday);
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
Sorry to bother. I wanted to add

and b.term_date is null

and also select blood_type from tab2 b

I know I did not add that in the table not thinking this could be so complex for me. The reason is some of the products are termed. and also I need the blood type like O POS A NEG etc
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
Dont worry I got the query. Added the term date and qty >0

Thanks,
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
But when I ran for another customer I cou,kd not get the right data.

Putting that data in the sample tables.


CREATE TABLE TAB1
(
  CONSIGNEE_ID  VARCHAR2(10 BYTE) ,
  WEEK_DAY      VARCHAR2(3 BYTE)   ,
  ITEM_NO       NUMBER(2)  ,
  ATTRIB_GROUP  VARCHAR2(5 BYTE)  ,
  ATTRIB_CODE   VARCHAR2(4 BYTE)  )



Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('EDW-CS0003', 'FRI', 3, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('EDW-CS0003', 'MON', 3, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('EDW-CS0003', 'SAT', 3, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('EDW-CS0003', 'SUN', 3, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('EDW-CS0003', 'THU', 3, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('EDW-CS0003', 'TUE', 3, 'SP', 'IR');
Insert into TAB1
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, ATTRIB_GROUP, ATTRIB_CODE)
 Values
   ('EDW-CS0003', 'WED', 3, 'SP', 'IR');
COMMIT;

--------------------------------


CREATE TABLE TAB2
(
  CONSIGNEE_ID  VARCHAR2(10 BYTE)               NOT NULL,
  WEEK_DAY      VARCHAR2(3 BYTE)                NOT NULL,
  ITEM_NO       NUMBER(2)                       NOT NULL,
  PRODUCT_TYPE  VARCHAR2(5 BYTE)                NOT NULL,
  TERM_DATE     DATE,
  QTY           NUMBER(5)                       NOT NULL
)
   


Insert into TAB2
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, PRODUCT_TYPE, TERM_DATE,
    QTY)
 Values
   ('EDW-CS0003', 'MON', 1, 'LPHER', NULL,
    1);
Insert into TAB2
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, PRODUCT_TYPE, TERM_DATE,
    QTY)
 Values
   ('EDW-CS0003', 'MON', 2, 'LPHER', NULL,
    1);
Insert into TAB2
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, PRODUCT_TYPE, TERM_DATE,
    QTY)
 Values
   ('EDW-CS0003', 'WED', 1, 'LPHER', NULL,
    1);
Insert into TAB2
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, PRODUCT_TYPE, TERM_DATE,
    QTY)
 Values
   ('EDW-CS0003', 'WED', 2, 'LPHER', NULL,
    1);
Insert into TAB2
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, PRODUCT_TYPE, TERM_DATE,
    QTY)
 Values
   ('EDW-CS0003', 'FRI', 1, 'LPHER', NULL,
    1);
Insert into TAB2
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, PRODUCT_TYPE, TERM_DATE,
    QTY)
 Values
   ('EDW-CS0003', 'FRI', 2, 'LPHER', NULL,
    1);
Insert into TAB2
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, PRODUCT_TYPE, TERM_DATE,
    QTY)
 Values
   ('EDW-CS0003', 'SAT', 1, 'LPHER', NULL,
    1);
Insert into TAB2
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, PRODUCT_TYPE, TERM_DATE,
    QTY)
 Values
   ('EDW-CS0003', 'SAT', 2, 'LPHER', NULL,
    1);
Insert into TAB2
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, PRODUCT_TYPE, TERM_DATE,
    QTY)
 Values
   ('EDW-CS0003', 'SUN', 1, 'LPHER', NULL,
    1);
Insert into TAB2
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, PRODUCT_TYPE, TERM_DATE,
    QTY)
 Values
   ('EDW-CS0003', 'SUN', 2, 'LPHER', NULL,
    1);
Insert into TAB2
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, PRODUCT_TYPE, TERM_DATE,
    QTY)
 Values
   ('EDW-CS0003', 'TUE', 1, 'LPHER', NULL,
    1);
Insert into TAB2
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, PRODUCT_TYPE, TERM_DATE,
    QTY)
 Values
   ('EDW-CS0003', 'TUE', 2, 'LPHER', NULL,
    1);
Insert into TAB2
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, PRODUCT_TYPE, TERM_DATE,
    QTY)
 Values
   ('EDW-CS0003', 'THU', 1, 'LPHER', NULL,
    1);
Insert into TAB2
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, PRODUCT_TYPE, TERM_DATE,
    QTY)
 Values
   ('EDW-CS0003', 'THU', 2, 'LPHER', NULL,
    1);
Insert into TAB2
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, PRODUCT_TYPE, TERM_DATE,
    QTY)
 Values
   ('EDW-CS0003', 'WED', 3, 'LPHER', NULL,
    1);
Insert into TAB2
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, PRODUCT_TYPE, TERM_DATE,
    QTY)
 Values
   ('EDW-CS0003', 'TUE', 3, 'LPHER', NULL,
    1);
Insert into TAB2
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, PRODUCT_TYPE, TERM_DATE,
    QTY)
 Values
   ('EDW-CS0003', 'THU', 3, 'LPHER', NULL,
    1);
Insert into TAB2
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, PRODUCT_TYPE, TERM_DATE,
    QTY)
 Values
   ('EDW-CS0003', 'SUN', 3, 'LPHER', NULL,
    1);
Insert into TAB2
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, PRODUCT_TYPE, TERM_DATE,
    QTY)
 Values
   ('EDW-CS0003', 'SAT', 3, 'LPHER', NULL,
    1);
Insert into TAB2
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, PRODUCT_TYPE, TERM_DATE,
    QTY)
 Values
   ('EDW-CS0003', 'MON', 3, 'LPHER', NULL,
    1);
Insert into TAB2
   (CONSIGNEE_ID, WEEK_DAY, ITEM_NO, PRODUCT_TYPE, TERM_DATE,
    QTY)
 Values
   ('EDW-CS0003', 'FRI', 3, 'LPHER', NULL,
    1);
COMMIT;

-------------------------------------

SELECT CASE
           WHEN id = prev_id
           THEN
               NULL
           ELSE
               (SELECT name
                  FROM consignee@plab.world c
                 WHERE c.consignee_id = id)
       END
           name,
       CASE WHEN id = prev_id AND wkday = prev_wkday THEN NULL ELSE wkday END wkday,
       CASE
           WHEN id = prev_id AND wkday = prev_wkday AND prd_typ = prev_prdtyp THEN NULL
           ELSE prd_typ
       END
           prd_typ,
       attrib_grp,
       attrib_cod
  FROM (SELECT   b.consignee_id id,
                 b.week_day wkday,
                 b.product_type prd_typ,
                 a.attrib_group attrib_grp,
                 a.attrib_code attrib_cod,
                 LAG(b.consignee_id) OVER (ORDER BY b.consignee_id, b.week_day) prev_id,
                 LAG(b.week_day) OVER (ORDER BY b.consignee_id, b.week_day) prev_wkday,
                 LAG(b.product_type) OVER (ORDER BY b.consignee_id, b.week_day, b.product_type)
                     prev_prdtyp
            FROM tab1 a
                 RIGHT JOIN
                 tab2 b
                     ON     a.consignee_id = b.consignee_id
                        where b.term_date is null
                                    AND a.item_no = b.item_no
                        AND a.week_day = b.week_day
        ORDER BY id, wkday);


enclosing two spread sheets
What I get and what is required
sample.xls
sample-data-required.xls
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
anumoses, could you use the code button when posting lengthy code please?
it's often helpful as it provides line numbers to refer to, plus the amount of vertical scrolling is substantially reduced. It's also much simpler for selecting the code to study.

(and they are especially good for the mobile version of this question)
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
using an INNER JOIN produced the required result I believe
NAME            WKDAY PRD_TYP ATTRIB_GRP ATTRIB_COD
EDWARD HINES    FRI    LPHER    SP        IR
(null)          MON    LPHER    SP        IR
(null)          SAT    LPHER    SP        IR
(null)          SUN    LPHER    SP        IR
(null)          THU    LPHER    SP        IR
(null)          TUE    LPHER    SP        IR
(null)          WED    LPHER    SP        IR

Open in new window

see this at http://sqlfiddle.com/#!4/12a49/13
SELECT CASE 
		WHEN id = prev_id
			THEN NULL
		ELSE (
				SELECT NAME
				FROM consignee_id c
				WHERE c.consignee_id = id
				)
		END NAME
	, CASE 
		WHEN id = prev_id
			AND wkday = prev_wkday
			THEN NULL
		ELSE wkday
		END wkday
	, CASE 
		WHEN id = prev_id
			AND wkday = prev_wkday
			AND prd_typ = prev_prdtyp
			THEN NULL
		ELSE prd_typ
		END prd_typ
	, attrib_grp
	, attrib_cod
FROM (
      SELECT
            b.consignee_id id
          , b.week_day wkday
          , b.product_type prd_typ
          , a.attrib_group attrib_grp
          , a.attrib_code attrib_cod
          , LAG(b.consignee_id) OVER (ORDER BY b.consignee_id, b.week_day) prev_id
          , LAG(b.week_day)     OVER (ORDER BY b.consignee_id, b.week_day) prev_wkday
          , LAG(b.product_type) OVER (ORDER BY b.consignee_id, b.week_day, b.product_type) prev_prdtyp
      FROM tab2 b
      INNER JOIN tab1 a ON a.consignee_id = b.consignee_id
          AND a.item_no = b.item_no
          AND a.week_day = b.week_day
	)
ORDER BY
  id
, wkday
;

Open in new window

0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
I want the data as per the sample data required xls file that is attached
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
if you're changing requirements please open a new question
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
oh dear - sorry - I confused the spreadsheets it seems ...  so my comment above is quite wrong
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
I did not change requirements.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
I thought http:#a39223106 answered the original question.


Everything else is additional/changed requirements.

is there a problem with http:#a39223106  or is this done?
0
 
LVL 6

Author Closing Comment

by:anumoses
Comment Utility
thanks
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
>>Both the queries work fast and very perfectly.
@sdstuber - apologies
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
on the proviso you use code block in future: (please?)
http://sqlfiddle.com/#!4/12a49/35
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now