?
Solved

oracle query

Posted on 2013-06-05
17
Medium Priority
?
423 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 74

Expert Comment

by:sdstuber
ID: 39223069
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 74

Expert Comment

by:sdstuber
ID: 39223106
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
ID: 39223124
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 39223162
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
ID: 39223261
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
ID: 39223376
Dont worry I got the query. Added the term date and qty >0

Thanks,
0
 
LVL 6

Author Comment

by:anumoses
ID: 39223454
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 49

Expert Comment

by:PortletPaul
ID: 39224610
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39224639
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
ID: 39225355
I want the data as per the sample data required xls file that is attached
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39225387
if you're changing requirements please open a new question
0
 
LVL 49

Expert Comment

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

Author Comment

by:anumoses
ID: 39225410
I did not change requirements.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39225434
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
ID: 39225509
thanks
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39225536
>>Both the queries work fast and very perfectly.
@sdstuber - apologies
0
 
LVL 49

Expert Comment

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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month14 days, 15 hours left to enroll

840 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