Partitioned View not operating as Documented

Hi All,
We have run into a huge problem at a large data-heavy multi-user
installation. In order to increase response times we split up a number of
tables into "Company" specific divisions to reduce the volume of records in
each Division and then placed Check constaints on each table by DIVID. We
Unioned the tables into partitioned views.
However, contrary to documentation and recommendations from this newsgroup,
even though we include DIVID in all our queries, Enterprise Manager and QA
indicate that SQL Server is opening ALL tables in the view during a Query or
an Update. I am thinking we must have done something wrong, or misunderstood
what partitoned views are supposed to do. We thought that the Check
constraints would allow SQL Server to key in immediately on the requested
divisional table. Here are some code examples:
A Table:
CREATE TABLE [PLTBDIV_ACT_DET_D00]
(
[DIVID] [CHAR] (3) NOT NULL CHECK (DIVID='D00')
,[ACCTID] [CHAR] (10) NOT NULL
,[ACT_TYPE] [CHAR] (1) NOT NULL
,[TRANID] [CHAR] (10) NOT NULL
,[TRANDATE] [SMALLDATETIME]
,[TRANCODE] [CHAR] (5)
,[TRANMODE] [CHAR] (5)
,[TRANREF] [CHAR] (25)
,[CSHREF] [CHAR] (25)
,[REVERSED] [CHAR] (1)
,[CHECKNUM] [CHAR] (25)
,[CHECKACCTID] [CHAR] (10)
,[INV_NUM] [CHAR] (12)
,[REBILLID] [CHAR] (3)
,[INV_TYPE] [CHAR] (1)
,[TRANAMOUNT] [NUMERIC] (19,4)
,[APPLIED] [NUMERIC] (19,4)
,[AVAILABLE] [NUMERIC] (19,4)
,[COMMENT] [CHAR] (90)
,[CREATION] [SMALLDATETIME]
,[BATCHNUM] [CHAR] (12) NULL
,[CLOSING] [CHAR] (1)
,[USERID] [CHAR] (15)
)
Note the Check constraint.
The Primary Key:
ALTER TABLE [dbo].[PLTBDIV_ACT_DET_D00] WITH NOCHECK ADD CONSTRAINT
[PLPKDIV_ACT_DET_D00]
PRIMARY KEY CLUSTERED
(
[DIVID]
,[TRANID]
) --WITH FILLFACTOR = 10
The View is a simple Union of all tables.
Some TSQL tests run indicate that the Constraint is trusted.
EXEC SP_HELPCONSTRAINT PLTBDIV_ACT_DET_D00
SELECT
OBJECTPROPERTY(OBJECT_ID('CK__PLTBDIV_A_
_DIVID__7DF19EE6'),'CnstIsNotTrusted
')
The 2nd query returns 0.
Can anyone see what might be the problem? Or are partitoned views not meant
to improve performance?
Thanks to all in advance."John Kotuby" <johnk@.powerlist.com> wrote in message
news:OW40DgpcGHA.3908@.TK2MSFTNGP02.phx.gbl...
> Hi All,
> We have run into a huge problem at a large data-heavy multi-user
> installation. In order to increase response times we split up a number of
> tables into "Company" specific divisions to reduce the volume of records
> in each Division and then placed Check constaints on each table by DIVID.
> We Unioned the tables into partitioned views.
> However, contrary to documentation and recommendations from this
> newsgroup, even though we include DIVID in all our queries, Enterprise
> Manager and QA indicate that SQL Server is opening ALL tables in the view
> during a Query or an Update. I am thinking we must have done something
> wrong, or misunderstood what partitoned views are supposed to do. We
> thought that the Check constraints would allow SQL Server to key in
> immediately on the requested divisional table. Here are some code
> examples:
> A Table:
> CREATE TABLE [PLTBDIV_ACT_DET_D00]
> (
> [DIVID] [CHAR] (3) NOT NULL CHECK (DIVID='D00')
> ,[ACCTID] [CHAR] (10) NOT NULL
> ,[ACT_TYPE] [CHAR] (1) NOT NULL
> ,[TRANID] [CHAR] (10) NOT NULL
> ,[TRANDATE] [SMALLDATETIME]
> ,[TRANCODE] [CHAR] (5)
> ,[TRANMODE] [CHAR] (5)
> ,[TRANREF] [CHAR] (25)
> ,[CSHREF] [CHAR] (25)
> ,[REVERSED] [CHAR] (1)
> ,[CHECKNUM] [CHAR] (25)
> ,[CHECKACCTID] [CHAR] (10)
> ,[INV_NUM] [CHAR] (12)
> ,[REBILLID] [CHAR] (3)
> ,[INV_TYPE] [CHAR] (1)
> ,[TRANAMOUNT] [NUMERIC] (19,4)
> ,[APPLIED] [NUMERIC] (19,4)
> ,[AVAILABLE] [NUMERIC] (19,4)
> ,[COMMENT] [CHAR] (90)
> ,[CREATION] [SMALLDATETIME]
> ,[BATCHNUM] [CHAR] (12) NULL
> ,[CLOSING] [CHAR] (1)
> ,[USERID] [CHAR] (15)
> )
> Note the Check constraint.
> The Primary Key:
> ALTER TABLE [dbo].[PLTBDIV_ACT_DET_D00] WITH NOCHECK ADD CONSTRAINT
> [PLPKDIV_ACT_DET_D00]
> PRIMARY KEY CLUSTERED
> (
> [DIVID]
> ,[TRANID]
> ) --WITH FILLFACTOR = 10
> The View is a simple Union of all tables.
> Some TSQL tests run indicate that the Constraint is trusted.
> EXEC SP_HELPCONSTRAINT PLTBDIV_ACT_DET_D00
> SELECT
> OBJECTPROPERTY(OBJECT_ID('CK__PLTBDIV_A_
_DIVID__7DF19EE6'),'CnstIsNotTrust
ed')
> The 2nd query returns 0.
> Can anyone see what might be the problem? Or are partitoned views not
> meant to improve performance?
>
Too little information.
Post the UNION view, along with a query againt the view, and the results for
running the query with SET STATISTICS_IO ON.
David|||John,
I don't know if it's what you're seeing, but a common confusion here
is that the *estimated* query plan shows all tables accessed with
equal cost, but the *actual* plan only accesses the relevant table.
In the query plan details, you will often see the dependence of
each table's access hinging on STARTUP_EXPR, which is
evaluated at run time before any of the tables are accessed.
If you run the query with SET STATISTICS IO ON in cases
like this, you will see all tables listed, but the number of reads
for the unneeded tables will be zero.
What exactly is indicating to you that "SQL Server is opening ALL
tables in the view" ?
Steve Kass
Drew University
John Kotuby wrote:

>Hi All,
>We have run into a huge problem at a large data-heavy multi-user
>installation. In order to increase response times we split up a number of
>tables into "Company" specific divisions to reduce the volume of records in
>each Division and then placed Check constaints on each table by DIVID. We
>Unioned the tables into partitioned views.
>However, contrary to documentation and recommendations from this newsgroup,
>even though we include DIVID in all our queries, Enterprise Manager and QA
>indicate that SQL Server is opening ALL tables in the view during a Query o
r
>an Update. I am thinking we must have done something wrong, or misunderstoo
d
>what partitoned views are supposed to do. We thought that the Check
>constraints would allow SQL Server to key in immediately on the requested
>divisional table. Here are some code examples:
>A Table:
>CREATE TABLE [PLTBDIV_ACT_DET_D00]
>(
> [DIVID] [CHAR] (3) NOT NULL CHECK (DIVID='D00')
>,[ACCTID] [CHAR] (10) NOT NULL
>,[ACT_TYPE] [CHAR] (1) NOT NULL
>,[TRANID] [CHAR] (10) NOT NULL
>,[TRANDATE] [SMALLDATETIME]
>,[TRANCODE] [CHAR] (5)
>,[TRANMODE] [CHAR] (5)
>,[TRANREF] [CHAR] (25)
>,[CSHREF] [CHAR] (25)
>,[REVERSED] [CHAR] (1)
>,[CHECKNUM] [CHAR] (25)
>,[CHECKACCTID] [CHAR] (10)
>,[INV_NUM] [CHAR] (12)
>,[REBILLID] [CHAR] (3)
>,[INV_TYPE] [CHAR] (1)
>,[TRANAMOUNT] [NUMERIC] (19,4)
>,[APPLIED] [NUMERIC] (19,4)
>,[AVAILABLE] [NUMERIC] (19,4)
>,[COMMENT] [CHAR] (90)
>,[CREATION] [SMALLDATETIME]
>,[BATCHNUM] [CHAR] (12) NULL
>,[CLOSING] [CHAR] (1)
>,[USERID] [CHAR] (15)
> )
>Note the Check constraint.
>The Primary Key:
>ALTER TABLE [dbo].[PLTBDIV_ACT_DET_D00] WITH NOCHECK ADD CONSTRAINT
>[PLPKDIV_ACT_DET_D00]
>PRIMARY KEY CLUSTERED
>(
> [DIVID]
>,[TRANID]
> ) --WITH FILLFACTOR = 10
>The View is a simple Union of all tables.
>Some TSQL tests run indicate that the Constraint is trusted.
>EXEC SP_HELPCONSTRAINT PLTBDIV_ACT_DET_D00
>SELECT
> OBJECTPROPERTY(OBJECT_ID('CK__PLTBDIV_A_
_DIVID__7DF19EE6'),'CnstIsNotTruste
d')
>The 2nd query returns 0.
>Can anyone see what might be the problem? Or are partitoned views not meant
>to improve performance?
>Thanks to all in advance.
>
>
>|||Got caught up in work....
Here is a simple query that seems to be hitting only one table.
SET STATISTICS IO ON
SELECT DIVID, TRANID, INV_NUM FROM PLVWDIV_ACT_DET
WHERE DIVID = 'D02' AND ACT_TYPE = 'P' AND TRANDATE > '01/01/2006'
(1509 row(s) affected)
Table 'PLTBDIV_ACT_DET_D02'. Scan count 1, logical reads 5456, physical
reads 0,
read-ahead reads 4.
There are approximately 320,000 records in the PLTBDIV_ACT_DET_D02 table.
We join about 30 tables in the view.
We were watching Current Activity in EM at the site and refreshing every few
seconds and saw all tables in multiple partitioned views open at the same
time. In some cases every table had locks on them. We were getting timeouts
from the VB client.
When we changed our programming techniques to use Paramaterized queries that
hit the tables directly most of that contention just disappeared.
CREATE VIEW [PLVWDIV_ACT_DET]
-- DESC: UNIONED VIEW INTO ACCOUNTS DETAIL
AS
SELECT * FROM [PLTBCOM_ACT_DET] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D00] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D01] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D02] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D03] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D04] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D05] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D06] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D07] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D08] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D09] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D10] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D11] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D12] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D13] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D14] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D15] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D16] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D17] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D18] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D19] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D20] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D21] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D22] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D23] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D24] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D25] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D26] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D27] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D28] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D29] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D30] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D98] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D99] WITH (NOLOCK)
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23jq5zRqcGHA.5048@.TK2MSFTNGP04.phx.gbl...
> John,
> I don't know if it's what you're seeing, but a common confusion here
> is that the *estimated* query plan shows all tables accessed with
> equal cost, but the *actual* plan only accesses the relevant table.
> In the query plan details, you will often see the dependence of
> each table's access hinging on STARTUP_EXPR, which is
> evaluated at run time before any of the tables are accessed.
> If you run the query with SET STATISTICS IO ON in cases
> like this, you will see all tables listed, but the number of reads
> for the unneeded tables will be zero.
> What exactly is indicating to you that "SQL Server is opening ALL
> tables in the view" ?
> Steve Kass
> Drew University
> John Kotuby wrote:
>|||Got caught up in work....
Here is a simple query that seems to be hitting only one table.
SET STATISTICS IO ON
SELECT DIVID, TRANID, INV_NUM FROM PLVWDIV_ACT_DET
WHERE DIVID = 'D02' AND ACT_TYPE = 'P' AND TRANDATE > '01/01/2006'
(1509 row(s) affected)
Table 'PLTBDIV_ACT_DET_D02'. Scan count 1, logical reads 5456, physical
reads 0,
read-ahead reads 4.
There are approximately 320,000 records in the PLTBDIV_ACT_DET_D02 table.
We join about 30 tables in the view.
We were watching Current Activity in EM at the site and refreshing every few
seconds and saw all tables in multiple partitioned views open at the same
time. In some cases every table had locks on them. We were getting timeouts
from the VB client.
When we changed our programming techniques to use Paramaterized queries that
hit the tables directly most of that contention just disappeared.
CREATE VIEW [PLVWDIV_ACT_DET]
-- DESC: UNIONED VIEW INTO ACCOUNTS DETAIL
AS
SELECT * FROM [PLTBCOM_ACT_DET] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D00] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D01] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D02] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D03] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D04] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D05] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D06] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D07] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D08] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D09] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D10] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D11] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D12] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D13] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D14] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D15] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D16] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D17] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D18] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D19] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D20] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D21] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D22] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D23] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D24] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D25] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D26] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D27] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D28] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D29] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D30] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D98] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D99] WITH (NOLOCK)
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eVErOIqcGHA.2404@.TK2MSFTNGP03.phx.gbl...
> "John Kotuby" <johnk@.powerlist.com> wrote in message
> news:OW40DgpcGHA.3908@.TK2MSFTNGP02.phx.gbl...
> Too little information.
> Post the UNION view, along with a query againt the view, and the results
> for running the query with SET STATISTICS_IO ON.
> David
>

