Re: Another SQL question: This time with VIEWs

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