Oracle DBA to learn MS SQL: created

Showing posts with label created. Show all posts

Friday, March 30, 2012

Order By with Query then bottom border

Reporting Services 2000
I have a SQL query that is properly sorting a list of items I have by using
the order by clause. I created a report using the report wizard and didn't
use any fields to group by because I already have the list in the correct
order. I did use RS to keep each team on it's own page. How can I have a
border or bgcolor change when the owner of a project changes. For example
I'd like a border after Bob and before John.
Sample Data:
Team Owner Project
NY bob Remote Access
NY bob Server Reboot
NY John Network Upgrade
Here is my order by clause:
ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 ENDDid you try to use an expression to set border width?
=IIF(Fields!OwnerValue = Previous(Fields!Owner.Value),1pt,0pt)
"Colin" wrote:
> Reporting Services 2000
> I have a SQL query that is properly sorting a list of items I have by using
> the order by clause. I created a report using the report wizard and didn't
> use any fields to group by because I already have the list in the correct
> order. I did use RS to keep each team on it's own page. How can I have a
> border or bgcolor change when the owner of a project changes. For example
> I'd like a border after Bob and before John.
> Sample Data:
> Team Owner Project
> NY bob Remote Access
> NY bob Server Reboot
> NY John Network Upgrade
> Here is my order by clause:
> ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 END
>
>|||I didn't try that. Thank you.
Where can I find a list of all the functions that can be called?
"isaak" <isaak.peretsman at usa.dupont.com (no spam)> wrote in message
news:E28C729B-AE19-4ADF-9983-233B666CE5D1@.microsoft.com...
> Did you try to use an expression to set border width?
> =IIF(Fields!OwnerValue = Previous(Fields!Owner.Value),1pt,0pt)
> "Colin" wrote:
>> Reporting Services 2000
>> I have a SQL query that is properly sorting a list of items I have by
>> using
>> the order by clause. I created a report using the report wizard and
>> didn't
>> use any fields to group by because I already have the list in the correct
>> order. I did use RS to keep each team on it's own page. How can I have
>> a
>> border or bgcolor change when the owner of a project changes. For
>> example
>> I'd like a border after Bob and before John.
>> Sample Data:
>> Team Owner Project
>> NY bob Remote Access
>> NY bob Server Reboot
>> NY John Network Upgrade
>> Here is my order by clause:
>> ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 END
>>
>>|||Can I confuse this just a tad more. What if I only want a border the very
first time the owner changes and don't need a border after that?
"Colin" <legendsfan@.spamhotmail.com> wrote in message
news:O1DEBwKwGHA.4460@.TK2MSFTNGP04.phx.gbl...
>I didn't try that. Thank you.
> Where can I find a list of all the functions that can be called?
> "isaak" <isaak.peretsman at usa.dupont.com (no spam)> wrote in message
> news:E28C729B-AE19-4ADF-9983-233B666CE5D1@.microsoft.com...
>> Did you try to use an expression to set border width?
>> =IIF(Fields!OwnerValue = Previous(Fields!Owner.Value),1pt,0pt)
>> "Colin" wrote:
>> Reporting Services 2000
>> I have a SQL query that is properly sorting a list of items I have by
>> using
>> the order by clause. I created a report using the report wizard and
>> didn't
>> use any fields to group by because I already have the list in the
>> correct
>> order. I did use RS to keep each team on it's own page. How can I have
>> a
>> border or bgcolor change when the owner of a project changes. For
>> example
>> I'd like a border after Bob and before John.
>> Sample Data:
>> Team Owner Project
>> NY bob Remote Access
>> NY bob Server Reboot
>> NY John Network Upgrade
>> Here is my order by clause:
>> ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 END
>>
>>
>|||For the function list go to SqlServer Books Online, "Using Functions in
Reporting Services" is the name of the topic.
To show the border the first time only, try this
=IIF(Fields!OwnerValue <> Previous(Fields!Owner.Value) AND
Previous(Fields!Owner.Value)= Min(Fields!Owner.Value),1pt,0pt)
"Colin" wrote:
> Can I confuse this just a tad more. What if I only want a border the very
> first time the owner changes and don't need a border after that?
> "Colin" <legendsfan@.spamhotmail.com> wrote in message
> news:O1DEBwKwGHA.4460@.TK2MSFTNGP04.phx.gbl...
> >I didn't try that. Thank you.
> >
> > Where can I find a list of all the functions that can be called?
> >
> > "isaak" <isaak.peretsman at usa.dupont.com (no spam)> wrote in message
> > news:E28C729B-AE19-4ADF-9983-233B666CE5D1@.microsoft.com...
> >> Did you try to use an expression to set border width?
> >>
> >> =IIF(Fields!OwnerValue = Previous(Fields!Owner.Value),1pt,0pt)
> >>
> >> "Colin" wrote:
> >>
> >> Reporting Services 2000
> >> I have a SQL query that is properly sorting a list of items I have by
> >> using
> >> the order by clause. I created a report using the report wizard and
> >> didn't
> >> use any fields to group by because I already have the list in the
> >> correct
> >> order. I did use RS to keep each team on it's own page. How can I have
> >> a
> >> border or bgcolor change when the owner of a project changes. For
> >> example
> >> I'd like a border after Bob and before John.
> >>
> >> Sample Data:
> >> Team Owner Project
> >> NY bob Remote Access
> >> NY bob Server Reboot
> >> NY John Network Upgrade
> >>
> >> Here is my order by clause:
> >> ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 END
> >>
> >>
> >>
> >>
> >
> >
>
>

Wednesday, March 28, 2012

Order by problem within a View

Hi,

I have created a view which uses 3 tables, i also have a sort on one of the columns. However when I open the view the sort does not work. It does however sort the view correctly when executing the query within design view

Can anyone explain this or is it a bug within SQL Server Express 2005?

thanks

David

This is an expected behavior. Any ORDER BY that is attached to a view when it is defined is basically ignored. If you want the columns of a view to be ordered, the order by must be specified at the time you construct your actual query from the view. The long and short of it is that you cannot pre-set the ORDER BY behavior by definiting it as part of a view definition.

Monday, March 26, 2012

order by in a view in sqlserver2005

Hi,

I've a problem with a created view in sqlserver2000 that I'm now using in sqlserver2005.

This is the view :

CREATE VIEW hsarti01_VD1 AS
SELECT TOP 100 PERCENT *
FROM hsarti01 WITH(index(hsarti01_PK))
ORDER BY 1,2 desc,3,4

When I do the "select * from hsarti01_VD1" in sql server 2000, I see in the result that the order by is been using. but in sql server 2005 it's just using the order of the primary key and not the order by !

Has anyone have a solution for it ?

Thanks

Hi I have found out that the percent directive gives this problem.
I guess it is a bad solution but replacing this with a ridiculus high number solves the problem say

Alter VIEW hsarti01_VD1 AS
SELECT TOP 10000000000 *
FROM hsarti01 WITH(index(hsarti01_PK))
ORDER BY 1,2 desc,3,4

solves this. I guess however that ordering in views is not a realy a good thing, emagine you select on the view with an order statment, wais a few cpu cycles

Walter

|||

Order of rows in a result is guaranteed only if you specify an ORDER BY clause in the outer-most SELECT statement. Anywhere else the optimizer is free to remove it or preserve order only within that scope. So you will be relying on a particular plan behavior and the expected output will change if the plan changes. This can happen between service packs or releases or hotfixes. It is hard to tell. The following warning has been added to the CREATE VIEW topic in SQL Server 2005 to reflect the correct behavior:

The ORDER BY clause is used only to determine the rows that are returned by the TOP clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.

So modify the SELECT that queries the view to include appropriate ORDER BY clause. This is the only sure way. Please see the link below for additional information on ordering guarantees in SQL Server:

http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx

|||

I think this is a horrible *BUG*. I think if an Order By is specified in the view, than the results should ALWAYS be returned in that order unless an outer query is used to resort the view (just as you can use Where to further filter results). Otherwise you constantly have to re-specify the order of the view everywhere it is used. This greatly reduces the value of using the view. With SQL 2000, I could simply open a view in my application and navigate through it, confident that the records were in the correct order. Now with 2005 I have to RESPECIFY the order by statement EVERYWHERE the view is used. This has introduced a number of logical errors in my application, and I think it was a horrible oversight and bug. You can try to call it a "feature" but that's garbage.

|||

Brent, I understand how this behavior is inconvenient, but it is consistent with ANSI/ISO standards, and it is consistent with Microsoft SQL Server documentation, which has recently been made clearer about this: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/bb394abe-cae6-4905-b5c6-8daaded77742.htm 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. In my opinion, the confusion is caused by the syntax Microsoft chose for its proprietary TOP clause. Instead of using ORDER BY, which already has a meaning in standard SQL, to specify the ordering used by TOP (thereby adding a second meaning to ORDER BY), the syntax should have been something like CREATE VIEW V AS SELECT TOP (10) OVER (ORDER BY someColumn) * FROM T Then an ORDER BY clause (at the end of the query statement) could have been prohibited completely from view definitions, and there would be no question about this. But unfortunately, that's not the way T-SQL is, and there is a lot of misunderstanding. In any case, the only way to guarantee a result set's ordering is to specify ORDER BY in the query that returns the result set. The view definition is not the query that returns the result set, so ORDER BY in the view definition for V does not control the order of the result set generated by SELECT * FROM V. The workaround you suggest may seem to solve the problem, but you should be aware that it is not guaranteed to work. Unless you want to risk going through this trouble again in the future (perhaps after a service pack that introduces new optimizer enhancements), you should specify an ORDER BY clause every time you want an ordered result set. You are not "re"specifying the ORDER BY clause, since the one in the view definition is not a specification for the order of the results - it is a specification (that works with TOP) of what rows the view contains, regardless of the order in which you might want to retrieve them. Steve Kass Drew University Brent Mullet@.discussions.microsoft.com wrote:
> I think this is a horrible *BUG*. I think if an Order By is specified
> in the view, than the results should ALWAYS be returned in that order
> unless an outer query is used to resort the view (just as you can use
> Where to further filter results). Otherwise you constantly have to
> re-specify the order of the view everywhere it is used. This greatly
> reduces the value of using the view. With SQL 2000, I could simply open
> a view in my application and navigate through it, confident that the
> records were in the correct order. Now with 2005 I have to RESPECIFY
> the order by statement EVERYWHERE the view is used. This has introduced
> a number of logical errors in my application, and I think it was a
> horrible oversight and bug. You can try to call it a "feature" but
> that's garbage.
>
>
>
>
>
>

|||

I still think it's stupid and makes no sense. You specify SELECT, WHERE, and GROUP BY statements in views and those are all respected but ORDER BY is not. There is no reason for this. If I want ordered results, I should be able to specify it in a view and be confident that wherever the view is used the Order By is respected. This is a basic programming principle. If the same view is used in many places in an application, and for some reason the order needs to change, I should be able to do that globally just as I can with WHERE. This is a BUG that needs FIXED.

If it's specified that way in the ANSI/ISO standards, than those standards need fixed. This is ludicrous.

|||

A prime problem is that what happens when you joint this "ordered view" with another "ordered view"? Who comes first?

A view is a table, which is by definition, unordered. The SELECT, WHERE, and GROUP BY clauses shape the data in the table, but the order is not a part of a table. To change that you would have to change the root of the theory that relational databases have been built upon for years. Not to mention the definition of a SET would need to be changed and all of the optimizers of database servers rethought, since this table's order could affect the users of the table.

If you want the data to be consistently returned in an order via code, it is best use a stored procedure.

|||

Ahah! Now at least I think it makes a little more sense. I did not think about using stored procedues; I've been using views for a long time, and that worked fine in 2000. So part of the issue was my ignorance (blush). Converting to stored procedures is a perfectly acceptable solution. (And there are probably other benefits to that as well?)

So it's no longer a STUPID BUG it's a DANGEROUS TRAP that IGNORANT USERS can fall into when they UPGRADE

If the ORDER BY is not respected in it's intuitive sense, I think it should not be specifyable unless it is somehow tied directly to the TOP statement.

Thanks for the response Louis.

|||

This is currently the case. Try to build a view with an order by any you get a nasty message:

create view test
as
select *
from sysobjects
order by 1

In 2000:
Msg 1033, Level 15, State 1, Procedure test, Line 5
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

In 2005:
Msg 1033, Level 15, State 1, Procedure test, Line 5
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

I know how you feel though. I had used this in my views in past versions, (as well as the fact that clustered data was naturally returned in clustered order) and for the most part it still holds true, especially in testing, because it is usually easier for to return the data in order because if the query processor needed it in order to determine the TOP rows, it is unlikely to be faster to reorder. But as the query optimizer/processor gets more and more sophisicated, the more likely it is that they will find ways to maximize output and lose the ordering in the process.

In all cases it is better to either use ORDER BY, or just let the client sort the data.

|||Yes but creating a new view in Enterprise Manager defaults to "TOP (100) PERCENT" - and when this is present, the Order By is accepted. In this case, the Order By is meaningless and dangerously misleading.|||

Now you've nailed it. This behavior, which unfortunately persists in SQL Server 2005 Enterprise Manager's Query and View Designer, is idiotic. A good place to add your voice to the chorus is at the Microsoft Product Feeback center, where you can vote on the at least three bugs/suggestions about this behavior (URL may wrap). http://lab.msdn.microsoft.com/productfeedback/SearchResults.aspx?text=view+%26quot%3border+by%26quot%3b+percent&stype=1&fields=1&type=0&witId=0&pId=0&category=0&os=0&oslang=0&status=0&msstatus=0&resolution=0&chgdays=&validation=0&votes=&voterating=0&workarounds=False&attachments=False SK Brent Mullet@.discussions.microsoft.com wrote:
> Yes but creating a new view in Enterprise Manager defaults to "TOP (100)
> PERCENT" - and when this is present, the Order By is accepted. In this
> case, the Order By is meaningless and dangerously misleading.
>

order by in a view in sqlserver2005

Hi,

I've a problem with a created view in sqlserver2000 that I'm now using in sqlserver2005.

This is the view :

CREATE VIEW hsarti01_VD1 AS
SELECT TOP 100 PERCENT *
FROM hsarti01 WITH(index(hsarti01_PK))
ORDER BY 1,2 desc,3,4

Has anyone have a solution for it ?

Thanks

Hi I have found out that the percent directive gives this problem.
I guess it is a bad solution but replacing this with a ridiculus high number solves the problem say

Alter VIEW hsarti01_VD1 AS
SELECT TOP 10000000000 *
FROM hsarti01 WITH(index(hsarti01_PK))
ORDER BY 1,2 desc,3,4

solves this. I guess however that ordering in views is not a realy a good thing, emagine you select on the view with an order statment, wais a few cpu cycles

Walter

|||

http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx

|||

If it's specified that way in the ANSI/ISO standards, than those standards need fixed. This is ludicrous.

|||

A prime problem is that what happens when you joint this "ordered view" with another "ordered view"? Who comes first?

If you want the data to be consistently returned in an order via code, it is best use a stored procedure.

|||

So it's no longer a STUPID BUG it's a DANGEROUS TRAP that IGNORANT USERS can fall into when they UPGRADE

If the ORDER BY is not respected in it's intuitive sense, I think it should not be specifyable unless it is somehow tied directly to the TOP statement.

Thanks for the response Louis.

|||

This is currently the case. Try to build a view with an order by any you get a nasty message:

create view test
as
select *
from sysobjects
order by 1

In 2000:
Msg 1033, Level 15, State 1, Procedure test, Line 5
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

In all cases it is better to either use ORDER BY, or just let the client sort the data.

Friday, March 23, 2012

Order by clause in View doesn''t order.

I have created view by jaoining two table and have order by clause.

The sql generated is as follows

SELECT TOP (100) PERCENT dbo.UWYearDetail.*, dbo.UWYearGroup.*
FROM dbo.UWYearDetail INNER JOIN
dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId = dbo.UWYearGroup.UWYearGroupId
ORDER BY dbo.UWYearDetail.PlanVersionId, dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear, dbo.UWYearGroup.MandDFlag,
dbo.UWYearGroup.EarningsMethod, dbo.UWYearGroup.EffectiveMonth

If I run sql the results are displayed in proper order but the view only order by first item in order by clause.

Has somebody experience same thing? How to fix this issue?

Thanks,

From Books Online 2005: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/bb394abe-cae6-4905-b5c6-8daaded77742.htm 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. If you want to see the contents of the view in a particular order, you must specify ORDER BY when you query the view. The ORDER BY in the view definition here does nothing at all. It's unfortunate that TOP (100) PERCENT is allowed, let alone added by the view designer, and my suggestion is never use TOP (100) PERCENT, because it is meaningless and leads you to think you can create an "ordered view", which you cannot. Steve Kass Drew University sg2000@.discussions.microsoft.com wrote:
> I have created view by jaoining two table and have order by clause.
>
> The sql generated is as follows
>
> SELECT TOP (100) PERCENT dbo.UWYearDetail.*, dbo.UWYearGroup.*
> FROM dbo.UWYearDetail INNER JOIN
> dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId
> = dbo.UWYearGroup.UWYearGroupId
> ORDER BY dbo.UWYearDetail.PlanVersionId,
> dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear,
> dbo.UWYearGroup.MandDFlag,
> dbo.UWYearGroup.EarningsMethod,
> dbo.UWYearGroup.EffectiveMonth
>
>
>
> If I run sql the results are displayed in proper order but the view only
> order by first item in order by clause.
>
> Has somebody experience same thing? How to fix this issue?
>
> Thanks,
>
>
>
>

|||Well, that's what I am doing for now, inserting order by in stored procedure. It's very confusing and order by shouldn't be valid in views.|||

This is a common complaint, but if order by wasn't allowed, then TOP (anything other than 100%) would not be of much use. That is why you can't put an order by clause in the view definition without the TOP clause.

The bottom line is, like Steve said, there is no guarantee that tables have any order, including views with Order By clauses, Tables with clustered indexes, etc. This allows the optimizer to work with the hardware in the very fastest manner if you don't explicitly ask for rows in a given order.

It's kind of annoying, but it makes sense and is not a real problem once you get used to it (and it took me a long time when I first realized it too :)

|||

We have lot of views in SQL server 2000. It will be big pain to convert to SQL 2005. i.e. we need to change views to remove 'order by' and then need to identify the calls to the view and add order by instead. This is going to be big problem in conversion from SQL Server 200 to SQL Server 2005 going forward. Do you see any easy way to acomplish this?

|||

Hello,

I dont know if its allowed here, but there is a tool that allows you to find references to SQL Server objects even in sourcecode. Take a closer loot at the apexsql suite of tools. They will help you to identify where calls to those views are made. I am not sure which product was able to do this, but i think it was ApexSQL Clean ( http://www.apexsql.com/sql_tools_clean.asp )

If tossing this add in here was not allowed post so and ill remove the link.

(Edit: Free No-Fuss trial Version is available for 30 days)

|||

It is allowed as long as it is on topic and not just an advertisement. Giving us information about legit tools that help with SQL Server development/managment is great.

|||

Yes the tools will be helpful but what if my application is calling view from dlls or externally. I don't think any tool will hepl here. I have application built with rapid application builder called Ironspeed and it generates code which is using view.

What are my options other than manully going in my application to fix ( which seems painful), to keep the things working as it were before in SQL 2000?

|||There are no options unfortunately. Specifying ORDER BY in the outermost SELECT statement in your query is the only way to ensure that the rows are returned in a particular order to the client.|||

Hi sg2000,

You can use
TOP (99) PERCENT
instead of
TOP (100) PERCENT

The results for the query "Select top 99 percent..." from a table, which has X row(s), is X row(s), even if the table has only one row.

Then, you will be able to use the "order by" clause whitout need to change anything in your application and without giving less rows as result.

|||

This still doesn't guarantee that rows will be returned in the same order to client. The whole point is not about use of TOP 100 PERCENT with ORDER BY. Any ORDER BY clause in derived table or view for example only applies within that scope. To present rows in a particular order to client, you HAVE to include ORDER BY clause in the outermost SELECT statement. This is the only way the query will produce expected results always. See blog post below also for ordering guarantees in SQL Server:

http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx

|||

OK, this is absolutely retarded.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/bb394abe-cae6-4905-b5c6-8daaded77742.htm

This says :

Specifies the sort order used on columns returned in a SELECT statement. The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

If it says "The ORDER BY clause is not valid in views" then why the heck is there a "sort by" column in the New View Screen?

|||

Because of the last bit:

The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless TOP is also specified

And yes, it is still a bit retarded, but the whole New View screen is pretty unpleasant if you ask me. And yes again, it is ironic that a tool that is made to make things easier for newer users often makes it less easy. Go figure :)

|||not trying to be harsh...i had a little wine in me at the time and things just came out. (this is my drunk apology haha) ...|||No problemo. I agree with you wholeheartedly that it should be better than it is, either way :)sql

Order by clause in View doesn't order.

I have created view by jaoining two table and have order by clause.

The sql generated is as follows

If I run sql the results are displayed in proper order but the view only order by first item in order by clause.

Has somebody experience same thing? How to fix this issue?

Thanks,

|||Well, that's what I am doing for now, inserting order by in stored procedure. It's very confusing and order by shouldn't be valid in views.|||

It's kind of annoying, but it makes sense and is not a real problem once you get used to it (and it took me a long time when I first realized it too :)

|||

Hello,

If tossing this add in here was not allowed post so and ill remove the link.

(Edit: Free No-Fuss trial Version is available for 30 days)

|||

It is allowed as long as it is on topic and not just an advertisement. Giving us information about legit tools that help with SQL Server development/managment is great.

|||

What are my options other than manully going in my application to fix ( which seems painful), to keep the things working as it were before in SQL 2000?

|||There are no options unfortunately. Specifying ORDER BY in the outermost SELECT statement in your query is the only way to ensure that the rows are returned in a particular order to the client.|||

Hi sg2000,

You can use
TOP (99) PERCENT
instead of
TOP (100) PERCENT

The results for the query "Select top 99 percent..." from a table, which has X row(s), is X row(s), even if the table has only one row.

Then, you will be able to use the "order by" clause whitout need to change anything in your application and without giving less rows as result.

|||

http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx

|||

OK, this is absolutely retarded.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/bb394abe-cae6-4905-b5c6-8daaded77742.htm

This says :

Specifies the sort order used on columns returned in a SELECT statement. The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

If it says "The ORDER BY clause is not valid in views" then why the heck is there a "sort by" column in the New View Screen?

|||

Because of the last bit:

The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless TOP is also specified

Order by clause in View doesn't order.

I have created view by jaoining two table and have order by clause.

The sql generated is as follows

If I run sql the results are displayed in proper order but the view only order by first item in order by clause.

Has somebody experience same thing? How to fix this issue?

Thanks,

From Books Online 2005:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/bb394abe-cae6-4905-b5c6-8daaded77742.htm

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.

If you want to see the contents of the view in a particular

order, you must specify ORDER BY when you query the view.

The ORDER BY in the view definition here does nothing at all.

It's unfortunate that TOP (100) PERCENT is allowed, let alone

added by the view designer, and my suggestion is never use

TOP (100) PERCENT, because it is meaningless and leads you

to think you can create an "ordered view", which you cannot.

Steve Kass

Drew University

sg2000@.discussions.microsoft.com wrote:

> I have created view by jaoining two table and have order by clause.

> The sql generated is as follows

> SELECT TOP (100) PERCENT dbo.UWYearDetail.*, dbo.UWYearGroup.*

> FROM dbo.UWYearDetail INNER JOIN

> dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId

> = dbo.UWYearGroup.UWYearGroupId

> ORDER BY dbo.UWYearDetail.PlanVersionId,

> dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear,

> dbo.UWYearGroup.MandDFlag,

> dbo.UWYearGroup.EarningsMethod,

> dbo.UWYearGroup.EffectiveMonth

> If I run sql the results are displayed in proper order but the view only

> order by first item in order by clause.

> Has somebody experience same thing? How to fix this issue?

> Thanks,

|||Well, that's what I am doing for now, inserting order by in stored procedure. It's very confusing and order by shouldn't be valid in views.|||

It's kind of annoying, but it makes sense and is not a real problem once you get used to it (and it took me a long time when I first realized it too :)

|||

Hello,

If tossing this add in here was not allowed post so and ill remove the link.

(Edit: Free No-Fuss trial Version is available for 30 days)

|||

It is allowed as long as it is on topic and not just an advertisement. Giving us information about legit tools that help with SQL Server development/managment is great.

|||

What are my options other than manully going in my application to fix ( which seems painful), to keep the things working as it were before in SQL 2000?

|||There are no options unfortunately. Specifying ORDER BY in the outermost SELECT statement in your query is the only way to ensure that the rows are returned in a particular order to the client.|||

Hi sg2000,

You can use
TOP (99) PERCENT
instead of
TOP (100) PERCENT

The results for the query "Select top 99 percent..." from a table, which has X row(s), is X row(s), even if the table has only one row.

Then, you will be able to use the "order by" clause whitout need to change anything in your application and without giving less rows as result.

|||

http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx

|||

OK, this is absolutely retarded.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/bb394abe-cae6-4905-b5c6-8daaded77742.htm

This says :

Specifies the sort order used on columns returned in a SELECT statement. The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

If it says "The ORDER BY clause is not valid in views" then why the heck is there a "sort by" column in the New View Screen?

|||

Because of the last bit:

The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless TOP is also specified

Order by clause in View doesn't order.

I have created view by jaoining two table and have order by clause.

The sql generated is as follows

If I run sql the results are displayed in proper order but the view only order by first item in order by clause.

Has somebody experience same thing? How to fix this issue?

Thanks,

From Books Online 2005:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/bb394abe-cae6-4905-b5c6-8daaded77742.htm

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.

If you want to see the contents of the view in a particular

order, you must specify ORDER BY when you query the view.

The ORDER BY in the view definition here does nothing at all.

It's unfortunate that TOP (100) PERCENT is allowed, let alone

added by the view designer, and my suggestion is never use

TOP (100) PERCENT, because it is meaningless and leads you

to think you can create an "ordered view", which you cannot.

Steve Kass

Drew University

sg2000@.discussions.microsoft.com wrote:

> I have created view by jaoining two table and have order by clause.

> The sql generated is as follows

> SELECT TOP (100) PERCENT dbo.UWYearDetail.*, dbo.UWYearGroup.*

> FROM dbo.UWYearDetail INNER JOIN

> dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId

> = dbo.UWYearGroup.UWYearGroupId

> ORDER BY dbo.UWYearDetail.PlanVersionId,

> dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear,

> dbo.UWYearGroup.MandDFlag,

> dbo.UWYearGroup.EarningsMethod,

> dbo.UWYearGroup.EffectiveMonth

> If I run sql the results are displayed in proper order but the view only

> order by first item in order by clause.

> Has somebody experience same thing? How to fix this issue?

> Thanks,

|||Well, that's what I am doing for now, inserting order by in stored procedure. It's very confusing and order by shouldn't be valid in views.|||

It's kind of annoying, but it makes sense and is not a real problem once you get used to it (and it took me a long time when I first realized it too :)

|||

Hello,

If tossing this add in here was not allowed post so and ill remove the link.

(Edit: Free No-Fuss trial Version is available for 30 days)

|||

It is allowed as long as it is on topic and not just an advertisement. Giving us information about legit tools that help with SQL Server development/managment is great.

|||

What are my options other than manully going in my application to fix ( which seems painful), to keep the things working as it were before in SQL 2000?

|||There are no options unfortunately. Specifying ORDER BY in the outermost SELECT statement in your query is the only way to ensure that the rows are returned in a particular order to the client.|||

Hi sg2000,

You can use
TOP (99) PERCENT
instead of
TOP (100) PERCENT

The results for the query "Select top 99 percent..." from a table, which has X row(s), is X row(s), even if the table has only one row.

Then, you will be able to use the "order by" clause whitout need to change anything in your application and without giving less rows as result.

|||

http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx

|||

OK, this is absolutely retarded.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/bb394abe-cae6-4905-b5c6-8daaded77742.htm

This says :

Specifies the sort order used on columns returned in a SELECT statement. The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

If it says "The ORDER BY clause is not valid in views" then why the heck is there a "sort by" column in the New View Screen?

|||

Because of the last bit:

The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless TOP is also specified

Wednesday, March 21, 2012

Order By Case Cast Convert Error

I have created a SQL Stored Procedure that uses a Case statement to
determine the Order By. For one of the Case statements I am trying to
turn a Char field into Datetime in for the Order By, however I can not
get it to work. Can someone please take a look and my code below and
tell me what I am doing wrong. Thank you.

ORDER BY
CASE WHEN @.SortBy = 'dttm_stamp' THEN dttm_stamp End,
CASE WHEN @.SortBy = 'Event_Date1' THEN CAST(CONVERT(char(10),
Event_Date1,101) as datetime) End,
CASE WHEN @.SortBy = 'FullName' THEN Emp_lastname + ', ' +
Emp_firstname End,
CASE WHEN @.SortBy = 'FullName Desc' THEN Emp_lastname + ', ' +
Emp_firstname End DESC,
CASE WHEN @.SortBy = 'Emp_SSN' THEN Emp_SSN End<jguilford@.cybergroup.com> wrote in message
news:1105983724.986065.70140@.c13g2000cwb.googlegro ups.com...
>I have created a SQL Stored Procedure that uses a Case statement to
> determine the Order By. For one of the Case statements I am trying to
> turn a Char field into Datetime in for the Order By, however I can not
> get it to work. Can someone please take a look and my code below and
> tell me what I am doing wrong. Thank you.
> ORDER BY
> CASE WHEN @.SortBy = 'dttm_stamp' THEN dttm_stamp End,
> CASE WHEN @.SortBy = 'Event_Date1' THEN CAST(CONVERT(char(10),
> Event_Date1,101) as datetime) End,
> CASE WHEN @.SortBy = 'FullName' THEN Emp_lastname + ', ' +
> Emp_firstname End,
> CASE WHEN @.SortBy = 'FullName Desc' THEN Emp_lastname + ', ' +
> Emp_firstname End DESC,
> CASE WHEN @.SortBy = 'Emp_SSN' THEN Emp_SSN End

What does "can not get it to work" mean? Do you get errors, or do you get
unexpected results? The best idea would probably be to post CREATE TABLE and
INSERT statements to set up a test case which illustrates your problem.

http://www.aspfaq.com/etiquette.asp?id=5006

Simon|||On 17 Jan 2005 09:42:05 -0800, jguilford@.cybergroup.com wrote:

>I have created a SQL Stored Procedure that uses a Case statement to
>determine the Order By. For one of the Case statements I am trying to
>turn a Char field into Datetime in for the Order By, however I can not
>get it to work. Can someone please take a look and my code below and
>tell me what I am doing wrong. Thank you.

Hi jquilford,

I guess that this is the line that's giving you trouble:

>CASE WHEN @.SortBy = 'Event_Date1' THEN CAST(CONVERT(char(10),
>Event_Date1,101) as datetime) End,

You are converting the datetime variable to the american mm/dd/yyyy
format, which is not very well suited for sorting. Then you are converting
it back to datetime, running alll kinds of risks because this format is
ambiguous - it's easily misinterpreted as dd/mm/yyyy, causing either wrong
sorting or conversion errors.

If your intention is to strip the time part from the datetime column, so
you can order by the date part only, use

CASE WHEN @.SortBy = 'Event_Date1' THEN DATEADD(day, DATEDIFF(day,
'20000101', Event_Date1), '20000101')

Or, if you really want to do it by conversion to string and back, use the
safe yyyymmdd format:

CASE WHEN @.SortBy = 'Event_Date1' THEN CAST(CONVERT(char(8),
Event_Date1,112) as datetime) End,

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> Or, if you really want to do it by conversion to string and back, use the
> safe yyyymmdd format:
> CASE WHEN @.SortBy = 'Event_Date1' THEN CAST(CONVERT(char(8),
> Event_Date1,112) as datetime) End,

Or simply say:

CASE WHEN @.SortBy = 'Event_Date1'
THEN CONVERT(char(8), Event_Date1,112)
End,

Then again, we have no idea jguildford mean "I can not get it to work".

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Mon, 17 Jan 2005 22:40:11 +0000 (UTC), Erland Sommarskog wrote:

>Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
>> Or, if you really want to do it by conversion to string and back, use the
>> safe yyyymmdd format:
>>
>> CASE WHEN @.SortBy = 'Event_Date1' THEN CAST(CONVERT(char(8),
>> Event_Date1,112) as datetime) End,
>Or simply say:
> CASE WHEN @.SortBy = 'Event_Date1'
> THEN CONVERT(char(8), Event_Date1,112)
> End,

Hi Erland,

Of course - no need to change it back to datetime in this case. Thanks!

>Then again, we have no idea jguildford mean "I can not get it to work".

Maybe he (she?) will post with more details?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||1) why are you using a CASE expression in an ORDER BY? Is the
destruction of portable code one of your design goals?

2) The column used for a sort should appear in the output so a human
being can use it to search the list. Basic human factors, etc. Put it
in the SELECT list.

3) Why are you making a date into a string in the first place? You can
sort of temporal datatypes too. And why are you converting it to a
string that is not in temporal order?|||Let me try to better explain my problem. I have created a page in
asp.net that has a datagrid that pulls a few fields form a SQL Server
table. Also on this page there is a dropdown box that allows you to
pick the way the data is sorted. You can pick to sort it by the data
the record was created, name (both ascending and descending), social
security number and by the date of the event. Now here is my problem,
the Event_Date1 field is a 10 character field (00/00/0000) instead of a
datetime field, it was set up like this a long time ago and can not be
changed. Because it is a character field when you use it to sort the
data it is sorted by month then day then year instead of year then
month then day. I tried to use a Cast and Convert statement to change
it to a datetime so it would sort correctly but now when I try to sort
by Event_Date1 I receive this error: "The conversion of a char data
type to a datetime data type resulted in an out-of-range datetime
value." I have included all of the code in my stored procedure below.
I hop this clears up some of the confusion. Let me know if there is
anything else I did not clear up. Thank you.

CREATE PROCEDURE spShowArchives
@.SecurityID int,
@.SortBy varchar(50)
AS
SELECT ID, Emp_lastname + ', ' + Emp_firstname as FullName, Emp_SSN,
Event_Date1, Injury_Illness_Type, Jurisdiction, Injury_Cause_Desc,
SISCO_claim, dttm_stamp FROM omni_table
WHERE security_id = @.SecurityID
AND omni_table.deleted_flag = 0
AND (SISCO_claim <> '' or SISCO_claim <> null or SISCO_claim <>
'Submit')
ORDER BY
CASE WHEN @.SortBy = 'dttm_stamp' THEN dttm_stamp End,
CASE WHEN @.SortBy = 'Event_Date1' THEN CAST(CONVERT(char(10),
Event_Date1,101) as datetime) End,
CASE WHEN @.SortBy = 'FullName' THEN Emp_lastname + ', ' +
Emp_firstname End,
CASE WHEN @.SortBy = 'FullName Desc' THEN Emp_lastname + ', ' +
Emp_firstname End DESC,
CASE WHEN @.SortBy = 'Emp_SSN' THEN Emp_SSN End
GO|||On 18 Jan 2005 11:53:03 -0800, jagguil4d wrote:

>Let me try to better explain my problem.
(snip)
> Now here is my problem,
>the Event_Date1 field is a 10 character field (00/00/0000) instead of a
>datetime field, it was set up like this a long time ago and can not be
>changed.

Hi jagguil4d / jguilford,

This is not correct. Of course it *can* be changed. You probably meant to
write that someone in your organization doesn't *want* it to be changed.
And that someone is most likely the person who's budget is impacted by the
cost of actually imprivong your system, but doesn't suffer from the
(eventually) much higher costs of numerous workarounds, bugfixes and error
recovery.

> Because it is a character field when you use it to sort the
>data it is sorted by month then day then year instead of year then
>month then day. I tried to use a Cast and Convert statement to change
>it to a datetime so it would sort correctly but now when I try to sort
>by Event_Date1 I receive this error: "The conversion of a char data
>type to a datetime data type resulted in an out-of-range datetime
>value."

You might try if you have more luck with
CASE WHEN @.SortBy = 'Event_Date1' THEN CONVERT(datetime, Event_Date1,
101) End,

If that fails as well, you have at least one row in your table with an
invalid date (and boy, are you lucky if it is indeed only one <g>). These
can be hard to find. A good starting point would be
SELECT KeyColumn, Event_Date1
FROM omni_table
WHERE ISDATE(Event_Date1) = 0

If the above returns no rows, but you get errors converting Event_Date1 to
datetime, then you probably have rows with a date in DD/MM/YYYY format, or
some other date format. The following will hopefully catch most of these
buggers:
SELECT KeyColumn, Event_Date1
FROM omni_table
WHERE Event_Date1 >= '13'
OR Event_Date1 NOT LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]'

Good luck! (You'll need it...)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||jagguil4d (jguilford@.cybergroup.com) writes:
> Now here is my problem, the Event_Date1 field is a 10 character field
> (00/00/0000) instead of a datetime field, it was set up like this a long
> time ago and can not be changed.

Of course it can! I hear this lame excuse every time, but seriously,
yes it can be changed. Just why would it be left unchanged?

Anyway, as Hugo points out you have garabge in this column, so if
you insist on that you don't want to change it, do this:

CASE WHEN @.SortBy = 'Event_Date1' THEN
substring(Event_Date1, 7, 4) + substring(Event_Date1, 1, 2) +
substring(Event_Date1, 4, 2)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||--CELKO-- (jcelko212@.earthlink.net) writes:
> 1) why are you using a CASE expression in an ORDER BY? Is the
> destruction of portable code one of your design goals?

Because he more cares about serving his users than paying sacrifice
to the Holy Church of Portability.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> he more cares about serving his users than paying sacrifice to the
Holy Church of Portability. <<

When you can get portability for no extra cost, there is no sacrifice
and considerable gains in maintaining the code over the life of the
system. Would really use getdate() instead of CURRENT_TIMESTAMP to
save a few keystrokes? Or ISNULL() instead of COALESCE()?|||--CELKO-- (jcelko212@.earthlink.net) writes:
> When you can get portability for no extra cost, there is no sacrifice
> and considerable gains in maintaining the code over the life of the
> system. Would really use getdate() instead of CURRENT_TIMESTAMP to
> save a few keystrokes? Or ISNULL() instead of COALESCE()?

In this case you questioned the use of CASE in ORDER BY, which
jguilford had added to offer desired functionailty to his application.
So there is a cost to be portable here.

By the way, there is a situations where isnull() works, but not
coalesce().

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

ORDER BY - parameter

In order to allow our users to create custom report queries, we've created a report with two parameters: "Where" and "OrderBy". In the Where parameter we pass the columns to be checked (Division = 'south' AND price > 100). This works great. The "OrderBy" parameter also works, if and only if, one column name is passed. If we attempt to pass a parameter (both in the designer and URL string) formatted as "col1, col2", the preview fails when the comma is encountered. Is it not possible to create a parameter that contains more than one column for the ORDER BY clause (parameter)?
ThanksThis works, I used this type of parameter all the time. Is you query an
expression? Or are you using an @. parameter. It will only work as an
expression. To pass the parameter on the ULR it must be encoded.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Joslin" <JohnJoslin@.discussions.microsoft.com> wrote in message
news:C26612BE-A503-44F8-9575-1B7C09332181@.microsoft.com...
> In order to allow our users to create custom report queries, we've created
a report with two parameters: "Where" and "OrderBy". In the Where parameter
we pass the columns to be checked (Division = 'south' AND price > 100). This
works great. The "OrderBy" parameter also works, if and only if, one column
name is passed. If we attempt to pass a parameter (both in the designer and
URL string) formatted as "col1, col2", the preview fails when the comma is
encountered. Is it not possible to create a parameter that contains more
than one column for the ORDER BY clause (parameter)?
> Thanks|||John.,
I changed your Where parameter name to WherePrm and added OrderBy1, OrderBy2
parameters. If needed assign default values.
SELECT TR_COID, TR_TranDate, TR_TranAmt, TR_Merchant, TR_MerchState,
TR_MerchCity, TR_MerchZip, TR_AcctCode, TR_MCC, TR_AcctNbr FROM TranDet
WHERE @.WherePrm ORDER BY @.OrderBy1, @.OrderBy2
Cem
"John Joslin" <JohnJoslin@.discussions.microsoft.com> wrote in message
news:7A469A37-A660-4626-A73A-09BCC1C28F46@.microsoft.com...
> Jason,
> Here is the actual statement: (again, the "where" works fine with multiple
entries)
> ="SELECT TR_COID, TR_TranDate, TR_TranAmt, TR_Merchant, TR_MerchState,
TR_MerchCity, TR_MerchZip, TR_AcctCode, TR_MCC, TR_AcctNbr FROM TranDet
WHERE (" & Parameters!Where.Value & ") ORDER BY (" &
Parameters!OrderBy.Value & ")"
> Do I need to further define whatever in the designer?
> Thanks,
> "Jason Carlson [MSFT]" wrote:
> > This works, I used this type of parameter all the time. Is you query an
> > expression? Or are you using an @. parameter. It will only work as an
> > expression. To pass the parameter on the ULR it must be encoded.
> >
> > --
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "John Joslin" <JohnJoslin@.discussions.microsoft.com> wrote in message
> > news:C26612BE-A503-44F8-9575-1B7C09332181@.microsoft.com...
> > > In order to allow our users to create custom report queries, we've
created
> > a report with two parameters: "Where" and "OrderBy". In the Where
parameter
> > we pass the columns to be checked (Division = 'south' AND price > 100).
This
> > works great. The "OrderBy" parameter also works, if and only if, one
column
> > name is passed. If we attempt to pass a parameter (both in the designer
and
> > URL string) formatted as "col1, col2", the preview fails when the comma
is
> > encountered. Is it not possible to create a parameter that contains more
> > than one column for the ORDER BY clause (parameter)?
> > >
> > > Thanks
> >
> >
> >|||Remove the parens in the order by. It is not valid SQL.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Joslin" <JohnJoslin@.discussions.microsoft.com> wrote in message
news:7A469A37-A660-4626-A73A-09BCC1C28F46@.microsoft.com...
> Jason,
> Here is the actual statement: (again, the "where" works fine with multiple
entries)
> ="SELECT TR_COID, TR_TranDate, TR_TranAmt, TR_Merchant, TR_MerchState,
TR_MerchCity, TR_MerchZip, TR_AcctCode, TR_MCC, TR_AcctNbr FROM TranDet
WHERE (" & Parameters!Where.Value & ") ORDER BY (" &
Parameters!OrderBy.Value & ")"
> Do I need to further define whatever in the designer?
> Thanks,
> "Jason Carlson [MSFT]" wrote:
> > This works, I used this type of parameter all the time. Is you query an
> > expression? Or are you using an @. parameter. It will only work as an
> > expression. To pass the parameter on the ULR it must be encoded.
> >
> > --
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "John Joslin" <JohnJoslin@.discussions.microsoft.com> wrote in message
> > news:C26612BE-A503-44F8-9575-1B7C09332181@.microsoft.com...
> > > In order to allow our users to create custom report queries, we've
created
> > a report with two parameters: "Where" and "OrderBy". In the Where
parameter
> > we pass the columns to be checked (Division = 'south' AND price > 100).
This
> > works great. The "OrderBy" parameter also works, if and only if, one
column
> > name is passed. If we attempt to pass a parameter (both in the designer
and
> > URL string) formatted as "col1, col2", the preview fails when the comma
is
> > encountered. Is it not possible to create a parameter that contains more
> > than one column for the ORDER BY clause (parameter)?
> > >
> > > Thanks
> >
> >
> >

Friday, March 9, 2012

oracle procedure help

i have this procedure written and compiled. ran the procedure, came out with 'procedure created'. no compilation errors. Now, my question is how do I run/execute this procedure...? No parameters in this procedure.
my procedure name is FIX_DISC_AMT.

At the SQL prompt, I called this procedure
SQL> fix_disc_amt ();
did not work...! ! :-(

thanks in advance.Originally posted by hnvhelp
i have this procedure written and compiled. ran the procedure, came out with 'procedure created'. no compilation errors. Now, my question is how do I run/execute this procedure...? No parameters in this procedure.
my procedure name is FIX_DISC_AMT.

At the SQL prompt, I called this procedure
SQL> fix_disc_amt ();
did not work...! ! :-(

thanks in advance.
This is Oracle and SQL Plus, isn't it? Then:

SQL> exec fix_disc_amt|||Originally posted by andrewst
This is Oracle and SQL Plus, isn't it? Then:

SQL> exec fix_disc_amt

Thank you Tony..., it works. Now I have a new error/problem.
Yes, it is in Oracle7; PL/SQL Release 2.3.4.3.0

First here is my code for my create procedure:

CREATE OR REPLACE PROCEDURE fix_disc_amt
IS
CURSOR disc_amt_fix IS
SELECT cust_id, div_code, order_id, sum(total_demand_amt), sum(order_disc_amt)
FROM order_item
WHERE order_disc_amt > 0
GROUP BY cust_id, div_code, order_id;
BEGIN
OPEN disc_amt_fix;
DECLARE
xcustid NUMBER;
xdivcode CHAR;
xorderid CHAR;
xtotaldemand NUMBER;
xorderdiscamt NUMBER;
BEGIN
FETCH disc_amt_fix
INTO xcustid, xdivcode, xorderid, xtotaldemand, xorderdiscamt;
UPDATE order_item
SET disc_amt=xorderdiscamt * (total_demand_amt / xtotaldemand)
WHERE cust_id = xcustid
AND div_code = xdivcode
AND order_id = xorderid;
END;
END;
/

after creating the procedure, I executed. Here is the error I get:
SQL> exec fix_disc_amt
begin fix_disc_amt; end;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "PCT5.FIX_DISC_AMT", line 17
ORA-06512: at line 1

SQL>
Any idea, Please help.
Once again thank you for your assistance.|||Originally posted by hnvhelp
Thank you Tony..., it works. Now I have a new error/problem.
Yes, it is in Oracle7; PL/SQL Release 2.3.4.3.0

First here is my code for my create procedure:

CREATE OR REPLACE PROCEDURE fix_disc_amt
IS
CURSOR disc_amt_fix IS
SELECT cust_id, div_code, order_id, sum(total_demand_amt), sum(order_disc_amt)
FROM order_item
WHERE order_disc_amt > 0
GROUP BY cust_id, div_code, order_id;
BEGIN
OPEN disc_amt_fix;
DECLARE
xcustid NUMBER;
xdivcode CHAR;
xorderid CHAR;
xtotaldemand NUMBER;
xorderdiscamt NUMBER;
BEGIN
FETCH disc_amt_fix
INTO xcustid, xdivcode, xorderid, xtotaldemand, xorderdiscamt;
UPDATE order_item
SET disc_amt=xorderdiscamt * (total_demand_amt / xtotaldemand)
WHERE cust_id = xcustid
AND div_code = xdivcode
AND order_id = xorderid;
END;
END;
/

after creating the procedure, I executed. Here is the error I get:
SQL> exec fix_disc_amt
begin fix_disc_amt; end;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "PCT5.FIX_DISC_AMT", line 17
ORA-06512: at line 1

SQL>
Any idea, Please help.
Once again thank you for your assistance.
This could be either a string to number conversion error, or an overflow.
I suspect it is one of the variables declared as CHAR, since the length is not specified and defaults to 1.

The best way to avoid such errors is to "anchor" variables to the cursor or table being processed using %TYPE or (better) %ROWTYPE. I would do this:

CREATE OR REPLACE PROCEDURE fix_disc_amt
IS
CURSOR disc_amt_fix IS
SELECT cust_id, div_code, order_id, sum(total_demand_amt) sum_total_demand_amt, sum(order_disc_amt) sum_order_disc_amt
FROM order_item
WHERE order_disc_amt > 0
GROUP BY cust_id, div_code, order_id;
daf_rec disc_amt_fix%ROWTYPE;
BEGIN
OPEN disc_amt_fix;
FETCH disc_amt_fix
INTO daf_rec;
CLOSE disc_amt_fix;

UPDATE order_item
SET disc_amt=daf_rec.sum_order_disc_amt * (total_demand_amt / daf_rec.sum_total_demand_amt)
WHERE cust_id = daf_rec.cust_id
AND div_code = daf_rec.div_code
AND order_id = daf_rec.order_id;
END;
/

Actually, this is a strange piece of code, because it only fetches 1 row from the cursor - what if there is more than one? If you meant to process all rows it is better written using a FOR loop:

CREATE OR REPLACE PROCEDURE fix_disc_amt
IS
CURSOR disc_amt_fix IS
SELECT cust_id, div_code, order_id, sum(total_demand_amt) sum_total_demand_amt, sum(order_disc_amt) sum_order_disc_amt
FROM order_item
WHERE order_disc_amt > 0
GROUP BY cust_id, div_code, order_id;
BEGIN
FOR daf_rec IN disc_amt_fix LOOP
UPDATE order_item
SET disc_amt=daf_rec.sum_order_disc_amt * (total_demand_amt / daf_rec.sum_total_demand_amt)
WHERE cust_id = daf_rec.cust_id
AND div_code = daf_rec.div_code
AND order_id = daf_rec.order_id;
END LOOP;
END;
/|||SQL>exec fix_disc_amt;
or
SQL>execute fix_disc_amt;

semicolon is optional|||hi i have created a package and a function inside that package , this function returns a cursor
how can i call this function on sql prompt..
when i try to run using exec command it gives this error

SQL>exec test50pack.test50_select(10)
BEGIN test50pack.test50_select(10); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'TEST50_SELECT' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

could this be because the function is returning a cursor ... if this is the problem is there any way we can store the returned cursor and display the contents of that cursor on sql prompt...

code for creating the package and function is as follows..

create or replace package test50pack as
type owntype is ref cursor return test50%ROWTYPE;
function test50_select (uid NUMBER) return owntype;
end test50pack;

create or replace package body test50pack as function test50_select(uid NUMBER) return owntype is
rc owntype ;
begin
open rc for select * from test50 where col1 = uid;
return rc;
end;
end test50pack;|||Originally posted by prashantbist
hi i have created a package and a function inside that package , this function returns a cursor
how can i call this function on sql prompt..
when i try to run using exec command it gives this error

SQL>exec test50pack.test50_select(10)
BEGIN test50pack.test50_select(10); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'TEST50_SELECT' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

could this be because the function is returning a cursor ... if this is the problem is there any way we can store the returned cursor and display the contents of that cursor on sql prompt...

code for creating the package and function is as follows..

create or replace package test50pack as
type owntype is ref cursor return test50%ROWTYPE;
function test50_select (uid NUMBER) return owntype;
end test50pack;

create or replace package body test50pack as function test50_select(uid NUMBER) return owntype is
rc owntype ;
begin
open rc for select * from test50 where col1 = uid;
return rc;
end;
end test50pack;

In SQL Plus, do this:

SQL> variable c refcursor
SQL> exec :c := test50pack.test50_select(10)
SQL> print c|||Hi ..
i am trying to call a stored function in a package through jdbc.. but i am
getting this exception..

error code ORA-17059
java.sql.SQLException: Fail to convert to internal representation
at
com.tcs.mastercraft.mcutil.DM_errHandler.DMErrHand ler(DM_errHandler.java:307
)
at
com.tcs.mastercraft.mcutil.DM_errHandler.DMErrHand ler(DM_errHandler.java:94)
at DB2.PerformanceTest50.Get(PerformanceTest50.java:4 465)
at DB2.PerformanceTest50.Get(PerformanceTest50.java:4 593)
at DB2.PerformanceTest50.Oper_PTest50(PerformanceTest 50.java:5064)
at
DB2.PerformanceTest50_Oper_PTest50_305_drv.xlmain( PerformanceTest50_Oper_PTe
st50_305_drv.java:58)
at
DB2.PerformanceTest50_Oper_PTest50_305_drv.main(Pe rformanceTest50_Oper_PTest
50_305_drv.java:119)

The package and function are as follows..

create or replace package test50pack as
type owntype is ref cursor return test50%ROWTYPE;
function test50_select (uid NUMBER) return owntype;
end test50pack;

create or replace package body test50pack as function test50_select(uid
NUMBER) return owntype is
rc owntype ;
begin
open rc for select * from test50 where col1 = uid;
end;
end test50pack;

and my calling code is

cst = con.prepareCall ("{ ? = call test50pack.test50_select (?) }");
cst.registerOutParameter (1, OracleTypes.CURSOR);
cst.setInt (2, h_col1);
cst.execute ();
rs=(ResultSet)cst.getObject(1);|||Hi,

I am just begin to using oracle, now i am using oracle 9.2. Before oracle most of the time i only use MS-Sql Server 2000. I have one problem in here that i want to make some simple stored procedure.Say i just want to select sysdate from dual ,i want to put it into stored proc but when i try :
create or replace Test ()
as
begin
select sysdate from dual
end

that i believe it will work in sqlserver(beside the sysdate function and dual which sqlserver dont have),it not worked in oracle, i using TOAD as 3rd party application to access my oracle. So i hope that u can help me what the different in using stored proc in oracle and sqlserver , for example if i want to have the above querry to run in oracle

thanx|||Berwin,

in Oracle, syntax for your procedure would be something like this:

create or replace procedure test
as
dummy date;
begin
select sysdate into dummy from dual;
end;

You explicitly have to say it is a procedure; if it has no parameters you don't need brackets; you have to have INTO in such an example.

Wednesday, March 7, 2012

Oracle not supported?

I installed de trial version of SQL Server 2005 Workgroup, then created a
small report using a Oracle Database.
In Visual Studio the report previews fine but after being deployed to SSRS
i't tells me that the data extension is not registered.
Is this a trial version issue or is the Oracle support for SSRS have to be
configured after instalation?
Juan Ignacio Herreralook at this page:
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
the workgroup edition support only data source against the same SQL Servre
installation only.
you can't directly access Oracle or any external source.
but... maybe you can create a linked server to your oracle database and
create your queries using the openquery SQL command.
the queries will come from your local server and not from your oracle
database.
"Juan Ignacio Herrera" <nacho(quitar)@.delta.com.gt> wrote in message
news:uQ8sNyvGGHA.3176@.TK2MSFTNGP12.phx.gbl...
>I installed de trial version of SQL Server 2005 Workgroup, then created a
> small report using a Oracle Database.
> In Visual Studio the report previews fine but after being deployed to SSRS
> i't tells me that the data extension is not registered.
> Is this a trial version issue or is the Oracle support for SSRS have to be
> configured after instalation?
> Juan Ignacio Herrera
>

Saturday, February 25, 2012

oracle connection using configuration file

I created a package that uses configuration file for db connections. SQL Server connection works fine, but for oracle it fails.

I m putting servername=alias from tnsnames.ora file.

the connection works fine from ide

For an OLE-DB connection the value of the config file property assignment, for connection string, would look something like this-

Data Source=ABC.XYZ;User ID=UserName;Password=secret;Provider=MSDAORA.1;Persist Security Info=True;

ABC.XYZ is the alias from tnsnames.

|||

Thanks Darren...

I exactly have the same line as you suggested. The issue was with InitialCatalog propterty. I took that out from configuration file and it worked fine.

I read somewhere only the servername, userid and password property is enough to make a connection to oracle database.

Thanks

Mahesh

Friday, March 30, 2012

Order By with Query then bottom border

Wednesday, March 28, 2012

Order by problem within a View

Monday, March 26, 2012

order by in a view in sqlserver2005

order by in a view in sqlserver2005

Friday, March 23, 2012

Order by clause in View doesn''t order.

Order by clause in View doesn't order.

Order by clause in View doesn't order.

Order by clause in View doesn't order.

Wednesday, March 21, 2012

Order By Case Cast Convert Error

ORDER BY - parameter

Friday, March 9, 2012

oracle procedure help

Wednesday, March 7, 2012

Oracle not supported?

Saturday, February 25, 2012

oracle connection using configuration file

Oracle DBA to learn MS SQL

Blog Archive

About Me