parameters in a webapplication with a webservice

Hi,
I used a webservice (a reference web) in my webapplication in order to view
my report. However, my report has a parameter.
I don't know what to do. Are they any function with the ReportingService to
do this?
What are the steps to give a parameter to my report?Hi,
Take a look at the Render method. It is used to call the ReportService to
render a given report. You can provide parameters, output format, device
information and some other information. It returns a byte().
Hope this would help you further.
Jan Pieter Posthuma
"r388042" wrote:
> Hi,
> I used a webservice (a reference web) in my webapplication in order to view
> my report. However, my report has a parameter.
> I don't know what to do. Are they any function with the ReportingService to
> do this?
> What are the steps to give a parameter to my report?

Parameters in a OleDb Command Transformation

Hi there,

In order to prevent lookup errors in a lookup transformation, I've decided to go for an OleDb Command Transformation.

This transformation should check the lookup and, if it turns out to be null, ir returns a dummy value. Otherwise, it would return the lookup value.

This should be done by doing something like this:

select coalesce( (select ID_Table2 from ID_Table2 where FK_Table1 = ?), 0)

suposing Table2 has an atribute called "FK_Table1" that should match a column in the data flow.

Now, such command result in this message:

"An OLE DB record is available. Source "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Syntax eror, permission violation, or other nonspecific error".

But, it I remove the coalesce and type the following command:

select ID_Table2 from ID_Table2 where FK_Table1 = ?

It presents me no errors and allows me to continue.

Did i did anything wrong or is this something that is not possible to be done?

I know i have the option to use a script task to do this operation, but that would turn the maintenance process a little more difficult.

Otherwise, i know i could also re-direct the error from the lookup transformation and handle it. Though, my package has about 10 lookups and that would turn my package a lot more complex than

Thanks in advance

Best Regards

Andr Santana

You do know that the OLE DB Command will do a row by row search in the dataflow, and that it will be much slower than using a cached lookup, right?

As for your coalesce statement, I don't think the OLE DB command can handle parameters inside a subquery.|||

Andre,

You have more than one option to return a dummy value if the lookup fails. Here is one:

1. Configure the lookup component error output to 'ignore failure'; the place a derived column after the lookup to replace the nulls in the lookup column by the dummy value

As Phil says, OLE DB command will perform the operation for every row then the performance is worse than using Lookup transform

|||

Hello again,

Thank you Phil and Rafael for the quick reply...

This solves my problem... I didn't know I could "return" a dummy that way...

And it also improves the overall performance.

Thanks for the help

Andr

Parameterized order by clause: doesnt work

Can someone tell me why SQL seems to ignore my order by clause?
I tried to run through the debugger, but the debugger stops at the
select statement line and then returns the result set; so, I have no
idea how it is evaluating the order by clause.
THANK YOU!

