8 Replies Latest reply on Aug 24, 2012 1:34 PM by David Last-Oracle

    Issue with view DDL generated by OSDM

    897831
      I used "Import -> DDL File" option to import a view into OSDM from DDL file.
      When I generate the view DDL I see that "AS" word is duplicated in DDL.
      I've made sure that the original DDL in the import file is fine.

      For example - This is generated DDL for view

      create or replace view W_EHA_PROTOCOL_V AS*
      as_
      select PPH.ROW_WID, PPH.PATIENT_WID, PPH.CREATED_BY_WID, PPH.CREATED_ON_DT,
      PRD.PROTOCOL_NAME, PRD.PROTOCOL_DESC
      from W_EHA_PROTOCOL_PATIENT_H PPH,
      W_EHA_PROTOCOL_D PRD
      where pph.protocol_wid = prd.row_wid ;
        • 1. Re: Issue with view DDL generated by OSDM
          Philip Stoyanov-Oracle
          Hi,

          what version of Data Modeler do you use? I cannot reproduce it in the latest version 3.1.1.703 There was such problem earlier but it's fixed

          Philip
          • 2. Re: Issue with view DDL generated by OSDM
            897831
            I'm using Version 3.1.0.691.
            Let me know if this issue is specific to the version I'm using.
            Thanks.
            • 3. Re: Issue with view DDL generated by OSDM
              Philip Stoyanov-Oracle
              I'm using Version 3.1.0.691
              this is beta version. It's better to move to latest production 3.1.1.703.

              Philip
              • 4. Re: Issue with view DDL generated by OSDM
                897831
                After installing latest v3.1.1.703, the problem is partially resolved.
                Now, DDL for most of views is generated fine except one scenario – if I’ve column names in the view DDL, then OSDM does not generated view DDL properly.

                Following is view definition in my DDL import file

                CREATE OR REPLACE VIEW W_EHA_RESEARCH_PATIENT_V ( ROW_WID,
                PATIENT_IDENTIFIER,
                DOB,
                GENDER_CODE,
                GENDER_NAME,
                GENDER_DESC,
                GENDER_CD_SYS_ID,
                GENDER_CD_SYS_NAME,
                GENDER_CD_SYSTEM_VER,
                CITY,
                STATE_NAME,
                STATE_CODE,
                STATE_DESC,
                STATE_CD_SYS_ID,
                STATE_CD_SYS_NAME,
                STATE_CD_SYS_VER,
                COUNTRY_NAME,
                COUNTRY_CODE,
                COUNTRY_DESC,
                COUNTRY_CD_SYS_ID,
                COUNTRY_CD_SYS_NAME,
                COUNTRY_CD_SYS_VER,
                POSTAL_CODE,
                COUNTY,
                DECEASED_DT,
                MARITAL_STATUS_CODE,
                MARITAL_STATUS_NAME,
                MARITAL_STATUS_DESC,
                MARITAL_STATUS_CD_SYS_ID,
                MARITAL_STATUS_CD_SYS_NAME,
                MARITAL_STATUS_CD_SYS_VER,
                SHARED_IDENTIFIER,
                AGE_IN_YEARS,
                CREATED_BY_WID,
                CHANGED_BY_WID,
                CREATED_ON_DT,
                CHANGED_ON_DT,
                DELETE_FLG,
                W_INSERT_DT,
                W_UPDATE_DT,
                DATASOURCE_NUM_ID,
                ETL_PROC_WID,
                INTEGRATION_ID,
                TENANT_ID,
                X_CUSTOM)
                AS SELECT
                PT.ROW_WID,
                PT.PATIENT_IDENTIFIER,
                PT.DOB,
                PT.GENDER_CODE,
                PT.GENDER_NAME,
                PT.GENDER_DESC,
                PT.GENDER_CD_SYS_ID,
                PT.GENDER_CD_SYS_NAME,
                PT.GENDER_CD_SYSTEM_VER,
                PT.CITY,
                PT.STATE_NAME,
                PT.STATE_CODE,
                PT.STATE_DESC,
                PT.STATE_CD_SYS_ID,
                PT.STATE_CD_SYS_NAME,
                PT.STATE_CD_SYS_VER,
                PT.COUNTRY_NAME,
                PT.COUNTRY_CODE,
                PT.COUNTRY_DESC,
                PT.COUNTRY_CD_SYS_ID,
                PT.COUNTRY_CD_SYS_NAME,
                PT.COUNTRY_CD_SYS_VER,
                PT.POSTAL_CODE,
                PT.COUNTY,
                PT.DECEASED_DT,
                PT.MARITAL_STATUS_CODE,
                PT.MARITAL_STATUS_NAME,
                PT.MARITAL_STATUS_DESC,
                PT.MARITAL_STATUS_CD_SYS_ID,
                PT.MARITAL_STATUS_CD_SYS_NAME,
                PT.MARITAL_STATUS_CD_SYS_VER,
                PT.SHARED_IDENTIFIER,
                (CUR_YEAR.YEAR - EXTRACT(YEAR FROM PT.DOB) - PT.AGE_OFFSET) AGE_IN_YEARS,
                PT.CREATED_BY_WID,
                PT.CHANGED_BY_WID,
                PT.CREATED_ON_DT,
                PT.CHANGED_ON_DT,
                PT.DELETE_FLG,
                PT.W_INSERT_DT,
                PT.W_UPDATE_DT,
                PT.DATASOURCE_NUM_ID,
                PT.ETL_PROC_WID,
                PT.INTEGRATION_ID,
                PT.TENANT_ID,
                PT.X_CUSTOM
                FROM
                ( SELECT
                EXTRACT(YEAR FROM SYSDATE) YEAR
                from DUAL
                )CUR_YEAR,
                ( SELECT
                ROW_WID,
                PATIENT_IDENTIFIER,
                DOB,
                GENDER_CODE,
                GENDER_NAME,
                GENDER_DESC,
                GENDER_CD_SYS_ID,
                GENDER_CD_SYS_NAME,
                GENDER_CD_SYSTEM_VER,
                CITY,
                STATE_NAME,
                STATE_CODE,
                STATE_DESC,
                STATE_CD_SYS_ID,
                STATE_CD_SYS_NAME,
                STATE_CD_SYS_VER,
                COUNTRY_NAME,
                COUNTRY_CODE,
                COUNTRY_DESC,
                COUNTRY_CD_SYS_ID,
                COUNTRY_CD_SYS_NAME,
                COUNTRY_CD_SYS_VER,
                POSTAL_CODE,
                COUNTY,
                DECEASED_DT,
                MARITAL_STATUS_CODE,
                MARITAL_STATUS_NAME,
                MARITAL_STATUS_DESC,
                MARITAL_STATUS_CD_SYS_ID,
                MARITAL_STATUS_CD_SYS_NAME,
                MARITAL_STATUS_CD_SYS_VER,
                SHARED_IDENTIFIER,
                CREATED_BY_WID,
                CHANGED_BY_WID,
                CREATED_ON_DT,
                CHANGED_ON_DT,
                DELETE_FLG,
                W_INSERT_DT,
                W_UPDATE_DT,
                DATASOURCE_NUM_ID,
                ETL_PROC_WID,
                INTEGRATION_ID,
                TENANT_ID,
                X_CUSTOM,
                CASE
                WHEN CUR.MONTH < EXTRACT(MONTH FROM DOB) THEN 1
                WHEN CUR.MONTH > EXTRACT(MONTH FROM DOB) THEN 0
                WHEN CUR.DAY < EXTRACT(DAY FROM DOB) THEN 1
                WHEN CUR.DAY > EXTRACT(DAY FROM DOB) THEN 0
                ELSE 0 END AGE_OFFSET
                FROM W_EHA_RESEARCH_PATIENT_D,
                (SELECT
                EXTRACT (MONTH FROM SYSDATE) MONTH,
                EXTRACT (DAY FROM SYSDATE) DAY
                FROM DUAL
                )CUR
                )PT ;



                When I import DDL file and then generated DDL out of OSDM, it generates as follows:
                CREATE OR REPLACE VIEW W_EHA_RESEARCH_PATIENT_V AS*
                ( ROW_WID,
                PATIENT_IDENTIFIER,
                DOB,
                .
                .
                .

                DDL fails due to additional word "AS" in generated DDL.
                • 5. Re: Issue with view DDL generated by OSDM
                  Philip Stoyanov-Oracle
                  Thanks for the example. I logged bug for the problem.

                  Philip
                  • 6. Re: Issue with view DDL generated by OSDM
                    897831
                    Can you please provide the bug#?
                    Also, can you updated this thread when the bug is resolved?
                    • 7. Re: Issue with view DDL generated by OSDM
                      David Last-Oracle
                      Hi,

                      The bug number is 14162112. We'll try to remember to update this thread when it's fixed.

                      David
                      • 8. Re: Issue with view DDL generated by OSDM
                        David Last-Oracle
                        Hi,

                        This bug is fixed in version 3.1.2.704 of Data Modeler which is now available.

                        David