March 14, 2009 at 3:12 am
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
March 14, 2009 at 3:33 am
Since you are doing a comparison of string data and not the numerical data, and in string comparisons the value "10 marla" comes before "8 marla" (or "10" comes before "8"). So, that's why you see such results. In order to do a numerical comparison, you first need to extract the numerical value from the text and then do the comparison.
--Ramesh
March 15, 2009 at 10:21 am
I'll give you a few hints here. The explanation above is correct.
Look at charindex/patindex to find the "marla" in your string. http://www.sqlservercentral.com/articles/Advanced+Querying/tamestrings2.asp/97/
Then use substring to grab the part before that and CAST it to a numeric.
Doing this in the WHERE clause with functions can slow your query, so if this is a big table, you might have slow queries. The only other alternative would be to properly split out the numeric values in the table. You could even use a computed column to do this.
March 18, 2009 at 10:50 am
maifs, something like this might work for you. I am assuming that all areas are of the same format, that is, there is a space between the last numerical digit and the 'm' of marla.
IF OBJECT_ID('ZTEST..tblPropertyDetail','u') IS NOT NULL
DROP TABLE tblPropertyDetail
CREATE TABLE tblPropertyDetail
(
City VARCHAR(50),
PropertyLocation VARCHAR(50),
Price BIGINT,
Area VARCHAR(50)
)
INSERT INTO tblPropertyDetail
SELECT 'MyTown','East',1000,'8 marla' UNION ALL
SELECT 'MyTown','East',2000,'9 marla' UNION ALL
SELECT 'MyTown','East',1000,'6 marla' UNION ALL
SELECT 'MyTown','East',1000,'10 marla' UNION ALL
SELECT 'OurTown','East',5000,'10 marla' UNION ALL
SELECT 'YourTown','West',1500,'5 marla' UNION ALL
SELECT 'HisTown','West',3000,'11 marla' UNION ALL
SELECT 'HerTown','West',4000,'6 marla'
GO
ALTER PROCEDURE dbo.AreaHome1
@city varchar (50),
@loc varchar (200),
@price1 bigint,
@price2 bigint
AS
BEGIN
SELECT DISTINCT City,PropertyLocation,Price,Area
FROM tblPropertyDetail
WHERE (CAST(SUBSTRING(area,1,PATINDEX('% marla',area)-1) AS INT) <=8)
AND(City = @city)AND(PropertyLocation = @loc) AND Price IN(@price1,@price2)
END
GO
EXEC AreaHome1'MyTown','East',1000,2000
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
March 18, 2009 at 2:51 pm
Steve Jones - Editor (3/15/2009)
I'll give you a few hints here. The explanation above is correct.Look at charindex/patindex to find the "marla" in your string. http://www.sqlservercentral.com/articles/Advanced+Querying/tamestrings2.asp/97/
Then use substring to grab the part before that and CAST it to a numeric.
It's not that simple.
Couple of blocks from my place there are these house numbers:
23, 1/23, 2/23, 3/23
How they suppose to be sorted?
Right no unit number first, then unit number ASC.
So, you need to split address to the parts and then sort according to the parts precedence.
And because people invented all sorts of funny address formats the task is quite non-trivial.
Such problems are the price cowboys have to pay for not following boring academic normalization rules.
_____________
Code for TallyGenerator
March 19, 2009 at 11:55 am
http://en.wikipedia.org/wiki/Marla_(unit)
The marla is a traditional unit of area in Pakistan, India and Bangladesh. The marla was standardized under British rule to be equal to the square rod, or 272.25 square feet, 30.25 square yards, or 25.2929 square metres.
...but why this numeric value of area is being stored in a character string rather than a numeric type is beyond me unless its a staging table.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply