Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to I fix query to get rid of error ORA-00923: FROM keyword not found where expected

Posted on 2011-09-23
6
Medium Priority
?
514 Views
Last Modified: 2012-05-12
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?
0
Comment
Question by:sikyala
[X]
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
  • 3
  • 2
6 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 36588188
Count is a function

Select count(1), to_char(publication_date, 'DD-MON-YYYY') a from...
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36588214
there is no need to convert the date if you're going to count it, just count the date as is

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;

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36588223
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-MON-YYYY'))  will also count NOT_NULL values only, so the extra conversion isn't necessary
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36588277
I assumed they wanted to count the days not the time portion which is why I moved the to_char outside the count.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1000 total points
ID: 36588327
ah, that changes the group by then too
and makes the order by tricky since the the dates will be strings

Select count(*) as count, to_char(publication_date,'dd-MON-yyyy') publication_date from bib_holdings group by to_char(publication_date,'dd-MON-yyyy')
order by to_char(publication_date,'dd-MON-yyyy')   -- this probably the wrong order by

alternately  use trunc to count by day

Select count(*) as count, trunc(publication_date) publication_date
from bib_holdings
group by trunc(publication_date)
order by trunc(publication_date)
0
 

Author Closing Comment

by:sikyala
ID: 36588352
Thanks it worked
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to recover a database from a user managed backup
In a previous video, we went over how to export a DynamoDB table into Amazon S3.  In this video, we show how to load the export from S3 into a DynamoDB table.
Suggested Courses

715 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