Solved

Get the first comment added oracle 11g

Posted on 2014-09-03
26
301 Views
Last Modified: 2014-09-12
Hi ,

I have two tables namely , Customer_invoice , customer_comments .  customer_invoice contains the invoice no and customer_id   and customer_comments has comment_id, invoice_id,comment_desc , customer1_id, customer2_id, customer3_id. I know this is an poor design , but now we cannot change the structure of customer_comments table.  

Data is as below
Customer_Invoice
Invoice_id  Customer_id date
1                  101                   01/01/2014
1                  102                   01/01/2014
1                  103                   01/01/2014

customer_comments
comment_id, invoice_id,comment_desc , customer1_id, customer2_id, customer3_id comment_date
1                      1                       hello1                 101                   102               NULL                  01/01/2014
2                       1                      hello12                  101                102                 NULL                   02/01/2014
3                       1                       hello1 23               101                   102                  NULL                 01/01/2014
4                       1                      hello123 4             101                  102              NULL                      02/01/2014

The way we insert the data in customer_comments table is , initially when we select customer1_id , and then add an comment , the first column that is customer1_id is populated and the comment_desc is stored with the comment date   . The same thing follows for the second comment added for customer_id=101. But when we add another comment for customer_id=102 , for the same invoice number , then the customer2_id is populated and its comment details is stored.

I need the below output:

Invoice_no , comment_date , customer_id
1                     02/01/2014        101          
1                     02/01/2014         102

 i need to get the latest comment for an invoice no , added by an customer.
0
Comment
Question by:sam_2012
  • 9
  • 6
  • 5
  • +2
26 Comments
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
I don't see how the customer1 and customer2 columns impact the results.


select ci.invoice_id, max(cc.comment_date), ci.customer_id
from customer_invoice ci, customer_comments cc
where ci.invoice_id = cc.invoice_id
group by ci.invoice_id, ci.customer_id


if this doesn't work, please expand your sample data and expected results to show the interactions you're looking for.  You can try to explain in words if you want, but showing data is almost always a far, far better method.
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
I think the above just needs a "group by ci.invoice_id, ci.customer_id" clause.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
already there - you must have caught me editing but posted after I was done
0
 
LVL 20

Expert Comment

by:flow01
Comment Utility
From your story I would expect customer2_id to be null for comment_id 1 and 2  ?
En what happens if customer1 gives comment 1, then customer2 gives comment 2 and then customer1 gives a comment ? customer3 gets the same id-value as customer ?
If not how to detect if customer 1 or customer 2 has given the comment ?
0
 
LVL 20

Expert Comment

by:flow01
Comment Utility
Assuming every change of commenting customer results in population of the next customer_id:
select invoice_id, max(comment_date), customer_id
from
(
select comment_id, invoice_id, comment_desc, nvl(customer3_id,nvl(customer2_id,customer1_id)) customer_id, comment_date
from customer_comments
) s
group by s.invoice_id, s.customer_id
order by s.invoice_id, s.customer_id
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
>>already there<<
I always seem to be a little late :-)
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
You say that you "cannot change the structure of customer_comments table".  But I say that this table design will cause problems for as long as you (or your organization) uses this application.  If the organization doesn't mind paying for the trouble that this table design causes, then that statement about not changing the design of this table is true.  I suspect that with a view and some "instead of" triggers on the view, you could actually change the table design *AND* not break anything in the application *AND* have a more normalized data structure that would work better long-term.

You will have to give us some more sample data, since the sample data you posted here does not match your description of what the data looks like in the customer_comments table (there are no rows with a null value in customer2_id).
0
 

Author Comment

by:sam_2012
Comment Utility
The way it works is , for an invoice , any customer can add an comment , and tag a maximum of 2 other customers to it. I.e I have an invoice , and I log into the application as customer 101 and add an comment  to the invoice and tag customer 102 and 103 to it . Then in the customer_comment table we will have an row as below
customer_comments
 comment_id, invoice_id,comment_desc , customer1_id, customer2_id, customer3_id comment_date
 1                      1                       hello1                 101                   102               103                  01/01/2014

