ORDER BY, CASE, with multiple columns

I'm unable to specify multiple columns in my order by statement if i use a case statement.
Does anyone know why this is, or what syntax would make this work?

Thanks

SELECT ...
ORDER BY (CASE Lower(@.SortExpression)
WHEN 'prodname' THEN prodname, prodprice
WHEN 'prodsize' THEN prodsize, prodname
WHEN 'prodprice' THEN prodprice, prodname
Else prodcompany, prodname
END)

Also, i realize that in your order by statement, when you use CASE, all of your columns have to be the same data type.

So in ORDER BY clause above, i am attempting to order by "prodprice" as one of the possibilities. This produces the error:Error converting data type varchar to numeric.

The whole IDEA of a case statement is to avoid opening yourself to injection attacks by Dynamic Execution.

So...how can you use the case statement to order by multiple columns, and to order with different datatypes?|||Not sure it this helps butview post 386101 discusses something close to your question. Maybe the method discussed near the bottom can be adapted.|||Right, so you'd have something like this:

SET @.SortExpression = Lower(@.SortExpression)
SELECT ...
ORDER BY
CASE WHEN @.SortExpression = 'prodname' THEN prodname END,
CASE WHEN @.SortExpression = 'prodname' THEN prodprice END,
CASE WHEN @.SortExpression = 'prodsize' THEN prodsize END,
CASE WHEN @.SortExpression = 'prodsize' THEN prodname END,
CASE WHEN @.SortExpression = 'prodprice' THEN prodprice END,
CASE WHEN @.SortExpression = 'prodprice' THEN prodname END,
prodcompany,
prodname

I'm not exactly sure what that'll do to performance. It'd be worth it to see what the execution plan says.

Terri|||that alleviated some of my problem. Thanks.

How exactly do i test the excecution plan? or check to see if it's compiling all the way?

Can i use a SQL Trace?|||Check out this article:SQL Server Query Execution Plan Analysis.

Terri

ORDER BY in view does not execute in SQL Server 2005

Hi,
I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
which was converted to: "Select Top (100) Percent..." when I recreated my
views.
However any ORDER BY's in my views are not executed until I remove the
keyword Percent, e.g. Select Top (100)... works fine,
Is this a known difference between 2000 and 2005. If so is there a list of
these differences?
PeterHi Peter
Have you checked the backward compatibility sections in books online?
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde84
1.htm or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
John
"Peter Jones" wrote:

> Hi,
> I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
> did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
> which was converted to: "Select Top (100) Percent..." when I recreated my
> views.
> However any ORDER BY's in my views are not executed until I remove the
> keyword Percent, e.g. Select Top (100)... works fine,
> Is this a known difference between 2000 and 2005. If so is there a list of
> these differences?
> Peter
>|||Hello John,
Thanks - I hadn't done that but now that I have I haven't found anything. I
did check BOL for syntax changes for TOP and ORDER BY and they seem to be th
e
same from 2000 to 2005. There is certainly nothing in the 2005 documentation
that I've seen that say don't use PERCENT if a view has an ORDER BY clause.
Cheers, Peter
"John Bell" wrote:
[vbcol=seagreen]
> Hi Peter
> Have you checked the backward compatibility sections in books online?
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde
841.htm or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
> John
>
> "Peter Jones" wrote:
>|||Peter Jones wrote:
> Hello John,
> Thanks - I hadn't done that but now that I have I haven't found anything.
I
> did check BOL for syntax changes for TOP and ORDER BY and they seem to be
the
> same from 2000 to 2005. There is certainly nothing in the 2005 documentati
on
> that I've seen that say don't use PERCENT if a view has an ORDER BY clause
.
>
Views are not ordered in 2000 or in 2005 so nothing changed. Sort order
is determined by the queries that are issued against the view, not by
the view definition itself. If you query the view using a SELECT
statement that doesn't include ORDER BY then the ordering of the result
is undefined.
The reason ORDER BY is permitted in views at all is to support the TOP
operator, which uses ORDER BY to select a subset of rows not to order
the view (yes, I agree that the TOP syntax is not a very user-friendly
one).
Here's the example given in Books Online:
ORDER BY guarantees a sorted result only for the outermost SELECT
statement of a query. For example, consider the following view
definition:
CREATE VIEW TopView AS
SELECT TOP 50 PERCENT * FROM Person.Contact
ORDER BY LastName
Then query the view:
SELECT * FROM TopView
Although the view definition contains an ORDER BY clause, that ORDER BY
clause is used only to determine the rows returned by the TOP clause.
When querying the view itself, SQL Server does not guarantee the
results will be ordered, unless you specify so explicitly, as shown in
the following query:
SELECT * FROM TopView
ORDER BY LastName
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi Peter,
a known issue about Order By in a view in SQL 2005 is that theres is no
guarantee for the ordering in a view even though you are using selct top
100. Well it is kind of odd but actually it wasn't guaranteed in SQL 2K
either. But it has always worked. Now in SQl 2005 it doesn't work anymore.
Confused ? Well it's like when MS is stating that you should avoid the
direct use of the system tables because it not guaranteed to work in future
version but it worked fine for a lot of version. But suddenly they changed
it. But they will state that they warned us So I guess this is the same
regarding views and order by. Only way to solve it is to sort the output
from the view :
Select col1, col2 from view
order by col1
Maby this will help :
http://blogs.msdn.com/sqltips/archi.../20/441053.aspx
Regards
Bobby Henningsen
"Peter Jones" <PeterJones@.discussions.microsoft.com> wrote in message
news:5C2EFE1A-6D04-4604-AB3E-4D8EA11F86EB@.microsoft.com...[vbcol=seagreen]
> Hello John,
> Thanks - I hadn't done that but now that I have I haven't found anything.
> I
> did check BOL for syntax changes for TOP and ORDER BY and they seem to be
> the
> same from 2000 to 2005. There is certainly nothing in the 2005
> documentation
> that I've seen that say don't use PERCENT if a view has an ORDER BY
> clause.
> Cheers, Peter
> "John Bell" wrote:
>|||Hi Peter
The issue David and Bobby refer to is documented at "Breaking Changes to
Database Engine Features in SQL Server 2005"
http://msdn2.microsoft.com/en-us/library/ms143179(SQL.90).aspx under "ORDER
BY in a view definition"
John
"Peter Jones" wrote:
[vbcol=seagreen]
> Hello John,
> Thanks - I hadn't done that but now that I have I haven't found anything.
I
> did check BOL for syntax changes for TOP and ORDER BY and they seem to be
the
> same from 2000 to 2005. There is certainly nothing in the 2005 documentati
on
> that I've seen that say don't use PERCENT if a view has an ORDER BY clause
.
> Cheers, Peter
> "John Bell" wrote:
>|||Gentlemen,
Thanks for your input. All is clear although it is perculiar that removing
the keyword PERCENT made things work.
Cheers, Peter
"John Bell" wrote:
[vbcol=seagreen]
> Hi Peter
> The issue David and Bobby refer to is documented at "Breaking Changes to
> Database Engine Features in SQL Server 2005"
> http://msdn2.microsoft.com/en-us/library/ms143179(SQL.90).aspx under "ORDE
R
> BY in a view definition"
> John
> "Peter Jones" wrote:
>|||Hi Peter
I am not sure why it put that in for you, I prefer to recompile all the code
(stored procedure/functions/views) from source when doing an upgrade which
have avoided this. You may want to consider using a version control system
and using that as the source of the code.
John
"Peter Jones" wrote:
[vbcol=seagreen]
> Gentlemen,
> Thanks for your input. All is clear although it is perculiar that removing
> the keyword PERCENT made things work.
> Cheers, Peter
> "John Bell" wrote:
>|||Hi John,
No - the conversion was fine - my original code had "Top 100 Percent"
already there. It was my understanding (in SQL Server 2000) that an ORDER BY
would not work without it.
My comment related to the fact that when the keyword "Percent" was removed
the ORDER BY worked in SQL Server 2005.
Cheers, Peter
"John Bell" wrote:
[vbcol=seagreen]
> Hi Peter
> I am not sure why it put that in for you, I prefer to recompile all the co
de
> (stored procedure/functions/views) from source when doing an upgrade which
> have avoided this. You may want to consider using a version control system
> and using that as the source of the code.
> John
>
> "Peter Jones" wrote:
>|||> My comment related to the fact that when the keyword "Percent" was removed
> the ORDER BY worked in SQL Server 2005.
Peter,
Please be aware that it is dangerous to rely on this ordering behavior. It
is unpredictable and your code may break in future service packs or
releases, as it did from SQL 2000 to SQL 2005. SQL Server is free to return
results in any sequence unless ORDER BY is specified in the *query that
selects from the view*.
Here's the relevant info from the SQL 2005 Books online:
<Excerpt
href="ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/bb394abe-cae6-4905
-b5c6-8daaded77742.htm">
Note:
When ORDER BY is used in the definition of a view, inline function, derived
table, or subquery, the clause is used only to determine the rows returned
by the TOP clause. The ORDER BY clause does not guarantee ordered results
when these constructs are queried, unless ORDER BY is also specified in the
query itself.
</Excerpt>
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter Jones" <PeterJones@.discussions.microsoft.com> wrote in message
news:3EF77828-3DE9-4E8A-B757-A46AF7505343@.microsoft.com...[vbcol=seagreen]
> Hi John,
> No - the conversion was fine - my original code had "Top 100 Percent"
> already there. It was my understanding (in SQL Server 2000) that an ORDER
> BY
> would not work without it.
> My comment related to the fact that when the keyword "Percent" was removed
> the ORDER BY worked in SQL Server 2005.
> Cheers, Peter
> "John Bell" wrote:
>

ORDER BY in view does not execute in SQL Server 2005

Hi,
I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
which was converted to: "Select Top (100) Percent..." when I recreated my
views.
However any ORDER BY's in my views are not executed until I remove the
keyword Percent, e.g. Select Top (100)... works fine,
Is this a known difference between 2000 and 2005. If so is there a list of
these differences?
PeterHi Peter
Have you checked the backward compatibility sections in books online
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde841.htm or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
John
"Peter Jones" wrote:
> Hi,
> I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
> did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
> which was converted to: "Select Top (100) Percent..." when I recreated my
> views.
> However any ORDER BY's in my views are not executed until I remove the
> keyword Percent, e.g. Select Top (100)... works fine,
> Is this a known difference between 2000 and 2005. If so is there a list of
> these differences?
> Peter
>|||Hello John,
Thanks - I hadn't done that but now that I have I haven't found anything. I
did check BOL for syntax changes for TOP and ORDER BY and they seem to be the
same from 2000 to 2005. There is certainly nothing in the 2005 documentation
that I've seen that say don't use PERCENT if a view has an ORDER BY clause.
Cheers, Peter
"John Bell" wrote:
> Hi Peter
> Have you checked the backward compatibility sections in books online?
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde841.htm or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
> John
>
> "Peter Jones" wrote:
> > Hi,
> >
> > I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
> > did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
> > which was converted to: "Select Top (100) Percent..." when I recreated my
> > views.
> >
> > However any ORDER BY's in my views are not executed until I remove the
> > keyword Percent, e.g. Select Top (100)... works fine,
> >
> > Is this a known difference between 2000 and 2005. If so is there a list of
> > these differences?
> >
> > Peter
> >|||Peter Jones wrote:
> Hello John,
> Thanks - I hadn't done that but now that I have I haven't found anything. I
> did check BOL for syntax changes for TOP and ORDER BY and they seem to be the
> same from 2000 to 2005. There is certainly nothing in the 2005 documentation
> that I've seen that say don't use PERCENT if a view has an ORDER BY clause.
>
Views are not ordered in 2000 or in 2005 so nothing changed. Sort order
is determined by the queries that are issued against the view, not by
the view definition itself. If you query the view using a SELECT
statement that doesn't include ORDER BY then the ordering of the result
is undefined.
The reason ORDER BY is permitted in views at all is to support the TOP
operator, which uses ORDER BY to select a subset of rows not to order
the view (yes, I agree that the TOP syntax is not a very user-friendly
one).
Here's the example given in Books Online:
ORDER BY guarantees a sorted result only for the outermost SELECT
statement of a query. For example, consider the following view
definition:
CREATE VIEW TopView AS
SELECT TOP 50 PERCENT * FROM Person.Contact
ORDER BY LastName
Then query the view:
SELECT * FROM TopView
Although the view definition contains an ORDER BY clause, that ORDER BY
clause is used only to determine the rows returned by the TOP clause.
When querying the view itself, SQL Server does not guarantee the
results will be ordered, unless you specify so explicitly, as shown in
the following query:
SELECT * FROM TopView
ORDER BY LastName
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi Peter,
a known issue about Order By in a view in SQL 2005 is that theres is no
guarantee for the ordering in a view even though you are using selct top
100. Well it is kind of odd but actually it wasn't guaranteed in SQL 2K
either. But it has always worked. Now in SQl 2005 it doesn't work anymore.
Confused ? Well it's like when MS is stating that you should avoid the
direct use of the system tables because it not guaranteed to work in future
version but it worked fine for a lot of version. But suddenly they changed
it. But they will state that they warned us :) So I guess this is the same
regarding views and order by. Only way to solve it is to sort the output
from the view :
Select col1, col2 from view
order by col1
Maby this will help :
http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx
Regards :)
Bobby Henningsen
"Peter Jones" <PeterJones@.discussions.microsoft.com> wrote in message
news:5C2EFE1A-6D04-4604-AB3E-4D8EA11F86EB@.microsoft.com...
> Hello John,
> Thanks - I hadn't done that but now that I have I haven't found anything.
> I
> did check BOL for syntax changes for TOP and ORDER BY and they seem to be
> the
> same from 2000 to 2005. There is certainly nothing in the 2005
> documentation
> that I've seen that say don't use PERCENT if a view has an ORDER BY
> clause.
> Cheers, Peter
> "John Bell" wrote:
>> Hi Peter
>> Have you checked the backward compatibility sections in books online?
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde841.htm
>> or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
>> John
>>
>> "Peter Jones" wrote:
>> > Hi,
>> >
>> > I've noticed that ORDER BY in views does not work in SQL Server 2005 as
>> > it
>> > did in SQL Server 2000. My old syntax was: "Select Top 100
>> > Percent...."
>> > which was converted to: "Select Top (100) Percent..." when I recreated
>> > my
>> > views.
>> >
>> > However any ORDER BY's in my views are not executed until I remove the
>> > keyword Percent, e.g. Select Top (100)... works fine,
>> >
>> > Is this a known difference between 2000 and 2005. If so is there a list
>> > of
>> > these differences?
>> >
>> > Peter
>> >|||Hi Peter
The issue David and Bobby refer to is documented at "Breaking Changes to
Database Engine Features in SQL Server 2005"
http://msdn2.microsoft.com/en-us/library/ms143179(SQL.90).aspx under "ORDER
BY in a view definition"
John
"Peter Jones" wrote:
> Hello John,
> Thanks - I hadn't done that but now that I have I haven't found anything. I
> did check BOL for syntax changes for TOP and ORDER BY and they seem to be the
> same from 2000 to 2005. There is certainly nothing in the 2005 documentation
> that I've seen that say don't use PERCENT if a view has an ORDER BY clause.
> Cheers, Peter
> "John Bell" wrote:
> > Hi Peter
> >
> > Have you checked the backward compatibility sections in books online?
> > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde841.htm or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
> >
> > John
> >
> >
> > "Peter Jones" wrote:
> >
> > > Hi,
> > >
> > > I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
> > > did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
> > > which was converted to: "Select Top (100) Percent..." when I recreated my
> > > views.
> > >
> > > However any ORDER BY's in my views are not executed until I remove the
> > > keyword Percent, e.g. Select Top (100)... works fine,
> > >
> > > Is this a known difference between 2000 and 2005. If so is there a list of
> > > these differences?
> > >
> > > Peter
> > >|||Gentlemen,
Thanks for your input. All is clear although it is perculiar that removing
the keyword PERCENT made things work.
Cheers, Peter
"John Bell" wrote:
> Hi Peter
> The issue David and Bobby refer to is documented at "Breaking Changes to
> Database Engine Features in SQL Server 2005"
> http://msdn2.microsoft.com/en-us/library/ms143179(SQL.90).aspx under "ORDER
> BY in a view definition"
> John
> "Peter Jones" wrote:
> > Hello John,
> >
> > Thanks - I hadn't done that but now that I have I haven't found anything. I
> > did check BOL for syntax changes for TOP and ORDER BY and they seem to be the
> > same from 2000 to 2005. There is certainly nothing in the 2005 documentation
> > that I've seen that say don't use PERCENT if a view has an ORDER BY clause.
> >
> > Cheers, Peter
> >
> > "John Bell" wrote:
> >
> > > Hi Peter
> > >
> > > Have you checked the backward compatibility sections in books online?
> > > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde841.htm or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
> > >
> > > John
> > >
> > >
> > > "Peter Jones" wrote:
> > >
> > > > Hi,
> > > >
> > > > I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
> > > > did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
> > > > which was converted to: "Select Top (100) Percent..." when I recreated my
> > > > views.
> > > >
> > > > However any ORDER BY's in my views are not executed until I remove the
> > > > keyword Percent, e.g. Select Top (100)... works fine,
> > > >
> > > > Is this a known difference between 2000 and 2005. If so is there a list of
> > > > these differences?
> > > >
> > > > Peter
> > > >|||Hi Peter
I am not sure why it put that in for you, I prefer to recompile all the code
(stored procedure/functions/views) from source when doing an upgrade which
have avoided this. You may want to consider using a version control system
and using that as the source of the code.
John
"Peter Jones" wrote:
> Gentlemen,
> Thanks for your input. All is clear although it is perculiar that removing
> the keyword PERCENT made things work.
> Cheers, Peter
> "John Bell" wrote:
> > Hi Peter
> >
> > The issue David and Bobby refer to is documented at "Breaking Changes to
> > Database Engine Features in SQL Server 2005"
> > http://msdn2.microsoft.com/en-us/library/ms143179(SQL.90).aspx under "ORDER
> > BY in a view definition"
> >
> > John
> >
> > "Peter Jones" wrote:
> >
> > > Hello John,
> > >
> > > Thanks - I hadn't done that but now that I have I haven't found anything. I
> > > did check BOL for syntax changes for TOP and ORDER BY and they seem to be the
> > > same from 2000 to 2005. There is certainly nothing in the 2005 documentation
> > > that I've seen that say don't use PERCENT if a view has an ORDER BY clause.
> > >
> > > Cheers, Peter
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi Peter
> > > >
> > > > Have you checked the backward compatibility sections in books online?
> > > > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde841.htm or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
> > > >
> > > > John
> > > >
> > > >
> > > > "Peter Jones" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
> > > > > did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
> > > > > which was converted to: "Select Top (100) Percent..." when I recreated my
> > > > > views.
> > > > >
> > > > > However any ORDER BY's in my views are not executed until I remove the
> > > > > keyword Percent, e.g. Select Top (100)... works fine,
> > > > >
> > > > > Is this a known difference between 2000 and 2005. If so is there a list of
> > > > > these differences?
> > > > >
> > > > > Peter
> > > > >|||Hi John,
No - the conversion was fine - my original code had "Top 100 Percent"
already there. It was my understanding (in SQL Server 2000) that an ORDER BY
would not work without it.
My comment related to the fact that when the keyword "Percent" was removed
the ORDER BY worked in SQL Server 2005.
Cheers, Peter
"John Bell" wrote:
> Hi Peter
> I am not sure why it put that in for you, I prefer to recompile all the code
> (stored procedure/functions/views) from source when doing an upgrade which
> have avoided this. You may want to consider using a version control system
> and using that as the source of the code.
> John
>
> "Peter Jones" wrote:
> >
> > Gentlemen,
> >
> > Thanks for your input. All is clear although it is perculiar that removing
> > the keyword PERCENT made things work.
> >
> > Cheers, Peter
> >
> > "John Bell" wrote:
> >
> > > Hi Peter
> > >
> > > The issue David and Bobby refer to is documented at "Breaking Changes to
> > > Database Engine Features in SQL Server 2005"
> > > http://msdn2.microsoft.com/en-us/library/ms143179(SQL.90).aspx under "ORDER
> > > BY in a view definition"
> > >
> > > John
> > >
> > > "Peter Jones" wrote:
> > >
> > > > Hello John,
> > > >
> > > > Thanks - I hadn't done that but now that I have I haven't found anything. I
> > > > did check BOL for syntax changes for TOP and ORDER BY and they seem to be the
> > > > same from 2000 to 2005. There is certainly nothing in the 2005 documentation
> > > > that I've seen that say don't use PERCENT if a view has an ORDER BY clause.
> > > >
> > > > Cheers, Peter
> > > >
> > > > "John Bell" wrote:
> > > >
> > > > > Hi Peter
> > > > >
> > > > > Have you checked the backward compatibility sections in books online?
> > > > > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde841.htm or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
> > > > >
> > > > > John
> > > > >
> > > > >
> > > > > "Peter Jones" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
> > > > > > did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
> > > > > > which was converted to: "Select Top (100) Percent..." when I recreated my
> > > > > > views.
> > > > > >
> > > > > > However any ORDER BY's in my views are not executed until I remove the
> > > > > > keyword Percent, e.g. Select Top (100)... works fine,
> > > > > >
> > > > > > Is this a known difference between 2000 and 2005. If so is there a list of
> > > > > > these differences?
> > > > > >
> > > > > > Peter
> > > > > >|||> My comment related to the fact that when the keyword "Percent" was removed
> the ORDER BY worked in SQL Server 2005.
Peter,
Please be aware that it is dangerous to rely on this ordering behavior. It
is unpredictable and your code may break in future service packs or
releases, as it did from SQL 2000 to SQL 2005. SQL Server is free to return
results in any sequence unless ORDER BY is specified in the *query that
selects from the view*.
Here's the relevant info from the SQL 2005 Books online:
<Excerpt
href="ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/bb394abe-cae6-4905-b5c6-8daaded77742.htm">
Note:
When ORDER BY is used in the definition of a view, inline function, derived
table, or subquery, the clause is used only to determine the rows returned
by the TOP clause. The ORDER BY clause does not guarantee ordered results
when these constructs are queried, unless ORDER BY is also specified in the
query itself.
</Excerpt>
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter Jones" <PeterJones@.discussions.microsoft.com> wrote in message
news:3EF77828-3DE9-4E8A-B757-A46AF7505343@.microsoft.com...
> Hi John,
> No - the conversion was fine - my original code had "Top 100 Percent"
> already there. It was my understanding (in SQL Server 2000) that an ORDER
> BY
> would not work without it.
> My comment related to the fact that when the keyword "Percent" was removed
> the ORDER BY worked in SQL Server 2005.
> Cheers, Peter
> "John Bell" wrote:
>> Hi Peter
>> I am not sure why it put that in for you, I prefer to recompile all the
>> code
>> (stored procedure/functions/views) from source when doing an upgrade
>> which
>> have avoided this. You may want to consider using a version control
>> system
>> and using that as the source of the code.
>> John
>>
>> "Peter Jones" wrote:
>> >
>> > Gentlemen,
>> >
>> > Thanks for your input. All is clear although it is perculiar that
>> > removing
>> > the keyword PERCENT made things work.
>> >
>> > Cheers, Peter
>> >
>> > "John Bell" wrote:
>> >
>> > > Hi Peter
>> > >
>> > > The issue David and Bobby refer to is documented at "Breaking Changes
>> > > to
>> > > Database Engine Features in SQL Server 2005"
>> > > http://msdn2.microsoft.com/en-us/library/ms143179(SQL.90).aspx under
>> > > "ORDER
>> > > BY in a view definition"
>> > >
>> > > John
>> > >
>> > > "Peter Jones" wrote:
>> > >
>> > > > Hello John,
>> > > >
>> > > > Thanks - I hadn't done that but now that I have I haven't found
>> > > > anything. I
>> > > > did check BOL for syntax changes for TOP and ORDER BY and they seem
>> > > > to be the
>> > > > same from 2000 to 2005. There is certainly nothing in the 2005
>> > > > documentation
>> > > > that I've seen that say don't use PERCENT if a view has an ORDER BY
>> > > > clause.
>> > > >
>> > > > Cheers, Peter
>> > > >
>> > > > "John Bell" wrote:
>> > > >
>> > > > > Hi Peter
>> > > > >
>> > > > > Have you checked the backward compatibility sections in books
>> > > > > online?
>> > > > > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde841.htm
>> > > > > or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
>> > > > >
>> > > > > John
>> > > > >
>> > > > >
>> > > > > "Peter Jones" wrote:
>> > > > >
>> > > > > > Hi,
>> > > > > >
>> > > > > > I've noticed that ORDER BY in views does not work in SQL Server
>> > > > > > 2005 as it
>> > > > > > did in SQL Server 2000. My old syntax was: "Select Top 100
>> > > > > > Percent...."
>> > > > > > which was converted to: "Select Top (100) Percent..." when I
>> > > > > > recreated my
>> > > > > > views.
>> > > > > >
>> > > > > > However any ORDER BY's in my views are not executed until I
>> > > > > > remove the
>> > > > > > keyword Percent, e.g. Select Top (100)... works fine,
>> > > > > >
>> > > > > > Is this a known difference between 2000 and 2005. If so is
>> > > > > > there a list of
>> > > > > > these differences?
>> > > > > >
>> > > > > > Peter
>> > > > > >|||Peter,
When you realize why you can use TOP xxx ORDER BY in a view, you will
understand.
If your view definition does a TOP selection, the ORDER BY will
determine which rows will be part of the result.
If you specify "CREATE VIEW my_view AS SELECT TOP 50 PERCENT * FROM
my_table ORDER BY my_primary_key_column" and you select from the view,
the query plan will show the TOP operator. The same is true if you
specify SELECT TOP 100.
If you specify TOP 100 PERCENT, the optimizer recognizes that no rows
will be eliminated from the resultset, regardless of any sorting, so it
does not add a TOP operator to the query plan, nor does it see need to
retrieve the table rows in an ordered fashion (unless you add an ORDER
BY clause to the SELECT that selects from the view). So from an
optimizer perspective it makes perfect sense to disregard the TOP 100
PERCENT .. ORDER BY from the view.
HTH,
Gert-Jan
Peter Jones wrote:
> Hi John,
> No - the conversion was fine - my original code had "Top 100 Percent"
> already there. It was my understanding (in SQL Server 2000) that an ORDER BY
> would not work without it.
> My comment related to the fact that when the keyword "Percent" was removed
> the ORDER BY worked in SQL Server 2005.
> Cheers, Peter
> "John Bell" wrote:
> > Hi Peter
> >
> > I am not sure why it put that in for you, I prefer to recompile all the code
> > (stored procedure/functions/views) from source when doing an upgrade which
> > have avoided this. You may want to consider using a version control system
> > and using that as the source of the code.
> >
> > John
> >
> >
> >
> > "Peter Jones" wrote:
> >
> > >
> > > Gentlemen,
> > >
> > > Thanks for your input. All is clear although it is perculiar that removing
> > > the keyword PERCENT made things work.
> > >
> > > Cheers, Peter
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi Peter
> > > >
> > > > The issue David and Bobby refer to is documented at "Breaking Changes to
> > > > Database Engine Features in SQL Server 2005"
> > > > http://msdn2.microsoft.com/en-us/library/ms143179(SQL.90).aspx under "ORDER
> > > > BY in a view definition"
> > > >
> > > > John
> > > >
> > > > "Peter Jones" wrote:
> > > >
> > > > > Hello John,
> > > > >
> > > > > Thanks - I hadn't done that but now that I have I haven't found anything. I
> > > > > did check BOL for syntax changes for TOP and ORDER BY and they seem to be the
> > > > > same from 2000 to 2005. There is certainly nothing in the 2005 documentation
> > > > > that I've seen that say don't use PERCENT if a view has an ORDER BY clause.
> > > > >
> > > > > Cheers, Peter
> > > > >
> > > > > "John Bell" wrote:
> > > > >
> > > > > > Hi Peter
> > > > > >
> > > > > > Have you checked the backward compatibility sections in books online?
> > > > > > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde841.htm or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
> > > > > >
> > > > > > John
> > > > > >
> > > > > >
> > > > > > "Peter Jones" wrote:
> > > > > >
> > > > > > > Hi,
> > > > > > >
> > > > > > > I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
> > > > > > > did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
> > > > > > > which was converted to: "Select Top (100) Percent..." when I recreated my
> > > > > > > views.
> > > > > > >
> > > > > > > However any ORDER BY's in my views are not executed until I remove the
> > > > > > > keyword Percent, e.g. Select Top (100)... works fine,
> > > > > > >
> > > > > > > Is this a known difference between 2000 and 2005. If so is there a list of
> > > > > > > these differences?
> > > > > > >
> > > > > > > Peter
> > > > > > >|||Hi Gert-Jan,
Thank you for taking the time to add to this thread. I was having trouble
understanding why ORDER BY was even permitted as part of a view - your
comment makes it clear.
Thanks - Peter
"Gert-Jan Strik" wrote:
> Peter,
> When you realize why you can use TOP xxx ORDER BY in a view, you will
> understand.
> If your view definition does a TOP selection, the ORDER BY will
> determine which rows will be part of the result.
> If you specify "CREATE VIEW my_view AS SELECT TOP 50 PERCENT * FROM
> my_table ORDER BY my_primary_key_column" and you select from the view,
> the query plan will show the TOP operator. The same is true if you
> specify SELECT TOP 100.
> If you specify TOP 100 PERCENT, the optimizer recognizes that no rows
> will be eliminated from the resultset, regardless of any sorting, so it
> does not add a TOP operator to the query plan, nor does it see need to
> retrieve the table rows in an ordered fashion (unless you add an ORDER
> BY clause to the SELECT that selects from the view). So from an
> optimizer perspective it makes perfect sense to disregard the TOP 100
> PERCENT .. ORDER BY from the view.
> HTH,
> Gert-Jan
>
> Peter Jones wrote:
> >
> > Hi John,
> >
> > No - the conversion was fine - my original code had "Top 100 Percent"
> > already there. It was my understanding (in SQL Server 2000) that an ORDER BY
> > would not work without it.
> >
> > My comment related to the fact that when the keyword "Percent" was removed
> > the ORDER BY worked in SQL Server 2005.
> >
> > Cheers, Peter
> >
> > "John Bell" wrote:
> >
> > > Hi Peter
> > >
> > > I am not sure why it put that in for you, I prefer to recompile all the code
> > > (stored procedure/functions/views) from source when doing an upgrade which
> > > have avoided this. You may want to consider using a version control system
> > > and using that as the source of the code.
> > >
> > > John
> > >
> > >
> > >
> > > "Peter Jones" wrote:
> > >
> > > >
> > > > Gentlemen,
> > > >
> > > > Thanks for your input. All is clear although it is perculiar that removing
> > > > the keyword PERCENT made things work.
> > > >
> > > > Cheers, Peter
> > > >
> > > > "John Bell" wrote:
> > > >
> > > > > Hi Peter
> > > > >
> > > > > The issue David and Bobby refer to is documented at "Breaking Changes to
> > > > > Database Engine Features in SQL Server 2005"
> > > > > http://msdn2.microsoft.com/en-us/library/ms143179(SQL.90).aspx under "ORDER
> > > > > BY in a view definition"
> > > > >
> > > > > John
> > > > >
> > > > > "Peter Jones" wrote:
> > > > >
> > > > > > Hello John,
> > > > > >
> > > > > > Thanks - I hadn't done that but now that I have I haven't found anything. I
> > > > > > did check BOL for syntax changes for TOP and ORDER BY and they seem to be the
> > > > > > same from 2000 to 2005. There is certainly nothing in the 2005 documentation
> > > > > > that I've seen that say don't use PERCENT if a view has an ORDER BY clause.
> > > > > >
> > > > > > Cheers, Peter
> > > > > >
> > > > > > "John Bell" wrote:
> > > > > >
> > > > > > > Hi Peter
> > > > > > >
> > > > > > > Have you checked the backward compatibility sections in books online?
> > > > > > > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde841.htm or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
> > > > > > >
> > > > > > > John
> > > > > > >
> > > > > > >
> > > > > > > "Peter Jones" wrote:
> > > > > > >
> > > > > > > > Hi,
> > > > > > > >
> > > > > > > > I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
> > > > > > > > did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
> > > > > > > > which was converted to: "Select Top (100) Percent..." when I recreated my
> > > > > > > > views.
> > > > > > > >
> > > > > > > > However any ORDER BY's in my views are not executed until I remove the
> > > > > > > > keyword Percent, e.g. Select Top (100)... works fine,
> > > > > > > >
> > > > > > > > Is this a known difference between 2000 and 2005. If so is there a list of
> > > > > > > > these differences?
> > > > > > > >
> > > > > > > > Peter
> > > > > > > >
>sql

ORDER BY error, need help!

When I run this query, I get an ORDER BY error "Incorrect syntax near the keyword 'ORDER'. I've bolded it below.

If I take the ORDER by out then it works correctly, but I need the ORDER BY because of the TOP 9. Any Suggestions?
Code: ( sql )

(SELECT TOP 9 trans.vchTrustee, trans.vchCaseNumber, Trans.TotalReceipts + SUM(a.mnyBalanceAmount) AS Top9Total FROM tblCase c1 LEFT JOIN tblAsset a ON c1.intCaseID = a.intCaseID LEFT JOIN (SELECT trans40.vchTrustee, trans40.vchCaseNumber, SUM(trans40.TdMnyAmount) AS TotalReceipts FROM (SELECT c.vchTrustee, c.vchCaseNumber, SUM(td.mnyAmount) AS TdMnyAmount FROM tblTransactionDetail td INNER JOIN tblTransaction t ON td.intTransactionID = t.intTransactionID INNER JOIN tblBankAccount b ON t.intAccountID = b.intAccountID INNER JOIN tblCase c ON b.intCaseID = c.intCaseID GROUP BY c.intDeleted, b.intDeleted, td.intDeleted, t.intDeleted, c.vchTrustee, c.vchCaseNumber, c.intCaseID, td.intTransactionType, t.intTransactionMethod, c.intStatus HAVING t.intDeleted = 0 AND td.intDeleted = 0 AND b.intDeleted = 0 AND c.intDeleted = 0 AND td.intTransactionType = 200 AND (t.intTransactionMethod = 40 OR t.intTransactionMethod = 41 OR t.intTransactionMethod = 43) AND c.vchTrustee = 'RLW' AND c.intStatus = 1 ) AS Trans40 GROUP BY Trans40.vchTrustee, Trans40.vchCaseNumber ) AS Trans ON c1.vchCaseNumber = Trans.vchCaseNumber GROUP BY trans.vchTrustee, trans.vchCaseNumber, a.intDeleted, c1.intDeleted, trans.TotalReceipts, c1.intStatus HAVING a.intDeleted = 0 AND c1.intDeleted = 0 AND trans.vchTrustee = 'RLW' AND c1.intStatus = 1 [B]ORDER BY (Trans.TotalReceipts + SUM(a.mnyBalanceAmount)) DESC[/B]) UNION ALL (SELECT Top9.vchTrustee, ' ', (AllCases.TR - SUM(Top9.Top9Total)) AS TRMinusTop9 FROM (SELECT AllTotalReceipts.vchTrustee, SUM(AllTotalReceipts.TotalMoney) AS TR FROM (SELECT trans.vchTrustee, trans.vchCaseNumber, Trans.TotalReceipts, SUM(a.mnyBalanceAmount) AS totalAssetBalance, (Trans.TotalReceipts + SUM(a.mnyBalanceAmount)) AS TotalMoney FROM tblCase c1 LEFT JOIN tblAsset a ON c1.intCaseID = a.intCaseID LEFT JOIN (SELECT trans40.vchTrustee, trans40.vchCaseNumber, SUM(trans40.TdMnyAmount) AS TotalReceipts FROM (SELECT c.vchTrustee, c.vchCaseNumber, SUM(td.mnyAmount) AS TdMnyAmount FROM tblTransactionDetail td INNER JOIN tblTransaction t ON td.intTransactionID = t.intTransactionID INNER JOIN tblBankAccount b ON t.intAccountID = b.intAccountID INNER JOIN tblCase c ON b.intCaseID = c.intCaseID GROUP BY c.intDeleted, b.intDeleted, td.intDeleted, t.intDeleted, c.vchTrustee, c.vchCaseNumber, c.intCaseID, td.intTransactionType, t.intTransactionMethod, c.intStatus HAVING t.intDeleted = 0 AND td.intDeleted = 0 AND b.intDeleted = 0 AND c.intDeleted = 0 AND td.intTransactionType = 200 AND (t.intTransactionMethod = 40 OR t.intTransactionMethod = 41 OR t.intTransactionMethod = 43) AND c.vchTrustee = 'RLW' AND c.intStatus = 1 ) AS Trans40 GROUP BY Trans40.vchTrustee, Trans40.vchCaseNumber ) AS Trans ON c1.vchCaseNumber = Trans.vchCaseNumber GROUP BY trans.vchTrustee, trans.vchCaseNumber, a.intDeleted, c1.intDeleted, trans.TotalReceipts, c1.intStatus HAVING a.intDeleted = 0 AND c1.intDeleted = 0 AND trans.vchTrustee = 'RLW' AND c1.intStatus = 1 ) AS AllTotalReceipts GROUP BY AllTotalReceipts.vchTrustee) AS AllCases LEFT JOIN (SELECT TOP 9 trans.vchTrustee, trans.vchCaseNumber, Trans.TotalReceipts, SUM(a.mnyBalanceAmount) AS totalAssetBalance, Trans.TotalReceipts + SUM(a.mnyBalanceAmount) AS Top9Total FROM tblCase c1 LEFT JOIN tblAsset a ON c1.intCaseID = a.intCaseID LEFT JOIN (SELECT trans40.vchTrustee, trans40.vchCaseNumber, SUM(trans40.TdMnyAmount) AS TotalReceipts FROM (SELECT c.vchTrustee, c.vchCaseNumber, SUM(td.mnyAmount) AS TdMnyAmount FROM tblTransactionDetail td INNER JOIN tblTransaction t ON td.intTransactionID = t.intTransactionID INNER JOIN tblBankAccount b ON t.intAccountID = b.intAccountID INNER JOIN tblCase c ON b.intCaseID = c.intCaseID GROUP BY c.intDeleted, b.intDeleted, td.intDeleted, t.intDeleted, c.vchTrustee, c.vchCaseNumber, c.intCaseID, td.intTransactionType, t.intTransactionMethod, c.intStatus HAVING t.intDeleted = 0 AND td.intDeleted = 0 AND b.intDeleted = 0 AND c.intDeleted = 0 AND td.intTransactionType = 200 AND (t.intTransactionMethod = 40 OR t.intTransactionMethod = 41 OR t.intTransactionMethod = 43) AND c.vchTrustee = 'RLW' AND c.intStatus = 1 ) AS Trans40 GROUP BY Trans40.vchTrustee, Trans40.vchCaseNumber ) AS Trans ON c1.vchCaseNumber = Trans.vchCaseNumber GROUP BY trans.vchTrustee, trans.vchCaseNumber, a.intDeleted, c1.intDeleted, trans.TotalReceipts, c1.intStatus HAVING a.intDeleted = 0 AND c1.intDeleted = 0 AND trans.vchTrustee = 'RLW' AND c1.intStatus = 1 ORDER BY Trans.TotalReceipts + SUM(a.mnyBalanceAmount) DESC ) AS Top9 ON AllCases.vchTrustee = Top9.vchTrustee GROUP BY Top9.vchTrustee, AllCases.TR )

