order by slows me down

sql2k sp2
Im aware that it normally slows everone down. But Ive got
a view, and when I select top 1 from it without an order
by it returns in 1 second. With order by takes over 4
minutes. Quite a jump. Any ideas?
TIA, ChrisChris,
The Top Engine simply fetches the 1st occurance, satisfying your where
criteria.
When you issue an Order BY Clause it have to construct a virutal table in
Tempdb and then sort and give you the 1st occurance.
To speed up I would look at the index plan and add/tweak the index
satisfying the Order By and Where.
HTH
Satish Balusa
Corillian Corp.
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:219f01c3e079$0670b460$a501280a@.phx.gbl...

quote:

---

> sql2k sp2
> Im aware that it normally slows everone down. But Ive got
> a view, and when I select top 1 from it without an order
> by it returns in 1 second. With order by takes over 4
> minutes. Quite a jump. Any ideas?
> TIA, Chris

---

order by slows me down

sql2k sp2
Im aware that it normally slows everone down. But Ive got
a view, and when I select top 1 from it without an order
by it returns in 1 second. With order by takes over 4
minutes. Quite a jump. Any ideas?
TIA, ChrisChris,
The Top Engine simply fetches the 1st occurance, satisfying your where
criteria.
When you issue an Order BY Clause it have to construct a virutal table in
Tempdb and then sort and give you the 1st occurance.
To speed up I would look at the index plan and add/tweak the index
satisfying the Order By and Where.
--
HTH
Satish Balusa
Corillian Corp.
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:219f01c3e079$0670b460$a501280a@.phx.gbl...
> sql2k sp2
> Im aware that it normally slows everone down. But Ive got
> a view, and when I select top 1 from it without an order
> by it returns in 1 second. With order by takes over 4
> minutes. Quite a jump. Any ideas?
> TIA, Chris

ORDER BY returns opposite sequence between SQL 2000 and 2005

