using sql case - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

using sql case

Author

Topic

padmagvs
Starting Member

9 Posts

Posted - 2006-05-18 : 01:51:00

I need to get the data in sorted form from a column of type varchar but stores different datatypes(numeric,string,data) (only one kind of data) i tried to use case how ever its not working . even for numeric data it ineterprets as datetime . here is my sqal query

select case when DistinctValue in ('[NULL]', '[SPACES]','[EMPTY]') then null
when IsNumeric(DistinctValue) = 1 then cast(DistinctValue as float)
when IsDate(DistinctValue) = 1 then cast(DistinctValue as datetime)
else null
end as distValue, DistinctValue as actualVal from table order by 1

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-18 : 06:46:25

A case statement has a single output type. As you have a datetime as one of the outputs and that has the highest presidence it will convert everything to datetime.
try
select case when DistinctValue in ('[NULL]', '[SPACES]','[EMPTY]') then null
when IsNumeric(DistinctValue) = 1 then cast(cast(DistinctValue as float) as varchar(100))
when IsDate(DistinctValue) = 1 then cast(cast(DistinctValue as datetime) as varchar(100))
else null
end as distValue, DistinctValue as actualVal from table order by 1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-18 : 06:47:35

You know that you can get a convert float error with an isnumeric success?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

padmagvs
Starting Member

9 Posts

Posted - 2006-05-18 : 07:32:32

thanks for the response . I need to get data in sorted way here everything is again type casted to varchar
select case when DistinctValue in ('[NULL]', '[SPACES]','[EMPTY]') then null
when IsNumeric(DistinctValue) = 1 then cast(cast(DistinctValue as float) as varchar(100))
when IsDate(DistinctValue) = 1 then cast(cast(DistinctValue as datetime) as varchar(100))
else null
end as distValue, DistinctValue as actualVal from abc order by 1

result
distValue DistinctValue
10 10
11 11
12 12
13 13
14 14
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-18 : 07:47:27

should 1 jan 2005 come before or after 1000
Don't think you want to use a single case statement for your ordering.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

padmagvs
Starting Member

9 Posts

Posted - 2006-05-18 : 07:53:18

can you please suggest me how should i go about now

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-18 : 07:55:14

convert your DistinctValue to varchar(10) using style 112

convert(varchar(8), DistinctValue, 112)

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-18 : 07:56:36

Depends what you really want but maybe
select DistinctValue as actualVal
from table
order by
case when DistinctValue in ('[NULL]', '[SPACES]','[EMPTY]') then 1 else 2 end ,
when IsNumeric(DistinctValue) = 1 then cast(DistinctValue as float) end ,
when IsDate(DistinctValue) = 1 then cast(DistinctValue as datetime) end

instead of
IsNumeric(DistinctValue) = 1
consider
DistinctValue not like '%[^0-9]%'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

padmagvs
Starting Member

9 Posts

Posted - 2006-05-18 : 07:59:57

DistinctValue is varchar but data dumped in it may be numeric ,varchar,date(in date time) . But not mixed it may contain other datatypes bu pertaining to single type . have to get data in sorted from based on its typeaccordingly

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-18 : 09:00:23

As an explanation that doesn't really help.
Try my last query and see ifthat's what you want. If not then it should give you an idea of how to go about it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

padmagvs
Starting Member

9 Posts

Posted - 2006-05-19 : 05:04:49

I tried last query it works fine for numeric and dates but how ever not for string data i mean varchar ,char data . can you please suggest me where can i include some logic so that char data also gets populated in sorted way

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-19 : 06:13:15

Why did you store different type of information in a single column? You should have used seperate columns for different datatypes.See this example


select DistinctValue as actualVal 
from 
(
	select '[NULL]' as DistinctValue union all Select '345' union all 
	Select 'test' union all Select '63d45' union all Select 'Feb 10 2005' union all
	Select '[EMPTY]'
) T
order by
case when DistinctValue in ('[NULL]', '[SPACES]','[EMPTY]') then 1 else 2 end , 
case when DistinctValue not like '%[^0-9]%' then cast(DistinctValue as float) end ,
case when IsDate(DistinctValue) = 1 then cast(DistinctValue as datetime) end

If you think it is not displaying as per your expectation then post some sample data and the result you want. As Nigel said, IsNumeric is not reliable. Read this http://aspfaq.com/show.asp?id=2390

Madhivanan

Failing to plan is Planning to fail

padmagvs
Starting Member

9 Posts

Posted - 2006-05-19 : 07:09:52

Madhivanan,
thanks for your reply. i have some table which has column A of type varchar . say i have 10 such tables. each of these tables have columnA with diffren datatypes like string,numeric,date but given a table all columns have same datatype either numeric,string,or date but not mixed.
now i need to query table such that i get max,min value in column A . so i need to get data in sorted way . i can't say select columnA from table order by 1 because this column is of type varchar so gives data as per string sort . hope you got my real problem . i need to check the datatype of column and sure that all columns in table will be of same type . once i check cast it relavant type and present it in sorted way .

padmagvs
Starting Member

9 Posts

Posted - 2006-05-19 : 07:21:50

Sample data

table1
column A (varchar)
ZEROS
A
B
C
D
F
G
H
J
K
M
N
O
P
R
S
T
W
X
Y

i expect result is
A
B
C
D
F
G
H
J
K
M
N
O
P
R
S
T
W
X
Y
ZEROS

table2
column A

0877
1389
1622
1756
9901
9952
9999
so numeric sort is to be done

table 3
column A
1988-06-19 00:00:00
1988-08-09 00:00:00
1988-09-29 00:00:00
1988-11-19 00:00:00
1989-01-09 00:00:00
1989-03-01 00:00:00
1989-04-21 00:00:00
1989-06-11 00:00:00
1989-08-01 00:00:00
1989-09-21 00:00:00
1989-11-11 00:00:00
1990-01-01 00:00:00
1990-02-21 00:00:00
1990-04-13 00:00:00
1990-06-03 00:00:00
1990-07-24 00:00:00
1990-09-13 00:00:00
1990-11-03 00:00:00
1990-12-24 00:00:00
1991-02-13 00:00:00
1991-04-05 00:00:00
1991-05-26 00:00:00
1991-07-16 00:00:00
1991-09-05 00:00:00
1991-10-26 00:00:00
1991-12-16 00:00:00
1992-02-05 00:00:00
1992-03-27 00:00:00
1992-05-17 00:00:00
1992-07-07 00:00:00
1992-08-27 00:00:00
1992-10-17 00:00:00
1992-12-07 00:00:00
1993-01-27 00:00:00
1993-03-19 00:00:00
1993-05-09 00:00:00
1993-06-29 00:00:00
1993-08-19 00:00:00
1993-10-09 00:00:00
1993-11-29 00:00:00
1994-01-19 00:00:00
1994-03-11 00:00:00
1994-05-01 00:00:00
here should get dates in sorted way how ever here dates are alreday in table in sorted way

this is my scenario and result should be in sortred based on data in varchar

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-19 : 09:11:58

So do you want combine the sorted data into a single recordset?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-05-19 : 09:14:53

Doesn't look like that to me Madhi - the sample output says "Table1", "Table2", "Table3" ...

Kristen

padmagvs
Starting Member

9 Posts

Posted - 2006-05-20 : 08:09:01

no i don't want combined . i showed how data will be. i need to query such that i get column data in sorted way . i don't want combbine . i showed some sample tables that data i need to sort is stored as varchar but can be numeric, dates,string . but table will have data of type will be of only numeric,date or string

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-20 : 08:54:04

Your example shows that only one type of data are stored in each table. Right?


Select A from Table1 Order by A --If A has always characters
Select A from Table2 Order by Cast(A as int) --If A has always numbers
Select A from Table3 Order by cast(A as datetime) --If A has always dates

Madhivanan

Failing to plan is Planning to fail

padmagvs
Starting Member

9 Posts

Posted - 2006-05-20 : 14:25:21

i can apply query provided i know the datatypes in column i don't know the data type so have to check the data type and then cast it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources