Difference between dates

[c3118526]

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;

[eight968]

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')

[nibe49]

HiI'm sure you don't need to use 'dual' here at all.Also, a semi-colon ( ; ) denotes an end of statement, so don't use it before 'THEN', that's just wrong syntax.regardsnibe

[c3118526]

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

[eight968]

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.