Here , customer1_id=101 becasue , customer 101 added the comment and then tagged customers 101 and 103 to it.
Now when iam fetching the invoice details for invoice 1 , i need to fetch the latest comment added for each customer_id in the invoice . when iam processing the first row , for example
Invoice_id  Customer_id date
 1                  101                   01/01/2014

For invoice no 1 , check in customer_comments , whether customer_id 101 is tagged to any comment for that invoice no , if it is tagged get the latest comment date.

Output:
Invoice_no , comment_date , customer_id
1                     02/01/2014        101          

Similarly, when I process second row for the invoice 1 , see whether any comment is added by the customer 102 in all the 3 fields namely customer1_id , customer2_id , customer3_id , if present , then pick the maximum comment date.


Output:
Invoice_no , comment_date , customer_id
1                     02/01/2014        102
0
 
LVL 20

Expert Comment

by:flow01
Comment Utility
And if customer 101 adds another comment
you will find 2 records with 102 in it ,
1                      1                       hello1                 101                   102               103                  01/01/2014
2                      1                       hello12               101                   102               103                  01/02/2014
for comment 1 since 101 the first mentiod you know is was a comment given by 101
But how do you know if comment 2 was given by 101 or 102 ?
0
 

Author Comment

by:sam_2012
Comment Utility
it means the customer has access to all the comments he is taged.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
So, are you saying you don't really care who made the comment?
You only care about who is tagged?
Which might be the commenter or some other user?

If you do care then please extend your example data with both input and expected output.

 make sure the data is sufficient to capture all of your scenarios.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
How about this...

c1 comments
c1 comments again and tags c3
c2 comments
c2 comments and tags c3
c1 comments and tags c2
c3 comments and tags c1
c3 comments and tags c1
c3 comments and tags c2
c1 comments and tags c2 and c3
c1 comments
c2 comments
c2 comments and tags c3 and c1
c2 comments and tags c1 and c3
c3 comments
c3 comments
c3 comments and tags c1
c2 comments and tags c1
c3 comments and tags c1
c3 comments and tags c1
c3 comments and tags c2
c3 comments and tags c2 and c1
c3 comments and tags c2
c3 comments and tags c2 and c1
c3 comments

assuming all comments are for the same invoice and are processed in the order specified what will that look like in your data and what would the expected results be?  

I included some duplicate operations intentionally because I don't know what that will look like in your data.
0
 

Author Comment

by:sam_2012
Comment Utility
Below is the data that needs to be populated :
 
 c1 comments again and tags c3
customer_comments
comment_id, invoice_id,comment_desc , customer1_id, customer2_id, customer3_id comment_date
 2                1         hello1         101           103        NULL          01/01/2014 12:12:12

 c2 comments
customer_comments
comment_id, invoice_id,comment_desc , customer1_id, customer2_id, customer3_id comment_date
 3                1         hello1         102           NULL        NULL          01/01/2014 12:11:12

 c2 comments and tags c3
customer_comments
comment_id, invoice_id,comment_desc , customer1_id, customer2_id, customer3_id comment_date
 4                1         hello1         102           103        NULL          01/01/2014 12:12:13


 c1 comments and tags c2
customer_comments
comment_id, invoice_id,comment_desc , customer1_id, customer2_id, customer3_id comment_date
 5                1         hello1         101           102        NULL          01/01/2014 12:12:14


 c3 comments and tags c1
 

customer_comments
comment_id, invoice_id,comment_desc , customer1_id, customer2_id, customer3_id comment_date
 6                1         hello1         103           101        NULL          01/01/2014 12:12:14

 
 
 c3 comments and tags c1
 
customer_comments
comment_id, invoice_id,comment_desc , customer1_id, customer2_id, customer3_id comment_date
 7                1         hello1         103           101        NULL          01/01/2014 12:12:15

 
 
 c3 comments and tags c2
 
