Column order/presentation in virtual table (result set from viewor UDF)

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')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:

Quote:

Originally Posted by

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')

|||It is indeed an MS Access problem (specific to ADP-s). You can reorder
the columns in the resulting datasheet freely and Access remembers the
position and width of the columns when you open the table/view/function
again. Access usually asks "Do you want to save changes to the layout
of function '...' ?". If you respond "yes", it stores this information
in the database, using extended properties for the objects and for the
columns.

Razvan|||Razvan Socol wrote:

Quote:

Originally Posted by

It is indeed an MS Access problem (specific to ADP-s). You can reorder
the columns in the resulting datasheet freely and Access remembers the
position and width of the columns when you open the table/view/function
again. Access usually asks "Do you want to save changes to the layout
of function '...' ?". If you respond "yes", it stores this information
in the database, using extended properties for the objects and for the
columns.

Thanks for the reply, even though the question turned out to be off-topic.

COLUMN GRAND TOTALS ARE INCORRECT FOR CALCULATED FIELDS IN VIRTUAL CUBE

Hello,

We have migrated some cubes from sqlserver 2000 to 2005 with success. The applicaticon contains one virtual cube made up from 6 other cubes. On the virtual cube level we specified calculated fields. The calculation contains elements from different cubes which shares dimensions.

while browsing the cube from within sql server management studio or from excel using OLEDb 9.0 Provider we discovered that the column grand totals for these calculated fields are incorrect. The row grand totals are fine.

Searching the internet (google) i have found a hotfix describing the problem SQL MSAS 2005 (http://support.microsoft.com/kb/918251). We have installed this hotfix on top of SP1 but without results.

Is somebody familiar with this problem or knows how to solve this !!!!

Thanks in advance.

Dirk

When you say "totals are incorrect", can you please provide the expressions of the calculated members, the expected values and the actual values. I.e. if you expected to see NULLs, but get values - it could be because of IgnoreUnrelatedDimensions setting etc.|||

Hi Mosha,

I expect values but the results are not correct ( so as an example i expect 82.0415,00 and i get 80.0345,53)

regards,

Dirk

|||

> please provide the expressions of the calculated members

The investigation will likely to go into the values on which these calculations are based. Another reason for disrepancies could be RI problems in the data.

|||

Hi Mosha,

I will provide details for you asap. At the moment i can't log in into the remote customer sql server (machine is down). I will provide you with full details tomorrow (same time).

Regards,

Dirk

|||

Hi Mosha,

Here are the details you asked for:

Virtual Cube: Cube_Omspanning

Calculated Measures:

[KostenquotientOmzet]

'[Measures].[Kostenquotient_OMZ]/[Measures].[Teller_OMZ]'

[KostprijsOmzet]

'[Measures].[EenhedenOmzet]*[Measures].[KostenquotientOmzet]'

[KostprijsOmzet]

'[Measures].[EenhedenOmzet]*[Measures].[KostenquotientOmzet]'

[RendementOmzet]

'[Measures].[BedragOmzet]-[Measures].[KostprijsOmzet]'

[RendementOmzet]

'[Measures].[BedragOmzet]-[Measures].[KostprijsOmzet]'

Cube_Omzet_Kostenquotient

Calculated measures:

[Gemiddelde Kostenquotient] (Not Used in virtual cube calculated fields)

'[Measures].[Kostenquotient_OMZ]/[Measures].[Teller_OMZ]'

Measures:

Kostenquotient_OMZ

Teller_OMZ

Cube_Omzet

Calculated measures:

None

Measures:

EenhedenOmzet

BedragOmzet

BedragBasis

A couple of general questions:

1.) Are Grand Totals calculated during load of the cube ?

2.) Could this be a Rounding (to integer) problem?

Regards,

Dirk

|||Based on these formulas - I can tell, that the differences must exist for the real measures. Please start comparing the real measures in AS2000 vs. AS2005, and start slicing by different dimensions. As soon as you identify dimension which causes the difference, I recommend checking whether the attribute relationships are defined correctly in that dimension - this seems to be the most probable cause of the difference. The difference is way too big to be explained by rounding here.