CREATE proc sprAllBooks

@.SortAscend varchar(4),
@.SortColumn varchar(10)

as

If @.SortAscend = 'DESC'
Select titles.title_id, title, au_lname, au_fname,
Convert(varchar(12), pubdate, 101) as PubDate

from authors
inner join
titleauthor
on
authors.au_id = titleauthor.au_id
inner join
titles
on
titleauthor.title_id = Titles.title_id

ORDER BY au_lname
CASE @.SortColumn WHEN 'title' THEN title END,
CASE @.SortColumn WHEN 'au_lname' THEN au_lname END,
CASE @.SortColumn WHEN 'PubDate' THEN PubDate END
DESC

ELSE

Select titles.title_id, title, au_lname, au_fname,
Convert(varchar(12), pubdate, 101) as PubDate

from authors

inner join
titleauthor
on
authors.au_id = titleauthor.au_id
inner join
titles
on
titleauthor.title_id = Titles.title_id

ORDER BY
CASE @.SortColumn WHEN 'title' THEN title END,
CASE @.SortColumn WHEN 'au_lname' THEN au_lname END,
CASE @.SortColumn WHEN 'PubDate' THEN PubDate END
GOOn 25 Feb 2005 08:39:07 -0800, JJ_377@.hotmail.com wrote:

>Can someone tell me why SQL seems to ignore my order by clause?
>I tried to run through the debugger, but the debugger stops at the
>select statement line and then returns the result set; so, I have no
>idea how it is evaluating the order by clause.
>THANK YOU!

Hi JJ,

You forgot to tell us how you call the procedure (what values for
@.SortAscend and @.SortColumn you use) and what results you get.

I tried your code and after fixing a syntax error, it works as I would
expect it to. You apparently expected something else, but what you
expected is not clear from your post.

Some small pointers to what might be your problem:

>If @.SortAscend = 'DESC'
(snip)
>ORDER BY au_lname
>CASE @.SortColumn WHEN 'title' THEN title END,
>CASE @.SortColumn WHEN 'au_lname' THEN au_lname END,
>CASE @.SortColumn WHEN 'PubDate' THEN PubDate END
>DESC

This resulted in an error. You either have to append a comma after ORDER
BY au_lname, or remove au_lname. I figured the latter is what you want,
so that's how I tested it.

The last CASE expression won't refer to the formatted pubdate, but to
the pubdate column in the table. Even though the ORDER BY does allow you
to refer to an alias from the SELECT clause, it does not allow you to do
so in an expression.

The DESC will only apply to the ordering by pubdate. The title and
au_lname are in seperate expressions that require a seperate DESC:
ORDER BY
CASE @.SortColumn WHEN 'title' THEN title END DESC,
CASE @.SortColumn WHEN 'au_lname' THEN au_lname END DESC,
CASE @.SortColumn WHEN 'PubDate' THEN pubdate END DESC

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you Hugo. Good points. For debugging purposes, I've been calling
the sproc from the debugger with the parameters @.SortAscend = 'DESC'
and @.SortColumn = 'title. The result set is not sorted by title desc,
as I would expect it should have been. Quite inexplicably (to me), the
result set now seems to be sorting by au_lname desc (!)

Note that the order by clause should only feature the case evaluation
statement (au_lname was there as a left-over from a debugging
attempt...):

ORDER BY
CASE @.SortColumn WHEN 'title' THEN title END,
CASE @.SortColumn WHEN 'au_lname' THEN au_lname END,
CASE @.SortColumn WHEN 'PubDate' THEN PubDate END

I think I see what you are getting at about the PubDate (alias) and
pubdate (table column) name and will give that a try...

Also, you related that one part of the code resulted in an error...can
you tell me how to unearth that? The Sql debugger seemed to be silent
on that point to me...

Thank you again...

J.