I have the following query returning results in opposite order between
versions 2000 and 2005:-
SELECT * FROM tblname where form= 'L'
order by acolumn desc
The database has been migrated from 2000 to 2005, so the structure and data
should be identical. Here's the DDL for the table in SQL2000:-
USE [MSD_Contracts]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblname](
[Form] [varchar](1) NOT NULL,
[acolumn] [varchar](1) NOT NULL,
[FieldNo] [int] NOT NULL,
[dbField] [varchar](30) NOT NULL,
[Validation] [varchar](20) NOT NULL,
[AllowNulls] [varchar](1) NOT NULL,
CONSTRAINT [PK_tblname_1_12] PRIMARY KEY CLUSTERED
(
[FieldNo] ASC,
[Form] ASC,
[acolumn] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON
[PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Any ideas please?Hi Adrian
The order should be the case assuming that the collations are the same. Do
you have some sample data? http://www.aspfaq.com/etiquette.asp?id=5006
You should change the varchar(1) columns.
John
"Adrian" wrote:
> I have the following query returning results in opposite order between
> versions 2000 and 2005:-
> SELECT * FROM tblname where form= 'L'
> order by acolumn desc
> The database has been migrated from 2000 to 2005, so the structure and data
> should be identical. Here's the DDL for the table in SQL2000:-
> USE [MSD_Contracts]
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_PADDING ON
> GO
> CREATE TABLE [dbo].[tblname](
> [Form] [varchar](1) NOT NULL,
> [acolumn] [varchar](1) NOT NULL,
> [FieldNo] [int] NOT NULL,
> [dbField] [varchar](30) NOT NULL,
> [Validation] [varchar](20) NOT NULL,
> [AllowNulls] [varchar](1) NOT NULL,
> CONSTRAINT [PK_tblname_1_12] PRIMARY KEY CLUSTERED
> (
> [FieldNo] ASC,
> [Form] ASC,
> [acolumn] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY => OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON
> [PRIMARY]
> ) ON [PRIMARY]
> GO
> SET ANSI_PADDING OFF
> Any ideas please?|||Collation sequence is the same in both versions -
SQL_Latin1_General_CP1_CI_AS. Here is some sample data:_
Form FrontBack FieldNo dbField Validation
AllowNulls
-- -- -- --
-- --
L F 470 SignaturePresent AlphaNum
N
L F 210 DependentQ Bit
N
L F 200 DependentN Bit
N
L F 190 DependentY Bit
N
L F 180 MarriageCertQ Bit
N
L F 170 MarriageCertN Bit
N
L F 160 MarriageCertY Bit
N
L F 150 ConsentQ Bit
N
L F 140 ConsentN Bit
N
L F 130 ConsentY Bit
N
L F 120 Under18Years Bit
N
L F 110 CAltered Bit
N
L F 100 SignedAndDatedQ Bit
N
L F 90 SignedAndDatedN Bit
N
L F 80 SignedAndDatedY Bit
N
L F 70 NameAndDOBQ Bit
N
L F 60 NameAndDOBN Bit
N
L F 50 NameAndDOBY Bit
N
L F 40 Barcode Num
N
L F 30 FormType1 AlphaNum
N
L F 20 ImageRef1 AlphaNum
N
L F 10 BatchNo Num
N
L B 420 EvidenceSent Bit
N
L B 410 EvidenceToWinz Bit
N
L B 400 ExtraEvidence Bit
N
L B 390 Oaltered Bit
N
L B 380 BankQ Bit
N
L B 370 BankN Bit
N
L B 360 BankY Bit
N
L B 340 IRDQ Bit
N
L B 330 IRDN Bit
N
L B 320 IRDY Bit
N
L B 310 ResidentQ Bit
N
L B 300 ResidentN Bit
N
L B 290 ResidentY Bit
N
L B 280 CitizenQ Bit
N
L B 270 CitizenN Bit
N
L B 260 CitizenY Bit
N
L B 250 Barcode Num
N
L B 240 FormType2 AlphaNum
N
L B 230 ImageRef2 AlphaNum
N
L B 220 BatchNo Num
N
(42 row(s) affected)
"John Bell" wrote:
> Hi Adrian
> The order should be the case assuming that the collations are the same. Do
> you have some sample data? http://www.aspfaq.com/etiquette.asp?id=5006
> You should change the varchar(1) columns.
> John
> "Adrian" wrote:
> > I have the following query returning results in opposite order between
> > versions 2000 and 2005:-
> > SELECT * FROM tblname where form= 'L'
> > order by acolumn desc
> >
> > The database has been migrated from 2000 to 2005, so the structure and data
> > should be identical. Here's the DDL for the table in SQL2000:-
> > USE [MSD_Contracts]
> > GO
> > SET ANSI_NULLS ON
> > GO
> > SET QUOTED_IDENTIFIER ON
> > GO
> > SET ANSI_PADDING ON
> > GO
> > CREATE TABLE [dbo].[tblname](
> > [Form] [varchar](1) NOT NULL,
> > [acolumn] [varchar](1) NOT NULL,
> > [FieldNo] [int] NOT NULL,
> > [dbField] [varchar](30) NOT NULL,
> > [Validation] [varchar](20) NOT NULL,
> > [AllowNulls] [varchar](1) NOT NULL,
> > CONSTRAINT [PK_tblname_1_12] PRIMARY KEY CLUSTERED
> > (
> > [FieldNo] ASC,
> > [Form] ASC,
> > [acolumn] ASC
> > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY => > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON
> > [PRIMARY]
> > ) ON [PRIMARY]
> >
> > GO
> > SET ANSI_PADDING OFF
> >
> > Any ideas please?|||Hi Arian
You did not read the link I posted about sample data.
Here is some code that would have been useful:
USE TEMPDB
GO
CREATE TABLE [dbo].[tblname](
[Form] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FrontBack] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FieldNo] [int] NOT NULL ,
[dbField] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Validation] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AllowNulls] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_tblname_1_12] PRIMARY KEY CLUSTERED
(
[FieldNo] ASC,
[Form] ASC,
[FrontBack] ASC
)
)
INSERT INTO tblname (Form, FrontBack, FieldNo, dbField, Validation,
AllowNulls)
SELECT 'L','F',470,'SignaturePresent','AlphaNum','N'
UNION ALL SELECT 'L','F',210,'DependentQ','Bit','N'
UNION ALL SELECT 'L','F',200,'DependentN','Bit','N'
UNION ALL SELECT 'L','F',190,'DependentY','Bit','N'
UNION ALL SELECT 'L','F',180,'MarriageCertQ','Bit','N'
UNION ALL SELECT 'L','F',170,'MarriageCertN','Bit','N'
UNION ALL SELECT 'L','F',160,'MarriageCertY','Bit','N'
UNION ALL SELECT 'L','F',150,'ConsentQ','Bit','N'
UNION ALL SELECT 'L','F',140,'ConsentN','Bit','N'
UNION ALL SELECT 'L','F',130,'ConsentY','Bit','N'
UNION ALL SELECT 'L','F',120,'Under18Years','Bit','N'
UNION ALL SELECT 'L','F',110,'CAltered','Bit','N'
UNION ALL SELECT 'L','F',100,'SignedAndDatedQ','Bit','N'
UNION ALL SELECT 'L','F',90,'SignedAndDatedN','Bit','N'
UNION ALL SELECT 'L','F',80,'SignedAndDatedY','Bit','N'
UNION ALL SELECT 'L','F',70,'NameAndDOBQ','Bit','N'
UNION ALL SELECT 'L','F',60,'NameAndDOBN','Bit','N'
UNION ALL SELECT 'L','F',50,'NameAndDOBY','Bit','N'
UNION ALL SELECT 'L','F',40,'Barcode','Num','N'
UNION ALL SELECT 'L','F',30,'FormType1','AlphaNum','N'
UNION ALL SELECT 'L','F',20,'ImageRef1','AlphaNum','N'
UNION ALL SELECT 'L','F',10,'BatchNo','Num','N'
UNION ALL SELECT 'L','B',420,'EvidenceSent','Bit','N'
UNION ALL SELECT 'L','B',410,'EvidenceToWinz','Bit','N'
UNION ALL SELECT 'L','B',400,'ExtraEvidence','Bit','N'
UNION ALL SELECT 'L','B',390,'Oaltered','Bit','N'
UNION ALL SELECT 'L','B',380,'BankQ','Bit','N'
UNION ALL SELECT 'L','B',370,'BankN','Bit','N'
UNION ALL SELECT 'L','B',360,'BankY','Bit','N'
UNION ALL SELECT 'L','B',340,'IRDQ','Bit','N'
UNION ALL SELECT 'L','B',330,'IRDN','Bit','N'
UNION ALL SELECT 'L','B',320,'IRDY','Bit','N'
UNION ALL SELECT 'L','B',310,'ResidentQ','Bit','N'
UNION ALL SELECT 'L','B',300,'ResidentN','Bit','N'
UNION ALL SELECT 'L','B',290,'ResidentY','Bit','N'
UNION ALL SELECT 'L','B',280,'CitizenQ','Bit','N'
UNION ALL SELECT 'L','B',270,'CitizenN','Bit','N'
UNION ALL SELECT 'L','B',260,'CitizenY','Bit','N'
UNION ALL SELECT 'L','B',250,'Barcode','Num','N'
UNION ALL SELECT 'L','B',240,'FormType2','AlphaNum','N'
UNION ALL SELECT 'L','B',230,'ImageRef2','AlphaNum','N'
UNION ALL SELECT 'L','B',220,'BatchNo','Num','N'
SELECT *
FROM tblname
where form= 'L'
order by FrontBack desc
The output I get from SQL 2000 is:
Form FrontBack FieldNo dbField Validation
AllowNulls
-- -- -- --
-- --
L F 10 BatchNo Num
N
L F 20 ImageRef1 AlphaNum
N
L F 30 FormType1 AlphaNum
N
L F 40 Barcode Num
N
L F 50 NameAndDOBY Bit
N
L F 60 NameAndDOBN Bit
N
L F 70 NameAndDOBQ Bit
N
L F 80 SignedAndDatedY Bit
N
L F 90 SignedAndDatedN Bit
N
L F 100 SignedAndDatedQ Bit
N
L F 110 CAltered Bit
N
L F 120 Under18Years Bit
N
L F 130 ConsentY Bit
N
L F 140 ConsentN Bit
N
L F 150 ConsentQ Bit
N
L F 160 MarriageCertY Bit
N
L F 170 MarriageCertN Bit
N
L F 180 MarriageCertQ Bit
N
L F 190 DependentY Bit
N
L F 200 DependentN Bit
N
L F 210 DependentQ Bit
N
L F 470 SignaturePresent AlphaNum
N
L B 220 BatchNo Num
N
L B 230 ImageRef2 AlphaNum
N
L B 240 FormType2 AlphaNum
N
L B 250 Barcode Num
N
L B 260 CitizenY Bit
N
L B 270 CitizenN Bit
N
L B 280 CitizenQ Bit
N
L B 290 ResidentY Bit
N
L B 300 ResidentN Bit
N
L B 310 ResidentQ Bit
N
L B 320 IRDY Bit
N
L B 330 IRDN Bit
N
L B 340 IRDQ Bit
N
L B 360 BankY Bit
N
L B 370 BankN Bit
N
L B 380 BankQ Bit
N
L B 390 Oaltered Bit
N
L B 400 ExtraEvidence Bit
N
L B 410 EvidenceToWinz Bit
N
L B 420 EvidenceSent Bit
N
(42 row(s) affected)
The output I get from SQL 2005 is:
Form FrontBack FieldNo dbField Validation
AllowNulls
-- -- -- --
-- --
L F 10 BatchNo Num
N
L F 20 ImageRef1 AlphaNum
N
L F 30 FormType1 AlphaNum
N
L F 40 Barcode Num
N
L F 50 NameAndDOBY Bit
N
L F 60 NameAndDOBN Bit
N
L F 70 NameAndDOBQ Bit
N
L F 80 SignedAndDatedY Bit
N
L F 90 SignedAndDatedN Bit
N
L F 100 SignedAndDatedQ Bit
N
L F 110 CAltered Bit
N
L F 120 Under18Years Bit
N
L F 130 ConsentY Bit
N
L F 140 ConsentN Bit
N
L F 150 ConsentQ Bit
N
L F 160 MarriageCertY Bit
N
L F 170 MarriageCertN Bit
N
L F 180 MarriageCertQ Bit
N
L F 190 DependentY Bit
N
L F 200 DependentN Bit
N
L F 210 DependentQ Bit
N
L F 470 SignaturePresent AlphaNum
N
L B 220 BatchNo Num
N
L B 230 ImageRef2 AlphaNum
N
L B 240 FormType2 AlphaNum
N
L B 250 Barcode Num
N
L B 260 CitizenY Bit
N
L B 270 CitizenN Bit
N
L B 280 CitizenQ Bit
N
L B 290 ResidentY Bit
N
L B 300 ResidentN Bit
N
L B 310 ResidentQ Bit
N
L B 320 IRDY Bit
N
L B 330 IRDN Bit
N
L B 340 IRDQ Bit
N
L B 360 BankY Bit
N
L B 370 BankN Bit
N
L B 380 BankQ Bit
N
L B 390 Oaltered Bit
N
L B 400 ExtraEvidence Bit
N
L B 410 EvidenceToWinz Bit
N
L B 420 EvidenceSent Bit
N
(42 row(s) affected)
The order by only guarantees that FrontBack will be ordered 'F' then 'B' and
this is the case on both instances. As FieldNo and Form are not in the order
by their order is not guaranteed.
John
"Adrian" wrote:
> Collation sequence is the same in both versions -
> SQL_Latin1_General_CP1_CI_AS. Here is some sample data:_
> Form FrontBack FieldNo dbField Validation
> AllowNulls
> -- -- -- --
> -- --
> L F 470 SignaturePresent AlphaNum
> N
> L F 210 DependentQ Bit
> N
> L F 200 DependentN Bit
> N
> L F 190 DependentY Bit
> N
> L F 180 MarriageCertQ Bit
> N
> L F 170 MarriageCertN Bit
> N
> L F 160 MarriageCertY Bit
> N
> L F 150 ConsentQ Bit
> N
> L F 140 ConsentN Bit
> N
> L F 130 ConsentY Bit
> N
> L F 120 Under18Years Bit
> N
> L F 110 CAltered Bit
> N
> L F 100 SignedAndDatedQ Bit
> N
> L F 90 SignedAndDatedN Bit
> N
> L F 80 SignedAndDatedY Bit
> N
> L F 70 NameAndDOBQ Bit
> N
> L F 60 NameAndDOBN Bit
> N
> L F 50 NameAndDOBY Bit
> N
> L F 40 Barcode Num
> N
> L F 30 FormType1 AlphaNum
> N
> L F 20 ImageRef1 AlphaNum
> N
> L F 10 BatchNo Num
> N
> L B 420 EvidenceSent Bit
> N
> L B 410 EvidenceToWinz Bit
> N
> L B 400 ExtraEvidence Bit
> N
> L B 390 Oaltered Bit
> N
> L B 380 BankQ Bit
> N
> L B 370 BankN Bit
> N
> L B 360 BankY Bit
> N
> L B 340 IRDQ Bit
> N
> L B 330 IRDN Bit
> N
> L B 320 IRDY Bit
> N
> L B 310 ResidentQ Bit
> N
> L B 300 ResidentN Bit
> N
> L B 290 ResidentY Bit
> N
> L B 280 CitizenQ Bit
> N
> L B 270 CitizenN Bit
> N
> L B 260 CitizenY Bit
> N
> L B 250 Barcode Num
> N
> L B 240 FormType2 AlphaNum
> N
> L B 230 ImageRef2 AlphaNum
> N
> L B 220 BatchNo Num
> N
> (42 row(s) affected)
>
> "John Bell" wrote:
> > Hi Adrian
> >
> > The order should be the case assuming that the collations are the same. Do
> > you have some sample data? http://www.aspfaq.com/etiquette.asp?id=5006
> >
> > You should change the varchar(1) columns.
> >
> > John
> >
> > "Adrian" wrote:
> >
> > > I have the following query returning results in opposite order between
> > > versions 2000 and 2005:-
> > > SELECT * FROM tblname where form= 'L'
> > > order by acolumn desc
> > >
> > > The database has been migrated from 2000 to 2005, so the structure and data
> > > should be identical. Here's the DDL for the table in SQL2000:-
> > > USE [MSD_Contracts]
> > > GO
> > > SET ANSI_NULLS ON
> > > GO
> > > SET QUOTED_IDENTIFIER ON
> > > GO
> > > SET ANSI_PADDING ON
> > > GO
> > > CREATE TABLE [dbo].[tblname](
> > > [Form] [varchar](1) NOT NULL,
> > > [acolumn] [varchar](1) NOT NULL,
> > > [FieldNo] [int] NOT NULL,
> > > [dbField] [varchar](30) NOT NULL,
> > > [Validation] [varchar](20) NOT NULL,
> > > [AllowNulls] [varchar](1) NOT NULL,
> > > CONSTRAINT [PK_tblname_1_12] PRIMARY KEY CLUSTERED
> > > (
> > > [FieldNo] ASC,
> > > [Form] ASC,
> > > [acolumn] ASC
> > > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY => > > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON
> > > [PRIMARY]
> > > ) ON [PRIMARY]
> > >
> > > GO
> > > SET ANSI_PADDING OFF
> > >
> > > Any ideas please?

ORDER BY NEWID() returning duplicates records

This returns 2 random records for me. However, on occasion, the 2 records
returned are identical. Does anybody have any thought for preventing this
from happening?
SELECT TOP 2 P.*, C.CatID FROM JEP_tblProducts P, JEP_LtblProductCategories
C WHERE P.Image1<>'' AND C.ProductID = P.ProductID ORDER BY NEWID()
TIA,
TonyGThat's because SQL evaluates NewId() only once in your query. If you are try
ing
to randomly return two records try this:
Select TOP2 NewId(), P.*, C.CatId
From JEP_tblProducts As P
, JEP_LtblProductCategories As C
Where P.Image1 <> ''
And C.ProductId = P.ProductId
Order By 1
Thomas|||Hi
It could be because the join results in 2 identical records in some instance
s.
Run the query with no TOP clause and see how that compares to the number of
records in the tables.
You may want to update your query to the ANSI style join:
SELECT
TOP 2
P.*,
C.CatID
FROM
JEP_tblProducts AS P
INNER JOJN JEP_LtblProductCategories AS C
ON C.ProductID = P.ProductID
WHERE
P.Image1<>''
ORDER BY NEWID()
Regards
Mike
"TonyG" wrote:

> This returns 2 random records for me. However, on occasion, the 2 records
> returned are identical. Does anybody have any thought for preventing this
> from happening?
> SELECT TOP 2 P.*, C.CatID FROM JEP_tblProducts P, JEP_LtblProductCategorie
s
> C WHERE P.Image1<>'' AND C.ProductID = P.ProductID ORDER BY NEWID()
> TIA,
> TonyG
>
>|||>> That's because SQL evaluates NewId() only once in your query.
Generally, any non-numeric expression used in the ORDER BY clause is
evaluated in the same way the expression is evaluated in the SELECT clause.
And it is applicable for NEWID() as well.
The duplication OP has is perhaps due to lack of keys/constraints in the
tables and/or the result of using non-unique columns in the JOIN clause. But
then without seeing any useful DDLs, it would be anyone's guess.
Anith|||Thats not true, using the newid () funtion in the order generates for every
row in the query a seperate GUID , every time you execute is. There is a
problem with your query it returns more than one row becasue your table in
not only related to the second table via one column.
Try to select this and youll see that there will be more than one row
returned:
Select count(*) from
> From JEP_tblProducts As P
> , JEP_LtblProductCategories As C
> Where P.Image1 <> ''
> And C.ProductId = P.ProductId
Perhaps you can make a join with the appropiate key in it.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Thomas" <thomas@.newsgroup.nospam> schrieb im Newsbeitrag
news:Onzdy%23nSFHA.248@.TK2MSFTNGP15.phx.gbl...
> That's because SQL evaluates NewId() only once in your query. If you are
> trying to randomly return two records try this:
> Select TOP2 NewId(), P.*, C.CatId
> From JEP_tblProducts As P
> , JEP_LtblProductCategories As C
> Where P.Image1 <> ''
> And C.ProductId = P.ProductId
> Order By 1
>
> Thomas
>|||Well, you have a 1-M relationship. Thus, it's possible to return 2 that are
identical. The newid() only randomizes the resultset and does not remove the
dupes.
Here is a trick to get the uniqueness.
SELECT TOP 2 *
from (select distinct
P.*, C.CatID FROM JEP_tblProducts P, JEP_LtblProductCategories
C WHERE P.Image1<>'' AND C.ProductID = P.ProductID
) derived
ORDER BY NEWID()
-oj
"TonyG" <groups@.RE-MO-VE-globalmagic.com> wrote in message
news:OUBU05nSFHA.3088@.TK2MSFTNGP15.phx.gbl...
> This returns 2 random records for me. However, on occasion, the 2 records
> returned are identical. Does anybody have any thought for preventing this
> from happening?
> SELECT TOP 2 P.*, C.CatID FROM JEP_tblProducts P,
> JEP_LtblProductCategories C WHERE P.Image1<>'' AND C.ProductID =
> P.ProductID ORDER BY NEWID()
> TIA,
> TonyG
>|||I stand corrected. Is there is any way to know ths for sure (documentation,
proof of concept etc)?
Thomas
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eGCxkEoSFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Generally, any non-numeric expression used in the ORDER BY clause is evalu
ated
> in the same way the expression is evaluated in the SELECT clause. And it i
s
> applicable for NEWID() as well.
> The duplication OP has is perhaps due to lack of keys/constraints in the
> tables and/or the result of using non-unique columns in the JOIN clause. B
ut
> then without seeing any useful DDLs, it would be anyone's guess.
> --
> Anith
>|||Build an example myself. Thanks for the correction.
Thomas
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eGCxkEoSFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Generally, any non-numeric expression used in the ORDER BY clause is evalu
ated
> in the same way the expression is evaluated in the SELECT clause. And it i
s
> applicable for NEWID() as well.
> The duplication OP has is perhaps due to lack of keys/constraints in the
> tables and/or the result of using non-unique columns in the JOIN clause. B
ut
> then without seeing any useful DDLs, it would be anyone's guess.
> --
> Anith
>|||Thanks, that did the trick.
I knew I was missing something.
TonyG
"oj" <nospam_ojngo@.home.com> wrote in message
news:OJSm6JoSFHA.2000@.TK2MSFTNGP10.phx.gbl...
> Well, you have a 1-M relationship. Thus, it's possible to return 2 that
> are identical. The newid() only randomizes the resultset and does not
> remove the dupes.
> Here is a trick to get the uniqueness.
> SELECT TOP 2 *
> from (select distinct
> P.*, C.CatID FROM JEP_tblProducts P, JEP_LtblProductCategories
> C WHERE P.Image1<>'' AND C.ProductID = P.ProductID
> ) derived
> ORDER BY NEWID()
> --
> -oj
>
> "TonyG" <groups@.RE-MO-VE-globalmagic.com> wrote in message
> news:OUBU05nSFHA.3088@.TK2MSFTNGP15.phx.gbl...
>

order by killing me

sql2k sp2
Im aware that it normally slows everone down. But Ive got
a view, and when I select top 1 from it without an order
by it returns in 1 second. With order by takes over 4
minutes. Quite a jump. Ive got a Clustered Primary Key on
the column that Im ordering by. Any ideas?
TIA, Chris"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:281e01c3e108$bae3dff0$a401280a@.phx.gbl...
> sql2k sp2
> Im aware that it normally slows everone down. But Ive got
> a view, and when I select top 1 from it without an order
> by it returns in 1 second. With order by takes over 4
> minutes. Quite a jump. Ive got a Clustered Primary Key on
> the column that Im ordering by. Any ideas?
What does the output from Query Analyzer > Show Plan indicate? How many rows
do you have?
Steve|||Please post DDL including index and with Execution plan and Statistics IO.
--
Satish Balusa
Corillian Corp.
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:281e01c3e108$bae3dff0$a401280a@.phx.gbl...
> sql2k sp2
> Im aware that it normally slows everone down. But Ive got
> a view, and when I select top 1 from it without an order
> by it returns in 1 second. With order by takes over 4
> minutes. Quite a jump. Ive got a Clustered Primary Key on
> the column that Im ordering by. Any ideas?
> TIA, Chris
>|||Hi,
Do the select statement with out Order by clause,Since you have primary key
on that field , automatically data will be arranged in that order. In this
case you do not require a order by clause.
Thanks
Hari
MCDBA
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:281e01c3e108$bae3dff0$a401280a@.phx.gbl...
> sql2k sp2
> Im aware that it normally slows everone down. But Ive got
> a view, and when I select top 1 from it without an order
> by it returns in 1 second. With order by takes over 4
> minutes. Quite a jump. Ive got a Clustered Primary Key on
> the column that Im ordering by. Any ideas?
> TIA, Chris
>|||Hari,
For your information: if you want the rows of the resultset in a
particular order, you must specify an ORDER BY clause. Without it,
SQL-Server is at liberty to return the rows in any order it likes. At
one occasion this might accidentally be the order you desired, but at
another occasions a different order might result.
Gert-Jan
Hari Prasad wrote:
> Hi,
> Do the select statement with out Order by clause,Since you have primary key
> on that field , automatically data will be arranged in that order. In this
> case you do not require a order by clause.
> Thanks
> Hari
> MCDBA
> "chris" <anonymous@.discussions.microsoft.com> wrote in message
> news:281e01c3e108$bae3dff0$a401280a@.phx.gbl...
> > sql2k sp2
> >
> > Im aware that it normally slows everone down. But Ive got
> > a view, and when I select top 1 from it without an order
> > by it returns in 1 second. With order by takes over 4
> > minutes. Quite a jump. Ive got a Clustered Primary Key on
> > the column that Im ordering by. Any ideas?
> >
> > TIA, Chris
> >

Order By computed columns

Hi all,
I have a long runing query took 70s and returns only 124 rows. I found the
problem is that it uses a compute column in the Order By clause. Something
like this
SELECT ... ORDER BY ISNULL(Table1.Field1, '') + '|' +
ISNULL(CONVERT(nvarchar, Table2.Field2), '''')
If I took that Order By away, it only takes 2s. (That make me think my C#
client code can sort better than that :P )
Can anyone show me what are the ways I can do to optimize it?
Any thoughts are appreciated.
ConradConrad Chan wrote:
> Hi all,
> I have a long runing query took 70s and returns only 124 rows. I found th
e
> problem is that it uses a compute column in the Order By clause. Somethin
g
> like this
> SELECT ... ORDER BY ISNULL(Table1.Field1, '') + '|' +
> ISNULL(CONVERT(nvarchar, Table2.Field2), '''')
> If I took that Order By away, it only takes 2s. (That make me think my C#
> client code can sort better than that :P )
> Can anyone show me what are the ways I can do to optimize it?
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
If that expression is in the SELECT clause you can use
ORDER BY n
where n is the ordinal number of the expression in the SELECT clause.
E.g.:
SELECT col1, col2, (col4 * 0.25) / 100, ...
FROM ...
ORDER BY 3
Will sort the resultset by the value of the 3rd column in the SELECT
clause.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjdSGYechKqOuFEgEQKsQgCggvSDYuQCwIcw
DXSdEtuVA3YD+b4AnixS
BnAeboIAn+Ja2WD/GUp486uA
=1vFd
--END PGP SIGNATURE--|||If you can do it in the client side, then do it.
AMB
"Conrad Chan" wrote:

> Hi all,
> I have a long runing query took 70s and returns only 124 rows. I found th
e
> problem is that it uses a compute column in the Order By clause. Somethin
g
> like this
> SELECT ... ORDER BY ISNULL(Table1.Field1, '') + '|' +
> ISNULL(CONVERT(nvarchar, Table2.Field2), '''')
> If I took that Order By away, it only takes 2s. (That make me think my C#
> client code can sort better than that :P )
> Can anyone show me what are the ways I can do to optimize it?
> Any thoughts are appreciated.
> Conrad|||I will say only if db really cannot do a better job.
Thanks
Conrad
"Alejandro Mesa" wrote:
> If you can do it in the client side, then do it.
>
> AMB
> "Conrad Chan" wrote:
>|||Unfortunately it doesn't make a difference :<
Conrad
"MGFoster" wrote:

> Conrad Chan wrote:
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> If that expression is in the SELECT clause you can use
> ORDER BY n
> where n is the ordinal number of the expression in the SELECT clause.
> E.g.:
> SELECT col1, col2, (col4 * 0.25) / 100, ...
> FROM ...
> ORDER BY 3
> Will sort the resultset by the value of the 3rd column in the SELECT
> clause.
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/ AwUBQjdSGYechKqOuFEgEQKsQgCggvSDYuQCwIcw
DXSdEtuVA3YD+b4AnixS
> BnAeboIAn+Ja2WD/GUp486uA
> =1vFd
> --END PGP SIGNATURE--
>|||You can simplify the ORDER BY clause to
ORDER BY COALESCE(Table1.Field1, ''), COALESCE(CAST(Table2.Field2 AS
nvarchar), '''')
or even to
ORDER BY Table1.Field1, Table2.Field2
HTH,
Gert-Jan
Conrad Chan wrote:
> Hi all,
> I have a long runing query took 70s and returns only 124 rows. I found th
e
> problem is that it uses a compute column in the Order By clause. Somethin
g
> like this
> SELECT ... ORDER BY ISNULL(Table1.Field1, '') + '|' +
> ISNULL(CONVERT(nvarchar, Table2.Field2), '''')
> If I took that Order By away, it only takes 2s. (That make me think my C#
> client code can sort better than that :P )
> Can anyone show me what are the ways I can do to optimize it?
> Any thoughts are appreciated.
> Conrad|||Conrad,
In General it should never take SQL Server 68 seconds to sort 124
records... SOmething else is going on here ... Run the query in Query
Analyzer with ShowPlan ON, and see what step in the showplan is taking that
long...
"Conrad Chan" wrote:

> Hi all,
> I have a long runing query took 70s and returns only 124 rows. I found th
e
> problem is that it uses a compute column in the Order By clause. Somethin
g
> like this
> SELECT ... ORDER BY ISNULL(Table1.Field1, '') + '|' +
> ISNULL(CONVERT(nvarchar, Table2.Field2), '''')
> If I took that Order By away, it only takes 2s. (That make me think my C#
> client code can sort better than that :P )
> Can anyone show me what are the ways I can do to optimize it?
> Any thoughts are appreciated.
> Conrad|||Thanks CBretana,
I did look into query analyzer. 90% is done on the Sort. The only thing I
found may be interested is that the estimated row count is 15,000 compared
with 124 row count.
Conrad
"CBretana" wrote:
> Conrad,
> In General it should never take SQL Server 68 seconds to sort 124
> records... SOmething else is going on here ... Run the query in Query
> Analyzer with ShowPlan ON, and see what step in the showplan is taking tha
t
> long...
>
> "Conrad Chan" wrote:
>|||Then you have a filter in the query somewhere, which is reducing the output
from 15,000 to 124, and the sort is happening on the entire 15k recordset,
not the final 124... Suggestion
Rewrite the query as
Select <Stuff>
From (SubSquery: Select Stuff
From <Tables>
Where <Here goes filter predicate tha treduces 15k - 124)
Order By <Order by Clause>
Then inner query must process the filter and deliver the 124 records to the
outer part, where the Order By is...
See if that works...
"Conrad Chan" wrote:
> Thanks CBretana,
> I did look into query analyzer. 90% is done on the Sort. The only thing
I
> found may be interested is that the estimated row count is 15,000 compared
> with 124 row count.
> Conrad
> "CBretana" wrote:
>|||No luck. I simply try to do exactly like you suggest. SQL is smart enough
to realize they are the same. (It is too smart to be stupid)
However, for testing purpose, if I put a TOP inside my sub-select it does
return in 2s.
SELECT * FROM (
SELECT TOP 124 * FROM ...
) ORDER BY 1, 4
Conrad
"CBretana" wrote:
> Then you have a filter in the query somewhere, which is reducing the outpu
t
> from 15,000 to 124, and the sort is happening on the entire 15k recordset,
> not the final 124... Suggestion
> Rewrite the query as
> Select <Stuff>
> From (SubSquery: Select Stuff
> From <Tables>
> Where <Here goes filter predicate tha treduces 15k - 124)
> Order By <Order by Clause>
> Then inner query must process the filter and deliver the 124 records to th
e
> outer part, where the Order By is...
> See if that works...
> "Conrad Chan" wrote:
>sql

Oracle:RowId <=> Sql-Server:?

Hi all

I have an easy question. In Oracle I can retrieve a column named "ROWID" which returns an unique identifier of the row in the
database. I want to have the same element in SQL Server.

Do you know how is this handle in SQL Server ?

Thanks in Advance

Fabian BonillaRefer this link (http://www.sqlteam.com/item.asp?ItemID=283) is any help.|||ROWID is maintained internally and is not accessible through T-SQL.|||Originally posted by rdjabarov
ROWID is maintained internally and is not accessible through T-SQL.

That pity: thanks for all .|||Originally posted by Satya
Refer this link (http://www.sqlteam.com/item.asp?ItemID=283) is any help.

Thanks for all|||Originally posted by rdjabarov
ROWID is maintained internally and is not accessible through T-SQL.
@.@.IDENTITY ?|||It returns the last-inserted identity value.

Oracle View returns ORA-01403: no data found

I am trying to get data from an Oracle view using an OLE DB data source and a "SQL Command". When I "preview" the data it looks fine, but when I execute the package I get the following error:

Error: 0xC0202009 at Data Flow Task, CEDAR View [1]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-01403: no data found".
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "CEDAR View" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.

hi,

are you totally sure that such destionation owns data?

|||I was using the "Oracle" version of the OLE DB driver. When I switched to the Microsoft OLE DB provider for Oracle it worked (with warnings). I guess not all OLE DB providers will work with SSIS. It's just very strange the way it worked in "preview" mode, but failed during execution

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

oracle linked server decimal problem

I'm attempting to query a linked server (Oracle) via QA. My query returns the expected record set, but the data for the decimal columns is not correct (only displays zeros to the right of the decimal point). My T-SQL:
SELECT amount
FROM oracle_src...budget
... yields the result:
..000
... whereas the equivalent query via MS Access yields
2784.56
The data type for the amount column in Oracle is defined as Numeric(9,3).
Any thoughts? I could pass the whole query process through MS Access to Oracle, but ... seems like there should be a more elegant solution. Thanks for any suggestions!
- Denis
I may have found a work around for this problem. I was using Oracle's ODBC driver, version 8.01.05.00. I switched to using the Microsoft ODBC for Oracle driver, version 2.573.9030.00, and the problem seems to have resolved.

oracle linked server decimal problem

I'm attempting to query a linked server (Oracle) via QA. My query returns th
e expected record set, but the data for the decimal columns is not correct (
only displays zeros to the right of the decimal point). My T-SQL:
SELECT amount
FROM oracle_src...budget
.. yields the result:
.000
.. whereas the equivalent query via MS Access yields
2784.56
The data type for the amount column in Oracle is defined as Numeric(9,3).
Any thoughts? I could pass the whole query process through MS Access to Orac
le, but ... seems like there should be a more elegant solution. Thanks for a
ny suggestions!
- DenisI may have found a work around for this problem. I was using Oracle's ODBC d
river, version 8.01.05.00. I switched to using the Microsoft ODBC for Oracle
driver, version 2.573.9030.00, and the problem seems to have resolved.

oracle linked server decimal problem

I'm attempting to query a linked server (Oracle) via QA. My query returns the expected record set, but the data for the decimal columns is not correct (only displays zeros to the right of the decimal point). My T-SQL
SELECT amoun
FROM oracle_src...budge
... yields the result
..00
... whereas the equivalent query via MS Access yield
2784.5
The data type for the amount column in Oracle is defined as Numeric(9,3)
Any thoughts? I could pass the whole query process through MS Access to Oracle, but ... seems like there should be a more elegant solution. Thanks for any suggestions
- DeniI may have found a work around for this problem. I was using Oracle's ODBC driver, version 8.01.05.00. I switched to using the Microsoft ODBC for Oracle driver, version 2.573.9030.00, and the problem seems to have resolved.