10 Replies Latest reply: Jul 18, 2013 3:44 AM by Juri_Wolfenhaut RSS

    Unable to comment columns in a view

    puchtec
      I am wondering how I can comment a column in a view. In some cases it might be very helpful to comment columns in a view. At present I am not able to store this information when specifying view.

      Any idea? Maybe it is still not implemented?


      Thanks in advance,
      Frank

      3.0.0.665 on Windows XP
        • 1. Re: Unable to comment columns in a view
          Philip Stoyanov-Oracle
          Maybe it is still not implemented
          Yes, it's not implemented. There is such request logged, so you'll get it in next release.

          Philip
          • 2. Re: Unable to comment columns in a view
            user8632123

            Is it supposed to work now? It seems to contain a bug: The schemaname of the view is not included in the "create comment" statement and hence creates an error.

            • 3. Re: Unable to comment columns in a view
              David Last-Oracle

              Hi,

               

              I assume you are using version 3.3 of Data Modeler?

              Setting the User property for the View in the Physical Model should cause a schema prefix to be included in the COMMENT ON COLUMN statement.

              However it seems that this is not working properly, and the schema prefix is only included if the Schema property is set for the View in the Relational Model.

               

              Does this explain what you are finding?

              I will log a bug on this.

               

              David

              • 4. Re: Unable to comment columns in a view
                user8632123

                Hi DavidLast,

                 

                actually I've been using the version included with SqlDeveloper 3.2.2, and there it doesn't work even if both properties are set correctly.

                With Datamodeler 3.3 it works if both properties are set.

                 

                Thanks,

                 

                Bernd.

                • 5. Re: Unable to comment columns in a view
                  Philip Stoyanov-Oracle

                  Hi Bernd,

                   

                  actually I've been using the version included with SqlDeveloper 3.2.2, and there it doesn't work even if both properties are set correctly.

                  You can use Check For Updates in SQL Developer 3.2 in order to upgrade Data Modeler to version 3.3

                   

                  Philip

                  • 6. Re: Unable to comment columns in a view
                    user8632123

                    Hi Philip and David,

                     

                    in datamodeler 3.3 it works for us, because we always set both user and schema anyway.

                     

                    (Unfortunately we cannot make the switch from 3.1.4 because we still have to resolve some issues).

                     

                    Thanks,

                     

                    Bernd.

                    • 7. Re: Unable to comment columns in a view
                      puchtec

                      In Data Modeler 3.3.0.747 I am now able to comment view columns.

                       

                       

                      Thx

                      • 8. Re: Unable to comment columns in a view
                        Juri_Wolfenhaut

                        Hi David and Philip,

                         

                        I am using Data Modeler 3.3.0.747 under Windows XP (SP3), and I have a problem when creating comments for those columns in a view which are expressions. For example, if the view query is

                         

                        SELECT cus.first_name

                              ,cus.last_name

                              ,CASE cus.type

                                   WHEN 1 THEN 'VIP'

                                   WHEN 2 THEN 'REGULAR'

                                   ELSE        'UNKNOWN'

                               END AS customer_type

                          FROM customers  cus

                         

                        then Data Modeler generates a correct "CREATE OR REPLACE VIEW" DDL-statement which does use the "customer_type" alias for the last view column (CASE-expression), but for the "COMMENT ON COLUMN" statement it does use some generated column name (also visible in the tree structure displayed in the Browser window):

                         

                        COMMENT ON COLUMN VW_CUSTOMERS."NULL SET GENERATED_1781" IS "Customer type description";

                         

                        Of course, this "COMMENT ON COLUMN" statement fails.

                         

                        I tried to set the "Header Alias" property in the "Column Comments" sheet of the "View Properties", but it seems to be ignored. The only workaround I found was to wrap the original query within an inline view to avoid any expressions in the outer query, e.g. as follows:

                         

                          WITH customer_data

                            AS (SELECT cus.first_name

                                      ,cus.last_name

                                      ,CASE cus.type

                                           WHEN 1 THEN 'VIP'

                                           WHEN 2 THEN 'REGULAR'

                                           ELSE        'UNKNOWN'

                                       END AS customer_type

                                  FROM customers  cus

                               )

                        SELECT dat.first_name

                              ,dat.last_name

                              ,dat.customer_type

                          FROM customer_data  dat

                         

                        Can you please check what is the reason for this behaviour? Is this the "Header Alias" property in the "Column Comments" sheet which does not work correctly?

                         

                        Best regards and many thanks for your support,

                        Juri

                        • 9. Re: Unable to comment columns in a view
                          Philip Stoyanov-Oracle

                          Hi Juri,

                           

                          that problem is fixed in Data Modeler 4.0 code. SQL Developer 4.0 EA1 contains DM 4.0 and you can use it there until standalone version is released.

                           

                          Philip

                          • 10. Re: Unable to comment columns in a view
                            Juri_Wolfenhaut

                            Hi Philip,

                             

                            many thanks for the prompt reply! It's amazing - how do you manage to answer within 8 minutes? 

                             

                            Best regards,

                            Juri