Re: Another SQL question: This time with VIEWs

[CRPence <CRPbottle@xxxxxxxxx>]

On 4/21/11 2:01 PM, James Lampert wrote:
> Given a VIEW in which records are censored and fields are renamed:
>
> CREATE VIEW FOO/BAZ AS
> SELECT . . .
>        , U01005 AS Highest_Credit
>        , . . .
> FROM FOO/BAR
> WHERE  CENSOR(U01430, U01429) = 'P'
>
> Is there a way to have the mixed-case name (derived programmatically
> from the field's descriptive text) actually go in as mixed-case? When
> I execute the actual CREATE VIEW, everything gets uppercased.
> And is there a way to preserve column headings when renaming fields?

  Names delimited with quotation marks will maintain mixed-case.  Of 
course lower-case alphabetic characters are variant rather than 
invariant, so in some [albeit very few] cases that might be an issue 
best avoided [since column names are stored in the catalog as the hex 
code point rather than as the character].

  As I recall, an expression in the field list will always be assigned 
a new column heading and text, derived from the column name and the 
expression respectively.  When the expression is just a column name and 
the column is renamed by using the column list of the CREATE VIEW versus 
using the AS-column-name in the SELECT statement, IIRC the original 
column heading is retained; i.e. try the following means to rename U01005:

     CREATE VIEW FOO/BAZ
        (. . .
        , Highest_Credit
        , . . .              ) AS
      SELECT . . .
             , U01005 /* AS Highest_Credit */
             , . . .
      FROM FOO/BAR . . .

  SQL offers the separate LABEL ON statement to establish TEXT() and 
COLHDG() versus any control within the CREATE statement being used.

    LABEL ON COLUMN FOO/BAZ
       ( . . .
       , Highest_Credit  IS 'Highest             Credit'
       , . . .
       )

    LABEL ON COLUMN FOO/BAZ
       ( . . .
       , Highest_Credit  TEXT IS 'Highest Credit ...'
       , . . .
       )

  Note: Of course if delimited name "Highest_Credit" is used in the 
CREATE VIEW, then the name must also be delimited in the LABEL ON 
statements [except when run from where mixed-case is defaulted, and 
delimiters are added by the client SQL utility\application].

Regards, Chuck