13 Replies Latest reply: Dec 5, 2012 10:43 AM by 976234 RSS

    How to use currnet date in check constraint

    976234
      Hi
      Would you help me with this?
      create table 
      test_1 (
      xxx number (1),
      yyy number(1), 
      zzz number(1), 
      sss date check (sss < .......));
      Thank you in advance for your help
        • 2. Re: How to use currnet date in check constraint
          6363
          You cannot use non-deterministic functions in a check constraint, you would need to use a trigger and raise an exception when your condition is violated instead.
          • 3. Re: How to use currnet date in check constraint
            Chanchal Wankhade
            Hi,
            SQL> create table table_name (id number, tdate date check (jdate < sysdate));
            create table table_name (id number, tdate date check (jdate < sysdate))
                                                                                  *
            ERROR at line 1:
            ORA-02438: Column check constraint cannot reference other columns
            • 4. Re: How to use currnet date in check constraint
              BluShadow
              Chanchal Wankhade wrote:
              ERROR at line 1:
              ORA-02438: Column check constraint cannot reference other columns
              Yes, I realise, but the OP didn't actually say what their issue was exactly.
              A poorly asked question deserves a poor answer.
              • 5. Re: How to use currnet date in check constraint
                Chanchal Wankhade
                yes Blu, Poor question.
                • 6. Re: How to use currnet date in check constraint
                  jeneesh
                  Chanchal Wankhade wrote:
                  Hi,
                  SQL> create table table_name (id number, tdate date check (jdate < sysdate));
                  create table table_name (id number, tdate date check (jdate < sysdate))
                  *
                  ERROR at line 1:
                  ORA-02438: Column check constraint cannot reference other columns
                  There are some work arounds..

                  And to add, your error here is different - you are referring different column JDATE (typo..?) in check constraint..Your actual column name is TDATE.. :)

                  Edited by: jeneesh on Dec 5, 2012 7:15 PM
                  • 7. Re: How to use currnet date in check constraint
                    976234
                    Maybe the question is poor, but these question was written by people from Oracle company :)
                    This question is related with 1Z0-047 exam

                    Thanks for your understanding
                    • 8. Re: How to use currnet date in check constraint
                      BluShadow
                      Tomi13 wrote:
                      Maybe the question is poor, but these question was written by people from Oracle company :)
                      This question is related with 1Z0-047 exam
                      You're taking the exam, but haven't even figured out how to look up check constraints in the documentation. That's not good.

                      If you have an issue, whether an exam question or not, you should always show what you've tried yourself. Never just post the question and expect others to answer it.
                      • 9. Re: How to use currnet date in check constraint
                        6363
                        Tomi13 wrote:
                        Maybe the question is poor, but these question was written by people from Oracle company :)
                        This question is related with 1Z0-047 exam
                        Then you should say at the beginning you are looking for answers to exam questions, then those of us who have no interest in helping with cheating can ignore such questions as they further devalue these already mostly worthless certifications.
                        • 10. Re: How to use currnet date in check constraint
                          976234
                          I'm sorry for this :(
                          But some of the questions are little confusing and not friendly for students (and not only for them).
                          I have coped some of them on forum because I dont have any idea how interpret them

                          For exemple
                          Which statements are true regarding subqueries?
                          a. The ORDER BY clause can be used in the subquery
                          b. A subquery can be used in the FROM clause of SELECT statment
                          c. If the subquery returns NULL, the main query may still return result rows.
                          
                          Correct answers according to author: a,b
                          Answer a,b
                          Subquery in FROM clause is named INLINE VIEW but INLINE VIEW is a subquery to?
                          In INLINE VIEW you can use ORDER BY, but in "normal" subquery you can't (I think so)
                          Answer c:
                          f you use a correlated subquery and NOT EXISTS operator. Subquery return NULL but main query returns some rows ...

                          I didn't want irritate anybody ;)
                          • 11. Re: How to use currnet date in check constraint
                            6363
                            Tomi13 wrote:
                            I'm sorry for this :(
                            But some of the questions are little confusing and not friendly for students (and not only for them).
                            I have coped some of them on forum because I dont have any idea how interpret them
                            A discussion about the correctness of questions in an exam is on topic for these forums, though just asking for answers to exam questions should be discouraged.

                            >
                            For exemple
                            Which statements are true regarding subqueries?
                            a. The ORDER BY clause can be used in the subquery
                            b. A subquery can be used in the FROM clause of SELECT statment
                            c. If the subquery returns NULL, the main query may still return result rows.
                            
                            Correct answers according to author: a,b
                            All three are true, so the question is wrong. Aside from all the people getting answers to these things off the internet the fact that the questions try so hard to be clever that they actually end up being wrong instead is what makes these certifications mostly worthless. Anyone would be better served spending the time reading the documentation.

                            http://www.oracle.com/pls/db112/homepage

                            http://docs.oracle.com/cd/E11882_01/server.112/e26088/queries007.htm#SQLRF52357
                            • 12. Re: How to use currnet date in check constraint
                              976234
                              I'm not cheating, a have the answers but I try undestand them. Some questions are valuable but some are worthless.
                              I have worked with oracle for few years and I read exam preparation book. My employer decide to send me on this exam. But I don't want to explain myself :P

                              I have started to share your opinion about the exam...
                              • 13. Re: How to use currnet date in check constraint
                                976234
                                Ii was most valuable information for me :)
                                Thanks