Results 1 to 5 of 5

Thread: Help with SQL

  1. #1
    Join Date
    Apr 2004
    Posts
    3

    Cool Unanswered: Help with SQL

    Hello,
    I'm working on an Equipment Tracking System that allows a user to assign a piece of equipment to a particular employee and I'm having trouble with some of the SQL.

    Here's the setup:
    3 tables
    Employees
    • employee_id
    • last_name
    • first_name
    • job

    Equipment
    • equipment_id
    • equip_type
    • manufacturer
    • model
    • serial

    Tracking
    • track_id
    • emp_employee_id
    • equip_equipment_id
    • issue_date
    • return_date

    Right now, I have 2 different procedures. One for viewing Unassigned Equipment and one for Assigned Equipment. The Unassigned Equipment procedure allows the user to click an item and assign it to an employee. The assigned equipment procedure will allow the user to "unassign" equipment by inserting the sysdate into tracking.return_date.

    I need some help with the SQL statements for each procedure.

    Right now for the Unassigned view I have:
    PROCEDURE view_unassigned_equip IS

    CURSOR equip_cur IS
    SELECT e.equipment_id, e.equip_type, e.manufacturer, e.model, e.serial,
    e.purchase_date, purchase_price
    FROM equipment e, tracking t
    WHERE e.equipment_id <> t.equip_equipment_id
    OR t.return_date IS NOT NULL
    ORDER BY e.equipment_id;


    --Basically, I need to return the equipment that is either:
    1. not even referenced in the tracking table, because it is a new item and has never been assigned to anyone before
    2. or, check the most recent record corresponding to the equipment_id in the tracking table and see if the return_date is null or not (if its null, it would not have been returned)


    For the Assigned view, I have:
    PROCEDURE view_assigned_equip IS

    CURSOR equip_cur IS
    SELECT e.equipment_id, e.equip_type, e.manufacturer, e.model, e.serial,
    e.purchase_date, purchase_price
    FROM equipment e, tracking t
    WHERE e.equipment_id = t.equip_equipment_id
    AND t.return_date IS NULL
    ORDER BY e.equipment_id;


    --This statement works as long as the equipment_id is referenced only once in the tracking table. I need it to check the most current row only and see if it is null.

    I've tried to set this up as well as I can...If its confusing just let me know. If anyone has a better solution for what I am trying to accomplish, I'd love to hear it!

    TIA!

  2. #2
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246

    to test for new records ...

    select a.column_name from parent_table a where not exists
    (select 'x' from child_table b where b.fkid = a.pkid);

    This is very fast, as EXISTS tells Oracle to test but not fetch. The subquery must be correlated, as shown above, for EXISTS to work correctly. Also, the reason I select 'x' is because SQL standards require you to select something. so, even though EXISTS performs no fetching you still have to put something after SELECT. It makes no difference what you put, be it an 'x' or 'i love oracle'.
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

  3. #3
    Join Date
    Apr 2004
    Posts
    3
    Wow that works perfect! Thanks for the tip!

    Any ideas as to how I can find the 'most recent' row corresponding to an equipment_id in the tracking table??

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Any ideas as to how I can find the 'most recent' row corresponding to an equipment_id in the tracking table??
    The most straight forward way is to add a TIMESTAMP column to the table,
    and populate this column when the row is inserted or updated.
    The actual "order" of rows in any table is indeterminate/unknown.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Apr 2004
    Posts
    3

    timestamp worked like a champ!

    Thanks for your help!

    I was able to grab the max(issue_date) in my SQL statements which worked like a champ!


Posting Permissions

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