-
Query re-write
Hi all,
I have this query:
select count(b.vrm) from plateimage b
where b.capturedate < trunc(sysdate-10)
and not exists
(select vrm from hotmatch h where b.vrm = h.vrm and datecreated < trunc(sysdate-10))
This basically gets a count of all records (vrms) that are older than are certain date and not part of a corresponding table. Now, as can be seen this gets the total count of vrms. If i wanted to rewrite this so that it reports on the total no of vrms per day before the cut off how would i do that? Any help will be highly appreciated.
Thanks in advance,
Chucks
-
Hi
Can you give us the create table statements ,insert statements and the sample output maybe we can work on a solution
regards
Hrishy
-
Hi Hrishy,
This is a desc on the tables:
PHP Code:
SQL> desc plateimage;
Name Null? Type
----------------------------------------- -------- ----------------------------
URN NOT NULL NUMBER(19)
CAPTUREID NUMBER(19)
RAWDATA LONG RAW
BLOB BLOB
CAPTUREDATE DATE
CAPTUREKEY VARCHAR2(14)
VRM VARCHAR2(20)
INTELHANDLINGCODETYPEID NUMBER(19)
INTELINTELLIGENCEGRADETYPEID NUMBER(19)
INTELSOURCEGRADETYPEID NUMBER(19)
GPMSMARKINGID NUMBER(19)
WEEDDATE DATE
DELETED NUMBER(1)
PHP Code:
SQL> desc hotmatch;
Name Null? Type
----------------------------------------- -------- ----------------------------
URN NOT NULL NUMBER(19)
VRM VARCHAR2(20)
STATUS VARCHAR2(20)
DATECREATED DATE
CAPTURE RAW(2000)
VEHICLE VARCHAR2(255)
HOTLISTENTRY LONG RAW
OVERVIEWIMAGE NUMBER(19)
PLATEIMAGE NUMBER(19)
PRIORITY NUMBER(10)
CAPTUREID NUMBER(19)
HOTLISTNAME VARCHAR2(100)
LATESTPIRCODE VARCHAR2(3)
INTELHANDLINGCODETYPEID NUMBER(19)
INTELINTELLIGENCEGRADETYPEID NUMBER(19)
INTELSOURCEGRADETYPEID NUMBER(19)
GPMSMARKINGID NUMBER(19)
WEEDDATE DATE
DELETED NUMBER(1)
I dont have the insert statements to be honest. As for the sample output, the current query jus outputs the no of records b4 the the cutoff point. The new query could possibly show (with the capturedate from the first table):
count(vrm), capturedate
----------- ------------
28 19-JUL-05
19 20-JUL-05
Please let me know if u need any more info?
Thanks again
Chucks
-
Hi
Is this what you want then ?
Code:
select count(b.vrm) ,b.capturedate
from plateimage b
where b.capturedate < trunc(sysdate-10)
and not exists
(select vrm
from hotmatch h
where b.vrm = h.vrm
and datecreated < trunc(sysdate-10)
)
group by b.capturedate
regards
Hrishy
-
Hi Hrishy,
That is part what i want ( i got there as well). This is the output with what u have provided:
PHP Code:
COUNT(B.VRM) CAPTUREDA
------------ ---------
1 18-JUL-05
1 18-JUL-05
1 18-JUL-05
1 18-JUL-05
1 18-JUL-05
1 18-JUL-05
1 18-JUL-05
1 18-JUL-05
1 18-JUL-05
But ideally i would like to have a more compact version of that so that is says (like a sum of all counts for each day):
PHP Code:
COUNT(B.VRM) CAPTUREDA
------------ ---------
9 18-JUL-05
Thanks in advance,
Chucks
-
Hi
Code:
select count(b.vrm) ,b.capturedate
from plateimage b
where b.capturedate < trunc(sysdate-10)
and not exists
(select vrm
from hotmatch h
where b.vrm = h.vrm
and datecreated < trunc(sysdate-10)
)
group by trunc(b.capturedate)
-
Hi Hrishy,
I got this error when i ran what you provided:
Code:
select count(b.vrm),b.capturedate from plateimage b
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
Any ideas?
Thanks Chucks
-
just match group by expression with offending selected item.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Yes, but that works without the trunc. Once you put the trunc the query generates the error.
-
mmhhhh... you are not listening, try this
Code:
select count(b.vrm) ,trunc(b.capturedate)
from plateimage b
where b.capturedate < trunc(sysdate-10)
and not exists
(select vrm
from hotmatch h
where b.vrm = h.vrm
and datecreated < trunc(sysdate-10)
)
group by trunc(b.capturedate)
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|