Solved

oracle query

Posted on 2013-06-05
17
402 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Accepted Solution

by:
sdstuber earned 500 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 48

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 48

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 48

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 48

Expert Comment

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

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DECLARATION OF CURSOR IS INCOMPLETE OR MALFORMED 5 44
Oracle Nested table uses ? 2 44
SQL query to select row with MAX date 7 41
Oracle SQL Developer - SubString 2 31
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

726 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