customer_comments
comment_id, invoice_id,comment_desc , customer1_id, customer2_id, customer3_id comment_date
 8                1         hello1         103           102        NULL          01/01/2014 12:12:16

 
 c1 comments and tags c2 and c3

customer_comments
comment_id, invoice_id,comment_desc , customer1_id, customer2_id, customer3_id comment_date
 9                1         hello1         101           102        103          01/01/2014 12:12:16
 
 
 
 c1 comments
 
customer_comments
comment_id, invoice_id,comment_desc , customer1_id, customer2_id, customer3_id comment_date
 10                1         hello1         101           NULL        NULL          01/01/2014 12:12:16

 c2 comments
 
customer_comments
comment_id, invoice_id,comment_desc , customer1_id, customer2_id, customer3_id comment_date
 11               1         hello1         102           NULL        NULL          01/01/2014 12:12:16

 
 c2 comments and tags c3 and c1
 
customer_comments
comment_id, invoice_id,comment_desc , customer1_id, customer2_id, customer3_id comment_date
 12              1         hello1         102           103        101          01/01/2014 12:12:17

 
 c2 comments and tags c1 and c3

customer_comments
comment_id, invoice_id,comment_desc , customer1_id, customer2_id, customer3_id comment_date
 13              1         hello1         102           101        103          01/01/2014 12:12:18
 
 
 c3 comments

customer_comments
comment_id, invoice_id,comment_desc , customer1_id, customer2_id, customer3_id comment_date
 14              1         hello1         103           NULL        NULL          01/01/2014 12:12:18
 
 c3 comments

customer_comments
comment_id, invoice_id,comment_desc , customer1_id, customer2_id, customer3_id comment_date
 15              1         hello1         103           NULL        NULL          01/01/2014 12:12:19
 
 
 c3 comments and tags c1
 

customer_comments
comment_id, invoice_id,comment_desc , customer1_id, customer2_id, customer3_id comment_date
 16              1         hello1         103           101        NULL          01/01/2014 12:12:19
 
 c2 comments and tags c1


customer_comments
comment_id, invoice_id,comment_desc , customer1_id, customer2_id, customer3_id comment_date
 17            1         hello1         102           101        NULL          01/01/2014 12:12:18
 
 c3 comments and tags c1

customer_comments
comment_id, invoice_id,comment_desc , customer1_id, customer2_id, customer3_id comment_date
 18              1         hello1         103           101        NULL          01/01/2014 12:12:18
 
 
 c3 comments and tags c1


customer_comments
comment_id, invoice_id,comment_desc , customer1_id, customer2_id, customer3_id comment_date
 19              1         hello1         103           101        NULL          01/01/2014 12:12:19
 
 c3 comments and tags c2
 

customer_comments
comment_id, invoice_id,comment_desc , customer1_id, customer2_id, customer3_id comment_date
 20              1         hello1         103           102        NULL          01/01/2014 12:12:18
 
 c3 comments and tags c2 and c1

customer_comments
comment_id, invoice_id,comment_desc , customer1_id, customer2_id, customer3_id comment_date
 21              1         hello1         103           102        101          01/01/2014 12:12:18
 
 
 c3 comments and tags c2
 

customer_comments
comment_id, invoice_id,comment_desc , customer1_id, customer2_id, customer3_id comment_date
 22              1         hello1         103           102       NULL          01/01/2014 12:12:18
 
 c3 comments and tags c2 and c1

customer_comments
comment_id, invoice_id,comment_desc , customer1_id, customer2_id, customer3_id comment_date
 23              1         hello1         103           102        101         01/01/2014 12:12:18
 

 
 c3 comments

 
customer_comments
comment_id, invoice_id,comment_desc , customer1_id, customer2_id, customer3_id comment_date
 24              1         hello1         103           NULL        NULL          01/01/2014 12:12:20
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.

 

Author Comment

by:sam_2012
Comment Utility
Expected Output:
Invoice Number , the highest or latest comment for an customer_id  table.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
My suggestion is to create a normalized view of the customer_comments data like this:
create or replace view customer_comments_vw
(comment_id, invoice_id, comment_desc, customer_id, comment_date, id_column)
as select comment_id, invoice_id, comment_desc, customer1_id, comment_date, 1
 from customer_comments
 where customer1_id is not null
