I need to create a view that lists all the vendor names and the average of month

Question

I need to create a view that lists all the vendor names and the average of months between the invoice dute date column and invoice date column. I also need to round the avg by to one decimal space. invoide due date and invoice date are both DATE data types. I created the view below. It compiles but when I select the view I get an error "ORA-01722 - Invalid number".I thik the issue is somewhere in my average function but not sure where. Please help.

Create view vendors_and_months as Select vendor_name, Round(Avg(to_number(INVOICE_DUE_DATE) + to_number(invoice_date)),1) as

Average_Months_Between from vendors join invoices on vendors.vendor_id = invoices.vendor_id

group by vendor_name

having Round(Avg(to_number(INVOICE_DUE_DATE) + to_number(invoice_date)),1) >= 1.5

order by Round(Avg(to_number(INVOICE_DUE_DATE) + to_number(invoice_date)),1) desc;

Explanation / Answer

"ORA-01722 - Invalid number" occurs in when the conversion to number failed.

your view creation seems to be correct,but there is a problem in to_number function, i.e. to_number(INVOICE_DUE_DATE) or to_number(invoice_date) or both.

To identify the error part, u can try running small queries like these:

1) create a table

for example:  

invoice date );

2) insert values (example, insert same value into the column INVOICE_DUE_DATE)

for example:

3) try the to_number function,because that is the part which is giving the error.

select to_number(INVOICE_DUE_DATE) from tablename;

and select to_number(invoice_date) from tablename;

for example :

select to_number(invoice) from vendor;

mostly,you will get an error ORA-01722 - Invalid number

now convert the date format to number format and then the round off can be done easily.

select sysdate - to_date('01112017','ddmmyyyy') as decimal_value from vendor;

Try this out...

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.