October 14, 2004 at 2:37 pm
I need to know if there is any way to efficiently troubleshoot what is the problem when syntax error pops up when writting stored procedures. I am working with a CASE statement and can not find the problem but is there have gone line by line over and over...
select distinct b.sizegroup, count(*) 'Number Of Parcels', sum(b.acres) 'Total Acreage'
(
select acres,
SizeGroup,
case a.acres
when < 2.5 then Less than 2.5 Acres
when >= 2.5 and <4.75 then 2.5 - 4.75 Acres
when >= 4.75 and < 35 then 4.75 - 35 Acres
when >= 35 and < 50 then 35 - 50 Acres
when >= 50 then more than 50 acres
else ''
end
I am faily new at doing this type of sp.
thanks
Sheila
Sheila Conlon (getting back)
October 14, 2004 at 3:42 pm
There are a couple major issues.
1. Your string literals are missing quotation marks.
2. The style of case you used only supports simple constant equality comparisons. You are trying to use a shorthand boolean comparison. You need to convert it to use a full boolean comparison.
select distinct b.sizegroup, count(*) 'Number Of Parcels', sum(b.acres) 'Total Acreage'
(
select acres,
SizeGroup,
case when a.acres < 2.5 then 'Less than 2.5 Acres'
when a.acres < 4.75 then '2.5 - 4.75 Acres'
when a.acres < 35 then '4.75 - 35 Acres'
when a.acres < 50 then '35 - 50 Acres'
when a.acres >= 50 then 'more than 50 acres'
else ''
end
October 14, 2004 at 4:17 pm
Thank you for the help.
Sheila
Sheila Conlon (getting back)
October 15, 2004 at 9:34 am
Okay I understand
2. The style of case you used only supports simple constant equality comparisons. You are trying to use a shorthand boolean comparison. You need to convert it to use a full boolean comparison.
and I did find that I can convert from acres smallint to convert (float,acres) but now my case stmnt is not working correctly I am wondering if maybe for effieciency I should create 2 stored procedures one that does the conversion then nest the 2 stored procedures to do the calculations
Sheila Conlon (getting back)
October 15, 2004 at 10:14 am
I doubt 2 stored procedures would perform better. I also don't understand why using a convert would cause the the case to fail. Could you post the entire SQL statement so that I have a better understanding of what you are trying to perform?
October 15, 2004 at 1:35 pm
I was thinking to do a convert as the 'acres' is a smallint and I need the decimal placement.
select 'Unincorporated'
select distinct b.sizegroup, count(b.acres) 'Number Of Parcels', sum(b.acres) 'Total Acreage'
from
(select acres ,
SizeGroup =
case
when acres < 2.5 then 'Less than 2.5 Acres'
when acres >= 2.5 and acres < 4.75 then '2.5 - 4.75 Acres'
when acres >= 4.75 and acres < 35 then '4.75 - 35 Acres'
when acres >= 35 and acres < 50 then '35 - 50 Acres'
when acres >= 50 then 'more than 50 acres'
end
from
(select convert (float, acres),
case land.ar_code
when 1 then area/100
when 2 then area/43560
else ''
end
from stat left join land
on (stat.par = land.par)
inner join ent_top
on (stat.txd = ent_top.txd)
left outer join ent_id
on (ent_top.[id] = ent_id.[id])
where (ent_id.cat = 3) -- unincorporated
group by b.sizegroup)) b
Sheila Conlon (getting back)
October 15, 2004 at 2:54 pm
The reason the case statement failed is because "acres" no longer exists. You now select an expression instead of a column and expressions are not named automatically. Try:
( select convert(float, acres) AS Acres,
Also, the case statement for land.ar_code is not named either and is not used anywhere. If you intend to use it you will need to provide a name for it as well. If you do not intend to use it just delete it.
Also, while reading your SQL, I think the group by line is inside one too many parenthesis and should be as follows:
) group by b.sizegroup) b
I see no reason to create a second stored procedure for performance reasons. I believe it would actually hamper performance. If you need to obtain that resultset from a lot of places you can use a stored procedure to avoid re-coding the same select multiple times, but you will incur a performance penalty for it. I think it would be a very minor penalty, though.
October 15, 2004 at 4:03 pm
thank you very much and the only thing that seems to be not working now is after I converted to float it did give me a decimal, however it cut off (rounded) to the nearest whole number. I think I can work this out and thank you again for your help with this...
Sheila Conlon (getting back)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply