• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 374
  • Last Modified:

ORACLE SQL Help Needed with EXTRACT and PARTION and GROUP BY

Just learned about EXTRACT and I am trying to put together a query of the number of a particular file type stored each month.

select extract(month from datestored),  extract(YEAR from datestored) , Count(unique_id) cnt
OVER (PARTION BY 1,2) from
emrcontent.indexdata@prod where substr(filename,-3) = 'OID'
RETURNS ERROR...

Error starting at line 1 in command:
select extract(month from datestored),  extract(YEAR from datestored) , Count(unique_id) cnt  
OVER (PARTION BY 1,2) from
emrcontent.indexdata@prod where substr(filename,-3) = 'OID' and rownum < 10  
Error at Command Line:1 Column:94
Error report:
SQL Error: ORA-00923: FROM keyword not found where expected
00923. 00000 -  "FROM keyword not found where expected"

Any help appreciated. Thanks in advance.


0
codefinger
Asked:
codefinger
  • 4
  • 3
  • 3
  • +1
2 Solutions
 
elimesikaCommented:
move OVER (PARTION BY 1,2)  to the end ...
0
 
Kevin CrossChief Technology OfficerCommented:
Try like this:
SELECT EXTRACT(month FROM datestored) AS "month"
     , EXTRACT(year FROM datestored) AS "year" 
	 , COUNT(unique_id) OVER (PARTITION BY 1,2) AS "cnt"
FROM "emrcontent"."indexdata@prod" 
WHERE SUBSTR(filename, -3) = 'OID' 
;

Open in new window


I would have to test in Oracle, but in MS SQL OVER(PARTITION BY 1, 2) treats those as literals and not column indexes, so it pretty much the same results as OVER(). Other than that, I think you just had the alias "cnt" in the wrong spot.
0
 
Kevin CrossChief Technology OfficerCommented:
I just did some tests in Oracle and at least in 10g, the behavior is the same as MS SQL; therefore, you may want to relook at the OVER(PARTITION BY 1,2). I could also duplicate exact error message by misplacing the alias. I additionally got that error if I used wildcard (*) without specifying table, then did a COUNT() OVER() even if I had the alias in the right place. So just some additional causes to help you in the future, but think the culprit was the "Count(unique_id) cnt OVER (PARTION BY 1,2)" in this case.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
slightwv (䄆 Netminder) Commented:
Why use extract and OVER at all?

select to_char(datestored,'MM') month, to_char(datestored,'YYYY') year, count(*)
from emrcontent.indexdata@prod where substr(filename,-3) = 'OID'
group by to_char(datestored,'MM'), to_char(datestored,'YYYY');
0
 
Kevin CrossChief Technology OfficerCommented:
I was wondering that same thing too last night Steve and did not get to post back. It does appear to be an attempt to get COUNT() by Year/Month combination. At least that is what my brain started to tell me when I figured 1, 2 where trying to refer to the EXTRACT() columns without having to retype it all out, but as you showed that IS what is required, i.e., you have to repeat the formula in the GROUP BY. Using OVER() way works better if what is needed is detail rows in addition to the aggregate over a specific grouping. My gut told me this scenario is probably the simpler one shown here -- http:#36924051
0
 
codefingerAuthor Commented:
Guys, thanks for your help so far....this is what I want my report to look like:

MONTH  YEAR  COUNT
-----------------------------------
12          2004      120
01          2005      145
02          2005      185
03          2005      262

I may be on completely the wrong track, so I am open to more suggestions.  What I have received so far does not quite do it, clearly because I did not provide enough detail in the original question.  I apologize for that and I hope this comment adequately corrects that over-site...

0
 
Kevin CrossChief Technology OfficerCommented:
Can you share what is different from what you want and what you get with slightwv's query...that may help us understand what is going on; however, aside from alias on count, his query should do exactly what you showed.
0
 
slightwv (䄆 Netminder) Commented:
Sample input data would help a lot.
0
 
codefingerAuthor Commented:
Actually, mwvsiat IS on target.  I made some mistake on my end when I tried first tried his query (not sure what), but it seems to be working quite well this morning once I added an order by clause (and a date limitation since there are 12 million records in this database and I am just testing now):

select to_char(datestored,'MM') month, to_char(datestored,'YYYY') year, count(*)
from emrcontent.indexdata@prod where substr(filename,-3) = 'OID' and datestored < to_date('12/31/2006','MM/DD/YYYY')
group by to_char(datestored,'MM'), to_char(datestored,'YYYY')
order by year,month;

....gives me:


MONTH YEAR COUNT(*)              
----- ---- ----------------------
12    2004 133                    
01    2005 231                    
02    2005 257                    
03    2005 908                    
04    2005 626                    
05    2005 1233

However, these counts do not quite agree with what I get when I run a query that looks like this:
SELECT COUNT(*) FROM EMRCONTENT.INDEXDATA WHERE SUBSTR(FILENAME,-3) = 'OID'  AND DATESTORED >= TO_DATE(:xfromdate,'MM/DD/YYYY') AND DATESTORED <= TO_DATE(:xtodate,'MM/DD/YYYY')

...and use beginning and end of the month as the from and to dates.

My eventual goal is to convert each group of files based on dates supplied by the user in a textbox, I want to be sure none are missed.   The datestored value is an Oracle timedate stamp.

Thanks again for your help so far.  I appreciate the help and the patience.









0
 
slightwv (䄆 Netminder) Commented:
The to_date as written in the second query defaults the time piece to: 00:00:00

Any records for thw last day of the month aftwr midnight will not be counted.  You are missing a days records.
0
 
codefingerAuthor Commented:
This was supposed to be the Assisted Solution:

slightwv:
------------------
The to_date as written in the second query defaults the time piece to: 00:00:00
Any records for thw last day of the month aftwr midnight will not be counted.  You are missing a days records.
...

and in one comment I said
--------------------------------------
Actually, mwvsiat IS on target  

 -- that was a typo, I meant to say
------------------------------------------
slightwv IS on target,
 
I was actually agreeing with mwvsiat.

0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now