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
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
502 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
  • 3
  • 2
6 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 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
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.

 
LVL 76

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 250 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

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.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

792 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