Plz point error in query

Posted by Sushant under Sql Server on 6/20/2013 | Points: 10 | Views : 791 | Status : [Member] | Replies : 5
Hi all

following is my query

select
pd.PropId as PropId,
pd.LocId as LocId,pd.PropNature as [PropNature],
lm.LocationName as LocationName,lm.Latitude as [Latitude],lm.Longitude as [Longitude],
CASE WHEN pd.Shop=1 THEN 'Shop ' else '' END +
CASE WHEN pd.Office=1 THEN 'Office ' else '' END +
CASE WHEN pd.ATM=1 THEN 'ATM ' else '' END +
CASE WHEN pd.Godown=1 THEN 'Godown ' else '' END +
CASE WHEN pd.Basement=1 THEN 'BMT ' else '' END +
CASE WHEN pd.Hall=1 THEN 'Hall ' else '' END +
CASE WHEN pd.Cabin=1 THEN 'Cabin ' else '' END +
CASE WHEN pd.Floor=1 THEN 'Floor ' else '' END +
CASE WHEN pd.ShowRoom=1 THEN 'Showroom ' else '' END +
CASE WHEN pd.Ind_House=1 THEN 'Ind. House ' else '' END +
CASE WHEN pd.Flat_Apartment=1 and DevId>0 THEN 'Builder Flat ' else '' END +
CASE WHEN pd.Flat_Apartment=1 and DevId=0 THEN 'Flat/Apartment ' else '' END as PropType,
Pd.NoOfFloor as [NoOfFloors],
case when FL_BMT=1 then 'BMT ' else '' end +
case when FL_GF=1 then 'GF ' else '' end +
case when FL_1F=1 then '1F ' else '' end +
case when FL_Mezz=1 then 'MZN ' else '' end +
case when FL_2F=1 then '2F ' else '' end +
case when FL_3F=1 then '3F ' else '' end +
case when FL_4F=1 then '4F ' else '' end +
case when FL_5F=1 then '5F ' else '' end +
case when FL_TRR=1 then 'TRR ' else '' end as [Floors],
convert(varchar(50),pd.Total_Area) + '' + pd.postfix as [TotalArea],
pd.Address_Visible as [Address],
pd.PropFor as PropFor,
convert(varchar(50),pd.Budget) + ' ' + pd.BudgetPostFix as [Budget],
pd.Bedroom as [Bedroom],bm.BrCaption as BrCaption,
(if not exists(select ImgUrl from PropertyImages where PropId=31)
begin
(select top(1) 'notavailable.jpg' as ImgUrl from PropertyImages)
end
else
begin
(select Imgurl as ImgUrl from PropertyImages where PropId=31)
end ) as ImgUrl
from
PropertyDetails as pd
left outer join LocationMaster as lm on pd.LocId=lm.LocID
left outer join BedroomMaster as bm on pd.Bedroom=bm.BrID
where pd.Flag=1 and pd.PropId=31

following is my error
Msg 156, Level 15, State 1, Line 32
Incorrect syntax near the keyword 'if'.
Msg 102, Level 15, State 1, Line 39
Incorrect syntax near ')'.



Plz Point error in above query
Provided i am using above query in writing stored procedure so that to check i am providing propId=31 for check and "notavailableImg" is in table
thx

Sushat


Responses

Posted by: Bageshkumarbagi on: 6/21/2013 [Member] Starter | Points: 25

Up
0
Down
Hi,

What error u are getting.


Thanks,
Bagesh Kumar Singh

Thanks,
Bagesh Kumar Singh
Mark as answer if you have been satisfied

Sushant, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Niladri.Biswas on: 6/21/2013 [Member] Platinum | Points: 25

Up
0
Down
The Error is in

(if not exists(select ImgUrl from PropertyImages where PropId=31) 

begin
(select top(1) 'notavailable.jpg' as ImgUrl from PropertyImages)
end
else
begin
(select Imgurl as ImgUrl from PropertyImages where PropId=31)
end ) as ImgUrl


