ORDER BY, only orders by the first character?

Hi,

I am using a stored procedure that is databound to a dropdownlist. The stored procedure works fine, aside from the fact that the ORDER BY only sorts the list of items using the first character in the cell. E.g. The numbers 1, 20, 100 and 200 would be ordered as follows:

1

100

20

200

clearly i am doing something wrong and i apologise if it is a stupid question, but could anyone please offer me some help?

Thank you,

Shep

ORDER BY a numeric data type as opposed to ORDER BY a character data type.

Run the following code in a test database. Note the absence of indexes.

CREATE TABLE dbo.OrderByInt(number int)

GO

INSERT INTO dbo.OrderByInt(number) VALUES(1)

INSERT INTO dbo.OrderByInt(number) VALUES(10)

INSERT INTO dbo.OrderByInt(number) VALUES(100)

INSERT INTO dbo.OrderByInt(number) VALUES(2)

INSERT INTO dbo.OrderByInt(number) VALUES(20)

INSERT INTO dbo.OrderByInt(number) VALUES(200)

SELECT number FROM dbo.OrderByInt

SELECT number FROM dbo.OrderByInt ORDER BY number

CREATE TABLE dbo.OrderByVarchar(number varchar(3))

GO

INSERT INTO dbo.OrderByVarchar(number) VALUES('1')

INSERT INTO dbo.OrderByVarchar(number) VALUES('10')

INSERT INTO dbo.OrderByVarchar(number) VALUES('100')

INSERT INTO dbo.OrderByVarchar(number) VALUES('2')

INSERT INTO dbo.OrderByVarchar(number) VALUES('20')

INSERT INTO dbo.OrderByVarchar(number) VALUES('200')

SELECT number FROM dbo.OrderByVarchar

SELECT number FROM dbo.OrderByVarchar ORDER BY number

SELECT number FROM dbo.OrderByVarchar ORDER BY CAST(number AS int)

|||

It sounds like you're storing numbers as text (in other words, storing "int" values in a "varchar" column). So, SQL Server is doing a text sort, which does indeed go by the first character.

The best solution is to use a data type that matches the data (use "int" for integer numbers, and nvarchar for text).

An alternative solution is to use a CAST or CONVERT in your order by to convert it to "int" before sorting -- ORDER BY CAST(myColumn AS int) -- but any sort of bulk operation on an expression like that cripples SQL Server's query optimizer and will hurt performance on large tables.

-Ryan / Kardax

Edit: "lkh" beat me by seconds, and provides a nice illustration of the problem and solution.

|||

Thank you for your reply lkd, however i am still having trouble:

When I try the above code i get the error message 'TABLE NOT SUPPORTED BY THIS EDITOR'

I am using Visual Web Developer 2005 Express Edition, could this be the problem?

Shep

|||

Sorry I haven't fully explained myself (i missed the most important part, my apologise!)

i am working in units of voltage & power, so there for the listing would be as follows:

1kW

10W

100W

20W

200W

so i dont think its an option to convert the values into integers, given that I have other non-numeric characters in there! Is it a lost cause?

Shep

|||I have never used Visual Web Developer but in a quick tour of the web site I don't see anything about creating objects in a database. You may have to use an editor in your database.|||

My honest though probably not helpful thought is that this is a database design problem. Can you redesign the table to have a column called Watt of int datatype?

Otherwise you could create a complicated CASE statement and sort on that column but that is not ideal.

|||Well the table is supplying the items for the dropdownlist, based on a certain product type. So just using numbers without the corresponding measurements would not look right in the dropdownlist.|||Can you add an int column that wouldn't appear in the UI to the table that would be used in the SELECT statement that populates the dropdown list?|||

If your are only using the single rightmost character ( as in your examples) as alpha, then the following may work for you.

BOTH strip off the last character, AND convert to an Integer ONLY in the ORDER BY clause.

DECLARE @.MyTestTable table
( MyColumn varchar(5) )

INSERT INTO @.MyTestTable ( MyColumn ) VALUES ( '2A' )
INSERT INTO @.MyTestTable ( MyColumn ) VALUES ( '10W' )
INSERT INTO @.MyTestTable ( MyColumn ) VALUES ( '1A' )
INSERT INTO @.MyTestTable ( MyColumn ) VALUES ( '100W' )
INSERT INTO @.MyTestTable ( MyColumn ) VALUES ( '200W' )
INSERT INTO @.MyTestTable ( MyColumn ) VALUES ( '20W' )

SELECT MyColumn
FROM @.MyTestTable
ORDER BY cast( left( MyColumn, ( len( MyColumn) - 1 )) AS int )

|||

Hi,

thanks to everyone for their help, in the end i have resorted to simply using VB to code in the items depending on the category: e.g.

If Value = 2 Then

ddlPara1.Items.Add(New ListItem("50V", "50V"))

ddlPara1.Items.Add(New ListItem("120V", "120V"))

ddlPara1.Items.Add(New ListItem("150V", "150V"))

ddlPara1.Items.Add(New ListItem("300V", "300V"))

ddlPara1.Items.Add(New ListItem("400V", "400V"))

If Value = 3 Then........

This shouldnt affect the performance of my application should it?

Thanks again,

Shep

Order By Parameter

Is it possible to use a variable like @.SelectOrderBy with the Order By of a stored procedure? or do I need several SP's?

Select *
FROM Table
Order By @.SelectOrderBy

I want to let a user select from a drop down on a webform the order by column to use.

ThanksYou'd either have seperate stored procedures (or a conditional within one stored procedure), or to implement it like your example above you would have to use dynamic SQL (look in Books Online for proper syntax, mine is iffy):

Declare @.strSQL VARCHAR(8000)
SELECT @.strSQL = "SELECT * FROM table ORDER BY " + @.SelectOrderBy
EXEC @.strSQL

It's not advisable to do it this way however, due to security and efficiencty issues. Stick with the conditional within one proc if it's not too complex!|||the other option is to pull the data back into a dataset and then use a dataview with a sort, passing in the column they selected.

cs

Order By madness

I have a simple case statement which changes the "ORDER BY" clause based on
a parameter passed to a stored proc. 4 out of 5 cases work, but one fails
with the following:
Server: Msg 295, Level 16, State 3, Procedure pr_reportPartnersDBTest, Line
12
Syntax error converting character string to smalldatetime data type.
If I comment out the case block and put each ORDER BY as an independent
statement (uncommenting one at a time), they all work without error.
When the case block is used and the int 4 is passed (my zip field), I
receive the error message above. If I comment out the case statement and
just use "ORDER BY zip" it works fine.
I must be missing something obvious, but I'm too close and suspect I'm
missing something that someone else will immediately see.
All comments, ideas or thoughts welcomed.
-- CODE snippet --
select ...
from ...
where...
order by
case @.orderBy
when 1 then dateRegistered
when 2 then gender
when 3 then bodyType
when 4 then zip
when 5 then ageRange
else dateRegistered
end
-- The next lines are for debugging purposes...
-- I commented out the case block above, then tested each of the lines below
one at a time. They all work as expected. But the case statement above fails
when I pass it a 4 (zip). All other values work as expected.
-- order by dateRegistered
-- order by gender
-- order by bodyType
-- order by zip
-- order by ageRangeCASE in SQL Server is an expression that returns a scalar value of a
specific datatype. That means that if the separate expressions for each WHEN
have a different datatype, they will explicitly be converted to the datatype
with the highest datatype precedence, in your case smalldatetime. And you
can't convert all zipcodes into a smalldatetime value.
So the simplest way to do it is to have a separate CASE for each column:
order by
case @.orderBy when 2 then gender end,
case @.orderBy when 3 then bodyType end,
case @.orderBy when 4 then zip end,
case @.orderBy when 5 then ageRange end,
case when @.OrderBy NOT IN (2,3,4,5) then dateRegistered end
Jacco Schalkwijk
SQL Server MVP
"Don B" <DonBaarns@.hotmail.com> wrote in message
news:OlsEGQOUFHA.1432@.TK2MSFTNGP09.phx.gbl...
>I have a simple case statement which changes the "ORDER BY" clause based on
> a parameter passed to a stored proc. 4 out of 5 cases work, but one fails
> with the following:
> Server: Msg 295, Level 16, State 3, Procedure pr_reportPartnersDBTest,
> Line
> 12
> Syntax error converting character string to smalldatetime data type.
> If I comment out the case block and put each ORDER BY as an independent
> statement (uncommenting one at a time), they all work without error.
> When the case block is used and the int 4 is passed (my zip field), I
> receive the error message above. If I comment out the case statement and
> just use "ORDER BY zip" it works fine.
> I must be missing something obvious, but I'm too close and suspect I'm
> missing something that someone else will immediately see.
> All comments, ideas or thoughts welcomed.
> -- CODE snippet --
> select ...
> from ...
> where...
> order by
> case @.orderBy
> when 1 then dateRegistered
> when 2 then gender
> when 3 then bodyType
> when 4 then zip
> when 5 then ageRange
> else dateRegistered
> end
> -- The next lines are for debugging purposes...
> -- I commented out the case block above, then tested each of the lines
> below
> one at a time. They all work as expected. But the case statement above
> fails
> when I pass it a 4 (zip). All other values work as expected.
>
> -- order by dateRegistered
> -- order by gender
> -- order by bodyType
> -- order by zip
> -- order by ageRange
>|||It is also worth mention that a CASE is not allowed in the ORDER BY in
Standard SQL-92. You can put it in a column in the select list, name
it and use that name in the ORDER BY. This way your code will port, be
readable to people who do not speak T-SQL dialect and your user will be
able to tell what the sort was done on, instead of trying to guess.
In the olde days, we would print the sort key at both the left and
right sides of a print out, so you could lay a ruler acorss the 132
column page. The printout were pretty fuzzy at times and this really
helped. Today, I assume that the sort column would be on the right and
locked so you can scroll horizonally.|||Jacco,
EXCELLENT... I clearly don't know enough fundamentals about T-SQL. That
works great for my example.
I would really like a secondary sort on zip code (a varchar in my case)
which would be something like this:
order by zip, dateRegistered
and/or
order by bodyType, zip
The two statements above work as expected.
I can't just put the two columns with a comma inside the Then/End
combination as the case statement uses the comma as a delimiter. I don't
know how to escape it (or the equivalent) so the case statement can return a
set of columns for the order by clause. I may also want to change the
ASC/DESC order on specific columns but I assume if I figure out the syntax
for the multiple columns it will be similar if I add specific sort orders
for some columns.
All ideas welcomed!
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:O0KSlYOUFHA.2892@.TK2MSFTNGP14.phx.gbl...
> CASE in SQL Server is an expression that returns a scalar value of a
> specific datatype. That means that if the separate expressions for each
WHEN
> have a different datatype, they will explicitly be converted to the
datatype
> with the highest datatype precedence, in your case smalldatetime. And you
> can't convert all zipcodes into a smalldatetime value.
> So the simplest way to do it is to have a separate CASE for each column:
> order by
> case @.orderBy when 2 then gender end,
> case @.orderBy when 3 then bodyType end,
> case @.orderBy when 4 then zip end,
> case @.orderBy when 5 then ageRange end,
> case when @.OrderBy NOT IN (2,3,4,5) then dateRegistered end
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Don B" <DonBaarns@.hotmail.com> wrote in message
> news:OlsEGQOUFHA.1432@.TK2MSFTNGP09.phx.gbl...
on
fails
>|||Use the same approach:
order by
case @.orderBy when 1 then zip end,
case @.orderBy when 1 then dateRegistered end,
case @.orderBy when 2 then bodyType end,
case @.orderBy when 2 then zip end ASC,
case @.orderBy when 3 then bodyType end,
case @.orderBy when 3 then zip end DESC
Terri Morton
MVP - ASP/ASP.NET
"Don B" <DonBaarns@.hotmail.com> wrote in message
news:OrWlLtOUFHA.3716@.TK2MSFTNGP12.phx.gbl...
> Jacco,
> EXCELLENT... I clearly don't know enough fundamentals about T-SQL. That
> works great for my example.
> I would really like a secondary sort on zip code (a varchar in my case)
> which would be something like this:
> order by zip, dateRegistered
> and/or
> order by bodyType, zip
> The two statements above work as expected.
> I can't just put the two columns with a comma inside the Then/End
> combination as the case statement uses the comma as a delimiter. I don't
> know how to escape it (or the equivalent) so the case statement can return
> a
> set of columns for the order by clause. I may also want to change the
> ASC/DESC order on specific columns but I assume if I figure out the syntax
> for the multiple columns it will be similar if I add specific sort orders
> for some columns.
> All ideas welcomed!
>
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote
> in message news:O0KSlYOUFHA.2892@.TK2MSFTNGP14.phx.gbl...
> WHEN
> datatype

ORDER BY in a SP

I'm getting a little frustrated... I'm trying to write a stored proc... the
following code works fine on its own in Query Analyzer... when I put it into
my stored proc... SQL Server complains about the ORDERS BY clause.
SELECT TOP 1 UserID FROM Users
WHERE UserID > 50
ORDER BY UserID DESC
According to MSDN "The ORDER BY clause is invalid in views, inline
functions, derived tables, and subqueries, unless TOP is also specified."...
so I don't see why it isn't working.what is the Procedure code you are using. Looks like some minor typing error
It works at my end .
CREATE TABLE Users
(
User_ID int,
User_Name varchar(10)
)
GO
INSERT INTO Users
SELECT 1, 'Jon'
UNION
SELECT 10,'Mary'
UNION
SELECT 50,'Ron'
UNION
SELECT 60, 'Harry'
UNION
SELECT 80, 'Nancy'
GO
CREATE PROCEDURE usp_test
AS
SELECT TOP 1 * FROM Users
WHERE User_ID > 50
Order BY User_ID DESC
GO
Exec usp_test
GO
Cheers,
Siaj
"andrew" wrote:

> I'm getting a little frustrated... I'm trying to write a stored proc... th
e
> following code works fine on its own in Query Analyzer... when I put it in
to
> my stored proc... SQL Server complains about the ORDERS BY clause.
> SELECT TOP 1 UserID FROM Users
> WHERE UserID > 50
> ORDER BY UserID DESC
> According to MSDN "The ORDER BY clause is invalid in views, inline
> functions, derived tables, and subqueries, unless TOP is also specified.".
.
> so I don't see why it isn't working.|||I figured it out... I had the SELECT statement completely enclosed in ( ) -
which seemed to work fine without an ORDER BY... but when I added in the
ORDER BY... which was still in between the ( )... suddenly there was a
problem... YAY computers.
"siaj" wrote:
> what is the Procedure code you are using. Looks like some minor typing err
or
> It works at my end .
> CREATE TABLE Users
> (
> User_ID int,
> User_Name varchar(10)
> )
> GO
> INSERT INTO Users
> SELECT 1, 'Jon'
> UNION
> SELECT 10,'Mary'
> UNION
> SELECT 50,'Ron'
> UNION
> SELECT 60, 'Harry'
> UNION
> SELECT 80, 'Nancy'
> GO
> CREATE PROCEDURE usp_test
> AS
> SELECT TOP 1 * FROM Users
> WHERE User_ID > 50
> Order BY User_ID DESC
> GO
> Exec usp_test
> GO
> Cheers,
> Siaj
> "andrew" wrote:
>

ORDER BY error

Hello,

in a complex stored procedure, i get an error when i want to apply an ORDER BY statement :

Here is a part of this Stored Procedure :

SELECT TaOperations.Id_Operation, TaOperations.HeureDebut, TaOperations.Signe, TaOperations.Facturer, TaOperations.IdUserVerrou,

TaOperations.Id_Client, TaTypeOperation.Facturable, CONVERT(CHAR(10), TaOperations.HeureDebut,

103) + ' - ' + TaTypeOperation.TypeOperation AS Code

FROM TaOperations INNER JOIN

TaTypeOperation ON TaOperations.Id_TypeOperation = TaTypeOperation.Id_TypeOperation

WHERE (TaOperations.Id_Client = CONVERT(int, @.Id_Client)) AND (TaTypeOperation.Facturable = 1) AND (TaOperations.IdUserVerrou IS NULL)

If I want to apply an ORDER BY statement, i have

SELECT TaOperations.Id_Operation, TaOperations.HeureDebut, TaOperations.Signe, TaOperations.Facturer, TaOperations.IdUserVerrou,

TaOperations.Id_Client, TaTypeOperation.Facturable, CONVERT(CHAR(10), TaOperations.HeureDebut, 103)

+ ' - ' + TaTypeOperation.TypeOperation AS Code

FROM TaOperations INNER JOIN

TaTypeOperation ON TaOperations.Id_TypeOperation = TaTypeOperation.Id_TypeOperation

WHERE (TaOperations.Id_Client = CONVERT(int, @.Id_Client)) AND (TaTypeOperation.Facturable = 1) AND (TaOperations.IdUserVerrou IS NULL)

ORDER BY TaOperations.HeureDebut

I get this error :

Msg 156, Level 15, State 1, Procedure SIE_ListeOperationsFacturablesParClient, Line 46

Incorrect syntax near the keyword 'ORDER'.

Does anyone cab help me ?

Is there anything else after the SELECT statement?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Here is the whole code :

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[SIE_ListeOperationsFacturablesParClient]

@.Id_Client char(5),

@.Cas int

AS

BEGIN

SET NOCOUNT ON;

If @.cas = 0 (

SELECT TaOperations.Id_Operation, TaOperations.HeureDebut, TaOperations.Signe, TaOperations.Facturer, TaOperations.IdUserVerrou,

TaOperations.Id_Client, TaTypeOperation.Facturable, CONVERT(CHAR(10), TaOperations.HeureDebut,

103) + ' - ' + TaTypeOperation.TypeOperation AS Code

FROM TaOperations INNER JOIN

TaTypeOperation ON TaOperations.Id_TypeOperation = TaTypeOperation.Id_TypeOperation

WHERE (TaOperations.Id_Client = CONVERT(int, @.Id_Client)) AND (TaTypeOperation.Facturable = 1) AND (TaOperations.Signe = 1) AND

(TaOperations.IdUserVerrou IS NOT NULL))

If @.cas = 1 (

SELECT TaOperations.Id_Operation, TaOperations.HeureDebut, TaOperations.Signe, TaOperations.Facturer, TaOperations.IdUserVerrou,

TaOperations.Id_Client, TaTypeOperation.Facturable, CONVERT(CHAR(10), TaOperations.HeureDebut,

103) + ' - ' + TaTypeOperation.TypeOperation AS Code

FROM TaOperations INNER JOIN

TaTypeOperation ON TaOperations.Id_TypeOperation = TaTypeOperation.Id_TypeOperation

WHERE (TaOperations.Id_Client = CONVERT(int, @.Id_Client)) AND (TaTypeOperation.Facturable = 1) AND (TaOperations.Signe = 0) AND

(TaOperations.IdUserVerrou IS NOT NULL))

If @.cas = 2 (

SELECT TaOperations.Id_Operation, TaOperations.HeureDebut, TaOperations.Signe, TaOperations.Facturer, TaOperations.IdUserVerrou,

TaOperations.Id_Client, TaTypeOperation.Facturable, CONVERT(CHAR(10), TaOperations.HeureDebut, 103)

+ ' - ' + TaTypeOperation.TypeOperation AS Code

FROM TaOperations INNER JOIN

TaTypeOperation ON TaOperations.Id_TypeOperation = TaTypeOperation.Id_TypeOperation

WHERE (TaOperations.Id_Client = CONVERT(int, @.Id_Client)) AND (TaTypeOperation.Facturable = 1) AND (TaOperations.IdUserVerrou IS NULL)

ORDER BY TaOperations.HeureDebut DESC

)

END

|||T-SQL normally uses the following syntax but you can leave out the BEGIN and END if you are only executing one query (or a single line of code).

IF @.var = x
BEGIN
YOUR CODE
END

Could you try leaving out the ( and ) for the if clauses?

If @.cas = 2

SELECT TaOperations.Id_Operation, TaOperations.HeureDebut, TaOperations.Signe, TaOperations.Facturer, TaOperations.IdUserVerrou,

TaOperations.Id_Client, TaTypeOperation.Facturable,CONVERT(CHAR(10), TaOperations.HeureDebut, 103)

+' - '+ TaTypeOperation.TypeOperation AS Code

FROM TaOperations INNERJOIN

TaTypeOperation ON TaOperations.Id_TypeOperation = TaTypeOperation.Id_TypeOperation

WHERE(TaOperations.Id_Client =CONVERT(int, @.Id_Client))AND(TaTypeOperation.Facturable = 1)AND(TaOperations.IdUserVerrou ISNULL)

ORDERBY TaOperations.HeureDebut DESC

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Wonderful !!! it works thanks a lotsql

ORDER BY Command in SQL Stored Procedure

How do you use a variable in the ORDER BY command in a sql statement.
I currently have:
****************************************
CREATE Procedure SS_POList
(
@.CompanyID nvarchar(10),
@.PO varchar (20) = '%'
)
As

SELECT
SS_Sendback.EndUserPO,
SS_Sendback.PO,
COUNT(SS_Sendback.EndUserPO) as pocount,
SUM(SS_Sendback.Customerprice) as totcost

FROM
SS_Sendback

WHERE
SS_Sendback.EndusercustomerNumber = @.CompanyID
AND
SS_Sendback.EnduserPO Like @.PO

GROUP BY
SS_Sendback.EndUserPO,
SS_Sendback.PO

ORDER BY
SS_Sendback.PO
GO
*************************************
I changed it to
*************************************
CREATE Procedure SS_POList
(
@.CompanyID nvarchar(10),
@.PO varchar (20) = '%',
@.Order varchar(20)
)
As

SELECT
SS_Sendback.EndUserPO,
SS_Sendback.PO,
COUNT(SS_Sendback.EndUserPO) as pocount,
SUM(SS_Sendback.Customerprice) as totcost

FROM
SS_Sendback

WHERE
SS_Sendback.EndusercustomerNumber = @.CompanyID
AND
SS_Sendback.EnduserPO Like @.PO

GROUP BY
SS_Sendback.EndUserPO,
SS_Sendback.PO

ORDER BY
@.Order
GO

and I receive the following error..
error1008: the select item identified by the Order By number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
How does it know @.order is a position and not a column name... It's a variable. I'm obviously doing something wrong. Can someone help.
Thanksas you've discovered, the order by cannot be dynamic. you'd have to use a CASE statement to use a variable here - pseudocode

CASE @.orderBy = 'column1'
ORDER BY column1

that kinda thing. I've seen an FAQ on this somewhere, I'll see if I can dig out the link.|||I'd like to see the FAQ if you can find it. I tried looking but I had no luck.|||try this one for size :

http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=344|||Rather than the method suggested in the article:

declare @.OrderBy varchar(20)
select @.Orderby = 'ContactName'
select * from Northwind.dbo.Customers
ORDER BY
 CASE @.OrderBy
 WHEN 'CompanyName' THEN CompanyName
 WHEN 'ContactName' THEN ContactName
 END
 ASC

I suggest this method:

declare @.OrderBy varchar(20)
select @.Orderby = 'ContactName'
select * from Northwind.dbo.Customers
ORDER BY
 CASE WHEN @.OrderBy ='CompanyName' THEN CompanyName END ASC,
 CASE WHEN @.OrderBy ='ContactName' THEN ContactName END ASC

The method in the article requires all of the columns to be of the same datatype (or CAST as the same datatype). The alternate method allows you to use different data types easily, plus use ASC and DESC as needed.

Terri|||Thanks for all you help.
This seems like a lot of work when a simple
ORDER BY @.somevariable
should work but apperently thats not supported by SQL server.
In my searches I also found some suggestions to assign the search string to a variable and execute the string but I think that would be the least desireable way to do this.
Thanks Again.

ORDER BY Clause On Bit Value Failure using SELECT DISTINCT

Hey,
I am trying to construct an ORDER BY clause for a SELECT DISTINCT query that
attempts to order the data based on a particular bit value stored in a
column named [Properties], but I get this failure message:
"ORDER BY items must appear in the select list if SELECT DISTINCT is
specified"
If I remove the "DISTINCT" keyword, then all is fine, but I do not
understand how to properly set this type of query up. Any help is very much
appreciated.
The query I am using is:
SELECT DISTINCT
ScanChangedParams.*, ScanStrategies.StrategyName AS
Expr1, ScanStrategies.TemplateID AS Expr2, ScanStrategies.ProjectID AS
Expr3,
ScanStrategies.RootTemplateID AS Expr4,
ScanStrategies.ScanError AS Expr5, TPSScanTemplates.ParamName AS Expr6
FROM ScanChangedParams WITH (NOLOCK) INNER JOIN
ScanStrategies WITH (NOLOCK) ON
ScanStrategies.ScanTaskID = ScanChangedParams.ScanTaskID AND
ScanStrategies.StrategyID =
ScanChangedParams.StrategyID INNER JOIN
TPSScanTemplates WITH (NOLOCK) ON
ScanChangedParams.ParamID = TPSScanTemplates.CommonParamID
WHERE (ScanChangedParams.ScanTaskID = 68)
ORDER BY ScanChangedParams.Properties & 1You can not use * in this specific case. Write down the list, it is a good
practice.
AMB
"AST" wrote:

> Hey,
> I am trying to construct an ORDER BY clause for a SELECT DISTINCT query th
at
> attempts to order the data based on a particular bit value stored in a
> column named [Properties], but I get this failure message:
> "ORDER BY items must appear in the select list if SELECT DISTINCT is
> specified"
> If I remove the "DISTINCT" keyword, then all is fine, but I do not
> understand how to properly set this type of query up. Any help is very mu
ch
> appreciated.
>
> The query I am using is:
> SELECT DISTINCT
> ScanChangedParams.*, ScanStrategies.StrategyName AS
> Expr1, ScanStrategies.TemplateID AS Expr2, ScanStrategies.ProjectID AS
> Expr3,
> ScanStrategies.RootTemplateID AS Expr4,
> ScanStrategies.ScanError AS Expr5, TPSScanTemplates.ParamName AS Expr6
> FROM ScanChangedParams WITH (NOLOCK) INNER JOIN
> ScanStrategies WITH (NOLOCK) ON
> ScanStrategies.ScanTaskID = ScanChangedParams.ScanTaskID AND
> ScanStrategies.StrategyID =
> ScanChangedParams.StrategyID INNER JOIN
> TPSScanTemplates WITH (NOLOCK) ON
> ScanChangedParams.ParamID = TPSScanTemplates.CommonParamID
> WHERE (ScanChangedParams.ScanTaskID = 68)
> ORDER BY ScanChangedParams.Properties & 1
>
>|||Hey AMB,
I tried this and it still does not work. If I simply remove the DISTINCT
keyword, then all is fine.
Best regards,
Bill
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:694E712B-78EB-44ED-8C4F-828649A73A6F@.microsoft.com...
> You can not use * in this specific case. Write down the list, it is a good
> practice.
>
> AMB
> "AST" wrote:
>
that
much
AS|||Include "ScanChangedParams.Properties & 1" in the select list.
AMB
"AST" wrote:

> Hey AMB,
> I tried this and it still does not work. If I simply remove the DISTINCT
> keyword, then all is fine.
> Best regards,
> Bill
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:694E712B-78EB-44ED-8C4F-828649A73A6F@.microsoft.com...
> that
> much
> AS
>
>|||Hey AMB,
Thanks for the assistance!
Yes, this did result in the query executing, but it filters the rowset to
only those that satisfy the bit operation (Properties & 1). What I want to
do is sort or order by this bit value only as this particular bit pattern is
used to display textual data representation on a client window.
Best regards,
Bill
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:658B4D3C-28A5-4519-BC06-FD1A185E9AE0@.microsoft.com...
> Include "ScanChangedParams.Properties & 1" in the select list.
>
> AMB
> "AST" wrote:
>
DISTINCT
message
good
query
a
very
ScanStrategies.StrategyName
AS
Expr6|||I think you have to be more specific or post DDL, sample data and expected
result.
AMB
"AST" wrote:

> Hey AMB,
> Thanks for the assistance!
> Yes, this did result in the query executing, but it filters the rowset to
> only those that satisfy the bit operation (Properties & 1). What I want t
o
> do is sort or order by this bit value only as this particular bit pattern
is
> used to display textual data representation on a client window.
> Best regards,
> Bill
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:658B4D3C-28A5-4519-BC06-FD1A185E9AE0@.microsoft.com...
> DISTINCT
> message
> good
> query
> a
> very
> ScanStrategies.StrategyName
> AS
> Expr6
>
>|||<snip>
As AMD indicated, inclusion of the expression should not alter the result
set (beyond the additional column). However, it is possible that there is
something specific to your query that is not directly evident without
knowledge of the underlying DDL and data characteristics. Below is an
example that shows the expression <<shouldn not>> affect the result set.
The last query is another alternative.
set nocount on
create table #test (numval smallint, descr varchar(25))
go
insert #test (numval, descr)
select 1, 'good by'
union all
select 2, 'good by'
union all
select 3, 'goodby'
union all
select 2, 'good by'
union all
select 4, 'good by'
union all
select null, 'good by'
go
select numval, descr from #test
select distinct numval, descr from #test
select distinct numval, descr, numval & 1 as bogus from #test
order by bogus
select distinct numval, descr, numval & 1 as bogus from #test
order by numval & 1
select numval, descr from
( select distinct numval, descr from #test ) as t1
order by numval & 1|||Hey Alejandro,
After further testing, I was able to get this query to work as you described
by adding the same column again in the select query.
Thanks again!
Best regards,
Bill
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:9D8FF8AD-3060-4C4B-8224-6A479DFA05A1@.microsoft.com...
> I think you have to be more specific or post DDL, sample data and expected
> result.
>
> AMB
> "AST" wrote:
>
to
to
pattern is
message
is a
DISTINCT
stored in
DISTINCT is
not
is
ScanStrategies.ProjectID|||Hey Scott,
Thanks for the feeback and examples. Based on AMD's and your comments and a
little more testing I have now worked this out.
Thanks again!
Best regards,
Bill
"Scott Morris" <bogus@.bogus.com> wrote in message
news:eU%23lFfJCFHA.328@.tk2msftngp13.phx.gbl...
> <snip>
> As AMD indicated, inclusion of the expression should not alter the result
> set (beyond the additional column). However, it is possible that there is
> something specific to your query that is not directly evident without
> knowledge of the underlying DDL and data characteristics. Below is an
> example that shows the expression <<shouldn not>> affect the result set.
> The last query is another alternative.
> set nocount on
> create table #test (numval smallint, descr varchar(25))
> go
> insert #test (numval, descr)
> select 1, 'good by'
> union all
> select 2, 'good by'
> union all
> select 3, 'goodby'
> union all
> select 2, 'good by'
> union all
> select 4, 'good by'
> union all
> select null, 'good by'
> go
> select numval, descr from #test
> select distinct numval, descr from #test
> select distinct numval, descr, numval & 1 as bogus from #test
> order by bogus
> select distinct numval, descr, numval & 1 as bogus from #test
> order by numval & 1
> select numval, descr from
> ( select distinct numval, descr from #test ) as t1
> order by numval & 1
>
>

Order by Clause : No difference in SQL Query Plan.

We are in the process of fine tuning a few stored procedures.
In one situation - the order by clause increased the duration of an SP.
This is expected behaviour.
We removed the order by clause and got a marginal gain.
Examined the query plan of both the statements (with orderBy and without
OrderBy).
There was no difference.
Why?
The order by clause causes the query optimizer to do quite a few more
additional steps (internally) before returning the final result set.
Should'nt this displayed in the graphical query plan?
Cheers!
SQLCatz
The answer of a midpaid consultant would be: "It depends" ;-)
It depends on your query you are going to run to order, if you order a
indexbased column and it is clustered it is already ordered. Perhaps you can
post your DDL or/and query here.
HTH, Jens Smeyer.
http://www.sqlserver2005.de
"SQLCatz" <SQLCatz@.discussions.microsoft.com> schrieb im Newsbeitrag
news:5EEF62B3-568C-4031-B1C2-C0A6DAE1D658@.microsoft.com...
> We are in the process of fine tuning a few stored procedures.
> In one situation - the order by clause increased the duration of an SP.
> This is expected behaviour.
> We removed the order by clause and got a marginal gain.
> Examined the query plan of both the statements (with orderBy and without
> OrderBy).
> There was no difference.
> Why?
> The order by clause causes the query optimizer to do quite a few more
> additional steps (internally) before returning the final result set.
> Should'nt this displayed in the graphical query plan?
> Cheers!
> SQLCatz
>
|||Hi SQLCatz
How do you know that SQL Server is doing quite a few more additional steps
if you don't see it in the query plan? It may be taking advantage of an
ordering that is already available and not having to do anything extra.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:5EEF62B3-568C-4031-B1C2-C0A6DAE1D658@.microsoft.com...
> We are in the process of fine tuning a few stored procedures.
> In one situation - the order by clause increased the duration of an SP.
> This is expected behaviour.
> We removed the order by clause and got a marginal gain.
> Examined the query plan of both the statements (with orderBy and without
> OrderBy).
> There was no difference.
> Why?
> The order by clause causes the query optimizer to do quite a few more
> additional steps (internally) before returning the final result set.
> Should'nt this displayed in the graphical query plan?
> Cheers!
> SQLCatz
>
sql

Order by Clause : No difference in SQL Query Plan.

We are in the process of fine tuning a few stored procedures.
In one situation - the order by clause increased the duration of an SP.
This is expected behaviour.
We removed the order by clause and got a marginal gain.
Examined the query plan of both the statements (with orderBy and without
OrderBy).
There was no difference.
Why?
The order by clause causes the query optimizer to do quite a few more
additional steps (internally) before returning the final result set.
Should'nt this displayed in the graphical query plan?
Cheers!
SQLCatzThe answer of a midpaid consultant would be: "It depends" ;-)
It depends on your query you are going to run to order, if you order a
indexbased column and it is clustered it is already ordered. Perhaps you can
post your DDL or/and query here.
HTH, Jens Süßmeyer.
--
http://www.sqlserver2005.de
--
"SQLCatz" <SQLCatz@.discussions.microsoft.com> schrieb im Newsbeitrag
news:5EEF62B3-568C-4031-B1C2-C0A6DAE1D658@.microsoft.com...
> We are in the process of fine tuning a few stored procedures.
> In one situation - the order by clause increased the duration of an SP.
> This is expected behaviour.
> We removed the order by clause and got a marginal gain.
> Examined the query plan of both the statements (with orderBy and without
> OrderBy).
> There was no difference.
> Why?
> The order by clause causes the query optimizer to do quite a few more
> additional steps (internally) before returning the final result set.
> Should'nt this displayed in the graphical query plan?
> Cheers!
> SQLCatz
>|||Hi SQLCatz
How do you know that SQL Server is doing quite a few more additional steps
if you don't see it in the query plan? It may be taking advantage of an
ordering that is already available and not having to do anything extra.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:5EEF62B3-568C-4031-B1C2-C0A6DAE1D658@.microsoft.com...
> We are in the process of fine tuning a few stored procedures.
> In one situation - the order by clause increased the duration of an SP.
> This is expected behaviour.
> We removed the order by clause and got a marginal gain.
> Examined the query plan of both the statements (with orderBy and without
> OrderBy).
> There was no difference.
> Why?
> The order by clause causes the query optimizer to do quite a few more
> additional steps (internally) before returning the final result set.
> Should'nt this displayed in the graphical query plan?
> Cheers!
> SQLCatz
>

Order by Clause : No difference in SQL Query Plan.

We are in the process of fine tuning a few stored procedures.
In one situation - the order by clause increased the duration of an SP.
This is expected behaviour.
We removed the order by clause and got a marginal gain.
Examined the query plan of both the statements (with orderBy and without
OrderBy).
There was no difference.
Why?
The order by clause causes the query optimizer to do quite a few more
additional steps (internally) before returning the final result set.
Should'nt this displayed in the graphical query plan?
Cheers!
SQLCatzThe answer of a midpaid consultant would be: "It depends" ;-)
It depends on your query you are going to run to order, if you order a
indexbased column and it is clustered it is already ordered. Perhaps you can
post your DDL or/and query here.
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"SQLCatz" <SQLCatz@.discussions.microsoft.com> schrieb im Newsbeitrag
news:5EEF62B3-568C-4031-B1C2-C0A6DAE1D658@.microsoft.com...
> We are in the process of fine tuning a few stored procedures.
> In one situation - the order by clause increased the duration of an SP.
> This is expected behaviour.
> We removed the order by clause and got a marginal gain.
> Examined the query plan of both the statements (with orderBy and without
> OrderBy).
> There was no difference.
> Why?
> The order by clause causes the query optimizer to do quite a few more
> additional steps (internally) before returning the final result set.
> Should'nt this displayed in the graphical query plan?
> Cheers!
> SQLCatz
>|||Hi SQLCatz
How do you know that SQL Server is doing quite a few more additional steps
if you don't see it in the query plan? It may be taking advantage of an
ordering that is already available and not having to do anything extra.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:5EEF62B3-568C-4031-B1C2-C0A6DAE1D658@.microsoft.com...
> We are in the process of fine tuning a few stored procedures.
> In one situation - the order by clause increased the duration of an SP.
> This is expected behaviour.
> We removed the order by clause and got a marginal gain.
> Examined the query plan of both the statements (with orderBy and without
> OrderBy).
> There was no difference.
> Why?
> The order by clause causes the query optimizer to do quite a few more
> additional steps (internally) before returning the final result set.
> Should'nt this displayed in the graphical query plan?
> Cheers!
> SQLCatz
>

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 CASe

I am creating a stored procedure to return a sorted list
Currently like this
DECLARE @.SORT tinyint
SET @.SORT = 1 --could be anything passed in
SELECT * FROM Table
ORDER BY
CASE
WHEN 1 THEN Col1
WHEN 2 THEN Col2
...etc
Now i would like to add 'ASC' or 'DESC' like follows
CASE
WHEN 1 THEN Col1 ASC
WHEN 2 THEN Col1 DESC
...etc
But this return an error 'Incorrect syntax near the keyword 'ASC'.'
IS there a way to make this work?
Yes, but the syntax is a bit weird:
create table #a(b char(1), c char(1))
insert #a values ('a', 'z')
insert #a values ('b', 'y')
insert #a values ('c', 'x')
declare @.orderby int
set @.orderby = 1
SELECT *
FROM #a
ORDER BY
CASE WHEN @.Orderby = 1 THEN b end asc,
CASE WHEN @.Orderby = 2 THEN b end desc,
CASE WHEN @.Orderby = 3 THEN c end asc,
CASE WHEN @.Orderby = 4 THEN c end desc
"GrantMagic" <grant@.magicalia.com> wrote in message
news:O0YlBadkEHA.3356@.TK2MSFTNGP15.phx.gbl...
> I am creating a stored procedure to return a sorted list
> Currently like this
> DECLARE @.SORT tinyint
> SET @.SORT = 1 --could be anything passed in
> SELECT * FROM Table
> ORDER BY
> CASE
> WHEN 1 THEN Col1
> WHEN 2 THEN Col2
> ...etc
> Now i would like to add 'ASC' or 'DESC' like follows
> CASE
> WHEN 1 THEN Col1 ASC
> WHEN 2 THEN Col1 DESC
> ...etc
> But this return an error 'Incorrect syntax near the keyword 'ASC'.'
> IS there a way to make this work?
>
|||Brilliant, that did the job perfectly!!
Thank You!
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OJZDGddkEHA.3648@.TK2MSFTNGP09.phx.gbl...
> Yes, but the syntax is a bit weird:
>
> create table #a(b char(1), c char(1))
> insert #a values ('a', 'z')
> insert #a values ('b', 'y')
> insert #a values ('c', 'x')
> declare @.orderby int
> set @.orderby = 1
> SELECT *
> FROM #a
> ORDER BY
> CASE WHEN @.Orderby = 1 THEN b end asc,
> CASE WHEN @.Orderby = 2 THEN b end desc,
> CASE WHEN @.Orderby = 3 THEN c end asc,
> CASE WHEN @.Orderby = 4 THEN c end desc
>
> "GrantMagic" <grant@.magicalia.com> wrote in message
> news:O0YlBadkEHA.3356@.TK2MSFTNGP15.phx.gbl...
>

ORDER BY CASe

I am creating a stored procedure to return a sorted list
Currently like this
DECLARE @.SORT tinyint
SET @.SORT = 1 --could be anything passed in
SELECT * FROM Table
ORDER BY
CASE
WHEN 1 THEN Col1
WHEN 2 THEN Col2
...etc
Now i would like to add 'ASC' or 'DESC' like follows
CASE
WHEN 1 THEN Col1 ASC
WHEN 2 THEN Col1 DESC
...etc
But this return an error 'Incorrect syntax near the keyword 'ASC'.'
IS there a way to make this work?Yes, but the syntax is a bit weird:
create table #a(b char(1), c char(1))
insert #a values ('a', 'z')
insert #a values ('b', 'y')
insert #a values ('c', 'x')
declare @.orderby int
set @.orderby = 1
SELECT *
FROM #a
ORDER BY
CASE WHEN @.Orderby = 1 THEN b end asc,
CASE WHEN @.Orderby = 2 THEN b end desc,
CASE WHEN @.Orderby = 3 THEN c end asc,
CASE WHEN @.Orderby = 4 THEN c end desc
"GrantMagic" <grant@.magicalia.com> wrote in message
news:O0YlBadkEHA.3356@.TK2MSFTNGP15.phx.gbl...
> I am creating a stored procedure to return a sorted list
> Currently like this
> DECLARE @.SORT tinyint
> SET @.SORT = 1 --could be anything passed in
> SELECT * FROM Table
> ORDER BY
> CASE
> WHEN 1 THEN Col1
> WHEN 2 THEN Col2
> ...etc
> Now i would like to add 'ASC' or 'DESC' like follows
> CASE
> WHEN 1 THEN Col1 ASC
> WHEN 2 THEN Col1 DESC
> ...etc
> But this return an error 'Incorrect syntax near the keyword 'ASC'.'
> IS there a way to make this work?
>|||Brilliant, that did the job perfectly!!
Thank You!
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OJZDGddkEHA.3648@.TK2MSFTNGP09.phx.gbl...
> Yes, but the syntax is a bit weird:
>
> create table #a(b char(1), c char(1))
> insert #a values ('a', 'z')
> insert #a values ('b', 'y')
> insert #a values ('c', 'x')
> declare @.orderby int
> set @.orderby = 1
> SELECT *
> FROM #a
> ORDER BY
> CASE WHEN @.Orderby = 1 THEN b end asc,
> CASE WHEN @.Orderby = 2 THEN b end desc,
> CASE WHEN @.Orderby = 3 THEN c end asc,
> CASE WHEN @.Orderby = 4 THEN c end desc
>
> "GrantMagic" <grant@.magicalia.com> wrote in message
> news:O0YlBadkEHA.3356@.TK2MSFTNGP15.phx.gbl...
> > I am creating a stored procedure to return a sorted list
> >
> > Currently like this
> >
> > DECLARE @.SORT tinyint
> > SET @.SORT = 1 --could be anything passed in
> >
> > SELECT * FROM Table
> > ORDER BY
> >
> > CASE
> > WHEN 1 THEN Col1
> > WHEN 2 THEN Col2
> > ...etc
> >
> > Now i would like to add 'ASC' or 'DESC' like follows
> >
> > CASE
> > WHEN 1 THEN Col1 ASC
> > WHEN 2 THEN Col1 DESC
> > ...etc
> >
> > But this return an error 'Incorrect syntax near the keyword 'ASC'.'
> >
> > IS there a way to make this work?
> >
> >
>

Oracle, SQL+ and calling a stored proc in a package

I have this code:

declare
return_status number;
return_msg varchar2(255);
variable retval number;
execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
/

I get SP2-0552: Bind variable "RETVAL" not declared.

I have searched Google and the Oracle docs and I cannot find ONE single decent example of calling a stored procedure that's in a package and where that stored procedure has OUT parameters.

HELP!!?!?!

n/m

this worked:

declare
return_status number;
return_msg varchar2(255);
begin
update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
end;
/Originally posted by nightowlky
I have this code:

declare
return_status number;
return_msg varchar2(255);
variable retval number;
execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
/

I get SP2-0552: Bind variable "RETVAL" not declared.

I have searched Google and the Oracle docs and I cannot find ONE single decent example of calling a stored procedure that's in a package and where that stored procedure has OUT parameters.

HELP!!?!?!

n/m

this worked:

declare
return_status number;
return_msg varchar2(255);
begin
update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
end;
/
The problem is that you are confusing SQL Plus syntax with PL/SQL syntax. VARIABLE and EXECUTE are SQL Plus commands, so your example should look like one of these:

1) All PL/SQL variables:

declare
return_status number;
return_msg varchar2(255);
retval number; -- no VARIABLE keyword
begin
retval := -- No EXECUTE and no ":"
update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
end;
/

2) SQL Plus variables:

SQL> variable retval number;
SQL> variable return_status number;
SQL> variable return_msg varchar2(255);
SQL> execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg);

3) Combination of SQL Plus and PL/SQL variables:

SQL> variable retval number;

declare
return_status number;
return_msg varchar2(255);
begin
:retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
end;
/

The difference is that SQL Plus variables are available to SQL Plus after the PL/SQL block has completed execution, i.e. in examples 2 and 3 you can go on to use :retval again.|||Thanks, Andrew.

But, using all 'variable' statements, I get this:

Wrote file afiedt.buf

1 variable retval number;
2 variable return_status number;
3 variable return_msg varchar2(255);
4* execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg);
SQL> run
1 variable retval number;
2 variable return_status number;
3 variable return_msg varchar2(255);
4* execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg);
SP2-0552: Bind variable "RETURN_MSG" not declared.

All I'm really trying to do is be able to print out the values of return_status and return_msg.

I'm all new to Oracle, SQL+, PL/SQL. I come from a long history of MS SQL Server. Speaking of which, are there tools similar to the MS tools for writing queries, importing data, etc.? Seems all I have installed on this workstation is SQL+ and WinSQL (which chokes on syntax that works in SQL+ ??)|||Originally posted by nightowlky
Thanks, Andrew.

But, using all 'variable' statements, I get this:

Wrote file afiedt.buf

1 variable retval number;
2 variable return_status number;
3 variable return_msg varchar2(255);
4* execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg);
SQL> run
1 variable retval number;
2 variable return_status number;
3 variable return_msg varchar2(255);
4* execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg);
SP2-0552: Bind variable "RETURN_MSG" not declared.

All I'm really trying to do is be able to print out the values of return_status and return_msg.

I'm all new to Oracle, SQL+, PL/SQL. I come from a long history of MS SQL Server. Speaking of which, are there tools similar to the MS tools for writing queries, importing data, etc.? Seems all I have installed on this workstation is SQL+ and WinSQL (which chokes on syntax that works in SQL+ ??)
The problem is that the RUN command treats contents of the buffer as a single command, which this is not. Instead of using RUN, try this:

SQL> @.afiedt.buf

That correctly processes 4 "statements" (3 VARIABLE commands and one EXECUTE command).|||ah...

well...

it gets better:

SQL> @.afiedt.buf

PL/SQL procedure successfully completed.

variable return_status number;
*
ERROR at line 1:
ORA-00900: invalid SQL statement

DOH!|||Originally posted by nightowlky
ah...

well...

it gets better:

SQL> @.afiedt.buf

PL/SQL procedure successfully completed.

variable return_status number;
*
ERROR at line 1:
ORA-00900: invalid SQL statement

DOH!
What does afiedt.buf contain now?|||VARIABLE return_status NUMBER
VARIABLE return_msg VARCHAR2(255)
EXECUTE update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg)
/

I don't believe the whole procedure returns a value (other than the out parameters) so I removed the retval variable.|||Originally posted by nightowlky
VARIABLE return_status NUMBER
VARIABLE return_msg VARCHAR2(255)
EXECUTE update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg)
/

I don't believe the whole procedure returns a value (other than the out parameters) so I removed the retval variable.
You don't need or want that "/" on the last line. Remove that and the error should go away!|||Ah!

The sweet smell of success!

Thank you, Andrew!

Thank you!

:D|||Why dont you try using dbms_output.put_line function to display the output ? The steps are as follows :

-- test1.sql
declare
l_ret_val number;
begin
l_ret_val := call_function();
dbms_output.put_line(l_ret_val);
end;
/

Now at sql prompt execute the following commands :
sql> set serveroutput on
sql> @.test1.sql

Hope this helps ...|||I'll try that, too.

As I said...I'm quite the Oracle newbie and I'm trying to just find stuff that works. And finding examples of some of this stuff is like trying to put your elbow in your ear (can almost get there but not quite) :)|||Originally posted by nightowlky
I'll try that, too.

As I said...I'm quite the Oracle newbie and I'm trying to just find stuff that works. And finding examples of some of this stuff is like trying to put your elbow in your ear (can almost get there but not quite) :)
When using VARIABLE in SQL Plus, you will find the PRINT command handy:

SQL> variable a number
SQL> exec :a := 123

PL/SQL procedure successfully completed.

SQL> print a

A
----
123

Oracle to SQLServer 2000 migration

Hi,

I need to migrate an Oracle database to SQLServer 2000 (tables, views, stored procedures, triggers, ...).

Can someone give me some good advice on how to do it properly?

I would appreciate for example a small list of steps to perform for doing this migration.

Thanks,
Isabel Sousa.try this

http://www.microsoft.com/sql/techinfo/deployment/2000/MigrateOracle.asp

You would probably want to take the opportunity to redesign though.

Oracle Stored Procedures VERSUS SQL Server Stored Procedures

I want to know the differences between SQL Server 2000 stored
procedures and oracle stored procedures? Do they have different
syntax? The concept should be the same that the stored procedures
execute in the database server with better performance?

Please advise good references for Oracle stored procedures also.

thanks!!<jrefactors@.hotmail.com> wrote in message
news:1120793932.351921.5760@.g43g2000cwa.googlegrou ps.com...
> I want to know the differences between SQL Server 2000 stored
> procedures and oracle stored procedures? Do they have different
> syntax? The concept should be the same that the stored procedures
> execute in the database server with better performance?
> Please advise good references for Oracle stored procedures also.
> thanks!!
SQL Server Stored procedures use transact sql and run on SQL Server
Oracle Stored Procedures use pl/sql (modeled on ADA) and run on Oracle.

Oracle!=SQL Server

They use two very different concurrent models and the syntax is totally
different.
(eg you don't need to create temp tables in Oracle like in SQL Server)
otn.oracle.com and look under documentation.
Jim|||On Thu, 07 Jul 2005 20:38:52 -0700, jrefactors wrote:

> I want to know the differences between SQL Server 2000 stored
> procedures and oracle stored procedures? Do they have different
> syntax?

No, you just have to obtain Microsoft version of PL/SQL.

--
http://www.mgogala.com|||On Thu, 07 Jul 2005 20:38:52 -0700, jrefactors interested us by writing:

> I want to know the differences between SQL Server 2000 stored
> procedures and oracle stored procedures? Do they have different
> syntax?

They are completely different languages.

Oracle PL/SQL uses the robust Ada language as it's template.

Not sure what Sybase used as it's template - seems like a mix of Pascal
and Basic. Very reasonable in it's own way as well. IIRC, Microsoft has
not extended the basic too much when they turned the core into SQL Server
- lots of nice add-ons though.

More to the point, because of the differences in database internals, such
as locking strategies, the way you need to approach your code is
significantly different. For example, in SQL Server it's reasonable to
create temp tables on the fly to hold intermediate results, whereas in
ORacle you use a predefined global temp table. In Oracle, readers don't
block writers and writers don't block readers so far less explicit locking
occurs in Oracle in general. And in Oracle, the fastest way to kill your
scalability is to build your SQL statement on the fly for each client -
bind variables are very, very useful.

> Please advise good references for Oracle stored procedures also.

All Oracle docco is online, free of charge, at http://docs.oracle.com ...
recommended reading for an app developer is (in order):

Oracle Concepts manual
Oracle Application Developer's Guide - Fundamentals
Oracle SQL Reference
Oracle PL/SQL User's Guide and Reference
Oracle Supplied PL/SQL Packages and Types Reference

Note that Oracle supplies a whole pile of added things in the database,
most at no added charge. Some examples in this category include:
message queueing; text indexing and search; doc indexing and search;
multimedia storage and retrieval; workflow; OLAP; XML. (Yes many also
occur in SQL Server.)

To avoid duplicating what is provided, I recommend selecting from the
remaining manuals and guides for API and examples.

The following books are, IMO, mandatory in your library:

O'Reilly "Mastering Oracle SQL"
- there's a lot that doesn't need PL/SQL
APress "Mastering Oracle PL/SQL"
- bible!
Oracle Press "Effective Oracle by Design"
- a MUST read if you care about performance
- goes a LONG way to explaining the differences in code style

Have fun
--
Hans Forbrich
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com
*** I no longer assist with top-posted newsgroup queries ***|||HansF wrote:
> Note that Oracle supplies a whole pile of added things in the database,
> most at no added charge. Some examples in this category include:
> message queueing; text indexing and search; doc indexing and search;
> multimedia storage and retrieval; workflow; OLAP; XML. (Yes many also
> occur in SQL Server.)

Careful withthe diffs between Standard Oracle and
EE or Personal: OLAP is not available on Standard
in 9i. Dunno about 10g but I suspect it's the same.|||jrefactors@.hotmail.com wrote:
> I want to know the differences between SQL Server 2000 stored
> procedures and oracle stored procedures? Do they have different
> syntax? The concept should be the same that the stored procedures
> execute in the database server with better performance?

Concept is the same (performance wise). Everything else is different.
PL/SQL is formal programming language like Java or C/C++ or Delphi.

It has OO support - so you can create classes and subclasses. You have
various APIs. You can talk to TCP servers (telnet, ftp), LDAP servers,
SMTP servers, HTTP servers. In other words, in PL/SQL you have a web
browser, an e-mail client, a TCP client, a LDAP client. Then there are
APIs for flat file I/O, encryption and decryption, IPC (Inter Process
Communication) via database pipes and message queues, data capture
streams, and many more.

PL/SQL is nothing like Transact-SQL. Transact-SQL is a macro language
at best. Which has its uses. But PL/SQL is as much a programming
language as any other formal development language today.

Case in point. The Oracle Application product suite consists of over a
million lines of PL/SQL. Oracle's replication is written in PL/SQL (and
not C).

I have a PL/SQL procedure that maps incoming router traffic to the
source and then inserts it into a table. This procedure is called by a
process that gets the network traffic data from various Cisco routers.
The stored proc is executed in excess of 300 times per second. Its SQL
statements (in the Oracle shared pool) typically shows (indvidually)
1.5 billion (and more) executions over time.

I also use PL/SQL as the application tier as oppose to Java/J2EE -
Oracle is both my app server and database server. This not only scales
better, but is faster to develop, easier to maintain, has less moving
parts and a lot cheaper than the traditional Java app server setup.

PL/SQL is the fastest and most optimal way to deal with data in Oracle.
Period.

> Please advise good references for Oracle stored procedures also.

PL/SQL Reference Guide. Application Developer Fundementals Guide.
Application Developer Object Relational Features Guide. PL/SQL Packages
and Types Guide.

The best is however to get your feet wet and start writing PL/SQL code
and getting to grips with the features and fundementals of PL/SQL and
Oracle.

--
Billy|||jrefactors@.hotmail.com wrote:
> I want to know the differences between SQL Server 2000 stored
> procedures and oracle stored procedures? Do they have different
> syntax? The concept should be the same that the stored procedures
> execute in the database server with better performance?
> Please advise good references for Oracle stored procedures also.
> thanks!!

A far simpler question might be "Is there anything similar between
SQL Server 2000 and Oracle stored procedures. The differences are
at the very core of what constitutes a transaction, the locking
model, the object types available, the concurrency model, and
capabilities within Oracle such as Flashback for which no similar
capability exists in SQL Server ... not 2000 and not Yukon.

My recommendation would be that you purchase Tom Kyte's book "Expert
one-on-one Oracle" and read, with special care, the first three
chapters. Especially the discussion of multiversion read consistency.

If you wish to explore further you can download Oracle for free from
http://otn.oracle.com, purchase the CD Packs for about $70 USD from
http://store.oracle.com, and should visit http://tahiti.oracle.com
for reference materials.

HTH
--
Daniel A. Morgan
http://www.psoug.org
damorgan@.x.washington.edu
(replace x with u to respond)|||On Thu, 07 Jul 2005 22:15:23 -0700, Noons interested us by writing:

> HansF wrote:
>> Note that Oracle supplies a whole pile of added things in the database,
>> most at no added charge. Some examples in this category include:
>> message queueing; text indexing and search; doc indexing and search;
>> multimedia storage and retrieval; workflow; OLAP; XML. (Yes many also
>> occur in SQL Server.)
> Careful withthe diffs between Standard Oracle and
> EE or Personal: OLAP is not available on Standard
> in 9i. Dunno about 10g but I suspect it's the same.

You are right that the OLAP option is only available on Enterprise Edition.

However, my reading of the OLAP manuals indicates that a number of the
analytic functions, such as lead/lag/rollup in SQL statements, are not
explicitly listed as part of the OLAP option and *may* therefore be
available in Standard. (Confirmation from Oracle appreciated!)

On re-reading, I see a potential confusion - the 'most in this category'
was meant to refer to the 'whole pile of things', not 'most at no added
charge'. Thanks for pointing that out.

--
Hans Forbrich
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com
*** I no longer assist with top-posted newsgroup queries ***|||You don't say if your background is in Oracle or MSSQL, but if it's
Oracle, then these links might be useful:

http://www.microsoft.com/sql/evalua...are/oracle.mspx
http://www.microsoft.com/resources/...art2/c0761.mspx

As other posters have said, the two products have a large number of
very significant differences, so it's often difficult to make very
direct comparisons.

Simon|||Simon Hayes wrote:
> You don't say if your background is in Oracle or MSSQL, but if it's
> Oracle, then these links might be useful:
> http://www.microsoft.com/sql/evalua...are/oracle.mspx
> http://www.microsoft.com/resources/...art2/c0761.mspx
> As other posters have said, the two products have a large number of
> very significant differences, so it's often difficult to make very
> direct comparisons.
> Simon

Just a quick note. The second link appears to refer to Oracle 8i which
has not been sold by Oracle for more than 4 years.

For example this statement:
"Blob type storage - One long or long raw per table, must be at end of
row, data stored on same block(s) with row."

LONG and LONG RAW data types have been deprecated. The BLOB data type,
as early as 9i held 4GB, had no restriction as to the number per table,
did not need to store the information in the same block with the row,
and did they need to be at any specific location in a table: A statement
which in and of itself is wrong as can be clearly seen:

1 create table ms_is_wrong (
2 col1 NUMBER(5),
3 longcol LONG,
4 col3 NUMBER(5),
5* col4 DATE)
SQL> /

Table created.

SQL> desc ms_is_wrong
Name Null? Type
------------- --- ---
COL1 NUMBER(5)
LONGCOL LONG
COL3 NUMBER(5)
COL4 DATE

Clearly the LONG does not need to be the last column. And clearly
Microsoft has done its user community no favor with this document
as there are a large number of similarly egregious errors.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@.x.washington.edu
(replace x with u to respond)|||"DA Morgan" <damorgan@.psoug.org> wrote in message
news:1120837514.778684@.yasure...
> Simon Hayes wrote:
>> You don't say if your background is in Oracle or MSSQL, but if it's
>> Oracle, then these links might be useful:
>>
>> http://www.microsoft.com/sql/evalua...are/oracle.mspx
>> http://www.microsoft.com/resources/...art2/c0761.mspx
>>
>> As other posters have said, the two products have a large number of
>> very significant differences, so it's often difficult to make very
>> direct comparisons.
>>
>> Simon
> Just a quick note. The second link appears to refer to Oracle 8i which
> has not been sold by Oracle for more than 4 years.
> For example this statement:
> "Blob type storage - One long or long raw per table, must be at end of
> row, data stored on same block(s) with row."
> LONG and LONG RAW data types have been deprecated. The BLOB data type,
> as early as 9i held 4GB, had no restriction as to the number per table,
> did not need to store the information in the same block with the row, and
> did they need to be at any specific location in a table: A statement
> which in and of itself is wrong as can be clearly seen:
> 1 create table ms_is_wrong (
> 2 col1 NUMBER(5),
> 3 longcol LONG,
> 4 col3 NUMBER(5),
> 5* col4 DATE)
> SQL> /
> Table created.
> SQL> desc ms_is_wrong
> Name Null? Type
> ------------- --- ---
> COL1 NUMBER(5)
> LONGCOL LONG
> COL3 NUMBER(5)
> COL4 DATE
> Clearly the LONG does not need to be the last column. And clearly
> Microsoft has done its user community no favor with this document
> as there are a large number of similarly egregious errors.
> --
> Daniel A. Morgan
> http://www.psoug.org
> damorgan@.x.washington.edu
> (replace x with u to respond)

Thanks for the information - I had another look at the MS docs for Oracle
migration, and it appears that a more useful (and more extensive) document
would be this one, which does indeed say that BLOB has now replaced LONG
(see chapter 6, table 6.5):

http://www.microsoft.com/technet/it...ql/default.mspx

Simon|||Simon Hayes wrote:
> "DA Morgan" <damorgan@.psoug.org> wrote in message
> news:1120837514.778684@.yasure...
>>Simon Hayes wrote:
>>
>>>You don't say if your background is in Oracle or MSSQL, but if it's
>>>Oracle, then these links might be useful:
>>>
>>>http://www.microsoft.com/sql/evalua...are/oracle.mspx
>>>http://www.microsoft.com/resources/...art2/c0761.mspx
>>>
>>>As other posters have said, the two products have a large number of
>>>very significant differences, so it's often difficult to make very
>>>direct comparisons.
>>>
>>>Simon
>>
>>Just a quick note. The second link appears to refer to Oracle 8i which
>>has not been sold by Oracle for more than 4 years.
>>
>>For example this statement:
>>"Blob type storage - One long or long raw per table, must be at end of
>>row, data stored on same block(s) with row."
>>
>>LONG and LONG RAW data types have been deprecated. The BLOB data type,
>>as early as 9i held 4GB, had no restriction as to the number per table,
>>did not need to store the information in the same block with the row, and
>>did they need to be at any specific location in a table: A statement
>>which in and of itself is wrong as can be clearly seen:
>>
>> 1 create table ms_is_wrong (
>> 2 col1 NUMBER(5),
>> 3 longcol LONG,
>> 4 col3 NUMBER(5),
>> 5* col4 DATE)
>>SQL> /
>>
>>Table created.
>>
>>SQL> desc ms_is_wrong
>> Name Null? Type
>> ------------- --- ---
>> COL1 NUMBER(5)
>> LONGCOL LONG
>> COL3 NUMBER(5)
>> COL4 DATE
>>
>>Clearly the LONG does not need to be the last column. And clearly
>>Microsoft has done its user community no favor with this document
>>as there are a large number of similarly egregious errors.
>>--
>>Daniel A. Morgan
>>http://www.psoug.org
>>damorgan@.x.washington.edu
>>(replace x with u to respond)
>
> Thanks for the information - I had another look at the MS docs for Oracle
> migration, and it appears that a more useful (and more extensive) document
> would be this one, which does indeed say that BLOB has now replaced LONG
> (see chapter 6, table 6.5):
> http://www.microsoft.com/technet/it...ql/default.mspx
> Simon

Better but still a long way from best practice. For example:
"Step 3: Fetch Strategy
Cursors are effective for row processing and batch processing."

The discussion seems to focus on technologies that are shared by both
Oracle and SQL Server such as cursors whereas any good PL/SQL developer
would take one look at a CURSOR LOOP and cut it from the code. In Oracle
9i and 10g best practice is to use bulk collection and FORALL.

The goal of the document seems to be get it to compile as close to the
original as possible and nothing more which will lead to poor
performance and scalability: Perhaps Microsoft's goal.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@.x.washington.edu
(replace x with u to respond)

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