3 Replies Latest reply: Mar 18, 2013 11:07 AM by Paul Horth RSS

    Impossibility to get wm_valid's fields validfrom and validtill

    997545
      Hi everybody!
      I enabled versionning in a table. As it is said in the section 3.5 of this web page (http://docs.oracle.com/cd/B28359_01/appdev.111/b28396/long_vt.htm#i1010273): it is possible to access to wm_valid fields: validfrom and validtill. However every time I try to do that I get this error :

      Select wm_valid.validFrom FROM EMP
      Erreur à la ligne de commande : 2, colonne : 8
      Rapport d'erreur :
      Erreur SQL : ORA-00904: "WM_VALID"."VALIDFROM" : identificateur non valide
      00904. 00000 - "%s: invalid identifier"
      *Cause:   
      *Action:

      Thank you for your help
        • 1. Re: Impossibility to get wm_valid's fields validfrom and validtill
          Paul  Horth
          994542 wrote:
          Hi everybody!
          I enabled versionning in a table. As it is said in the section 3.5 of this web page (http://docs.oracle.com/cd/B28359_01/appdev.111/b28396/long_vt.htm#i1010273): it is possible to access to wm_valid fields: validfrom and validtill. However every time I try to do that I get this error :

          Select wm_valid.validFrom FROM EMP
          Erreur à la ligne de commande : 2, colonne : 8
          Rapport d'erreur :
          Erreur SQL : ORA-00904: "WM_VALID"."VALIDFROM" : identificateur non valide
          00904. 00000 - "%s: invalid identifier"
          *Cause:   
          *Action:

          Thank you for your help
          You're not actually following the documentation
          SELECT * from employees e 
             WHERE e.wm_valid.validFrom <= TO_DATE('01-01-1991', 'MM-DD-YYYY')
               AND e.wm_valid.validTill > TO_DATE('01-03-1991', 'MM-DD-YYYY');
          You are missing the alias for the emp table on the front of wm_valid.
          • 2. Re: Impossibility to get wm_valid's fields validfrom and validtill
            997545
            Thank you for your answer!
            What is the role of the Alias here?
            Thanks
            • 3. Re: Impossibility to get wm_valid's fields validfrom and validtill
              Paul  Horth
              994542 wrote:
              Thank you for your answer!
              What is the role of the Alias here?
              Thanks
              My suspicion is that the wm_valid col is actually an object_type (that is it has a structure to it). One of the rules of accessing object_types
              is that you must have a table alias on the front of the column name.
              SQL> create or replace type test_obj as object (
                2  id number(6)
                3  , text_column varchar2(50)
                4  );
                5  /
              
              Type created.
              
              SQL> create table test_table_obj (
                2  col1 number(8)
                3  , col2 test_obj
                4  );
              
              Table created.
              
              SQL> select col1,t.col2.text_column
                2  from test_table_obj t;
              
              no rows selected
              
              SQL> select col1, col2.text_column
                2  from test_table_obj t;
              select col1, col2.text_column
                           *
              ERROR at line 1:
              ORA-00904: "COL2"."TEXT_COLUMN": invalid identifier