Jump to content

Difference between dates


c3118526
 Share

Recommended Posts

Hi I am working with 3 tables HIRE, INVENTORY and MEDIA_PRODUCT HIRE is linked to INVENTORY and INVENTORY is linked with MEDIA_PRODUCT.I need to find if a dvd is older than 4 weeks, if it is then it can be rented for 3 days if not it can only be rented for 2 days. I have used the code below to subtract 28 from the rental date (HIRE.RENTAL_DATE) which is todays date then see if this is greater than the release date (MEDIA_PRODUCT.RELEASE_DATE). I have a data block on the same form for each of my tables and they are all joined together. I cannot seem to get it right I keep getting different error messages can anyone help me please :) BEGINIF HIRE.RENTAL_DATE - 3 from dual > MEDIA_PRODUCT.RELEASE_DATE;THENINSERT INTO HIRE.DATE_DUE VALUES (HIRE.RENTAL_DATE + 3 from dual);ELSEINSERT INTO HIRE.DATE_DUE VALUES(HIRE.RENTAL_DATE +2 from dual);END IF;END;

Link to comment
Share on other sites

You need to mention what kind of object your date is. Are you using one built into your database? Which database, do you want to be tied to using that database?For instance, a query like this could work. It does in postgres, but maybe not in others. Datediff would also be useful for youupdate HIRE set HIRE.DUE_DATE = (NOW() + interval '3 days') where (HIRE.RENTAL_DATE > MEDIA_PRODUCT.RELEASE_DATE + interval '28 days')

Link to comment
Share on other sites

When I tried without the ; I keep getting the message expecting one of the following ,;So I changed my code to BEGINupdate HIRE set HIRE.DATE_DUE = (sysdate + interval '3' DAY) where (HIRE.RENTAL_DATE > MEDIA_PRODUCT.RELEASE_DATE + interval '28' DAY) AND HIRE.ISSUE# = INVENTORY.ISSUE# AND MEDIA_PRODUCT.ITEM# = INVENTORY.ITEM#;update HIRE set HIRE.DATE_DUE = (sysdate + interval '2' DAY) where (HIRE.RENTAL_DATE <= :MEDIA_PRODUCT.RELEASE_DATE + interval '28' DAY) AND HIRE.ISSUE# = INVENTORY.ISSUE# AND MEDIA_PRODUCT.ITEM# = INVENTORY.ITEM#;END;However now I keep getting the message invalid identifier inventory.issue# both the table and column names are spelled correctlyany ideas please?I'm new to PL/SQL so don't know a great deal

Link to comment
Share on other sites

Your problem is that # is a special character. Try one of these:... AND HIRE.[iSSUE#] = INVENTORY.[iSSUE#] AND MEDIA_PRODUCT.[iTEM#] = INVENTORY.[iTEM#];... AND HIRE.ISSUE## = INVENTORY.ISSUE## AND MEDIA_PRODUCT.ITEM## = INVENTORY.ITEM##;The two #'s should escape it if the brackets don't fix it.It would probably be less trouble if you renamed your tables to be item_no or something similar.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...