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
Solved

oracle query

Posted on 2013-06-05
17
398 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
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 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  â€¦
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

856 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