Parents of a SET of members

Hello,

I have a set of members, generated with a SET clause at the top of my MDX query.
Once I have this set, what function can I use on an axis to determine the parents (i.e the level above) of these members? Those parents will be a set too as the original set will have many different parents.

Or am I going about this the wrong way?

Thanks in advance
Mgale1

Try something like:

SELECT Generate({...} as Set1, ANCESTORS(Set1.CurrentMember, 1)) FROM MyCube

|||

Matt,

Thanks for the reply - unfortunately it gives me an error of "The CURRENT function cannot be invoked in this context because the 'MissedJobs' set is not in the scope."

My full query is :

WITH
MEMBER [Measures].[Incidence TY] AS SUM({[Media].[Media].CURRENTMEMBER},([Timeslice].[Timeslice].&[1], [Measures].[Incidence])),FORMAT_STRING = "#,##0.00",NON_EMPTY_BEHAVIOR=[Measures].[Incidence]
MEMBER [Measures].[MyPublicationValue] AS SUM({[Media].[Media].&[7004]},[Measures].[Incidence TY]) ,NON_EMPTY_BEHAVIOR=[Measures].[Incidence]
SET [MissedJobs] AS Filter(Existing([Job].[Job].CHILDREN),NOT [Measures].[MyPublicationValue])

--Produce Report
SELECT CROSSJOIN
(VISUALTOTALS({[Media].[Media].[All], [Media].[Media].&[7002],[Media].[Media].&[7004]})
,{[Measures].[Incidence TY]
}) ON COLUMNS,
NON EMPTY ANCESTORS([MissedJobs].CurrentMember,1) ON ROWS
FROM jdx_cube
WHERE [Date].[Full Date].&[2007-07-01T00:00:00]:[Date].[Full Date].&[2007-07-30T00:00:00]

The critical dimension here is the Job dimension. It has 2 attributes where the "Advertiser" atrib is the parent of the "Job" atrb. In my Set statement in the WITH section I work out which Jobs I dont have and then I want the query to return the Advertisers that 'parent' the jobs I dont have.

Can you offer anymore help please?

Thanks

Mgale1

|||

Try:

...

NON EMPTY GENERATE([MissedJobs], ANCESTORS([MissedJobs].CurrentMember, 1)) ON ROWS

...

|||

Hello Matt,

Thanks for the reply and I am sorry for not getting back to this thread earlier (been ill).

Unfortunately this is not quite what I want and I should have been clearer in my initial question.
I want to work out the missed jobs set but then display on the axis the name of the 'parent' advertiser but the value in the cellset should be the value of the missed jobs and NOT the value for the parent (which is what this GENERATE solution gives me).

What I mean is, if Advertiser1 has two jobs (Job11 and Job12) with values of 11 and 12 respectively and my Missed Jobs set calculates that only Job 11 applies, then I want to see "Advertiser1, 11 "on the axis.
The GENERATE approach displays "Advertiser1, 23" which is not what I am after.

Do you have any other ideas please?

With continuing thanks
Mgale1

Parents of a SET of members

Hello,

I have a set of members, generated with a SET clause at the top of my MDX query.
Once I have this set, what function can I use on an axis to determine the parents (i.e the level above) of these members? Those parents will be a set too as the original set will have many different parents.

Or am I going about this the wrong way?

Thanks in advance
Mgale1

Try something like:

SELECT Generate({...} as Set1, ANCESTORS(Set1.CurrentMember, 1)) FROM MyCube

|||

Matt,

Thanks for the reply - unfortunately it gives me an error of "The CURRENT function cannot be invoked in this context because the 'MissedJobs' set is not in the scope."

My full query is :

WITH
MEMBER [Measures].[Incidence TY] AS SUM({[Media].[Media].CURRENTMEMBER},([Timeslice].[Timeslice].&[1], [Measures].[Incidence])),FORMAT_STRING = "#,##0.00",NON_EMPTY_BEHAVIOR=[Measures].[Incidence]
MEMBER [Measures].[MyPublicationValue] AS SUM({[Media].[Media].&[7004]},[Measures].[Incidence TY]) ,NON_EMPTY_BEHAVIOR=[Measures].[Incidence]
SET [MissedJobs] AS Filter(Existing([Job].[Job].CHILDREN),NOT [Measures].[MyPublicationValue])

--Produce Report
SELECT CROSSJOIN
(VISUALTOTALS({[Media].[Media].[All], [Media].[Media].&[7002],[Media].[Media].&[7004]})
,{[Measures].[Incidence TY]
}) ON COLUMNS,
NON EMPTY ANCESTORS([MissedJobs].CurrentMember,1) ON ROWS
FROM jdx_cube
WHERE [Date].[Full Date].&[2007-07-01T00:00:00]:[Date].[Full Date].&[2007-07-30T00:00:00]

The critical dimension here is the Job dimension. It has 2 attributes where the "Advertiser" atrib is the parent of the "Job" atrb. In my Set statement in the WITH section I work out which Jobs I dont have and then I want the query to return the Advertisers that 'parent' the jobs I dont have.

Can you offer anymore help please?

Thanks

Mgale1

|||

Try:

...

NON EMPTY GENERATE([MissedJobs], ANCESTORS([MissedJobs].CurrentMember, 1)) ON ROWS

...

|||

Hello Matt,

Thanks for the reply and I am sorry for not getting back to this thread earlier (been ill).

Unfortunately this is not quite what I want and I should have been clearer in my initial question.
I want to work out the missed jobs set but then display on the axis the name of the 'parent' advertiser but the value in the cellset should be the value of the missed jobs and NOT the value for the parent (which is what this GENERATE solution gives me).

What I mean is, if Advertiser1 has two jobs (Job11 and Job12) with values of 11 and 12 respectively and my Missed Jobs set calculates that only Job 11 applies, then I want to see "Advertiser1, 11 "on the axis.
The GENERATE approach displays "Advertiser1, 23" which is not what I am after.

Do you have any other ideas please?

With continuing thanks
Mgale1

Parameters in select clause

Hi all,
My query needs to work with different back ends say oracle, sql server,db2
etc. so i need to make use of only the ansi sql. I need something like
="select col1,col2,"&_
"(case when parameters!gender.value='m' then 'some calculation'"&_
"when parameters!gender.value='f' then 'some other calculation'"&_
"end) as calc_name,"&
"from table1" & parameters!where_clause.value
but i could not make the above code work. Please let me the correct syntax
to get the above kind of query work.
Thanks,
RSUserWhat you should do is set that expression to a textbox first so you can see
what you are getting. Otherwise any little thing gets the same result, it
doesn't work but you don't know why. I notice below you have an comma right
before from.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"RSUser" <RSUser@.discussions.microsoft.com> wrote in message
news:C0AA0793-2606-4D38-AC7A-C37162EA4EDE@.microsoft.com...
> Hi all,
> My query needs to work with different back ends say oracle, sql server,db2
> etc. so i need to make use of only the ansi sql. I need something like
> ="select col1,col2,"&_
> "(case when parameters!gender.value='m' then 'some calculation'"&_
> "when parameters!gender.value='f' then 'some other calculation'"&_
> "end) as calc_name,"&
> "from table1" & parameters!where_clause.value
> but i could not make the above code work. Please let me the correct syntax
> to get the above kind of query work.
> Thanks,
> RSUser
>|||Hi, RSUser
you can use an immediate if :
="select col1,col2," & IIF(Parameters!Gender.Value ='M', "some
calculation","some other calculation") & " as calc_name from table1" &
Parameters!where_clause.value"
Just copy and paste it.
Please, vote.
ProJester, MSN Programmer.
"RSUser" wrote:
> Hi all,
> My query needs to work with different back ends say oracle, sql server,db2
> etc. so i need to make use of only the ansi sql. I need something like
> ="select col1,col2,"&_
> "(case when parameters!gender.value='m' then 'some calculation'"&_
> "when parameters!gender.value='f' then 'some other calculation'"&_
> "end) as calc_name,"&
> "from table1" & parameters!where_clause.value
> but i could not make the above code work. Please let me the correct syntax
> to get the above kind of query work.
> Thanks,
> RSUser
>|||Use the Immediate If as Projester indicates. The reason is that the dynamic
query is parsed and fixed up as a VBScript first. Then the results are sent
to SQL ... The SQL Case you are using should be replaced by IIF so that the
VBScript can generate the correct SQL.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"RSUser" <RSUser@.discussions.microsoft.com> wrote in message
news:C0AA0793-2606-4D38-AC7A-C37162EA4EDE@.microsoft.com...
> Hi all,
> My query needs to work with different back ends say oracle, sql server,db2
> etc. so i need to make use of only the ansi sql. I need something like
> ="select col1,col2,"&_
> "(case when parameters!gender.value='m' then 'some calculation'"&_
> "when parameters!gender.value='f' then 'some other calculation'"&_
> "end) as calc_name,"&
> "from table1" & parameters!where_clause.value
> but i could not make the above code work. Please let me the correct syntax
> to get the above kind of query work.
> Thanks,
> RSUser
>|||Hi all,
Thanks for your replies. My case statement is so complex as i have to check
for 10 different values and do different calculations for each case unlike
the example i provided where there was just two, male and female. Thus i need
further help as to decide if it is good to use iif or to use external code
like vb.net or c# and solve the problem.
Thanks,
RSUser
"Wayne Snyder" wrote:
> Use the Immediate If as Projester indicates. The reason is that the dynamic
> query is parsed and fixed up as a VBScript first. Then the results are sent
> to SQL ... The SQL Case you are using should be replaced by IIF so that the
> VBScript can generate the correct SQL.
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "RSUser" <RSUser@.discussions.microsoft.com> wrote in message
> news:C0AA0793-2606-4D38-AC7A-C37162EA4EDE@.microsoft.com...
> > Hi all,
> >
> > My query needs to work with different back ends say oracle, sql server,db2
> > etc. so i need to make use of only the ansi sql. I need something like
> >
> > ="select col1,col2,"&_
> > "(case when parameters!gender.value='m' then 'some calculation'"&_
> > "when parameters!gender.value='f' then 'some other calculation'"&_
> > "end) as calc_name,"&
> > "from table1" & parameters!where_clause.value
> >
> > but i could not make the above code work. Please let me the correct syntax
> > to get the above kind of query work.
> >
> > Thanks,
> > RSUser
> >
>
>

Parameters as Column names

I have a simple query but I need the parameter to select the column name
of the WHERE clause.
SELECT col1, col2, col3, col4 FROM Table WHERE @.colname = 1
The query builder is changing this everytime I set a parameter as a column
name. Is there a way to do this?I figured out my problem. I made the query a dynamic query by making it:
="SELECT col1, col2, col3, col4 FROM Table WHERE " & Parameters!Colname.Value
& " = 1"
I was running into all sorts of errors, but I found out that the query needs
to be all on one line with no linebreaks.
> I have a simple query but I need the parameter to select the column
> name of the WHERE clause.
> SELECT col1, col2, col3, col4 FROM Table WHERE @.colname = 1
> The query builder is changing this everytime I set a parameter as a
> column name. Is there a way to do this?
>

Parameterized where clause

Hello,
I have an add stored procedure in Yukon (would work in 2000 too), where I select the ID from the table to make sure that it doesn't already have the data. So it looks like:
create procedure ..
..
set transaction isolation level serializable
begin transaction
declare @.ID int
select @.ID = rowid from tblBusinessInformation where Name = @.Name and Rules = @.Rules
if ( @.ID is NULL )
begin
insert into tblBusinessInformation (..) values (@.Name, @.Rules)
end
commit transaction
The problem is the values could be:
Name Rules
NULL 'Test'
'Test' NULL
'Test' 'Test'
When one of the values was NULL, it would never select the ID, unless I changed it to "where Name is @.Name", and then it worked, because where Name is NULL, which is correct in SQL; so how do I allow for both; I can use the CLR, but would like to avoid rewriting the proc if possible, and I thought that was to work...
Thanks.perhaps you can use the CASE stmt. So what xactly is your condition..you want to allow NULLs in both columns or select an ID if either Name or Rules is NULL ? If you can state what conditions you are trying to match we can help you with the SQL.|||

Hello,
Well, what I need to do is ensure uniqueness in the table, for the name/rules pairing, so for example, the proc searches for a @.name value of "bob" and a @.rules value of "must be a bob", this must be unique in the table. It isn't the primary key; I use an identity value for the key, so I have three fields, the ID, name, and rules. Now the @.names can be null or the @.rules can be null, but not both.
The problem is, with this query:
declare @.ID int
select @.ID = rowid from tblBusinessInformation where Name = @.Name and Rules = @.Rules
if @.Name is null and @.Rules has a value, it doesn't find the ID; however, for Name, when I change "Name = @.Name" to "Name is @.Name", then I find that it works; however, I can't use is because whenever @.Name isn't null, then that causes a problem. I thought this wasn't supposed to be the case.
I can use the CLR, but I was trying to avoid it because of all the code necessary for a simpler statement. Any ideas how to avoid this? As an alternative, I can try putting in a blank space for when it is null and then use the ISNULL function, but I would like to use the NULL value.
Thanks for looking at this.

|||

I dont think I completely understood but I will take a guess. You could do something like :

SELECT
@.ID = rowid
FROM
tblBusinessInformation
WHERE
ISNULL(Name,'') = ISNULL(@.Name,'') AND ISNULL(Rules,'') = ISNULL(@.Rules,'')

Parameterized SP in WHERE Clause of Another SP

I've got this SP:
CREATE PROCEDURE
EWF_spCustom_AddProfiles_CompanyYear
@.prmSchoolYear char(11)
AS
SELECT
ContactID
FROM
dbo.EWF_tblCustom_CompanyProfile
WHERE
SchoolYear = @.prmSchoolYear

I'd like to be able to reference that in the where clause of another
SP. Is that possible?

I'd like to end up with something like this:
CREATE PROCEDURE
MyNewProc
@.prmSchoolYear2 char(11)
AS
SELECT
ContactID, SomeOtherFields
FROM
tblContact
WHERE
ContactID IN (exec EWF_spCustom_AddProfiles_CompanyYear
@.prmSchoolYear2)

How would I make that happen?

If this isn't possible, what else might I try?

Thanks much for any pointers.

Jeremy

PS: I accidentally crossposted this in another group
(http://tinyurl.com/gksq4) thinking it was this one. Sorry for that.jeremygetsmail@.gmail.com (jeremygetsmail@.gmail.com) writes:
> I've got this SP:
> CREATE PROCEDURE
> EWF_spCustom_AddProfiles_CompanyYear
> @.prmSchoolYear char(11)
> AS
> SELECT
> ContactID
> FROM
> dbo.EWF_tblCustom_CompanyProfile
> WHERE
> SchoolYear = @.prmSchoolYear
> I'd like to be able to reference that in the where clause of another
> SP. Is that possible?
> I'd like to end up with something like this:
> CREATE PROCEDURE
> MyNewProc
> @.prmSchoolYear2 char(11)
> AS
> SELECT
> ContactID, SomeOtherFields
> FROM
> tblContact
> WHERE
> ContactID IN (exec EWF_spCustom_AddProfiles_CompanyYear
> @.prmSchoolYear2)
> How would I make that happen?

First of all, if your code is as simple as that, it may not even
be worth the effort. Code reuse in all its glory, but it is not always
the best in a database. That is not to say that code reuse is not
a virtue in SQL at all, but just a little smaller virtue.

Anyway, this article of mine may give you some ideas:
http://www.sommarskog.se/share_data.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Parameterized queries taking more time

Do parameterized queries take up more CPU resources than if the where clause
values were hard coded ?
Thanks.Chakravarthy,
can you tell us where are you testing this and how?
AMB
"Chakravarthy" wrote:
> Do parameterized queries take up more CPU resources than if the where clause
> values were hard coded ?
> Thanks.
>
>|||I'm sure they take up a very, very, very small amount more than a
hard-coded query. The query PLAN that's used is far and away the most
important thing to consider, though. If the plans are identical, you
will have queries which, under the same conditions, will perform
identically.
Chakravarthy wrote:
> Do parameterized queries take up more CPU resources than if the where clause
> values were hard coded ?
> Thanks.
>

Parameterized part of the query?

I have a standard report that I would like to use over and over. The
difference for each version is the WHERE clause on the dataset SQL. I tried
using a parameter and sending in the information but it doesn't seem to work.
Perhaps I am formatting it incorrectly. Any suggestions? Can I do this?
StephanieStephanie,
I hope, you do not want to pass T-SQL as a parameter. If you do, you should
have sql injection attack.
Try to do the following:
Open BIDS and create new Report project from file menu.
Add a new report item to your project.
Navigate to the data tab in the designer, then create a new dataset.
Choose command type - I recommend to use stored procedures, because of
security reason.
Type stored procedure name to the query string.
All of the stored procedure's parameters will be added automatically to your
parameter list in the report.
If you use command type text, then you should add manually the parameters if
BIDS does not do automatically.
You should find a really good step by step guide in the BOL under the SQL
Server 2005 Tutorials --> Reporting Services Tutorials --> Using a Dynamic
Query in a Report section.
I hope it will help for you.
Kind Regards,
Janos
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:4AE93DB5-D7B0-4C04-AA61-399062840200@.microsoft.com...
>I have a standard report that I would like to use over and over. The
> difference for each version is the WHERE clause on the dataset SQL. I
> tried
> using a parameter and sending in the information but it doesn't seem to
> work.
> Perhaps I am formatting it incorrectly. Any suggestions? Can I do this?
> Stephanie|||Janos,
Thanks for the help. I was able to find what I needed using the tutorial
you identified.
Stephanie
"BERKE Janos" wrote:
> Stephanie,
> I hope, you do not want to pass T-SQL as a parameter. If you do, you should
> have sql injection attack.
> Try to do the following:
> Open BIDS and create new Report project from file menu.
> Add a new report item to your project.
> Navigate to the data tab in the designer, then create a new dataset.
> Choose command type - I recommend to use stored procedures, because of
> security reason.
> Type stored procedure name to the query string.
> All of the stored procedure's parameters will be added automatically to your
> parameter list in the report.
> If you use command type text, then you should add manually the parameters if
> BIDS does not do automatically.
> You should find a really good step by step guide in the BOL under the SQL
> Server 2005 Tutorials --> Reporting Services Tutorials --> Using a Dynamic
> Query in a Report section.
> I hope it will help for you.
> Kind Regards,
> Janos
> "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> news:4AE93DB5-D7B0-4C04-AA61-399062840200@.microsoft.com...
> >I have a standard report that I would like to use over and over. The
> > difference for each version is the WHERE clause on the dataset SQL. I
> > tried
> > using a parameter and sending in the information but it doesn't seem to
> > work.
> > Perhaps I am formatting it incorrectly. Any suggestions? Can I do this?
> >
> > Stephanie
>

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)