|
|
Posted by Beowulf on 09/01/06 17:12
I forgot to mention an important detail. I'm creating the VIEW and
FUNCTION within the context of a Microsoft Access ADP file that's
pointed at the SQL Server 2000 database in question.
If I execute this statement in Query Analyzer:
SELECT * FROM dbo.Function1('4.2.%')
the columns are output as specified in the column list inside the
Function definition (Ident, Text, ..., etc.).
If I double-click on the Function's object in MS Access and enter 4.2.%
in the prompt for the parameter, then the column list is output in the
strange order as noted below (Type, Text, ElseStmt, ..., etc.).
So, this may actually be a Microsoft Access problem, but if anyone has
any information, I'd appreciate it. Thanks.
Beowulf wrote:
> I was just messing around with some ad hoc views and table returning
> UDFs today so I could look at and print out data from a small table
> and noticed something strange.
>
> If I stick my select statement into a View the columns are returned in
> the order I specify in the SELECT, but if the same statement is in a UDF
> (so I can specify a parameter), the columns are not returned in the
> order specified in statement.
>
> I know that relations don't have a specified column order, but it was my
> understanding that a SELECT statement could be used to define how you
> want your data presented. Views seem to respect the order specified in
> the SELECT, but functions don't.
>
> What am I missing? Is there some way to force the order of the columns
> returned from a SELECT?
>
> View:
>
> CREATE VIEW dbo.View1
> AS
> SELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt,
> ThenStmt, ElseStmt, NextStmt
> FROM dbo.tblStmt
> WHERE (Ident LIKE '4.2.%')
>
> Column order from this view:
> Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt, ThenStmt,
> ElseStmt, NextStmt
>
> Function:
>
> ALTER FUNCTION dbo.Function1
> (@SearchPrm varchar(255))
> RETURNS TABLE
> AS
> RETURN ( SELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt,
> ChildStmt, ThenStmt, ElseStmt, NextStmt
> FROM dbo.tblStmt
> WHERE (Ident LIKE @SearchPrm) )
>
> Column order from this function:
> Type, Text, ElseStmt, NextStmt, IfStmt, ChildStmt, ThenStmt, Ident,
> ParentStmt, ForStmt
>
> Table:
> (I know that this table isn't entirely normalized, but it serves my
> purposes to have a matrix instead of a fully normalized relation):
>
> CREATE TABLE dbo.tblStmt (
> StmtID INT IDENTITY(1,1) CONSTRAINT PK_Stmt PRIMARY KEY,
> Ident VARCHAR(255),
> Text TEXT,
> ErrorText TEXT,
> Type INT,
> ParentStmt VARCHAR(255),
> ChildStmt VARCHAR(255),
> IfStmt VARCHAR(255),
> ForStmt VARCHAR(255),
> ThenStmt VARCHAR(255),
> ElseStmt VARCHAR(255),
> NextStmt VARCHAR(255),
> FullName VARCHAR(255),
> LocalName VARCHAR(255),
> Method INT
> )
>
> INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmt
> VALUES('4.2.1', 'LineNumberOfResp := EMPTY' 64, '4.2', '4.2.2')
>
> INSERT INTO tblStmt Ident, Text, Type, ParentStmt, ChildStmt, ForStmt,
> NextStmt
> VALUES('4.2.2', 'FOR K:= 1 TO 2', 128, '4.2', '4.2.3','4.2.7')
>
> INSERT INTO tblStmt Ident, Text, Type ParentStmt, ChildStmt, ForStmt,
> NextStmt
> VALUES('4.2.3', 'Person[K].KEEP', 16, '4.2', '4.2.3.1', '4.2.2', '4.2.4')
>
> INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmt
> VALUES('4.2.3.1' 'AuxInterviewerName := DOSENV', 64, '4.2.3', '4.2.3.2')
Navigation:
[Reply to this message]
|