union all
 select comment_id, invoice_id, comment_desc, customer1_id, comment_date, 2
 from customer_comments
 where customer2_id is not null
union all
 select comment_id, invoice_id, comment_desc, customer1_id, comment_date, 3
 from customer_comments
 where customer3_id is not null;

Note: the "id_column" is not required, but it will be useful if you ever want to query separately or differently for records where the customer_id is actually in a particular column.

This view should make your query quite simple to write.  Something like this should be close to what you are looking for:
Select invoice_no, max(comment_date) comment_date, customer_id
from customer_comments_vw
group by invoice_no, customer_id
order by invoice_no, customer_id;
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
Oops!  I missed one detail.  After the first "union all" the "select..." line should have; "customer2_id", and after the second "union all" the "select..." line should have; "customer3_id".
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
Expected Output:
Invoice Number , the highest or latest comment for an customer_id  table.

That's not helpful, what should the output look like - show data.
0
 
LVL 20

Expert Comment

by:flow01
Comment Utility
with customer_comments
as
(
select 1 comment_id,  1 invoice_id, ' hello1   ' comment_desc,              101  customer1_id      ,           102   customer2_id,            NULL         customer3_id,  to_date('01/01/2014','dd/mm/yyyy') comment_date from dual union
select 2 comment_id,  1 invoice_id, 'hello12   ' comment_desc,               101 customer1_id      ,         102     customer2_id,            NULL         customer3_id,  to_date('02/01/2014','dd/mm/yyyy') comment_date from dual union
select 3 comment_id,  1 invoice_id, ' hello1 23' comment_desc,               101 customer1_id      ,            102  customer2_id,                NULL     customer3_id,  to_date('01/01/2014','dd/mm/yyyy') comment_date from dual union
select 4 comment_id,  1 invoice_id, 'hello123 4' comment_desc,             101   customer1_id      ,         102     customer2_id,           NULL          customer3_id,  to_date('02/01/2014','dd/mm/yyyy') comment_date from dual union
select 1 comment_id,  2 invoice_id, ' hello1   ' comment_desc,              101  customer1_id      ,           102   customer2_id,            NULL         customer3_id,  to_date('01/01/2014','dd/mm/yyyy') comment_date from dual union
select 2 comment_id,  2 invoice_id, 'hello12   ' comment_desc,               101 customer1_id      ,         102     customer2_id,           103           customer3_id,  to_date('02/01/2014','dd/mm/yyyy') comment_date from dual union
select 3 comment_id,  2 invoice_id, ' hello1 23' comment_desc,               101 customer1_id      ,            102  customer2_id,                NULL     customer3_id,  to_date('01/01/2014','dd/mm/yyyy') comment_date from dual union
select 4 comment_id,  2 invoice_id, 'hello123 4' comment_desc,             101   customer1_id      ,         102     customer2_id,           NULL          customer3_id,  to_date('02/01/2014','dd/mm/yyyy') comment_date from dual
)
select invoice_id, max(comment_date), customer_id
from
(
select * from customer_comments
unpivot
      ( customer_id                  
         for customer_id_type in (customer1_id, customer2_id,customer3_id)
       )    
)
group by invoice_id, customer_id
order by invoice_id, customer_id
0
 

Author Comment

by:sam_2012
Comment Utility
hi sdstuber,

Expected OutPut is :

Invoice No , customer_id , latest comment date

1            101     02-JAN-14
1            102     02-JAN-14
2            101    02-JAN-14
2            102   02-JAN-14
2            103   02-JAN-14.
 
Thanks flow1 , unpivot is an costly operation , is there any way for the query , if not I will accept it as an solution.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
I don't understand

2            101    02-JAN-14
2            102   02-JAN-14
2            103   02-JAN-14.

where does invoice_id 2 come from?  The sample data above only has 1 invoice