Quote:

Originally Posted by speavey

When I run this query, I get an ORDER BY error "Incorrect syntax near the keyword 'ORDER'. I've bolded it below.

If I take the ORDER by out then it works correctly, but I need the ORDER BY because of the TOP 9. Any Suggestions?

(SELECT TOP 9 trans.vchTrustee, trans.vchCaseNumber, Trans.TotalReceipts + SUM(a.mnyBalanceAmount) As Top9Total

FROM tblCase c1

LEFT JOIN tblAsset a ON c1.intCaseID = a.intCaseID
LEFT JOIN
(Select trans40.vchTrustee, trans40.vchCaseNumber, SUM(trans40.TdMnyAmount) As TotalReceipts

From
(SELECT c.vchTrustee, c.vchCaseNumber, SUM(td.mnyAmount) As TdMnyAmount
FROM tblTransactionDetail td

INNER JOIN tblTransaction t ON td.intTransactionID = t.intTransactionID
INNER JOIN tblBankAccount b ON t.intAccountID = b.intAccountID
INNER JOIN tblCase c ON b.intCaseID = c.intCaseID

GROUP BY c.intDeleted, b.intDeleted, td.intDeleted, t.intDeleted,
c.vchTrustee, c.vchCaseNumber, c.intCaseID, td.intTransactionType,
t.intTransactionMethod, c.intStatus

HAVING t.intDeleted = 0 AND td.intDeleted = 0 AND b.intDeleted = 0 AND c.intDeleted = 0
AND td.intTransactionType = 200
AND (t.intTransactionMethod = 40 OR t.intTransactionMethod = 41 OR t.intTransactionMethod = 43)
AND c.vchTrustee = 'RLW' AND c.intStatus = 1

) As Trans40

GROUP BY Trans40.vchTrustee, Trans40.vchCaseNumber
) As Trans ON c1.vchCaseNumber = Trans.vchCaseNumber

GROUP BY trans.vchTrustee, trans.vchCaseNumber, a.intDeleted, c1.intDeleted, trans.TotalReceipts, c1.intStatus

HAVING a.intDeleted = 0 AND c1.intDeleted = 0 AND trans.vchTrustee = 'RLW' AND c1.intStatus = 1

ORDER BY (Trans.TotalReceipts + SUM(a.mnyBalanceAmount)) DESC)

