This discussion is archived
10 Replies Latest reply: Jul 18, 2013 1:37 AM by Philip Stoyanov RSS

Unable to comment columns in a view

puchtec Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    Hi Philip,

     

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

     

    Best regards,

    Juri

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points