DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Query re-write

  1. #1
    Join Date
    Dec 2001
    Posts
    337

    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

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Can you give us the create table statements ,insert statements and the sample output maybe we can work on a solution


    regards
    Hrishy

  3. #3
    Join Date
    Dec 2001
    Posts
    337
    Hi Hrishy,

    This is a desc on the tables:

    PHP Code:
    SQLdesc 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:
    SQLdesc 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

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    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

  5. #5
    Join Date
    Dec 2001
    Posts
    337
    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.VRMCAPTUREDA
    ------------ ---------
               
    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.VRMCAPTUREDA
    ------------ ---------
    9  18-JUL-05 
    Thanks in advance,
    Chucks

  6. #6
    Join Date
    Jan 2001
    Posts
    2,828
    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)

  7. #7
    Join Date
    Dec 2001
    Posts
    337
    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

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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.

  9. #9
    Join Date
    Dec 2001
    Posts
    337
    Yes, but that works without the trunc. Once you put the trunc the query generates the error.

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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
  •  


Click Here to Expand Forum to Full Width