Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 10, 2004

MDX Essentials: Basic Set Functions: Subset Functions: The Head() Function - Page 5

By William Pearson

10. Execute the query by clicking the Run Query button in the toolbar.

We receive a message box, as shown in Illustration 5, stating that the Sample Application is unable to display the opened cellset - essentially because the dataset is empty.

Illustration 5: Message - Empty Cellset

11. Click OK.

The message box closes, and we are confronted with the empty cellset (perhaps only briefly), as expressed by the Sample Application (See Illustration 6.)

Illustration 6: Empty Cellset as Expressed by the Sample Application

12. Re-save the file as MDX19-3.

And so we see that a numerical expression less than 1 within the Head() function elicits the return of an empty cellset. Now, let's examine one last provision for oddball numeric input: what happens when we input a number that is higher than the total number of tuples in the specified set?

13. Within the query we have saved as MDX19-3, replace the top comment line of the query with the following:

-- MDX019-4, Use of Head() Function - Numeric Expression > Total Tuples in Set

14. Save the query as MDX19-4.

15. Replace the numeral -1 with the numeral 6, in the following line of the existing query:

              {Head([Time].[1998].Children, -1)} ON COLUMNS,

The Query pane appears as shown in Illustration 7, with the inserted coding circled in red.

Illustration 7: The Query with Numerical Expression Larger than Set

16. Execute the query by clicking the Run Query button in the toolbar.

The Results pane is populated, and the dataset shown in Illustration 8 appears.

Illustration 8: Results of the Modified Numeric Expression

We see that, even though the numerical expression that we input exceeds the number of tuples in the specified set (only four Quarters exist in any single Year), Head() returns the Full Set only.

17. Re-save the file as MDX19-4.

We have explored examples of the behavior of the Head() function under various scenarios of numeric expression input for a specified set. We saw that the function manages potential input by providing a "default" numeric expression to drive its behavior, with regard to the results it produces.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM