Order of columns using drillthrough

I need to implement drillthrough options in a cube using MSAS 2000.
But required a given order of columns for showing these columns and I
don't know how can I set an order of columns.
It looks like I can only select and deselect column outputting but
there is no possibility to set an order.
Thanks for help,
AndriyOn Jul 9, 3:40 pm, andr...@.rambler.ru wrote:
> I need to implement drillthrough options in a cube using MSAS 2000.
> But required a given order of columns for showing these columns and I
> don't know how can I set an order of columns.
> It looks like I can only select and deselect column outputting but
> there is no possibility to set an order.
> Thanks for help,
> Andriy
The only way I know is to edit the MDX directly - IDE editor doesn't
allow to set the order.
Marco

Order of columns using drillthrough

I need to implement drillthrough options in a cube using MSAS 2000.
But required a given order of columns for showing these columns and I
don't know how can I set an order of columns.
It looks like I can only select and deselect column outputting but
there is no possibility to set an order.
Thanks for help,
Andriy
On Jul 9, 3:40 pm, andr...@.rambler.ru wrote:
> I need to implement drillthrough options in a cube using MSAS 2000.
> But required a given order of columns for showing these columns and I
> don't know how can I set an order of columns.
> It looks like I can only select and deselect column outputting but
> there is no possibility to set an order.
> Thanks for help,
> Andriy
The only way I know is to edit the MDX directly - IDE editor doesn't
allow to set the order.
Marco

order of columns in composite index

I'm know that for composite indexes, the recommendation is always to specify
the most selective column first, but can someone please explain why this
makes such a huge difference in perfomance' I have a table where column A
only has 1 unique value and column B is basically unique among all rows. A
query that has A and B in the WHERE clause takes ALOT longer if my composite
index was created with (A, B) instead of (B, A). Thanks for any help.
BobBy the way, I did verify that in both cases, my index is being used, since I
know that index statistics are gathered based on the first column.
"Bob Gabor" <rjg@.mindspring.com> wrote in message
news:Yclkf.8556$N45.2454@.newsread1.news.atl.earthlink.net...
> I'm know that for composite indexes, the recommendation is always to
> specify the most selective column first, but can someone please explain
> why this makes such a huge difference in perfomance' I have a table
> where column A only has 1 unique value and column B is basically unique
> among all rows. A query that has A and B in the WHERE clause takes ALOT
> longer if my composite index was created with (A, B) instead of (B, A).
> Thanks for any help.
> Bob
>|||It depends on the query. Rules like the one you quote are just
general guidelines. For example, if one of the columns is used
in a range or LIKE comparison, it may be best to index that column
first regardless of selectivity. Depending on the query and data,
the two-column statistics may be more or less accurate predictors
of row count for the index in one order than in the other, also.
If you look at the query plans in more detail, you may be able
to see whether the faster solution is resulting in a better plan that
the other ordering can't allow, or if the faster solution is a result
of better row count estimates.
Steve Kass
Drew University
Bob Gabor wrote:

>I'm know that for composite indexes, the recommendation is always to specif
y
>the most selective column first, but can someone please explain why this
>makes such a huge difference in perfomance' I have a table where column A
>only has 1 unique value and column B is basically unique among all rows. A
>query that has A and B in the WHERE clause takes ALOT longer if my composit
e
>index was created with (A, B) instead of (B, A). Thanks for any help.
>Bob
>
>|||>> is always to specify
the most selective column first, but can someone please explain why
this
makes such a huge difference in perfomance' <<
there is only one hard and fast rule in our trade:
there are no hard and fast rules in database programming.
;)
For instance, if you frequently join on some column, putting it first
frequently speeds up joins.|||It does depend on many things, but I can give you some insight into why this
might be a problem (though I can not say it is necessarily a problem for
your application).
If you have a 2-column index with a non-selective leading column and a very
selective secondary column, it can cause additional I/O when compared to a
query run over an index with the columns defined in the opposite order
(selective column first). If the query does a s on the first column and
then later columns, this could be less efficient.
SELECT col1, col2 FROM Table WHERE col1=4 and col3 > 5;
I will point out that it can vary from database engine to database engine.
It can vary on the predicates being used. It can vary based on the mix of
queries and the hardware. In short, it really does depend. However, it is
generally good to index selective fields since the cost of searching and the
cost of maintaining these indexes in updates is less than non-selective
columns.
Another reason to potentially pick a more selective leading index column,
all other factors being equal, is that SQL Server builds histograms on the
leading column. If it is very unselective, this can make the process of
cardinality estimation more difficult for the optimizer. This could cause
errors that lead to less than optimal plans being picked in some cases.
I hope that this gives you some insights into the internals to understand
why it might matter.
Thanks,
Conor Cunningham
SQL Server Query Optimization Development Lead
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1133653242.800468.130470@.g47g2000cwa.googlegroups.com...
> the most selective column first, but can someone please explain why
> this
> makes such a huge difference in perfomance' <<
> there is only one hard and fast rule in our trade:
> there are no hard and fast rules in database programming.
> ;)
> For instance, if you frequently join on some column, putting it first
> frequently speeds up joins.
>

