Oracle DBA to learn MS SQL: numbers

Showing posts with label numbers. Show all posts

Wednesday, March 28, 2012

Order by problem

Hi,
How can I order the column in the correct order if my numbers are in string
fields? I am now getting
1
11
10
etc...
Regards,you could always convert/CAST the field in to numerics and sort on this
column?
"EDom" <technical@.peoplewareindia.com> wrote in message
news:eEWcaSpwFHA.720@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How can I order the column in the correct order if my numbers are in
string
> fields? I am now getting
> 1
> 11
> 10
> etc...
> Regards,
>
>|||Hi
CREATE TABLE #Test
(
col VARCHAR(10)
)
INSERT INTO #Test VALUES ('1')
INSERT INTO #Test VALUES ('11')
INSERT INTO #Test VALUES ('10')
SELECT * FROM #Test ORDER BY col ASC
"EDom" <technical@.peoplewareindia.com> wrote in message
news:eEWcaSpwFHA.720@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How can I order the column in the correct order if my numbers are in
> string
> fields? I am now getting
> 1
> 11
> 10
> etc...
> Regards,
>
>|||I f you are sure that only numeric data is inserted you could cast this
as follow to an INT (or any other numeric type)
Create Table #temp
(
Col varchar(10)
)
INSERt INTO #Temp
Select '1'
INSERt INTO #Temp
Select '11'
INSERt INTO #Temp
Select '10'
Select * from #Temp order by col
Select * from #Temp order by CAST(col AS INT)
Drop table #temp
HTH, Jens Suessmeyer.|||I f you are sure that only numeric data is inserted you could cast this
as follow to an INT (or any other numeric type)
Create Table #temp
(
Col varchar(10)
)
INSERt INTO #Temp
Select '1'
INSERt INTO #Temp
Select '11'
INSERt INTO #Temp
Select '10'
Select * from #Temp order by col
Select * from #Temp order by CAST(col AS INT)
Drop table #temp
HTH, Jens Suessmeyer.|||Ricky
Yes , but what if he has a literal characters in the column as well. CAST
conversion will fail
"Ricky" <MSN.MSN.com> wrote in message
news:uzx3lXpwFHA.2880@.TK2MSFTNGP10.phx.gbl...
> you could always convert/CAST the field in to numerics and sort on this
> column?
> "EDom" <technical@.peoplewareindia.com> wrote in message
> news:eEWcaSpwFHA.720@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> How can I order the column in the correct order if my numbers are in
> string
>> fields? I am now getting
>> 1
>> 11
>> 10
>> etc...
>> Regards,
>>
>|||Hi,
I do have char attached to the numbers.
A1, A11, A10, A11B, A21C
etc
Regards,
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eK7ROcpwFHA.3556@.TK2MSFTNGP12.phx.gbl...
> Ricky
> Yes , but what if he has a literal characters in the column as well. CAST
> conversion will fail
>
> "Ricky" <MSN.MSN.com> wrote in message
> news:uzx3lXpwFHA.2880@.TK2MSFTNGP10.phx.gbl...
> > you could always convert/CAST the field in to numerics and sort on this
> > column?
> >
> > "EDom" <technical@.peoplewareindia.com> wrote in message
> > news:eEWcaSpwFHA.720@.TK2MSFTNGP10.phx.gbl...
> >> Hi,
> >>
> >> How can I order the column in the correct order if my numbers are in
> > string
> >> fields? I am now getting
> >> 1
> >> 11
> >> 10
> >> etc...
> >>
> >> Regards,
> >>
> >>
> >>
> >
> >
>|||Good Point Uri!!!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eK7ROcpwFHA.3556@.TK2MSFTNGP12.phx.gbl...
> Ricky
> Yes , but what if he has a literal characters in the column as well. CAST
> conversion will fail
>
> "Ricky" <MSN.MSN.com> wrote in message
> news:uzx3lXpwFHA.2880@.TK2MSFTNGP10.phx.gbl...
> > you could always convert/CAST the field in to numerics and sort on this
> > column?
> >
> > "EDom" <technical@.peoplewareindia.com> wrote in message
> > news:eEWcaSpwFHA.720@.TK2MSFTNGP10.phx.gbl...
> >> Hi,
> >>
> >> How can I order the column in the correct order if my numbers are in
> > string
> >> fields? I am now getting
> >> 1
> >> 11
> >> 10
> >> etc...
> >>
> >> Regards,
> >>
> >>
> >>
> >
> >
>|||Hi,
I dont get it correct
1A
11A
10A
this gives me the same result even I do sorting.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#YowWYpwFHA.664@.tk2msftngp13.phx.gbl...
> Hi
> CREATE TABLE #Test
> (
> col VARCHAR(10)
> )
> INSERT INTO #Test VALUES ('1')
> INSERT INTO #Test VALUES ('11')
> INSERT INTO #Test VALUES ('10')
> SELECT * FROM #Test ORDER BY col ASC
>
> "EDom" <technical@.peoplewareindia.com> wrote in message
> news:eEWcaSpwFHA.720@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > How can I order the column in the correct order if my numbers are in
> > string
> > fields? I am now getting
> > 1
> > 11
> > 10
> > etc...
> >
> > Regards,
> >
> >
> >
>|||Hi
SELECT * FROM #Test ORDER BY right('0000'+col,4) ASC
"EDom" <technical@.peoplewareindia.com> wrote in message
news:eFapS40wFHA.2656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I dont get it correct
> 1A
> 11A
> 10A
> this gives me the same result even I do sorting.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#YowWYpwFHA.664@.tk2msftngp13.phx.gbl...
>> Hi
>> CREATE TABLE #Test
>> (
>> col VARCHAR(10)
>> )
>> INSERT INTO #Test VALUES ('1')
>> INSERT INTO #Test VALUES ('11')
>> INSERT INTO #Test VALUES ('10')
>> SELECT * FROM #Test ORDER BY col ASC
>>
>> "EDom" <technical@.peoplewareindia.com> wrote in message
>> news:eEWcaSpwFHA.720@.TK2MSFTNGP10.phx.gbl...
>> > Hi,
>> >
>> > How can I order the column in the correct order if my numbers are in
>> > string
>> > fields? I am now getting
>> > 1
>> > 11
>> > 10
>> > etc...
>> >
>> > Regards,
>> >
>> >
>> >
>>
>

