#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    5
    Rep Power
    0

    Question Help with SQLPLUS script timelapse


    Hi,
    I need to make a particular query on an Oracle 10g DB via SQLPLUS.

    I have a table structured this way:
    NAME - VALUE1 - VALUE2 - DATE

    This table is populated every day by a procedure that does a count on 2 other tables that should be identical (they are the master table and its snapshot on a daily Fast Refresh).
    A line is written by the procedure in the first table with name of the counted table (NAME) value of first count (VALUE1) value of second count (VALUE2) and DATE.

    I need to find a query that allows me to identify all those tables that have not been identical for the past 5/7 days. I can't do it by hand because everyday we have a refresh of over 20K tables.

    So to be extra clear I'll do a couple of examples:

    Case1:

    TabA and SnapA have not been identical for the past 5 days
    10 vs 9
    12 vs 10
    15 vs 14
    23 vs 20
    38 vs 29

    in this case I need to see this table on the query.

    Case2:
    TabB and SnapB have not been identical for the past 5 days apart from day 2

    10 vs 9
    12 vs 12
    15 vs 14
    23 vs 20
    38 vs 29

    I don't need this data because I need to be sure that the table and the snapshot are not aligned for at least 5 consecutive days.

    I hope my problem is clear . I'm not sure there is a way to solve it easily .

    Any help well be GREATLY appreciated .

    Phelit Leafwise
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    5
    Rep Power
    0

    Lightbulb


    I have found this query:

    select NAME, count(NAME) from USER.TABLE where
    (to_char(DATE, 'dd/mm/yyyy') > to_char(sysdate - 7,'dd/mm/yyyy') and VALUE1<>VALUE2)
    group by NAME order by NAME;

    It gives me a good result. But I need to get only the Count value that is > 7 and not all of them. So to pinpoint directly the tables with issues.

    Any help on how to put a limitation in the query above so to see only the counts > of 7?

    Thanks again!
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,777
    Rep Power
    348
    First: please use code tags to make your SQL readable (See http://forums.devshed.com/misc.php?do=bbcode#code for details)

    Second: please post in the relevant forum in the future (there is a dedicated Oracle forum here!)

    Third: there is no need to use to_char() on a DATE column.

    The condition: to_char(DATE, 'dd/mm/yyyy') > to_char(sysdate - 7,'dd/mm/yyyy') can simply be written as: DATE > sysdate - 7. But as Oracle's DATE datatype contains a time part as well, you need to get rid of that using trunc() (which "normalizes" the time to 00:00:00)

    (I hope you don't really have a column called "DATE"? It is not a very good habit to use reserved words for columns.)

    After having said all that, your actual query (including the "count > 7" part) should look like this:

    Code:
    select NAME, 
           count(NAME) as name_count
    from USER.TABLE 
    where trunc(DATE) > trunc(sysdate) - 7 
       and VALUE1<>VALUE2
    group by NAME order by NAME;
    having count(NAME) > 7
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    5
    Rep Power
    0
    Originally Posted by shammat
    First: please use code tags to make your SQL readable (See http://forums.devshed.com/misc.php?do=bbcode#code for details)

    Second: please post in the relevant forum in the future (there is a dedicated Oracle forum here!)

    Third: there is no need to use to_char() on a DATE column.

    The condition: to_char(DATE, 'dd/mm/yyyy') > to_char(sysdate - 7,'dd/mm/yyyy') can simply be written as: DATE > sysdate - 7. But as Oracle's DATE datatype contains a time part as well, you need to get rid of that using trunc() (which "normalizes" the time to 00:00:00)

    (I hope you don't really have a column called "DATE"? It is not a very good habit to use reserved words for columns.)

    After having said all that, your actual query (including the "count > 7" part) should look like this:

    Code:
    select NAME, 
           count(NAME) as name_count
    from USER.TABLE 
    where trunc(DATE) > trunc(sysdate) - 7 
       and VALUE1<>VALUE2
    group by NAME order by NAME;
    having count(NAME) > 7

    Sorry for not using code tags. My bad. And sorry for not using the right forum. I was writing an SQLPLUS statement. I thought it as a generic SQLPLUS statement and not Oracle specific. Again, my mistake!

    Thank you for the TRUNC advice. When I work with DATES I always get srangely confused and forget the easiest parts of coding . (the column is not called DATE no worry! )

    My only problem is that
    Code:
    having count(NAME) > 7
    I don't understand where to put it in the query. I tried as you wrote at the end of the query but it doesn't work. What am I doing wrong?

    Code:
    select nomesnap, count(nomesnap) as NUMERO
    from russor.controllarepliche 
    where trunc(dataconta) > trunc(sysdate) - 7 
    and righesnapceps<>righesnapsez 
    group by nomesnap order by nomesnap having count(nomesnap) > 7;
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,777
    Rep Power
    348
    Originally Posted by phelit
    I thought it as a generic SQLPLUS statement and not Oracle specific
    SQL*Plus is an Oracle specific tool...

    I don't understand where to put it in the query. I tried as you wrote at the end of the query but it doesn't work. What am I doing wrong?
    The HAVING clause goes after the GROUP BY but before the ORDER BY

    Code:
    select nomesnap, 
           count(nomesnap) as NUMERO
    from russor.controllarepliche 
    where trunc(dataconta) > trunc(sysdate) - 7 
      and righesnapceps<>righesnapsez 
    group by nomesnap 
    having count(nomesnap) > 7
    order by nomesnap;
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    5
    Rep Power
    0

    Red face



    Originally Posted by shammat
    SQL*Plus is an Oracle specific tool...

    The HAVING clause goes after the GROUP BY but before the ORDER BY

    Code:
    select nomesnap, 
           count(nomesnap) as NUMERO
    from russor.controllarepliche 
    where trunc(dataconta) > trunc(sysdate) - 7 
      and righesnapceps<>righesnapsez 
    group by nomesnap 
    having count(nomesnap) > 7
    order by nomesnap;
    Thank you so much! I've been working with sqlplus and oracle for 5 years now....but clearly I still have a loooong way to go before being really proficient.

    you rock! Thank you again for the help!

IMN logo majestic logo threadwatch logo seochat tools logo