problem in query? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

problem in query?

Author

Topic

maifs
Yak Posting Veteran

57 Posts

Posted - 2009-03-14 : 05:14:09

i want to select the required city,location,price as the result of these an area should be displayed.

area must be less than '8 marla' but when i am trying this, it includes '10 marla'.

it should not be greater than to 8 marla.

i am trying this.

ALTER PROCEDURE dbo.AreaHome1
@city varchar (50),

@loc varchar (200),
@price1 bigint,

@price2 bigint
AS

BEGIN

SELECT DISTINCT Area
FROM tblPropertyDetail

WHERE (Area <='8 marla') AND(City = @city)AND(PropertyLocation = @loc) AND Price IN(@price1,@price2)
END

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-03-14 : 11:37:56

The problem is that in the where clause

WHERE (Area <='8 marla')

the inequality is evaluated using string sort rather than numeric sort. In string sort, the string '10' is less than the string '8'.

So you will need to do something else to get the results you want. What you can do depends on your data and how much control you have over the database.

For example: if you are generating the data, store the numeric portion in its own column as a number.

Another possibility is to extract the numeric portion of the column, cast it as an int, and then do the comparison.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources