sikyala
asked on
How to I fix query to get rid of error ORA-00923: FROM keyword not found where expected
when I execute the following query:
Select count to_char(publication_date, 'DD-MON-YYYY') as count, publication_date from bib_holdings group by publication_date order by publication_date;
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
What is wrong with this query?
Select count to_char(publication_date, 'DD-MON-YYYY') as count, publication_date from bib_holdings group by publication_date order by publication_date;
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
What is wrong with this query?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
note count(*) and count(1) are equivalent, count(*) is recommended practice by oracle
internally it will rewrite it as count(*) anyway
the difference between count(*) and count(publication_date) is the handling of NULL values.
count(*) will count rows, regardless of whether they have nulls or not
count(publication_date) will count NOT-NULL values only
count(to_char(publication_ date,'DD-M ON-YYYY')) will also count NOT_NULL values only, so the extra conversion isn't necessary
internally it will rewrite it as count(*) anyway
the difference between count(*) and count(publication_date) is the handling of NULL values.
count(*) will count rows, regardless of whether they have nulls or not
count(publication_date) will count NOT-NULL values only
count(to_char(publication_
I assumed they wanted to count the days not the time portion which is why I moved the to_char outside the count.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks it worked
Select count(publication_date) as count, publication_date from bib_holdings group by publication_date order by publication_date;
or
Select count(*) as count, publication_date from bib_holdings group by publication_date order by publication_date;