oracle query

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
LVL 6
anumosesAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
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
 
sdstuberCommented:
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
 
sdstuberCommented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
anumosesAuthor Commented:
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
 
anumosesAuthor Commented:
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
 
anumosesAuthor Commented:
Dont worry I got the query. Added the term date and qty >0

Thanks,
0
 
anumosesAuthor Commented:
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
 
PortletPaulfreelancerCommented:
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
 
PortletPaulfreelancerCommented:
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
 
anumosesAuthor Commented:
I want the data as per the sample data required xls file that is attached
0
 
sdstuberCommented:
if you're changing requirements please open a new question
0
 
PortletPaulfreelancerCommented:
oh dear - sorry - I confused the spreadsheets it seems ...  so my comment above is quite wrong
0
 
anumosesAuthor Commented:
I did not change requirements.
0
 
sdstuberCommented:
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
 
anumosesAuthor Commented:
thanks
0
 
PortletPaulfreelancerCommented:
>>Both the queries work fast and very perfectly.
@sdstuber - apologies
0
 
PortletPaulfreelancerCommented:
on the proviso you use code block in future: (please?)
http://sqlfiddle.com/#!4/12a49/35
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.