UNION All

(SELECT Top9.vchTrustee, ' ', (AllCases.TR - SUM(Top9.Top9Total)) As TRMinusTop9
FROM

(SELECT AllTotalReceipts.vchTrustee, SUM(AllTotalReceipts.TotalMoney) As TR
FROM

(SELECT trans.vchTrustee, trans.vchCaseNumber, Trans.TotalReceipts, SUM(a.mnyBalanceAmount) As totalAssetBalance,
(Trans.TotalReceipts + SUM(a.mnyBalanceAmount)) As TotalMoney

FROM tblCase c1

LEFT JOIN tblAsset a ON c1.intCaseID = a.intCaseID
LEFT JOIN
(Select trans40.vchTrustee, trans40.vchCaseNumber, SUM(trans40.TdMnyAmount) As TotalReceipts

From
(SELECT c.vchTrustee, c.vchCaseNumber, SUM(td.mnyAmount) As TdMnyAmount
FROM tblTransactionDetail td
INNER JOIN tblTransaction t ON td.intTransactionID = t.intTransactionID
INNER JOIN tblBankAccount b ON t.intAccountID = b.intAccountID
INNER JOIN tblCase c ON b.intCaseID = c.intCaseID

GROUP BY c.intDeleted, b.intDeleted, td.intDeleted, t.intDeleted,
c.vchTrustee, c.vchCaseNumber, c.intCaseID, td.intTransactionType,
t.intTransactionMethod, c.intStatus

HAVING t.intDeleted = 0 AND td.intDeleted = 0 AND b.intDeleted = 0 AND c.intDeleted = 0
AND td.intTransactionType = 200
AND (t.intTransactionMethod = 40 OR t.intTransactionMethod = 41 OR t.intTransactionMethod = 43)
AND c.vchTrustee = 'RLW' AND c.intStatus = 1

) As Trans40

GROUP BY Trans40.vchTrustee, Trans40.vchCaseNumber
) As Trans ON c1.vchCaseNumber = Trans.vchCaseNumber

GROUP BY trans.vchTrustee, trans.vchCaseNumber, a.intDeleted, c1.intDeleted, trans.TotalReceipts, c1.intStatus
HAVING a.intDeleted = 0 AND c1.intDeleted = 0 AND trans.vchTrustee = 'RLW' AND c1.intStatus = 1
) As AllTotalReceipts

GROUP BY AllTotalReceipts.vchTrustee) As AllCases

LEFT JOIN

(SELECT TOP 9 trans.vchTrustee, trans.vchCaseNumber, Trans.TotalReceipts, SUM(a.mnyBalanceAmount) As totalAssetBalance,
Trans.TotalReceipts + SUM(a.mnyBalanceAmount) As Top9Total

FROM tblCase c1

LEFT JOIN tblAsset a ON c1.intCaseID = a.intCaseID
LEFT JOIN
(Select trans40.vchTrustee, trans40.vchCaseNumber, SUM(trans40.TdMnyAmount) As TotalReceipts

From
(SELECT c.vchTrustee, c.vchCaseNumber, SUM(td.mnyAmount) As TdMnyAmount
FROM tblTransactionDetail td

INNER JOIN tblTransaction t ON td.intTransactionID = t.intTransactionID
INNER JOIN tblBankAccount b ON t.intAccountID = b.intAccountID
INNER JOIN tblCase c ON b.intCaseID = c.intCaseID

GROUP BY c.intDeleted, b.intDeleted, td.intDeleted, t.intDeleted,
c.vchTrustee, c.vchCaseNumber, c.intCaseID, td.intTransactionType,
t.intTransactionMethod, c.intStatus

HAVING t.intDeleted = 0 AND td.intDeleted = 0 AND b.intDeleted = 0 AND c.intDeleted = 0
AND td.intTransactionType = 200 AND (t.intTransactionMethod = 40 OR t.intTransactionMethod = 41 OR t.intTransactionMethod = 43)
AND c.vchTrustee = 'RLW' AND c.intStatus = 1

) As Trans40

GROUP BY Trans40.vchTrustee, Trans40.vchCaseNumber
) As Trans ON c1.vchCaseNumber = Trans.vchCaseNumber

GROUP BY trans.vchTrustee, trans.vchCaseNumber, a.intDeleted, c1.intDeleted, trans.TotalReceipts, c1.intStatus

HAVING a.intDeleted = 0 AND c1.intDeleted = 0 AND trans.vchTrustee = 'RLW' AND c1.intStatus = 1
ORDER BY Trans.TotalReceipts + SUM(a.mnyBalanceAmount) DESC
) As Top9

ON AllCases.vchTrustee = Top9.vchTrustee
GROUP By Top9.vchTrustee, AllCases.TR

)

i believe ORDER BY has to be placed on the outermost QUERY, resulting in a sorted final returned resultset|||

Quote:

Originally Posted by ck9663

i believe ORDER BY has to be placed on the outermost QUERY, resulting in a sorted final returned resultset

I tried that and it won't produce the correct order by, because the First query with the TOP 9 isn't being sorted.

So if I put it on the outermost query, it doesn't sort correctly.

Maybe you can help me with my query, its very long and probably too much code. I'm trying to create a query the will SUM a value from two tables and pull the TOP 9 cases. Then also add a TOTAL SUM value - Top 9 Sum Value.

Any help would be great!!

ORDER BY before UNION syntax error

I have a list of Nationalities which I want to sort alphabetically except
for the value of Nationality which is "not disclosed" which I would like to
put at the top of the list.
I'm trying to do this with the following query but there seems to be a
problem with putting the ORDER BY in front of the UNION keyword
:
SELECT TOP 100 PERCENT NationalityID, Nationality
FROM dbo.Nationality
WHERE (Nationality <>'not disclosed')
ORDER BY Nationality
UNION
SELECT TOP 100 PERCENT NationalityID, Nationality
FROM dbo.Nationality
WHERE (Nationality = 'not disclosed')
I tried using brackets around the first part of the query but that didn't
work.
Any help much appreciated.
PeteHi
You can add an extra columns (examples are not tested!)
SELECT TOP 100 PERCENT NationalityID, Nationality, 0 as OrderBy
FROM dbo.Nationality
WHERE Nationality <>'not disclosed'
UNION
SELECT TOP 100 PERCENT NationalityID, Nationality, 1
FROM dbo.Nationality
WHERE Nationality = 'not disclosed'
ORDER BY OrderBy, Nationality
Although you do not need a UNION in this example:
SELECT DISTINCT NationalityID, Nationality, CASE WHEN Nationality = 'not
disclosed' THEN 1 ELSE 0 END as OrderBy
FROM dbo.Nationality
ORDER BY OrderBy, Nationality
If you want to remove this from the result set you can use a derived table.
SELECT NationalityID, Nationality
FROM
( SELECT DISTINCT NationalityID, Nationality, CASE WHEN Nationality =
'not disclosed' THEN 1 ELSE 0 END as OrderBy
FROM dbo.Nationality ) A
ORDER BY OrderBy, Nationality
John
"Italian Pete" wrote:

> I have a list of Nationalities which I want to sort alphabetically except
> for the value of Nationality which is "not disclosed" which I would like t
o
> put at the top of the list.
> I'm trying to do this with the following query but there seems to be a
> problem with putting the ORDER BY in front of the UNION keyword
> :
> SELECT TOP 100 PERCENT NationalityID, Nationality
> FROM dbo.Nationality
> WHERE (Nationality <>'not disclosed')
> ORDER BY Nationality
> UNION
> SELECT TOP 100 PERCENT NationalityID, Nationality
> FROM dbo.Nationality
> WHERE (Nationality = 'not disclosed')
> I tried using brackets around the first part of the query but that didn't
> work.
> Any help much appreciated.
> Pete|||Perfect!! Works a treat.
Thanks John
"John Bell" wrote:
> Hi
> You can add an extra columns (examples are not tested!)
> SELECT TOP 100 PERCENT NationalityID, Nationality, 0 as OrderBy
> FROM dbo.Nationality
> WHERE Nationality <>'not disclosed'
> UNION
> SELECT TOP 100 PERCENT NationalityID, Nationality, 1
> FROM dbo.Nationality
> WHERE Nationality = 'not disclosed'
> ORDER BY OrderBy, Nationality
> Although you do not need a UNION in this example:
> SELECT DISTINCT NationalityID, Nationality, CASE WHEN Nationality = 'n
ot
> disclosed' THEN 1 ELSE 0 END as OrderBy
> FROM dbo.Nationality
> ORDER BY OrderBy, Nationality
> If you want to remove this from the result set you can use a derived table
.
> SELECT NationalityID, Nationality
> FROM
> ( SELECT DISTINCT NationalityID, Nationality, CASE WHEN Nationality =
> 'not disclosed' THEN 1 ELSE 0 END as OrderBy
> FROM dbo.Nationality ) A
> ORDER BY OrderBy, Nationality
> John
> "Italian Pete" wrote:
>|||Italian wrote on Fri, 27 May 2005 04:59:53 -0700:
> Perfect!! Works a treat.
> Thanks John
> "John Bell" wrote:
>
Rather than using a UNION, you can do this in a single SELECT which should
be more efficient:
SELECT TOP 100 PERCENT NationalityID, Nationality
FROM dbo.Nationality
ORDER BY CASE WHEN Nationality = 'not disclosed' THEN 0 ELSE 1 END
Dan|||Daniel wrote to Italian Pete on Fri, 27 May 2005 15:25:34 +0100:

> Italian wrote on Fri, 27 May 2005 04:59:53 -0700:
>
> Rather than using a UNION, you can do this in a single SELECT which should
> be more efficient:
> SELECT TOP 100 PERCENT NationalityID, Nationality
> FROM dbo.Nationality
> ORDER BY CASE WHEN Nationality = 'not disclosed' THEN 0 ELSE 1 END
>
Just noticed that's almost the same as Italian Pete posted. However, this
gives you just the 2 columns you wanted and doesn't require a derived table.
Dan|||Daniel wrote to Daniel Crichton on Fri, 27 May 2005 15:39:22 +0100:

> Just noticed that's almost the same as Italian Pete posted. However, this
> gives you just the 2 columns you wanted and doesn't require a derived
> table.
You know what, I need more caffeine and sleep. I meant John Bell.
:\
Dan

Order By

How would i add order by to the syntax below:

"Select * Into ETCLog_holding from etclog where box# BETWEEN " & Box1 &
" and " & Box2

i have tried adding it after Box2 but it doesnt work.

Any ideas?I have tried and its executed ok adding "Order by" after Box2...may be
you shoud to revise "box#" part.

Good luck.|||(pkruti@.hotmail.com) writes:
> How would i add order by to the syntax below:
> "Select * Into ETCLog_holding from etclog where box# BETWEEN " & Box1 &
> " and " & Box2
> i have tried adding it after Box2 but it doesnt work.

You can add an ORDER BY clause after Box2, just to be careful to add a
space.

However, it is a fairly pointless thing to do. If you expect data in
ETCLog_holding to have a certain order, you are forgetting the fact
that tables are unordered sets.

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

Order By

How would i add order by to this syntax below:
"Select * Into ETCLog_holding from etclog where box# BETWEEN " & Box1 &
" and " & Box2
i have tried adding it to the end after box2 but its doesnt work.
Any ideas?How did you try "adding it to the end"? Were you careful to add a SPACE?
& Box2 & " ORDER BY ..."
--^ important!
Is your column really named box#? Ugh.
<pkruti@.hotmail.com> wrote in message
news:1140540628.486260.116830@.f14g2000cwb.googlegroups.com...
> How would i add order by to this syntax below:
> "Select * Into ETCLog_holding from etclog where box# BETWEEN " & Box1 &
> " and " & Box2
> i have tried adding it to the end after box2 but its doesnt work.
> Any ideas?
>|||pkr...@.hotmail.com wrote:
> How would i add order by to this syntax below:
> "Select * Into ETCLog_holding from etclog where box# BETWEEN " & Box1 &
> " and " & Box2
> i have tried adding it to the end after box2 but its doesnt work.
> Any ideas?
What are "Box1" and "Box2"? Is this code taken from your
user-interface? Never concatenate unverified input from users like
that. It's dangerously insecure as well as inefficient. The best way is
to pass a pair of a parameters to a SQL stored procedure:
CREATE PROC usp_foo
(@.box1 INTEGER, @.box2 INTEGER)
SELECT
..
WHERE [box#] BETWEEN @.box1 AND @.box2
GO
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Aaron Bertrand [SQL Server MVP] wrote:
> How did you try "adding it to the end"? Were you careful to add a SPACE?
> & Box2 & " ORDER BY ..."
> --^ important!
> Is your column really named box#? Ugh.
>
> <pkruti@.hotmail.com> wrote in message
> news:1140540628.486260.116830@.f14g2000cwb.googlegroups.com...
SELECT INTO creates a table and tables are unordered. For that reason
the ORDER BY may be ignored. Don't use ORDER BY here because it doesn't
achieve anything useful that you can rely on.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Did you tried this?
"Select * Into ETCLog_holding from etclog where box# BETWEEN " & Box1 & "
and " & Box2 & " order by [column from etclog]"
"pkruti@.hotmail.com" wrote:

> How would i add order by to this syntax below:
> "Select * Into ETCLog_holding from etclog where box# BETWEEN " & Box1 &
> " and " & Box2
> i have tried adding it to the end after box2 but its doesnt work.
> Any ideas?
>|||> SELECT INTO creates a table and tables are unordered. For that reason
> the ORDER BY may be ignored. Don't use ORDER BY here because it doesn't
> achieve anything useful that you can rely on.
I was just trying to point out a potential correction the syntax (especially
given we have no idea what "doesnt work" means). The query in querstion
might very well be used for other purposes than SELECT INTO.|||Aaron Bertrand [SQL Server MVP] wrote:
> I was just trying to point out a potential correction the syntax (especial
ly
> given we have no idea what "doesnt work" means). The query in querstion
> might very well be used for other purposes than SELECT INTO.
Aaron,
I knew that you knew. I didn't know if the OP knew. I don't know what
"doesn't work" means either.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Oracle to MS SQL error

Hello,
I have this query that was written for Oracle, but when I run it in MS
SQL I get */*** ERROR *** Line 9: Incorrect syntax near 'start'.
I can't seem to figure it out, can you someone please point in the
right direction?
select 99, login_name, (full_name)
from shr_wr_approvers
where ACCOUNTING_NUMBER = ''
union select level, C240000005, ( C240000001)
from T265
where level in (2,3,4)
start with upper(C240000006) = '0397639'
connect by prior upper(C800000108) = upper(C240000006)
union select 0,'','No Data' from dual where not exists (select
login_name from shr_wr_approvers where ACCOUNTING_NUMBER = '') and not
exists (select C1 from T265 where level in (2,3,4) start with
upper(C240000006) = '0397639' connect by prior upper(C800000108) =
upper(C240000006)) order by 1,2Did you try running the query through the Microsoft SSMA (SQL Server
Migration Assistant) tool first? It's a free download from Microsoft.com and
it will convert PL/SQL to T-SQL for you so you can use it in SQL Server.
BTW ... Is this a Remedy database schema that you are converting?
BR, mkro
--
mkro
"Ybleu" wrote:

> Hello,
> I have this query that was written for Oracle, but when I run it in MS
> SQL I get */*** ERROR *** Line 9: Incorrect syntax near 'start'.
> I can't seem to figure it out, can you someone please point in the
> right direction?
> select 99, login_name, (full_name)
> from shr_wr_approvers
> where ACCOUNTING_NUMBER = ''
> union select level, C240000005, ( C240000001)
> from T265
> where level in (2,3,4)
> start with upper(C240000006) = '0397639'
> connect by prior upper(C800000108) = upper(C240000006)
> union select 0,'','No Data' from dual where not exists (select
> login_name from shr_wr_approvers where ACCOUNTING_NUMBER = '') and not
> exists (select C1 from T265 where level in (2,3,4) start with
> upper(C240000006) = '0397639' connect by prior upper(C800000108) =
> upper(C240000006)) order by 1,2
>|||Hi
start with and connect by prior is not available in SQL server as they are
proprietary Oracle extensions
You could use recursive CTEs in SQL 2005, if you are using SQL 2000 you may
have to resort to using a temporary table.
Check out Joe Celko's "Trees and Hierarchies" ISBN 1-55860-920-2 which talks
about this and methods of implementing hierarchical data.
Posting DDL, example data and expected results is always useful when
answering this sort of question see
http://www.aspfaq.com/etiquette.asp?id=5006
John
"Ybleu" wrote:

> Hello,
> I have this query that was written for Oracle, but when I run it in MS
> SQL I get */*** ERROR *** Line 9: Incorrect syntax near 'start'.
> I can't seem to figure it out, can you someone please point in the
> right direction?
> select 99, login_name, (full_name)
> from shr_wr_approvers
> where ACCOUNTING_NUMBER = ''
> union select level, C240000005, ( C240000001)
> from T265
> where level in (2,3,4)
> start with upper(C240000006) = '0397639'
> connect by prior upper(C800000108) = upper(C240000006)
> union select 0,'','No Data' from dual where not exists (select
> login_name from shr_wr_approvers where ACCOUNTING_NUMBER = '') and not
> exists (select C1 from T265 where level in (2,3,4) start with
> upper(C240000006) = '0397639' connect by prior upper(C800000108) =
> upper(C240000006)) order by 1,2
>|||Ybleu wrote:
> Hello,
> I have this query that was written for Oracle, but when I run it in MS
> SQL I get */*** ERROR *** Line 9: Incorrect syntax near 'start'.
> I can't seem to figure it out, can you someone please point in the
> right direction?
> select 99, login_name, (full_name)
> from shr_wr_approvers
> where ACCOUNTING_NUMBER = ''
> union select level, C240000005, ( C240000001)
> from T265
> where level in (2,3,4)
> start with upper(C240000006) = '0397639'
> connect by prior upper(C800000108) = upper(C240000006)
> union select 0,'','No Data' from dual where not exists (select
> login_name from shr_wr_approvers where ACCOUNTING_NUMBER = '') and not
> exists (select C1 from T265 where level in (2,3,4) start with
> upper(C240000006) = '0397639' connect by prior upper(C800000108) =
> upper(C240000006)) order by 1,2
START WITH... CONNECT BY is not standard SQL it's an Oracle special.
SQL Server has the ANSI/ISO standard syntax for recursive queries.
Lookup the WITH keyword in Books Online (SQL Server 2005 only).
If you want an exact solution then it would help if you could post DDL,
sample data and show your required end result. Also tell us what
version you are using.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Oracle to MS SQL error

Hello,
I have this query that was written for Oracle, but when I run it in MS
SQL I get */*** ERROR *** Line 9: Incorrect syntax near 'start'.
I can't seem to figure it out, can you someone please point in the
right direction?
select 99, login_name, (full_name)
from shr_wr_approvers
where ACCOUNTING_NUMBER = ''
union select level, C240000005, ( C240000001)
from T265
where level in (2,3,4)
start with upper(C240000006) = '0397639'
connect by prior upper(C800000108) = upper(C240000006)
union select 0,'','No Data' from dual where not exists (select
login_name from shr_wr_approvers where ACCOUNTING_NUMBER = '') and not
exists (select C1 from T265 where level in (2,3,4) start with
upper(C240000006) = '0397639' connect by prior upper(C800000108) = upper(C240000006)) order by 1,2Did you try running the query through the Microsoft SSMA (SQL Server
Migration Assistant) tool first? It's a free download from Microsoft.com and
it will convert PL/SQL to T-SQL for you so you can use it in SQL Server.
BTW ... Is this a Remedy database schema that you are converting?
BR, mkro
--
mkro
"Ybleu" wrote:
> Hello,
> I have this query that was written for Oracle, but when I run it in MS
> SQL I get */*** ERROR *** Line 9: Incorrect syntax near 'start'.
> I can't seem to figure it out, can you someone please point in the
> right direction?
> select 99, login_name, (full_name)
> from shr_wr_approvers
> where ACCOUNTING_NUMBER = ''
> union select level, C240000005, ( C240000001)
> from T265
> where level in (2,3,4)
> start with upper(C240000006) = '0397639'
> connect by prior upper(C800000108) = upper(C240000006)
> union select 0,'','No Data' from dual where not exists (select
> login_name from shr_wr_approvers where ACCOUNTING_NUMBER = '') and not
> exists (select C1 from T265 where level in (2,3,4) start with
> upper(C240000006) = '0397639' connect by prior upper(C800000108) => upper(C240000006)) order by 1,2
>|||Hi
start with and connect by prior is not available in SQL server as they are
proprietary Oracle extensions
You could use recursive CTEs in SQL 2005, if you are using SQL 2000 you may
have to resort to using a temporary table.
Check out Joe Celko's "Trees and Hierarchies" ISBN 1-55860-920-2 which talks
about this and methods of implementing hierarchical data.
Posting DDL, example data and expected results is always useful when
answering this sort of question see
http://www.aspfaq.com/etiquette.asp?id=5006
John
"Ybleu" wrote:
> Hello,
> I have this query that was written for Oracle, but when I run it in MS
> SQL I get */*** ERROR *** Line 9: Incorrect syntax near 'start'.
> I can't seem to figure it out, can you someone please point in the
> right direction?
> select 99, login_name, (full_name)
> from shr_wr_approvers
> where ACCOUNTING_NUMBER = ''
> union select level, C240000005, ( C240000001)
> from T265
> where level in (2,3,4)
> start with upper(C240000006) = '0397639'
> connect by prior upper(C800000108) = upper(C240000006)
> union select 0,'','No Data' from dual where not exists (select
> login_name from shr_wr_approvers where ACCOUNTING_NUMBER = '') and not
> exists (select C1 from T265 where level in (2,3,4) start with
> upper(C240000006) = '0397639' connect by prior upper(C800000108) => upper(C240000006)) order by 1,2
>|||Ybleu wrote:
> Hello,
> I have this query that was written for Oracle, but when I run it in MS
> SQL I get */*** ERROR *** Line 9: Incorrect syntax near 'start'.
> I can't seem to figure it out, can you someone please point in the
> right direction?
> select 99, login_name, (full_name)
> from shr_wr_approvers
> where ACCOUNTING_NUMBER = ''
> union select level, C240000005, ( C240000001)
> from T265
> where level in (2,3,4)
> start with upper(C240000006) = '0397639'
> connect by prior upper(C800000108) = upper(C240000006)
> union select 0,'','No Data' from dual where not exists (select
> login_name from shr_wr_approvers where ACCOUNTING_NUMBER = '') and not
> exists (select C1 from T265 where level in (2,3,4) start with
> upper(C240000006) = '0397639' connect by prior upper(C800000108) => upper(C240000006)) order by 1,2
START WITH... CONNECT BY is not standard SQL it's an Oracle special.
SQL Server has the ANSI/ISO standard syntax for recursive queries.
Lookup the WITH keyword in Books Online (SQL Server 2005 only).
If you want an exact solution then it would help if you could post DDL,
sample data and show your required end result. Also tell us what
version you are using.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Oracle Syntax Error.

Hi,

I have a Oracle query which runs fine at Oracle side(through TOAD or Oracle Client).

I am using sysdate+1 in my query to get the next date.

Now I am using same query in RS using Oracle Client which returns error "ORA-00932:Inconsistent datatypes.Expected DATE got NUMBER".

Basically the driver is unable to interpret to add 1(Number) to the Sysdate(Date) field.

Any suggestions/ideas are greatly appreciated.

Thanks !!

Maybe in your query you should do an explicit CONVERT back to date on the sysdate + 1 expression? I think you may be relying on an implicit conversion which is not happening in this case.

Oracle Stored Procedure Syntax

I'd like to write a report with a data set that calls an Oracle data source
for its result set. I have a package with a procedure that returns a ref
cursor as an out variable. What is the syntax for calling this procedure
from the Report Designer? Does the out variable with the cursor need to be
mapped to anything? Are input parameters on the procedure anything other
than regular @.parameter references?
Thanks!John,
The sql syntax is identical to the that used for MS SQL Server, except for
the @.. Switch it to a : (colon)
and the parameters will work just fine, example:
Select Blah
From tblBlah
where Blah.tblBlah = :param
I like to use the IN Keyword. If you set your parameters as multi-select,
the parameters are passed in as an string seperated by commas, so your Where
clause would look like this:
Where Blah.tblBlah IN (:Param)
Hope this helps,
rwiethorn
"John W at Sungard HE" wrote:
> I'd like to write a report with a data set that calls an Oracle data source
> for its result set. I have a package with a procedure that returns a ref
> cursor as an out variable. What is the syntax for calling this procedure
> from the Report Designer? Does the out variable with the cursor need to be
> mapped to anything? Are input parameters on the procedure anything other
> than regular @.parameter references?
> Thanks!|||Hi,
Continuing with John's question, I want to know, What is the syntax for
calling the oracle stored procedure from the Report Designer? My procedure is
returning a ref cursor as an out variable.
Regards,
Aruna
"rwiethorn" wrote:
> John,
> The sql syntax is identical to the that used for MS SQL Server, except for
> the @.. Switch it to a : (colon)
> and the parameters will work just fine, example:
> Select Blah
> From tblBlah
> where Blah.tblBlah = :param
> I like to use the IN Keyword. If you set your parameters as multi-select,
> the parameters are passed in as an string seperated by commas, so your Where
> clause would look like this:
> Where Blah.tblBlah IN (:Param)
> Hope this helps,
> rwiethorn
>
> "John W at Sungard HE" wrote:
> > I'd like to write a report with a data set that calls an Oracle data source
> > for its result set. I have a package with a procedure that returns a ref
> > cursor as an out variable. What is the syntax for calling this procedure
> > from the Report Designer? Does the out variable with the cursor need to be
> > mapped to anything? Are input parameters on the procedure anything other
> > than regular @.parameter references?
> >
> > Thanks!|||Thanks for the response - the bit with the parameters defintely helps. Do
you have an example that references a stored procedure instead of a select
statement? For example, do I have to wrap the procedure call in a PL/SQL
block like:
BEGIN
CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
END
or is it sufficient to call the procedure without the block like:
CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
And is the above parameter syntax going to work? What about the output
parameter that accepts the cursor back from the stored procedure? Is that
just set up as a report parameter like any other, even though it's an output
parameter? Does it matter where the output parameter is placed in the call
list, i.e. does it need to appear either first or last?
Thanks!
JW
"rwiethorn" wrote:
> John,
> The sql syntax is identical to the that used for MS SQL Server, except for
> the @.. Switch it to a : (colon)
> and the parameters will work just fine, example:
> Select Blah
> From tblBlah
> where Blah.tblBlah = :param
> I like to use the IN Keyword. If you set your parameters as multi-select,
> the parameters are passed in as an string seperated by commas, so your Where
> clause would look like this:
> Where Blah.tblBlah IN (:Param)
> Hope this helps,
> rwiethorn
>
> "John W at Sungard HE" wrote:
> > I'd like to write a report with a data set that calls an Oracle data source
> > for its result set. I have a package with a procedure that returns a ref
> > cursor as an out variable. What is the syntax for calling this procedure
> > from the Report Designer? Does the out variable with the cursor need to be
> > mapped to anything? Are input parameters on the procedure anything other
> > than regular @.parameter references?
> >
> > Thanks!|||change the command type to stored procedure.
The syntax then looks like this
packagename.procedurename
no need to put any parameters in here.
John W at Sungard HE wrote:
> Thanks for the response - the bit with the parameters defintely helps. Do
> you have an example that references a stored procedure instead of a select
> statement? For example, do I have to wrap the procedure call in a PL/SQL
> block like:
> BEGIN
> CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> END
> or is it sufficient to call the procedure without the block like:
> CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> And is the above parameter syntax going to work? What about the output
> parameter that accepts the cursor back from the stored procedure? Is that
> just set up as a report parameter like any other, even though it's an output
> parameter? Does it matter where the output parameter is placed in the call
> list, i.e. does it need to appear either first or last?
> Thanks!
> JW
> "rwiethorn" wrote:
> > John,
> > The sql syntax is identical to the that used for MS SQL Server, except for
> > the @.. Switch it to a : (colon)
> > and the parameters will work just fine, example:
> > Select Blah
> > From tblBlah
> > where Blah.tblBlah = :param
> >
> > I like to use the IN Keyword. If you set your parameters as multi-select,
> > the parameters are passed in as an string seperated by commas, so your Where
> > clause would look like this:
> > Where Blah.tblBlah IN (:Param)
> >
> > Hope this helps,
> > rwiethorn
> >
> >
> > "John W at Sungard HE" wrote:
> >
> > > I'd like to write a report with a data set that calls an Oracle data source
> > > for its result set. I have a package with a procedure that returns a ref
> > > cursor as an out variable. What is the syntax for calling this procedure
> > > from the Report Designer? Does the out variable with the cursor need to be
> > > mapped to anything? Are input parameters on the procedure anything other
> > > than regular @.parameter references?
> > >
> > > Thanks!|||John,
I'm a MS SQL Server guy myself, so I'm limited on the Oracle. I have a
Oracle DBA that makes tables for me when I define my sql statement. I've not
had the need to do any processing, I'm been only needing simple selects with
groupings.
>What about the output
> parameter that accepts the cursor back from the stored procedure?
I think you can do with out it, the system may just 'swallow' the return
value (ie a 0 or 1) indicating a success or failure.
>Does it matter where the output parameter is placed in the call
> list, i.e. does it need to appear either first or last?
Yes, parameter order does matter when your calling stored procs, you need to
match the order and the data type.
I hope this helps,
rwiethorn
"John W at Sungard HE" wrote:
> Thanks for the response - the bit with the parameters defintely helps. Do
> you have an example that references a stored procedure instead of a select
> statement? For example, do I have to wrap the procedure call in a PL/SQL
> block like:
> BEGIN
> CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> END
> or is it sufficient to call the procedure without the block like:
> CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> And is the above parameter syntax going to work? What about the output
> parameter that accepts the cursor back from the stored procedure? Is that
> just set up as a report parameter like any other, even though it's an output
> parameter? Does it matter where the output parameter is placed in the call
> list, i.e. does it need to appear either first or last?
> Thanks!
> JW
> "rwiethorn" wrote:
> > John,
> > The sql syntax is identical to the that used for MS SQL Server, except for
> > the @.. Switch it to a : (colon)
> > and the parameters will work just fine, example:
> > Select Blah
> > From tblBlah
> > where Blah.tblBlah = :param
> >
> > I like to use the IN Keyword. If you set your parameters as multi-select,
> > the parameters are passed in as an string seperated by commas, so your Where
> > clause would look like this:
> > Where Blah.tblBlah IN (:Param)
> >
> > Hope this helps,
> > rwiethorn
> >
> >
> > "John W at Sungard HE" wrote:
> >
> > > I'd like to write a report with a data set that calls an Oracle data source
> > > for its result set. I have a package with a procedure that returns a ref
> > > cursor as an out variable. What is the syntax for calling this procedure
> > > from the Report Designer? Does the out variable with the cursor need to be
> > > mapped to anything? Are input parameters on the procedure anything other
> > > than regular @.parameter references?
> > >
> > > Thanks!|||Sorry, I did not get it. How I can send an OUT parameter (CURSOR) and use it
to generate report?
Thank you
"rwiethorn" wrote:
> John,
> The sql syntax is identical to the that used for MS SQL Server, except for
> the @.. Switch it to a : (colon)
> and the parameters will work just fine, example:
> Select Blah
> From tblBlah
> where Blah.tblBlah = :param
> I like to use the IN Keyword. If you set your parameters as multi-select,
> the parameters are passed in as an string seperated by commas, so your Where
> clause would look like this:
> Where Blah.tblBlah IN (:Param)
> Hope this helps,
> rwiethorn
>
> "John W at Sungard HE" wrote:
> > I'd like to write a report with a data set that calls an Oracle data source
> > for its result set. I have a package with a procedure that returns a ref
> > cursor as an out variable. What is the syntax for calling this procedure
> > from the Report Designer? Does the out variable with the cursor need to be
> > mapped to anything? Are input parameters on the procedure anything other
> > than regular @.parameter references?
> >
> > Thanks!|||Sorry, can I have more details, please. Like, what driver did you use, did
you use parameters Tab to set parameter and so on.
Thank you
"gene.furibondo@.gmail.com" wrote:
> change the command type to stored procedure.
> The syntax then looks like this
> packagename.procedurename
> no need to put any parameters in here.
> John W at Sungard HE wrote:
> > Thanks for the response - the bit with the parameters defintely helps. Do
> > you have an example that references a stored procedure instead of a select
> > statement? For example, do I have to wrap the procedure call in a PL/SQL
> > block like:
> >
> > BEGIN
> > CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> > END
> >
> > or is it sufficient to call the procedure without the block like:
> >
> > CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> >
> > And is the above parameter syntax going to work? What about the output
> > parameter that accepts the cursor back from the stored procedure? Is that
> > just set up as a report parameter like any other, even though it's an output
> > parameter? Does it matter where the output parameter is placed in the call
> > list, i.e. does it need to appear either first or last?
> >
> > Thanks!
> >
> > JW
> >
> > "rwiethorn" wrote:
> >
> > > John,
> > > The sql syntax is identical to the that used for MS SQL Server, except for
> > > the @.. Switch it to a : (colon)
> > > and the parameters will work just fine, example:
> > > Select Blah
> > > From tblBlah
> > > where Blah.tblBlah = :param
> > >
> > > I like to use the IN Keyword. If you set your parameters as multi-select,
> > > the parameters are passed in as an string seperated by commas, so your Where
> > > clause would look like this:
> > > Where Blah.tblBlah IN (:Param)
> > >
> > > Hope this helps,
> > > rwiethorn
> > >
> > >
> > > "John W at Sungard HE" wrote:
> > >
> > > > I'd like to write a report with a data set that calls an Oracle data source
> > > > for its result set. I have a package with a procedure that returns a ref
> > > > cursor as an out variable. What is the syntax for calling this procedure
> > > > from the Report Designer? Does the out variable with the cursor need to be
> > > > mapped to anything? Are input parameters on the procedure anything other
> > > > than regular @.parameter references?
> > > >
> > > > Thanks!
>