And, why are there no comments for 103 on invoice 1.  Also, the dates are wrong for all of the rows.  all of your data is for 1/1/2014.  How is the output for 02-JAN-14  ?

You missed one row in the sample data
c1 comments

based on the other data I assume that should have looked something like this...

1                1         hello1         101           NULL        NULL          01/01/2014 12:11:12


but even with that, your expected output seems to have almost no correlation to your input data - or at least not the input data you provided for me
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
Did you try the view and the simple query from the view that I had suggested?  I expect that to work fairly efficiently.

Here is the corrected "create view..." statement:

create or replace view customer_comments_vw
 (comment_id, invoice_id, comment_desc, customer_id, comment_date, id_column)
 as select comment_id, invoice_id, comment_desc, customer1_id, comment_date, 1
  from customer_comments
  where customer1_id is not null
 union all
  select comment_id, invoice_id, comment_desc, customer2_id, comment_date, 2
  from customer_comments
  where customer2_id is not null
 union all
  select comment_id, invoice_id, comment_desc, customer3_id, comment_date, 3
  from customer_comments
  where customer3_id is not null;
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
Comment Utility
If all you need is a cheap unpivot then try this...

  SELECT invoice_id,
         DECODE(n,  1, customer1_id,  2, customer2_id,  3, customer3_id) customer_id,
         MAX(comment_date)
    FROM customer_comments,
         (SELECT 1 n FROM DUAL
          UNION ALL
          SELECT 2 FROM DUAL
          UNION ALL
          SELECT 3 FROM DUAL)
   WHERE DECODE(n,  1, customer1_id,  2, customer2_id,  3, customer3_id) IS NOT NULL
GROUP BY invoice_id, DECODE(n,  1, customer1_id,  2, customer2_id,  3, customer3_id)
ORDER BY 1, 2;
0
 

Author Closing Comment

by:sam_2012
Comment Utility
awesome.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
OK, so that suggestion seems to work.  But, is it efficient?  How many records does your customer_comments table have now?  How many records do you expect it to have in a year or two (or more)?  Did you try the view that I had suggested?  I'm curious about which approach works most efficiently as the table gets more records.  It looks to me like the approach you accepted will cause a "full-table scan" of the customer_comments table.  Those are usually not good, if you care about performance.  If you have Oracle Exadata or all-flash storage hardware, or if this table will never have more than 100 records, then maybe this approach won't be a performance problem.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
all of the methods require a full scan of the customer_comments table since there are no conditions upon which to filter the table - other than nullability which (probably) doesn't help with a "is not null" condition.

At best, you might be able to get a fast-full scan of an index that covers invoice_id, customer_id, and comment_date

or, (unlikely but)  if most of your values are null so that it might possible to use other indexes
then simply add another where clause

And (customer1_id is not null or customer2_id is not null or customer3 is not null)

The efficiency in the accepted post is that the unpivot logic is very simple.  Oracle doesn't need to derive it via the UNPIVOT syntax (plus, it's backward compatible to versions before UNPIVOT)

Plus, it only needs to read the table (or index) one time, instead of 3 with unions.


I did test all of the versions using the extended sample data and the version I posted was efficient.  On larger data sets I'd expect the native unpivot to also scale well but not the union method.


In the worst case, using an additional where clause on mostly-null data, the optimizer could probably expand the OR conditions into unions but that would be a special case of data where that would be most efficient; but nice in that a single syntax should still support it
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
here's another way to look at it:

the reason I say the "mostly null" is not likely (maybe even impossible) is that at least one customer must be provided in order to get a comment.  - at least, as I read the description, and sample data seems to indicate.

So customer1_id will always be not null.  Thus "WHERE customer_id is not null" means FULL TABLE SCAN since that condition is true for every row, we don't want to use an index.

Since we're already going to be reading every row, then we definitely want to reduce other operations. The simple cartesian against dual gives an efficient means of scanning across all 3 columns to create an unpivot without extra io to the table to reread the 2nd and 3rd customers.  Note "unpivot" itself will also skip this extra io and, despite the asker's fears can be quite efficient.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

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

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
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 video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now