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

Thread: Sysdate Query Help

  1. #1
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    374

    Sysdate Query Help

    I have a table called GLANCE with three columns
    Entry_number
    page_sum
    total_ct_pag

    This table has 7 years worth of data. I am trying to write a query to return just 6 months of data?

    How can I manipute sysdate to give me the result without a datefield column?

    Thanks in advance
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    You don't have a leg, and try to run in Marathon.

    Tamil

  3. #3
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    374
    Tamil
    If you don't have any advise to give don't try to be like you know it all.
    thanks
    Last edited by Ablakios; 12-20-2004 at 12:04 PM.
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Does Entry_number exist in another table with a date that show when the record was created? If not, you have no way of going back six months. But you could add a date column and a trigger now to update the date column. Then you will have that information going forward.

    IF you knew how many rows were added per month, you could guess as to what is 6 months of data.

  5. #5
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    374
    gandolf989
    Thank you for your input.
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    =====
    have a table called GLANCE with three columns
    Entry_number
    page_sum
    total_ct_pag

    This table has 7 years worth of data. I am trying to write a query to return just 6 months of data?

    How can I manipute sysdate to give me the result without a datefield column?

    You don't have a leg, and try to run in Marathon.

    If you don't have any advise to give don't try to be like you know it all.

    =========
    Leg means a date column in the table.
    Runnning in Marathon means running a SQL.

    I hope you understand the phrase.

    Tamil

  7. #7
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    374
    Tamil
    I now understand your phrase. I thought is one of those RTFM phrase. Any way. I know there is no date column but I thought there a workaround it if you one consider the table creation date.
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Are you trying to extract rows that were inserted in the last six months?
    If your answer is "yes", then it is NOT possible.

    Or Are you trying to extract any rows inserted any time but the total number of rows matches 6 months of volume?
    If your answer is "YES", then it is possible.

    Tamil

  9. #9
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    374
    Tamil,
    Are you trying to extract rows that were inserted in the last six months?
    If your answer is "yes", then it is NOT possible.

    Ok..Not Possible.

    Or Are you trying to extract any rows inserted any time but the total number of rows matches 6 months of volume?
    If your answer is "YES", then it is possible.

    What about this? And how to do this
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  10. #10
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    I should assume that you know the schema, but if the ENTRY_NUMBER column exists in a table where you can get a date, then you can find the value of ENTRY_NUMBER from 6 months ago. Once you get that value use it as the low number in the table where you are trying to select from. I don't know how else you can get the data.


    Code:
    SELECT DISTINCT table_name, column_name
      FROM all_tab_columns
     WHERE owner     = 'MYSCHEMA' AND
           data_type = 'DATE'     AND
           table_name IN
           ( SELECT table_name
               FROM all_tab_columns
              WHERE owner       = 'MYSCHEMA'         AND
                    column_name = 'ENTRY_NUMBER' );

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