Results 1 to 10 of 10

Thread: sql

  1. #1
    Join Date
    Jan 2004
    Location
    ny
    Posts
    16

    Unanswered: sql

    hello,
    I have a table Person
    fields are
    id, name, date_entered, date_changed
    This table got updated every week
    if a new name and id is added, the date_entered field is changed,

    if name is changed the date_changed is changed

    How to get a right sql to be shure what was changed or added
    during a week

    Thank you so much

  2. #2
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Write your where with your date_entered between date1 and date2 or date_changed between date1 and date2.

    I hope that gives you enough to finish your class assignment.
    NOTE: Please disregard the label "Senior Member".

  3. #3
    Join Date
    Jan 2004
    Posts
    492

    Re: sql

    Originally posted by igorem
    hello,
    I have a table Person
    fields are
    id, name, date_entered, date_changed
    This table got updated every week
    if a new name and id is added, the date_entered field is changed,

    if name is changed the date_changed is changed

    How to get a right sql to be shure what was changed or added
    during a week

    Thank you so much
    Select *
    from person
    where date_entered >= sysdate - 7
    or date_changed >= sysdate - 7


    This should show you any person entered between today and 7 days ago, or any entry changed between today and 7 days ago.

  4. #4
    Join Date
    Jan 2004
    Location
    ny
    Posts
    16

    Re: sql

    Originally posted by ss659
    Select *
    from person
    where date_entered >= sysdate - 7
    or date_changed >= sysdate - 7


    This should show you any person entered between today and 7 days ago, or any entry changed between today and 7 days ago.

    thank you, it did show it, but I have to also show that if changed_date is null , i have to specify that this person was only added, not changed

    create table temp (id , name , Status )

    insert into temp(id, name, status)
    Select id, name , "ACD" (id added A, if changed C if no name D deleted)
    from person
    where date_entered >= sysdate - 7
    or date_changed >= sysdate - 7

    is there a way of duing so ?

    thank you so much

  5. #5
    Join Date
    Jan 2004
    Posts
    492

    Re: sql

    Originally posted by igorem
    thank you, it did show it, but I have to also show that if changed_date is null , i have to specify that this person was only added, not changed

    create table temp (id , name , Status )

    insert into temp(id, name, status)
    Select id, name , "ACD" (id added A, if changed C if no name D deleted)
    from person
    where date_entered >= sysdate - 7
    or date_changed >= sysdate - 7

    is there a way of duing so ?

    thank you so much
    select id, name, decode(date_changed, null, 'A', 'C')
    from person
    where date_entered >= sysdate - 7
    or date_changed >= sysdate - 7


    So you look at the date_changed column. If it is null, then you know a person was just added. If it has anything but null, it must have been changed.


    If someone is deleted from the table, my guess is that all their information would be deleted with them. So my query doesnt test for anyone deleted. If this does not suffice, please explain how you know someone has been deleted b/c I have no way of knowing.

  6. #6
    Join Date
    Jan 2004
    Location
    ny
    Posts
    16

    Re: sql

    Originally posted by ss659
    select id, name, decode(date_changed, null, 'A', 'C')
    from person
    where date_entered >= sysdate - 7
    or date_changed >= sysdate - 7


    So you look at the date_changed column. If it is null, then you know a person was just added. If it has anything but null, it must have been changed.


    If someone is deleted from the table, my guess is that all their information would be deleted with them. So my query doesnt test for anyone deleted. If this does not suffice, please explain how you know someone has been deleted b/c I have no way of knowing.

    it worked, thank you
    probably to get deleted one I have to have a temp table that will be loaded every week and make compar. between original table to the temp table to get deleted

    thank you

  7. #7
    Join Date
    Jan 2004
    Location
    ny
    Posts
    16

    Unhappy Re: sql

    Originally posted by igorem
    it worked, thank you
    probably to get deleted one I have to have a temp table that will be loaded every week and make compar. between original table to the temp table to get deleted

    thank you


    I found another thing:

    I have a column date_entered and date_changed
    in some cases both of them are null
    how can I show that is null value with decode for both ????


    Thank you so much

  8. #8
    Join Date
    Jan 2004
    Posts
    492
    Quite honestly you need to review the logic of your table.

    You said:
    1) When a new person is entered, the date_entered is populated with today's date.

    2) When a person is modified, the date_changed gets updated with the date the record is modified.


    Now if these records are null, somehow your logic is flawed. In the case neither the date_changed or date_entered have values, how could you possibly know whether these were entered or changed in the past week?

    Do you see what I'm saying here? You are looking for new/updated records in the past week. If there are no dates, how could this query know when a record was updated/inserted?

    You should look into TRIGGERS. Set a trigger to insert the SYSDATE when inserting a new person. Also set a trigger to update the date_changed column with the SYSDATE when updating. Check out the Oracle docs for more info on TRIGGERS.

    With triggers, you never have to manually insert/update the SYSDATE again, and then my query will always work.

  9. #9
    Join Date
    Jan 2004
    Location
    ny
    Posts
    16
    Originally posted by ss659
    Quite honestly you need to review the logic of your table.

    You said:
    1) When a new person is entered, the date_entered is populated with today's date.

    2) When a person is modified, the date_changed gets updated with the date the record is modified.


    Now if these records are null, somehow your logic is flawed. In the case neither the date_changed or date_entered have values, how could you possibly know whether these were entered or changed in the past week?

    Do you see what I'm saying here? You are looking for new/updated records in the past week. If there are no dates, how could this query know when a record was updated/inserted?


    You should look into TRIGGERS. Set a trigger to insert the SYSDATE when inserting a new person. Also set a trigger to update the date_changed column with the SYSDATE when updating. Check out the Oracle docs for more info on TRIGGERS.

    With triggers, you never have to manually insert/update the SYSDATE again, and then my query will always work.



    Thank you and you are absolutely right, table had no trigger for update, that is why the values was null, same for insert.
    i did create update trig. and its working.
    Unfortunately since there was no dates before I'm not able to tell when it was changed or inserted.
    Only new info will have all information and your q. will work


    Thanks a lot one more time.

  10. #10
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by igorem
    Thank you and you are absolutely right, table had no trigger for update, that is why the values was null, same for insert.
    i did create update trig. and its working.
    Unfortunately since there was no dates before I'm not able to tell when it was changed or inserted.
    Only new info will have all information and your q. will work


    Thanks a lot one more time.
    I think since you understand there was a problem with your logic, you take a step today to do a mass update to the created by date. I think it makes the most sense to just put a date in now so you dont run into problems in the future.

    TRY:

    UPDATE Person
    SET date_created = SYSDATE
    WHERE date_created is null;

    Commit;

    And then if you rerun your query, you could exclude those you created today by :

    select id, name, decode(date_changed, null, 'A', 'C')
    from person
    where (date_entered >= sysdate - 7
    or date_changed >= sysdate - 7)
    and date_entered <> '03-FEB-2004'

    Something like this would be my approach.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •