Select Query - Cast [SQL Server 2008]

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

Select Query - Cast [SQL Server 2008]

Author

Topic

paramu
Posting Yak Master

151 Posts

Posted - 2011-07-03 : 07:21:33

I can't understand my mistake...

MyQuery:-
========

select cast(sl_no=row_number() over (order by len(door_no),door_no,(0+emp_city)) as varchar(10)) ,v.street_name,v.road_name,v.city_name from emplyee_details...

Thanks & Regards
PARAMU

Paramu @ PARANTHAMAN

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-03 : 07:41:33

What is the fault/error?

N 56°04'39.26"
E 12°55'05.63"

paramu
Posting Yak Master

151 Posts

Posted - 2011-07-03 : 07:52:39

incorrect syntax near '='

Thanks

Paramu @ PARANTHAMAN

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-03 : 08:01:55

select cast(sl_no=row_number() over (order by len(door_no),door_no,(0+emp_city)) as varchar(10))
select cast(row_number() over (order by len(door_no),door_no,(0+emp_city)) as varchar(10)) AS sl_no

N 56°04'39.26"
E 12°55'05.63"

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-07-03 : 17:15:49

Why do you think that it is good coding to format data for display in the database? Why do you want to throw out ~50 years of tiered architecture? Why do think that a table should have not have a collective names – Employee versus Personnel?

The kludge is to replace the old Sybase = with the ANSI/ISO Standard “<expr> AS <data element name>” instead, so it is no this mess. Here is the kludge:

SELECT CAST(ROW_NUMBER()
OVER (ORDER BY LEN(door_no), door_no, (0 + emp_city_name))
AS VARCHAR(10)) AS display_formatted_sl_no,
V.street_name, V.road_name, V.city_name
FROM Personnel_Details...

The right answer is to let the front end format your data:

SELECT V.something_door_no, V.emp_city_name,
V.street_name, V.road_name, V.city_name
FROM Personnel_Details...

Since we have no DDL (basic Netiquette, please) it is hard to see what sl_no means and why it is derived and not a data element.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources