Problem With Query

[wroot]

Hello Everyone,
I have strugled for days trying to figure out how i can get this query to run right. I have tried everything i can think of to get it to work.

Th query will run but for one characteristic i.e. bedroom, bathroom, but try and run a query that has both of these characteristics in it i get 0 for both. I know where the problem is, these characteristics are defined by the elemline and when the code generates the sql statement the elemline cannot be a 3 and a 4 at the same time so i get 0. My question is: is there a way around this i have not thought of, other than running seperate queries. These queries are lump together in the interest in speed.

Here is the Query

SELECT DISTINCTROW linkLegalLand.Key,

IIF([linkBuilding].[Model] IN(10), iif(([linkBuildingChars].[CharType]=2) and
([linkBuildingChars].[ElemLine]=3),val([linkBuildingChars].[Data]),0),

IIF([linkBuilding].[Model] IN(1, 90, 99), iif(([linkBuildingChars].[CharType]=2) and
([linkBuildingChars].[ElemLine]=4),val([linkBuildingChars].[Data]),0),0)) AS BATHROOMSData,

IIF([linkBuilding].[Model] IN(10), iif(([linkBuildingChars].[CharType]=2) and
([linkBuildingChars].[ElemLine]=2),val([linkBuildingChars].[Data]),0),

IIF([linkBuilding].[Model] IN(1, 90, 99), iif(([linkBuildingChars].[CharType]=2) and
([linkBuildingChars].[ElemLine]=3),val([linkBuildingChars].[Data]),0),0)) AS BEDROOMSData,

linkLegalLand.MAPAD, linkBuilding.Model, linkBuildingChars.CharType

FROM linkBuildingChars RIGHT JOIN (linkBuilding RIGHT JOIN (linkLegalLand) ON
[linkBuilding].[Key] = [linkLegalLand].[Key] AND [linkBuilding].[Year] = [linkLegalLand].[Year]) ON
[linkBuildingChars].[Key] = [linkBuilding].[Key] AND [linkBuildingChars].[Year] = [linkBuilding].[Year]
AND [linkBuildingChars].[BldgNumber] = [linkBuilding].[BldgNumber] and

(IIF([linkBuilding].[Model] IN(10),
iif(([linkBuildingChars].[CharType]=2) and ([linkBuildingChars].[ElemLine]=3),-1,0),
IIF([linkBuilding].[Model] IN(1, 90, 99), iif(([linkBuildingChars].[CharType]=2) and ([linkBuildingChars].[ElemLine]=4),-1,0),0))=-1+0*[linkBuilding].[Model])

and

(IIF([linkBuilding].[Model] IN(10),
iif(([linkBuildingChars].[CharType]=2) and ([linkBuildingChars].[ElemLine]=2),-1,0),
IIF([linkBuilding].[Model] IN(1, 90, 99), iif(([linkBuildingChars].[CharType]=2) and ([linkBuildingChars].[ElemLine]=3),-1,0),0))=-1+0*[linkBuilding].[Model] )



WHERE [linkLegalLand].[Year] = 2003 ORDER BY [linkLegalLand].[MAPAD]


Thanks in advance
Dave

 

[ldandy]

Hi Dave,

Your query is quite complex and not so easy to follow, however I have a few suggestions. One is to move all conditions from the FROM clause that are not related to table joining (links) to the WHERE clause. And the other: if you say it works for elimline=3 and also for elimline=4 but not for both, you can try making a UNION query of two SELECT's (one for elimline=3 and the other for elimline=4). This way you can eliminate a few IIF's, the query should work and maybe the performance will not be that bad.

Danny.