Monday, March 19, 2012

ORDBY BY difference between two numbers

I have a database table with people's ages in. I would like to order
the results of a query on this table by the differnce between their age
and a target age.
eg. database
userID, age
1, 27
2, 28
3, 29
4, 30
5, 31
6, 32
7, 33
I would like to do something like SELECT userID FROM database ORDER BY
difference_from 30 ASC, userId ASC
and get something like..
4,3,5,2,6,1,7 as the result
Any ideas?Hello,
Try something like this:
SELECT userID FROM database ORDER BY ABS(age-30), userId
Note that using an expression in an ORDER BY clause is not supported by
ANSI-SQL99. It is an extension created by Microsoft and later adopted
in SQL:2003.
Razvan|||Correction: using an expression in an ORDER BY clause is not supported
by
ANSI-SQL92, but it is supported in SQL99.
Razvan|||You dont need to order by the difference as long as you are comparing age to
a costant value. Subtracting the same value from the age will not change
the order.
10 -5 = 5
9 -5 = 4
8 -5 = 3
7 -5 = 2
6 -5 = 1
5 -5 = 0
Just order by age. If you want to display the difference in addition to
ordering by it, just select age - 30.
Select UserID, age - 30 as Difference
from SomeTable
order by age asc, userid asc
<webmaster@.ukescorts.com> wrote in message
news:1141729033.746642.173110@.j52g2000cwj.googlegroups.com...
> I have a database table with people's ages in. I would like to order
> the results of a query on this table by the differnce between their age
> and a target age.
> eg. database
> userID, age
> 1, 27
> 2, 28
> 3, 29
> 4, 30
> 5, 31
> 6, 32
> 7, 33
> I would like to do something like SELECT userID FROM database ORDER BY
> difference_from 30 ASC, userId ASC
> and get something like..
> 4,3,5,2,6,1,7 as the result
> Any ideas?
>

Friday, March 9, 2012

Oracle OLEDB drivers problem with Numbers

Hi All:

I am using oracle oledb drivers to write to a oledb destination.

if i give decimal values to decimal fields in the source table, i get the same in destination. But if the input is integers, in some cases, the value in the destination is different from that of source

Source Target

50 50.00
100 0.000
111 111.000
600 0.0000
520 20.00
178 178
4546.50 4546.50

I have Sql server SP2 9.0.3042 installed on my machine. Please let me know if theres something i am missing out.

Thanks,

Vipul

There is no such thing as an integer in Oracle. It's a NUMERIC(p,0) field. That is, it has no scale. SSIS doesn't support this at the moment. Instead, write your query such that you convert the "integer" field into a NUMERIC(p+1,1) field, or something like that. Then map to a decimal field in SSIS. From there, if you want integers out of the data, use a derived column to cast the values to integers.|||

Let me put it this way phil. Have you come across decimal data being changed from source to target without any transforms in between ? I was not correct in putting the question but the cause of my concern is that if the source has 500 how the target is getting it as 0.

Is there some problem in SSIS for this or this is oracle oledb driver problem?

|||Have you looked at the data with a data viewer to see what is contained there? You might need to recreate the OLE DB source.|||

ya i have viewed data with the data viewer before the oledb destination. Data is fine till data viewer. Theres something happening in oledb destination and thats why i suspect the drivers.

Also, the same behaviour is not happening on one of my other machine. The machine confguration of both the machine are same. I am executing the same pacakge from both the machines.

The version of software on both mahcine are:

SQL Server sp2 9.0.3042

Oracle 10g

Let me know your thoughts on this..

|||Is your destination SQL Server?

Have you looked at the advanced properties of the OLE DB Destination to ensure that the data types for all of the columns are correct?|||

Destination is Oracle.

And i have checked all the datatypes as per ur suggestion but still the problem exists.

|||I'm going to have to bow out as I don't have an Oracle instance to test with.

Wednesday, March 28, 2012

Order by problem

Order by problem

Order by problem

Monday, March 19, 2012

ORDBY BY difference between two numbers

Friday, March 9, 2012

Oracle OLEDB drivers problem with Numbers

Oracle DBA to learn MS SQL

Blog Archive

About Me