you cannot use if inside a select clause.

Best Regards,
Niladri Biswas

Sushant, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Niladri.Biswas on: 6/21/2013 [Member] Platinum | Points: 25

Up
0
Down
Try

Declare ImgUrl AS Varchar(100)

if not exists(select ImgUrl from PropertyImages where PropId=31)
begin
select ImgUrl = top(1) 'notavailable.jpg' as ImgUrl from PropertyImages
end
else
begin
select ImgUrl = Imgurl from PropertyImages where PropId=31
end

select
pd.PropId as PropId,
pd.LocId as LocId,
pd.PropNature as [PropNature],
lm.LocationName as LocationName,
lm.Latitude as [Latitude],
lm.Longitude as [Longitude],
CASE WHEN pd.Shop=1 THEN 'Shop ' else '' END +
CASE WHEN pd.Office=1 THEN 'Office ' else '' END +
CASE WHEN pd.ATM=1 THEN 'ATM ' else '' END +
CASE WHEN pd.Godown=1 THEN 'Godown ' else '' END +
CASE WHEN pd.Basement=1 THEN 'BMT ' else '' END +
CASE WHEN pd.Hall=1 THEN 'Hall ' else '' END +
CASE WHEN pd.Cabin=1 THEN 'Cabin ' else '' END +
CASE WHEN pd.Floor=1 THEN 'Floor ' else '' END +
CASE WHEN pd.ShowRoom=1 THEN 'Showroom ' else '' END +
CASE WHEN pd.Ind_House=1 THEN 'Ind. House ' else '' END +
CASE WHEN pd.Flat_Apartment=1 and DevId>0 THEN 'Builder Flat ' else '' END +
CASE WHEN pd.Flat_Apartment=1 and DevId=0 THEN 'Flat/Apartment ' else '' END as PropType,
Pd.NoOfFloor as [NoOfFloors],
case when FL_BMT=1 then 'BMT ' else '' end +
case when FL_GF=1 then 'GF ' else '' end +
case when FL_1F=1 then '1F ' else '' end +
case when FL_Mezz=1 then 'MZN ' else '' end +
case when FL_2F=1 then '2F ' else '' end +
case when FL_3F=1 then '3F ' else '' end +
case when FL_4F=1 then '4F ' else '' end +
case when FL_5F=1 then '5F ' else '' end +
case when FL_TRR=1 then 'TRR ' else '' end as [Floors],
convert(varchar(50),pd.Total_Area) + '' + pd.postfix as [TotalArea],
pd.Address_Visible as [Address],
pd.PropFor as PropFor,
convert(varchar(50),pd.Budget) + ' ' + pd.BudgetPostFix as [Budget],
pd.Bedroom as [Bedroom],bm.BrCaption as BrCaption,
ImgUrl
from PropertyDetails as pd
left outer join LocationMaster as lm on pd.LocId=lm.LocID
left outer join BedroomMaster as bm on pd.Bedroom=bm.BrID
where pd.Flag=1 and pd.PropId=31


Best Regards,
Niladri Biswas

Sushant, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: NARAAYANAN on: 6/21/2013 [Member] Starter | Points: 25

Up
0
Down
hI,


YOUR CODE
(if not exists(select ImgUrl from PropertyImages where PropId=31)


TRY THIS

(if not exists(select ImgUrl from PropertyImages where PropId=31))


Regards,
Lakshmi Naraayanan.S
http://dotnettechrocks.blogspot.in/
http://abaprocker.blogspot.com/

Sushant, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sushant on: 6/21/2013 [Member] Starter | Points: 25

Up
0
Down
hi Mr. Niladri.Biswas
when i am executing above query giving following error

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'top'.

when i am removing "AS" giving following error

Msg 155, Level 15, State 2, Line 1
'Varchar' is not a recognized CURSOR option.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'top'.


Plz reply
thanks

Sushat

Sushant, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response