Hugo Kornelis wrote:
> On 25 Feb 2005 08:39:07 -0800, JJ_377@.hotmail.com wrote:
> >Can someone tell me why SQL seems to ignore my order by clause?
> >I tried to run through the debugger, but the debugger stops at the
> >select statement line and then returns the result set; so, I have no
> >idea how it is evaluating the order by clause.
> >THANK YOU!
> Hi JJ,
> You forgot to tell us how you call the procedure (what values for
> @.SortAscend and @.SortColumn you use) and what results you get.
> I tried your code and after fixing a syntax error, it works as I
would
> expect it to. You apparently expected something else, but what you
> expected is not clear from your post.
> Some small pointers to what might be your problem:
> >If @.SortAscend = 'DESC'
> (snip)
> >ORDER BY au_lname
> >CASE @.SortColumn WHEN 'title' THEN title END,
> >CASE @.SortColumn WHEN 'au_lname' THEN au_lname END,
> >CASE @.SortColumn WHEN 'PubDate' THEN PubDate END
> >DESC
> This resulted in an error. You either have to append a comma after
ORDER
> BY au_lname, or remove au_lname. I figured the latter is what you
want,
> so that's how I tested it.
> The last CASE expression won't refer to the formatted pubdate, but to
> the pubdate column in the table. Even though the ORDER BY does allow
you
> to refer to an alias from the SELECT clause, it does not allow you to
do
> so in an expression.
> The DESC will only apply to the ordering by pubdate. The title and
> au_lname are in seperate expressions that require a seperate DESC:
> ORDER BY
> CASE @.SortColumn WHEN 'title' THEN title END DESC,
> CASE @.SortColumn WHEN 'au_lname' THEN au_lname END DESC,
> CASE @.SortColumn WHEN 'PubDate' THEN pubdate END DESC
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||I figured it out. The order by clause has to look like this:
ORDER BY
CASE @.SortColumn WHEN 'title' THEN title END DESC,
CASE @.SortColumn WHEN 'au_lname' THEN au_lname END DESC,
CASE @.SortColumn WHEN 'PubDate' THEN pubdate END DESC

I put the 'DESC' parameter inside each of the case statements and now
my result set is sorting!

JJ_377@.hotmail.com wrote:
> Thank you Hugo. Good points. For debugging purposes, I've been
calling
> the sproc from the debugger with the parameters @.SortAscend = 'DESC'
> and @.SortColumn = 'title. The result set is not sorted by title desc,
> as I would expect it should have been. Quite inexplicably (to me),
the
> result set now seems to be sorting by au_lname desc (!)
> Note that the order by clause should only feature the case evaluation
> statement (au_lname was there as a left-over from a debugging
> attempt...):
> ORDER BY
> CASE @.SortColumn WHEN 'title' THEN title END,
> CASE @.SortColumn WHEN 'au_lname' THEN au_lname END,
> CASE @.SortColumn WHEN 'PubDate' THEN PubDate END
> I think I see what you are getting at about the PubDate (alias) and
> pubdate (table column) name and will give that a try...
> Also, you related that one part of the code resulted in an
error...can
> you tell me how to unearth that? The Sql debugger seemed to be silent
> on that point to me...
> Thank you again...
> J.
>
>
> Hugo Kornelis wrote:
> > On 25 Feb 2005 08:39:07 -0800, JJ_377@.hotmail.com wrote:
> > >Can someone tell me why SQL seems to ignore my order by clause?
> > >I tried to run through the debugger, but the debugger stops at the
> > >select statement line and then returns the result set; so, I have
no
> > >idea how it is evaluating the order by clause.
> > >THANK YOU!
> > Hi JJ,
> > You forgot to tell us how you call the procedure (what values for
> > @.SortAscend and @.SortColumn you use) and what results you get.
> > I tried your code and after fixing a syntax error, it works as I
> would
> > expect it to. You apparently expected something else, but what you
> > expected is not clear from your post.
> > Some small pointers to what might be your problem:
> > >If @.SortAscend = 'DESC'
> > (snip)
> > >ORDER BY au_lname
> > >CASE @.SortColumn WHEN 'title' THEN title END,
> > >CASE @.SortColumn WHEN 'au_lname' THEN au_lname END,
> > >CASE @.SortColumn WHEN 'PubDate' THEN PubDate END
> > >DESC
> > This resulted in an error. You either have to append a comma after
> ORDER
> > BY au_lname, or remove au_lname. I figured the latter is what you
> want,
> > so that's how I tested it.
> > The last CASE expression won't refer to the formatted pubdate, but
to
> > the pubdate column in the table. Even though the ORDER BY does
allow
> you
> > to refer to an alias from the SELECT clause, it does not allow you
to
> do
> > so in an expression.
> > The DESC will only apply to the ordering by pubdate. The title and
> > au_lname are in seperate expressions that require a seperate DESC:
> > ORDER BY
> > CASE @.SortColumn WHEN 'title' THEN title END DESC,
> > CASE @.SortColumn WHEN 'au_lname' THEN au_lname END DESC,
> > CASE @.SortColumn WHEN 'PubDate' THEN pubdate END DESC
> > Best, Hugo
> > --
> > (Remove _NO_ and _SPAM_ to get my e-mail address)|||On 25 Feb 2005 09:15:39 -0800, JJ_377@.hotmail.com wrote:

>Thank you Hugo. Good points. For debugging purposes, I've been calling
>the sproc from the debugger with the parameters @.SortAscend = 'DESC'
>and @.SortColumn = 'title. The result set is not sorted by title desc,
>as I would expect it should have been. Quite inexplicably (to me), the
>result set now seems to be sorting by au_lname desc (!)
>Note that the order by clause should only feature the case evaluation
>statement (au_lname was there as a left-over from a debugging
>attempt...):

Hi J,

I was unable to reproduce this. If I run the query you posted here
(after removing the left-over au_lname) with argument DESC and title, I
get the results in ascending order of title. Not sorted by au_lname.

The only way to get it to sort by descending au_lname is to leave the
left-over au_lname in and remove or comment the three CASE expressions.

>Also, you related that one part of the code resulted in an error...can
>you tell me how to unearth that? The Sql debugger seemed to be silent
>on that point to me...

I'm talking about the code as you posted it here in your original
message (with the left-over au_lname included). I get an error when I
try to create the procedure, or when I try to run that statements by
themselves. The only ways tol solve it are to remove au_lname, to add a
comma at the end of the line or to remove the three case expressions.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks very much again Hugo. All is well now -- with the DESC keywords
within each CASE statement:

ORDER BY
CASE @.SortColumn WHEN 'title' THEN title END DESC,
CASE @.SortColumn WHEN 'au_lname' THEN au_lname END DESC,
CASE @.SortColumn WHEN 'PubDate' THEN pubdate END DESC

I am just starting to use the debugger in SQL and therefore am *very*
interested in learning as much as I can about using it to advantage.
Again, it didn't "complain" about:

ORDER BY
CASE @.SortColumn WHEN 'title' THEN title END,
CASE @.SortColumn WHEN 'au_lname' THEN au_lname END ,
CASE @.SortColumn WHEN 'PubDate' THEN pubdate END
DESC

but, nor did the stored procedure return the intended result set!
I have used QA and the Profiler exclusively to help debug problems.

Jules|||On 25 Feb 2005 13:00:22 -0800, JJ_377@.hotmail.com wrote:

(snip)
>Again, it didn't "complain" about:
>ORDER BY
>CASE @.SortColumn WHEN 'title' THEN title END,
>CASE @.SortColumn WHEN 'au_lname' THEN au_lname END ,
>CASE @.SortColumn WHEN 'PubDate' THEN pubdate END
>DESC
>but, nor did the stored procedure return the intended result set!

Hi Jules,

It should not complain about this - it's valid T-SQL syntax. It
specifies that the results should be ordered by three columns:
* first by (depending on the value of @.SortColumn) either title or NULL,
ascending (since no direction is specified);
* next by (depending on the value of @.SortColumn) either au_lname or
NULL, again ascending;
* and finally, if the previous two didn't suffice to define the sort
order, by (depending on @.SortColumn) either pubdate or NULL, but for
this column a descending sort is specified.

>I have used QA and the Profiler exclusively to help debug problems.

Those tools are the best (IMO), both for writing and debugging SQL code.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||hmm...I understand what you are saying...interesting
it makes sense now - thanks!

Hugo Kornelis wrote:
> On 25 Feb 2005 13:00:22 -0800, JJ_377@.hotmail.com wrote:
> (snip)
> >Again, it didn't "complain" about:
> >ORDER BY
> >CASE @.SortColumn WHEN 'title' THEN title END,
> >CASE @.SortColumn WHEN 'au_lname' THEN au_lname END ,
> >CASE @.SortColumn WHEN 'PubDate' THEN pubdate END
> >DESC
> >but, nor did the stored procedure return the intended result set!
> Hi Jules,
> It should not complain about this - it's valid T-SQL syntax. It
> specifies that the results should be ordered by three columns:
> * first by (depending on the value of @.SortColumn) either title or
NULL,
> ascending (since no direction is specified);
> * next by (depending on the value of @.SortColumn) either au_lname or
> NULL, again ascending;
> * and finally, if the previous two didn't suffice to define the sort
> order, by (depending on @.SortColumn) either pubdate or NULL, but for
> this column a descending sort is specified.
>
> >I have used QA and the Profiler exclusively to help debug problems.
> Those tools are the best (IMO), both for writing and debugging SQL
code.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)