Need Help On write Oracle SQL

Hi Experts,
I need a help on build a report by using the SQL.
I have a two tables
1) Document table
2) Fields table

The relation between these tables are
documentid in both the tables..

Basically I need the SQL to get the report as shown in the spread sheet. ( Attached excel sheet)

Thank you
Regards,
KumarTest.xlsx
PRAVEEN TAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewCommented:
Please share what columns exist in which table...


»bp
0
slightwv (䄆 Netminder) Commented:
I agree that it is impossible to answer without more information.

Here is a guess:
select
	Vendor_Name,
	d.DocumenID,
	Invoice_Name
	Invoice_Name_Confidence
	Invoice_Date
	Invoice_date_Confidence
	Invoice_Name_Confidence+Invoice_date_Confidence as Total
	(Invoice_Name_Confidence+Invoice_date_Confidence)/2 myAVG
from documents d join fields f on d.documentid=f.documentid
/

Open in new window


I don't think my Total column is correct given your numbers but you get the idea.  I also don't understand the Total-Confidence column.  My AVG example is missing the Total Column because of the above statement that I don't think my guess is correct.

Anyway, it's an example loosely based on your data.
0
PRAVEEN TAuthor Commented:
Hi,

Thank you for the reply.

I have attached the excel sheet.
Document - table have a one column - 1) documentID
Field - Table have following columns
1)DocumentID
2)Field
3)Value
4)Confidence

Please let me know if any questions ..

Regards,
Kumar
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
>>Please let me know if any questions ..

Please provide sample raw data from the two tables and expected results from your sample data.

Are you saying you have a field column containing a string like 'invoice_name' and it's value is 3333, a string 'vendor_name' with value 'Xyz Tech'?

If so, yuck.  Not really a Relational Design, is it.  Not sure I would pay for Oracle RDBMS and not leverage the 'R'elational part of it.
0
slightwv (䄆 Netminder) Commented:
I'll let you do the confidence and AVG math.  I gave you the basics in the post above.

Here is an example of turning key-value pairs into columns.

If you can change the design, I would strongly encourage you to do so!

/*
create table documents(
	DocumentID number
);

insert into documents values(1);
insert into documents values(2);
insert into documents values(3);
commit;

create table fields(
	DocumentID number,
	Field varchar2(10),
	Value  varchar2(10),
	Confidence number
);


insert into fields values(1,'Vendor','Bob',10);
insert into fields values(2,'Vendor','Fred',10);
insert into fields values(1,'Invoice','3333',10);
commit;
*/


select  d.documentid, vendor_name, invoice_name from (
	select documentid,
		max(case when field='Vendor' then value end) vendor_name,
		max(case when field='Invoice' then value end) invoice_name
	from fields
	group by documentid
) f
join documents d on f.documentid=d.documentid
/

Open in new window

0
PRAVEEN TAuthor Commented:
Hi
Thank you for the reply.

I have given the sample data only.

Are you saying you have a field column containing a string like 'invoice_name' and it's value is 3333, a string 'vendor_name' with value 'Xyz Tech'?
Sorry.. it is invoice_number
'vendor_name' with value 'Xyz Tech' - it's sample value only..

Please let me know if you have any questions..

Regards,
Kumar
0
slightwv (䄆 Netminder) Commented:
>>I have given the sample data only.

I understand that but again, what information you have provided isn't enough to help us understand the problem.

If the test case I posted above isn't right, please change it or add to it.  Then post the expected results based on the actual test case.
0
PRAVEEN TAuthor Commented:
Hi,
Can we use the pivot to convert the row to column?

Regards,
0
slightwv (䄆 Netminder) Commented:
Pretty sure you cannot use pivot with unknown key/pair values.

Now if you hard-code the values to pivot on then sure but that defaults the perceived flexibility you have with unknown key-pair values in your design.  Although off the top of my head, I'm not sure how.  You need to aggregate columns to use pivot.
0
slightwv (䄆 Netminder) Commented:
It wasn't that bad.  You still need to hard-code the values but here is the pivot example using the test case I posted above:
select * from (
	select documentid, field, value from fields
)
pivot (max(value) for field in ('Vendor' vendor_name, 'Invoice' invoice_name))
/

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle SQL Developer

From novice to tech pro — start learning today.