ORDER BY, CASE, with multiple columns

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

Thanks

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

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

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

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

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

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

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

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

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

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

Terri

Order by problem within a View

Hi,

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

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

thanks

David

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

order by issue

I'm trying to get a query to work that specifies how many records to return
(TOP(###)) and order them by one of the columns in the table. What I'm
getting is a server timeout.
My query looks kinda like this "select top(500) * from mytable order by
mycolumn"
I've tried this on a couple of different servers with the same timeout
error.
Any Ideas on how to fix?
How many rows are in the table? Is there an index on mycolumn?
"Jimmy Stewart" <jstewart@.globalparadigmsolutions.com> wrote in message
news:u7RQrc1mIHA.4292@.TK2MSFTNGP04.phx.gbl...
> I'm trying to get a query to work that specifies how many records to
> return
> (TOP(###)) and order them by one of the columns in the table. What I'm
> getting is a server timeout.
> My query looks kinda like this "select top(500) * from mytable order by
> mycolumn"
> I've tried this on a couple of different servers with the same timeout
> error.
> Any Ideas on how to fix?
>
|||There are something over 1.2 million records (rows) in the table.
No the column is not indexed.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:F3DD40E4-5AF5-4392-8A0D-A09AEC62FF14@.microsoft.com...
> How many rows are in the table? Is there an index on mycolumn?
>
> "Jimmy Stewart" <jstewart@.globalparadigmsolutions.com> wrote in message
> news:u7RQrc1mIHA.4292@.TK2MSFTNGP04.phx.gbl...
>
|||In that case, each query requires a full table scan to determine the 500
rows you want to select.
Either increase the connection timeout or (preferably) add proper
indexes.
Also, it is best to only select the columns that you need, and thus
avoid SELECT *
Gert-Jan
Jimmy Stewart wrote:[vbcol=seagreen]
> There are something over 1.2 million records (rows) in the table.
> No the column is not indexed.
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:F3DD40E4-5AF5-4392-8A0D-A09AEC62FF14@.microsoft.com...

order by issue

I'm trying to get a query to work that specifies how many records to return
(TOP(###)) and order them by one of the columns in the table. What I'm
getting is a server timeout.
My query looks kinda like this "select top(500) * from mytable order by
mycolumn"
I've tried this on a couple of different servers with the same timeout
error.
Any Ideas on how to fix?How many rows are in the table? Is there an index on mycolumn?
"Jimmy Stewart" <jstewart@.globalparadigmsolutions.com> wrote in message
news:u7RQrc1mIHA.4292@.TK2MSFTNGP04.phx.gbl...
> I'm trying to get a query to work that specifies how many records to
> return
> (TOP(###)) and order them by one of the columns in the table. What I'm
> getting is a server timeout.
> My query looks kinda like this "select top(500) * from mytable order by
> mycolumn"
> I've tried this on a couple of different servers with the same timeout
> error.
> Any Ideas on how to fix?
>|||There are something over 1.2 million records (rows) in the table.
No the column is not indexed.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:F3DD40E4-5AF5-4392-8A0D-A09AEC62FF14@.microsoft.com...
> How many rows are in the table? Is there an index on mycolumn?
>
> "Jimmy Stewart" <jstewart@.globalparadigmsolutions.com> wrote in message
> news:u7RQrc1mIHA.4292@.TK2MSFTNGP04.phx.gbl...
> > I'm trying to get a query to work that specifies how many records to
> > return
> > (TOP(###)) and order them by one of the columns in the table. What I'm
> > getting is a server timeout.
> >
> > My query looks kinda like this "select top(500) * from mytable order by
> > mycolumn"
> >
> > I've tried this on a couple of different servers with the same timeout
> > error.
> >
> > Any Ideas on how to fix?
> >
> >
>|||In that case, each query requires a full table scan to determine the 500
rows you want to select.
Either increase the connection timeout or (preferably) add proper
indexes.
Also, it is best to only select the columns that you need, and thus
avoid SELECT *
--
Gert-Jan
Jimmy Stewart wrote:
> There are something over 1.2 million records (rows) in the table.
> No the column is not indexed.
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:F3DD40E4-5AF5-4392-8A0D-A09AEC62FF14@.microsoft.com...
> > How many rows are in the table? Is there an index on mycolumn?
> >
> >
> > "Jimmy Stewart" <jstewart@.globalparadigmsolutions.com> wrote in message
> > news:u7RQrc1mIHA.4292@.TK2MSFTNGP04.phx.gbl...
> > > I'm trying to get a query to work that specifies how many records to
> > > return
> > > (TOP(###)) and order them by one of the columns in the table. What I'm
> > > getting is a server timeout.
> > >
> > > My query looks kinda like this "select top(500) * from mytable order by
> > > mycolumn"
> > >
> > > I've tried this on a couple of different servers with the same timeout
> > > error.
> > >
> > > Any Ideas on how to fix?
> > >
> > >
> >

ORDER BY earliest date in a row

Hi,

I have a table (SQL Server 2000) with several date columns in it, all of
which are individually NULLable, but in any one row, not all the dates can
be NULL.

I want a query which ORDERs BY the earliest date it finds in each row. I'm
guessing I have to do this in two steps:

STEP 1
Using a UDF, find the earliest date and stick it in a new calculated
column "earliest date"

STEP 2
ORDER BY this UDF-created column

If this is the right way to go about this, is there a simple SQL way of
determining which is the lowest of several dates? (ie of doing STEP 1).

Or am I looking at this the wrong way, and missing an easy *one-step* way of
getting what I want?

TIA,

JON"Jon Maz" <jonmaz@.NOSPAM.surfeu.de> wrote in message
news:bk7kdg$gva$1@.online.de...
> Hi,
> I have a table (SQL Server 2000) with several date columns in it, all of
> which are individually NULLable, but in any one row, not all the dates can
> be NULL.
> I want a query which ORDERs BY the earliest date it finds in each row.
I'm
> guessing I have to do this in two steps:
> STEP 1
> Using a UDF, find the earliest date and stick it in a new
calculated
> column "earliest date"
> STEP 2
> ORDER BY this UDF-created column
> If this is the right way to go about this, is there a simple SQL way of
> determining which is the lowest of several dates? (ie of doing STEP 1).
> Or am I looking at this the wrong way, and missing an easy *one-step* way
of
> getting what I want?
> TIA,
> JON

This is probably easier to do in a client/reporting tool than in pure SQL,
but one possible solution is as follows (performance won't be good on a
large table):

create view dbo.DateCols
as
select KeyCol, DateCol1 as 'DateCol'
from dbo.MyTable
union
select KeyCol, DateCol2
from dbo.MyTable
union
select KeyCol, DateCol3
from dbo.MyTable

select t.*
from dbo.MyTable t
join
(
KeyCol, min(DateCol) as 'MinDate'
from dbo.MyTable
group by KeyCol
) as dt
on t.KeyCol = dt.KeyCol
order by dt.MinDate

Simon|||[sent to microsoft.public.sqlserver.programming separately - newsreader can't
sent to 2 news servers at once.]

Jon,

Here is another option, using Alejandro's definitions (thanks, Alejandro!)

create view vwTable1A
as
select table1.c1,
case n when 2 then c2 when 3 then c3 when 4 then c4 end c,
case n when 2 then '2' when 3 then '3' when 4 then '4' end i
from table1, (
select 2 n union all select 3 union all select 4
) N
go

select
c1,
(select c from vwtable1A where c1 = T.c1 and i = '2') c2,
(select c from vwtable1A where c1 = T.c1 and i = '3') c3,
(select c from vwtable1A where c1 = T.c1 and i = '4') c4
from vwtable1A T
group by c1 order by min(c)

-- Steve Kass
-- Drew University
-- Ref: 044B6F84-937C-4CDE-B9F0-BBEB959DBB7F

Jon Maz wrote:

>Hi,
>I have a table (SQL Server 2000) with several date columns in it, all of
>which are individually NULLable, but in any one row, not all the dates can
>be NULL.
>I want a query which ORDERs BY the earliest date it finds in each row. I'm
>guessing I have to do this in two steps:
> STEP 1
> Using a UDF, find the earliest date and stick it in a new calculated
>column "earliest date"
> STEP 2
> ORDER BY this UDF-created column
>If this is the right way to go about this, is there a simple SQL way of
>determining which is the lowest of several dates? (ie of doing STEP 1).
>Or am I looking at this the wrong way, and missing an easy *one-step* way of
>getting what I want?
>TIA,
>JON
>|||Jon,

I would try this (Air coded):

SELECT MT.*
FROM MyTable MT
INNER JOIN(
SELECT T.RecordID, MIN(T.MinDate) AS MinDate
FROM
(SELECT RecordID, Date1 AS MinDate
FROM MyTable
UNION ALL
SELECT RecordID, Date2 AS MinDate
FROM MyTable
UNION ALL
SELECT RecordID, Date3 AS MinDate
FROM MyTable
UNION ALL
... /* Any other date field in your record ... */
) AS T
GROUP BY T.RecordID) AS T ON T.RecordID = MT.RecordID
ORDER BY T.MinDate

HTH

Yannick

"Jon Maz" <jonmaz@.NOSPAM.surfeu.de> wrote in message
news:bk7kdg$gva$1@.online.de...
> Hi,
> I have a table (SQL Server 2000) with several date columns in it, all of
> which are individually NULLable, but in any one row, not all the dates can
> be NULL.
> I want a query which ORDERs BY the earliest date it finds in each row.
I'm
> guessing I have to do this in two steps:
> STEP 1
> Using a UDF, find the earliest date and stick it in a new
calculated
> column "earliest date"
> STEP 2
> ORDER BY this UDF-created column
> If this is the right way to go about this, is there a simple SQL way of
> determining which is the lowest of several dates? (ie of doing STEP 1).
> Or am I looking at this the wrong way, and missing an easy *one-step* way
of
> getting what I want?
> TIA,
> JON
>|||Another one (using Alejandro's DDL):

SELECT *
FROM Table1
ORDER BY
(SELECT MIN(dt)
FROM
(SELECT c2 AS dt
UNION ALL
SELECT c3
UNION ALL
SELECT c4) AS d)

--
David Portas
----
Please reply only to the newsgroup
--|||Hi,

Thanks to all for the great replies.

I ended up using a variant on David's code (because it was the shortest) and
using it in a UDF. A cut-down version of this UDF is below, and I was just
wondering if there a more succint way of writing it (ie without all the
repetition of 'WHERE CaseID=@.CaseID'?)

Cheers,

JON

__________________________________________________ __

CREATE FUNCTION EarliestDate(@.CaseID as INT, @.DateType as VarChar(255))
RETURNS DateTime
AS
BEGIN

DECLARE @.RESULT DateTime
SET @.RESULT = ''

IF @.DateType = 'NonWECLetters_SentToClient'

SELECT @.RESULT =

MIN(dt)
FROM
(SELECT DateBWSLettSentClient AS dt FROM tblCases WHERE CaseID=@.CaseID
UNION ALL
SELECT DateRMLLettSentClient FROM tblCases WHERE CaseID=@.CaseID
UNION ALL
SELECT DateBWSLettEqChSentClient FROM tblCases WHERE CaseID=@.CaseID) AS
d

RETURN @.RESULT

END
GO

ORDER BY DESC

I want to select 3 columns so that the result set is sorted descending on
each column. But this doesn't seem to get me the results that I want:
SELECT col1, col2, col3
FROM tbl1
ORDER BY col1, col2, col3 DESC
And this does not work (syntax error):
...ORDER BY col1 DESC, col2 DESC, col3 DESC
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave MustaneMike,
This should work:
> ...ORDER BY col1 DESC, col2 DESC, col3 DESC
Can you show the actual query?
Andrew J. Kelly SQL MVP
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:urUl$OLBGHA.3164@.TK2MSFTNGP10.phx.gbl...
>I want to select 3 columns so that the result set is sorted descending on
>each column. But this doesn't seem to get me the results that I want:
> SELECT col1, col2, col3
> FROM tbl1
> ORDER BY col1, col2, col3 DESC
> And this does not work (syntax error):
> ...ORDER BY col1 DESC, col2 DESC, col3 DESC
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||> This should work:
Whoops! Nevermind, you are correct. One of the commas in the order by
clause was acidentally a decimal.
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane

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

ORDER BY columns in SELECT list?

According to BOL, columns in an ORDER BY clause do not have to be in the SELECT
column list unless the SELECT includes DISTINCT, or the UNION operator.

Is this a SQL Server thing, or SQL standard behavior? That is, if I were to write
absolutely pure SQL-92, must columns in the ORDER BY clause be present in the SELECT
list?SQL-92 demands the columns specified in the ORDER BY clause be present in
the SELECT list. I guess the recent standards (99, 03 etc) does not have
this requirement.

--
- Anith
( Please reply to newsgroups only )

ORDER BY <VarChar Field>

Hi group,

I've got a table with two columns named [Year] and [Month]. They are both defined as VarChar.

Question:
Is it possible to ORDER THEM as if they where of type DateTime?

EG
select [year], [month]
from tbl_WeightedAverageGenerated
where [Year] = 2006
ORDER BY [Month]

Returns:
2006, 10
2006, 11
2006, 12
2006, 5
2006, 6
etc...

I need it to return:
2006 5
2006 6
2006 7
2006 8
2006 9
2006 10
2006 11
2006 12

Is this possible....and how?

TIA

Regards,

SDerix

Yes, cast them as integers first.

select [year], [month]
from tbl_WeightedAverageGenerated
where [Year] = 2006
ORDER BY cast([Month] as int)

-Jamie

order by

hi i have the following
create table #station_mix
(
station varchar(100)
--several other columns
)
insert into #station_mix values('qtq')
insert into #station_mix values('nws')
insert into #station_mix values('stw')
insert into #station_mix values('tcn')
insert into #station_mix values('gtv')
when i do a select * from #station_mix i want the output to be in the order.
'tcn', 'gtv', 'qtq', 'nws','stw'
how do i do this?
Thanks
ICHORHi ichor,
since you havent got any sort crteria in the name itself, you need some
other column to do the ordering:
create table #station_mix (
station varchar(100),
order_no INT
--several other columns)
insert into #station_mix (station, order_no) values('qtq', 2)
insert into #station_mix (station, order_no) values('nws', 3)
insert into #station_mix (station, order_no) values('stw', 4)
insert into #station_mix (station, order_no) values('tcn', 0)
insert into #station_mix (station, order_no) values('gtv', 1)
select * from #station_mix order by order_no
Micha
"ichor" <ichor@.hotmail.com> schrieb im Newsbeitrag
news:OzQacBbkFHA.3380@.TK2MSFTNGP12.phx.gbl...
> hi i have the following
> create table #station_mix
> (
> station varchar(100)
> --several other columns
> )
> insert into #station_mix values('qtq')
> insert into #station_mix values('nws')
> insert into #station_mix values('stw')
> insert into #station_mix values('tcn')
> insert into #station_mix values('gtv')
> when i do a select * from #station_mix i want the output to be in the
> order.
> 'tcn', 'gtv', 'qtq', 'nws','stw'
> how do i do this?
> Thanks
> ICHOR
>
>|||Hi,
Which order you need to get the output. If it is alphabetical order why dont
you try ORDER BY clause.
Incase if you cant use order by clause then , create a CLUSTERED index on
station column
Thanks
Hari
SQL Server MVP
"ichor" <ichor@.hotmail.com> wrote in message
news:OzQacBbkFHA.3380@.TK2MSFTNGP12.phx.gbl...
> hi i have the following
> create table #station_mix
> (
> station varchar(100)
> --several other columns
> )
> insert into #station_mix values('qtq')
> insert into #station_mix values('nws')
> insert into #station_mix values('stw')
> insert into #station_mix values('tcn')
> insert into #station_mix values('gtv')
> when i do a select * from #station_mix i want the output to be in the
> order.
> 'tcn', 'gtv', 'qtq', 'nws','stw'
> how do i do this?
> Thanks
> ICHOR
>
>|||Any reason you want this order?
One way is
Select station from #station_mix
Order by case when station ='tcn' then 1 when station ='gtv' then 2
when station ='qtq' then 3 when station ='nws' then 4
when station ='stw' then 5|||ichor wrote:

> when i do a select * from #station_mix i want the output to be in the
> order.
> 'tcn', 'gtv', 'qtq', 'nws','stw'
> how do i do this?
> Thanks
> ICHOR
You could add a "Priority" column to #station_mix, then Order By the
Priority column.
create table #station_mix
(
station varchar(100),
priority int
)
insert into #station_mix values('qtq', 3)
insert into #station_mix values('nws', 4)
insert into #station_mix values('stw', 5)
insert into #station_mix values('tcn', 1)
insert into #station_mix values('gtv', 2)
select station from #station_mix order by priority
Ben|||Hi
I can't see that these are in any specific order, is there another column
that determines this order? If not try
SELECT station
FROM (
SELECT station, CASE station WHEN 'tcn' THEN 1
WHEN 'gtv' THEN 2
WHEN 'qtq' THEN 3
WHEN 'nws' THEN 4
WHEN 'stw' THEN 5
ELSE 6
END AS Orderby
FROM #station_mix ) A
ORDER BY Orderby
John
"ichor" wrote:

> hi i have the following
> create table #station_mix
> (
> station varchar(100)
> --several other columns
> )
> insert into #station_mix values('qtq')
> insert into #station_mix values('nws')
> insert into #station_mix values('stw')
> insert into #station_mix values('tcn')
> insert into #station_mix values('gtv')
> when i do a select * from #station_mix i want the output to be in the orde
r.
> 'tcn', 'gtv', 'qtq', 'nws','stw'
> how do i do this?
> Thanks
> ICHOR
>
>|||>Incase if you cant use order by clause then , create a CLUSTERED index on
>station column
Hari,
As you probably know, this will not always guarantee that the rows will be
returned in the
order of the Clustered Index.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Hari Pra" <hari_pra_k@.hotmail.com> wrote in message
news:%23YZ$9QbkFHA.1464@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Which order you need to get the output. If it is alphabetical order why
> dont you try ORDER BY clause.
> Incase if you cant use order by clause then , create a CLUSTERED index on
> station column
> Thanks
> Hari
> SQL Server MVP
>
> "ichor" <ichor@.hotmail.com> wrote in message
> news:OzQacBbkFHA.3380@.TK2MSFTNGP12.phx.gbl...
>|||Ichor,
In case you didn't get the message from the other replies: the only way
to guarantee a specific output order is to use the ORDER BY clause.
In the ORDER BY clause you can specify a column name (for example if you
want the output to be sorted alphabetically) or an expression (for
example a CASE .. WHEN expression, as demonstrated in Madhivanan's
reply). This will specify the values that are used for sorting.
In addition to that, you can specify a Collation and whether the result
should be sorted ASCending or DESCending. By default, the column's
default collation is used, or the database default collation, and the
result is sorted ascending. The collation determines the sorting rules,
for example whether the ordering should be case sensitive or not.
Checkout BOL for further details.
Hope this helps,
Gert-Jan
ichor wrote:
> hi i have the following
> create table #station_mix
> (
> station varchar(100)
> --several other columns
> )
> insert into #station_mix values('qtq')
> insert into #station_mix values('nws')
> insert into #station_mix values('stw')
> insert into #station_mix values('tcn')
> insert into #station_mix values('gtv')
> when i do a select * from #station_mix i want the output to be in the orde
r.
> 'tcn', 'gtv', 'qtq', 'nws','stw'
> how do i do this?
> Thanks
> ICHOR

Order a character field numercially

I have a table with code and description columns. The code column is a CHAR
(10) with values like:
1, 101, 15, DC1, etc.
This code is displayed in a drop down list and sorts as follows because it i
s a character field:
1
101
15
DC1
I need to have this drop down sorted in a manner that takes into account the
numeric order of the code, as follows:
1
15
101
DC1
The codes are industry standard codes, so I cannot tell users to enter 001,
015, 101, etc. The codes are alphanumeric in nature. There are hundreds of
codes, which makes it difficult for a user to scroll down to find the corre
ct code. Is there a way to
sort a character field in this manner? ThanksORDER BY CASE ISNUMERIC(column) WHEN 1 THEN CONVERT(INT, column) ELSE column
END
( Except note that there are some isNumeric exceptions...
http://www.aspfaq.com/show.asp?id=2390 )
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>|||you could try:
ORDER BY
CASE PATINDEX('[a-z]', LEFT(YourColumn, 1))
WHEN 0 THEN RIGHT(REPLICATE('0', 10) + RTRIM(YourColumn), 10)
ELSE YourColumn
END
This assumes that all codes with alphas begin with an alpha. If that's not
true, change it to:
ORDER BY
CASE PATINDEX('%[a-z]%', YourColumn)
WHEN 0 THEN RIGHT(REPLICATE('0', 10) + RTRIM(YourColumn), 10)
ELSE YourColumn
END
You also may have to compensate for a case-sensitive collation setting, and
possibly other issues... But this is a start, at least.
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>|||Don
CREATE TABLE TEST
(
COL VARCHAR(4)
)
GO
INSERT INTO TEST VALUES ('1')
INSERT INTO TEST VALUES ('101')
INSERT INTO TEST VALUES ('15')
INSERT INTO TEST VALUES ('DC1')
GO
SELECT * FROM TEST ORDER BY RIGHT('0000'+COL,4)
GO
DROP TABLE TEST
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>|||Yet another solution:
ORDER BY LEN(YourColumn), YourColumn
Hope this helps,
Gert-jan
Don Jones wrote:
> I have a table with code and description columns. The code column is a CH
AR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account t
he numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter 001, 015, 1
01, etc. The codes are alphanumeric in nature. There are hundreds of codes, which
makes it difficult for a user to scroll down to find the correct code. Is there a w
ay
to sort a character field in this manner? Thanks
(Please reply only to the newsgroup)

Order a character field numercially

I have a table with code and description columns. The code column is a CHAR(10) with values like:
1, 101, 15, DC1, etc.
This code is displayed in a drop down list and sorts as follows because it is a character field:
1
101
15
DC1
I need to have this drop down sorted in a manner that takes into account the numeric order of the code, as follows:
1
15
101
DC1
The codes are industry standard codes, so I cannot tell users to enter 001, 015, 101, etc. The codes are alphanumeric in nature. There are hundreds of codes, which makes it difficult for a user to scroll down to find the correct code. Is there a way to
sort a character field in this manner? Thanks
ORDER BY CASE ISNUMERIC(column) WHEN 1 THEN CONVERT(INT, column) ELSE column
END
( Except note that there are some isNumeric exceptions...
http://www.aspfaq.com/show.asp?id=2390 )
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>
|||you could try:
ORDER BY
CASE PATINDEX('[a-z]', LEFT(YourColumn, 1))
WHEN 0 THEN RIGHT(REPLICATE('0', 10) + RTRIM(YourColumn), 10)
ELSE YourColumn
END
This assumes that all codes with alphas begin with an alpha. If that's not
true, change it to:
ORDER BY
CASE PATINDEX('%[a-z]%', YourColumn)
WHEN 0 THEN RIGHT(REPLICATE('0', 10) + RTRIM(YourColumn), 10)
ELSE YourColumn
END
You also may have to compensate for a case-sensitive collation setting, and
possibly other issues... But this is a start, at least.
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>
|||Don
CREATE TABLE TEST
(
COL VARCHAR(4)
)
GO
INSERT INTO TEST VALUES ('1')
INSERT INTO TEST VALUES ('101')
INSERT INTO TEST VALUES ('15')
INSERT INTO TEST VALUES ('DC1')
GO
SELECT * FROM TEST ORDER BY RIGHT('0000'+COL,4)
GO
DROP TABLE TEST
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>
|||Yet another solution:
ORDER BY LEN(YourColumn), YourColumn
Hope this helps,
Gert-jan
Don Jones wrote:
> I have a table with code and description columns. The code column is a CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter 001, 015, 101, etc. The codes are alphanumeric in nature. There are hundreds of codes, which makes it difficult for a user to scroll down to find the correct code. Is there a way
to sort a character field in this manner? Thanks
(Please reply only to the newsgroup)

Order a character field numercially

I have a table with code and description columns. The code column is a CHAR(10) with values like
1, 101, 15, DC1, etc
This code is displayed in a drop down list and sorts as follows because it is a character field
10
1
DC
I need to have this drop down sorted in a manner that takes into account the numeric order of the code, as follows
1
10
DC
The codes are industry standard codes, so I cannot tell users to enter 001, 015, 101, etc. The codes are alphanumeric in nature. There are hundreds of codes, which makes it difficult for a user to scroll down to find the correct code. Is there a way to sort a character field in this manner? ThankORDER BY CASE ISNUMERIC(column) WHEN 1 THEN CONVERT(INT, column) ELSE column
END
( Except note that there are some isNumeric exceptions...
http://www.aspfaq.com/show.asp?id=2390 )
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>|||you could try:
ORDER BY
CASE PATINDEX('[a-z]', LEFT(YourColumn, 1))
WHEN 0 THEN RIGHT(REPLICATE('0', 10) + RTRIM(YourColumn), 10)
ELSE YourColumn
END
This assumes that all codes with alphas begin with an alpha. If that's not
true, change it to:
ORDER BY
CASE PATINDEX('%[a-z]%', YourColumn)
WHEN 0 THEN RIGHT(REPLICATE('0', 10) + RTRIM(YourColumn), 10)
ELSE YourColumn
END
You also may have to compensate for a case-sensitive collation setting, and
possibly other issues... But this is a start, at least.
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>|||Don
CREATE TABLE TEST
(
COL VARCHAR(4)
)
GO
INSERT INTO TEST VALUES ('1')
INSERT INTO TEST VALUES ('101')
INSERT INTO TEST VALUES ('15')
INSERT INTO TEST VALUES ('DC1')
GO
SELECT * FROM TEST ORDER BY RIGHT('0000'+COL,4)
GO
DROP TABLE TEST
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>|||Yet another solution:
ORDER BY LEN(YourColumn), YourColumn
Hope this helps,
Gert-jan
Don Jones wrote:
> I have a table with code and description columns. The code column is a CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter 001, 015, 101, etc. The codes are alphanumeric in nature. There are hundreds of codes, which makes it difficult for a user to scroll down to find the correct code. Is there a way to sort a character field in this manner? Thanks
--
(Please reply only to the newsgroup)

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.

Oracle and unicode

Since the .net oracle providers are the only ones that work in x64 it is extremely annoying that it insists on mapping ALL varchar columns in oracle to wstring. Is there a way to fix this? The oledb providers don't have this problem but I don't like being forced to use the 32 bit runtime as a workaround.I remember that this has already been discussed:
I was told, that all .NET providers return UNICODE text. You have to do an explicit data conversion with ALL your fields.

@.Donald Farmer: Since you've said, that when developing SSIS it was meant to avoid implicit data conversion due to performance issues you've had with DTS, I'm wondering how this paradigm copes with this?

@.Brent: Sorry I cannot help...|||

Sorry, I didn't see that post.

I'm just having a hard time explaining to my developers why there's 18 different workarounds to get oracle to work in the first place. The last thing they want to do is explicitly map 150 different columns.

|||

Brent Mills wrote:

Since the .net oracle providers are the only ones that work in x64 it is extremely annoying that it insists on mapping ALL varchar columns in oracle to wstring. Is there a way to fix this? The oledb providers don't have this problem but I don't like being forced to use the 32 bit runtime as a workaround.

i'm not sure if this will help, but openlink has a 64 bit ole db to obdc provider: http://uda.openlinksw.com/oledb/st/oledb-odbc-st/