Dan,
There is a difference in the way RBase stores column information
for VIEWS. While the "ColumnName/Expression AS name" syntax works
great when you use a SELECT statement alone. If you incorporate
it into a VIEW without using the "CREATE VIEW viewname (columnlist)
AS SELECT" syntax, RBase does not pickup and store the "AS name"
name in the SYS_COLUMNS table rather it stores the name "UNKNOWN"
for any column (expression) that is not a straight name retrevial.
Thus your views may not be storing name and even datatypes as
you believe them to be. It seems that whenever you use an expression.
RBase may identify that item in a VIEW as the NOTE datatype.
The following code revewals what is stored in the SYS_COLUMNS
table for every VIEW. Be aware that depending on the number of
view and the number of columns this code may take a few moments
to execute.
SELECT SYS_COLUMN_ID=8 AS ColId, SYS_COLUMN_NAME=18 +
AS ColName, (SYS_TYPE_NAME & (IFEQ(T1.SYS_DATA_TYPE,3,('('+
+
CTXT(T1.SYS_LENGTH)+')'), (IFEQ(T1.SYS_DATA_TYPE,9,('('+ +
CTXT(T1.SYS_LENGTH)+','+ CTXT(T1.SYS_SCALE)+')'),(IFEQ(T1.SYS_DATA_TYPE,12,+
('('+ CTXT(T1.SYS_LENGTH)+')'),' ')))))))=12 AS ColType, SYS_TABLE_ID=6
+
AS TblID, SYS_TABLE_NAME AS TblName, SYS_PRECISION=5 AS Prec,
SYS_LENGTH=4 +
AS Lnth FROM SYS_COLUMNS T1, SYS_TABLES T2 +
WHERE T2.SYS_TABLE_TYPE='VIEW' AND T2.SYS_TABLE_ID = T1.SYS_TABLE_ID
+
ORDER BY T2.SYS_TABLE_NAME ASC, SYS_COLUMN_ID ASC
--
Jim Bentley
American Celiac Society
[EMAIL PROTECTED] - email
(973) 325-8837 voice
(973) 669-8808 Fax
---- "Bill Downall" <[EMAIL PROTECTED]> wrote:
> Dan,
>
> Use the SQL syntax version of aliasing in your CREATE VIEW
> statement:
>
> CREATE VIEW myview +
> (Firstdate, LastDate, TotalOrders, TotalRevenue, ClientNum)
> +
> AS SELECT +
> (min(orddate)),(max(OrdDate)),(count(OrdDate)),(sum(Total)),
> +
> clientnum +
> from orders +
> group by clientnum +
> where cancelled <> 1
>
> Bill
>
> On Mon, 24 Jun 2002 17:34:05 -0500, Dan wrote:
>
> >select (min(orddate)) as FirstDate,(max(OrdDate)) as
> >LastDate,(count(OrdDate))
> >AS TotalOrders,(sum(Total)) as TotalRevenue,clientnum from
> orders
> group by
> >clientnum where cancelled <> 1
> >
> >on which I'm trying to design a report. However, when I use
> the report
> >designer, only the column <clientnum> appears available. What
>
> happened to
> >the other columns?
> >
>
>
>
>
>
> ================================================
> TO SEE MESSAGE POSTING GUIDELINES:
> Send a plain text email to [EMAIL PROTECTED]
> In the message body, put just two words: INTRO rbase-l
> ================================================
> TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
> In the message body, put just two words: UNSUBSCRIBE rbase-l
> ================================================
> TO SEARCH ARCHIVES:
> http://www.mail-archive.com/rbase-l%40sonetmail.com/
>
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
================================================
TO SEARCH ARCHIVES:
http://www.mail-archive.com/rbase-l%40sonetmail.com/
