Case function in a Join
-
I am trying to use a case in a join (Sql Server 2005 - T-Sql)
I get the error
Incorrect syntax near '='
A section of the sql used is
select
*
from
paf
inner join full_address_indexed2 fav
on
paf.new_postcode = fav.new_postcode
AND
(
CASE
WHEN ISNULL(paf.new_housenumber,'') <> ''
AND ISNULL(new_flat,'') = ''
THEN paf.new_housenumber = fav.building_no
AND ISNULL(fav.sub_building_name,'') = ''
..................
..................
..................
Any suggestions?
Thanx
-
To answer your immediate question:
A case when statement must be of form in your condition:
case when <condition> then <value>
when <condition> then <value>
else <value> end
The point is that the whole "case when" statement will return a value (1, 2, 'bill gates', or whatever). In your conditional you must check the value returned by the whole case when statement against some other value.
To answer what you need:
Convert the case when to something like:
(
(ISNULL(paf.new_housenumber,'') = ''
or ISNULL(new_flat,'') = '')
or
(ISNULL(paf.new_housenumber,'') <> ''
AND ISNULL(new_flat,'') = ''
and paf.new_housenumber = fav.building_no
AND ISNULL(fav.sub_building_name,'') = '')
)
I might consider ways to optimize this query in some way.
Russel Loski, MCSE Business Intelligence, Data Platform
-
I don't understand what you are trying to do here:
CASE WHEN ISNULL(paf.new_housenumber,'') <> ''
AND ISNULL(new_flat,'') = ''
THEN paf.new_housenumber = fav.building_no
AND ISNULL(fav.sub_building_name,'') = ''
Why do you use ISNULL(paf.newhousenumber,'') and ISNULL(new_flat,'') = ''?
Why do you just use new_flat is null or LEN(new_flat) = 0
CASE WHEN paf.newhousenumber IS NOT NULL AND new_flat is NULL
THEN paf.new_housenumber = fav_building_no
I don't think you can use 'AND' in the "THEN"
-
The reason i need the case is to prioritse the join.
eg when housenumber is not empty AND flat is empty match on housenumber AND flat where possible. But if that isn't satisfied then match on housenumber only etc....
Any further idea's?
Thanx!
-
It is difficult to tell what you want and which columns are meant to match. In future please follow the instructions at http://www.aspfaq.com/etiquette.asp?id=5006
You may want to do something like the following:
SELECT *
FROM paf P
JOIN full_address_indexed2 F
ON P.new_postcode = F.new_postcode
AND ISNULL(P.new_housenumber, '') =
CASE
WHEN LEN(ISNULL(P.new_housenumber, '')) > 0 AND LEN(ISNULL(F.building_no, '')) > 0
THEN F.building_no
ELSE ISNULL(P.new_housenumber, '')
END
AND ISNULL(P.new_flat, '') =
CASE
WHEN LEN(ISNULL(P.new_flat, '')) > 0 AND LEN(ISNULL(F.sub_building_name, '')) > 0
THEN F.sub_building_name
ELSE ISNULL(P.new_flat, '')
END
-
When you get into nested CASE statements in a Join, while syntacically correct, it can often become a code maintenance nightmare. Who's going to know what that logic is all about 2 years from now when it needs to be altered ?
Take your data in "paf" and build a derived table around it that sets indicator flags, then join to it, using meaningful indicator column names to explain the logic.
Select *
from full_address_indexed2 fav
Inner Join
-- Start derived table
(
Select *,
Case
When new_housenumber Is Null Then 'N' Else 'Y'
End As HasHouseNumber,
Case
When new_flat Is Null Then 'N' Else 'Y'
End As IsNewFlat,
etc,
etc
From paf
) dtpaf
-- Now join to the derived table using the indicator flags
-- to express the join logic
on (dtpaf.new_postcode = fav.new_postcode And (
(HasHouseNumber = 'Y' And
IsNewFlat = 'N' And
paf.new_housenumber = fav.building_no)
Or
(HasSubBuildingName = 'N' And etc etc etc
 
-
select t1.* from t1
join t2
on ... and t1.HouseNumber=t2.HouseNumber and ('' in (t1.Flat,t2.Flat) or t1.Flat=t2.Flat)
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be. -
This part is wrong (syntax)..
inner join full_address_indexed2 fav
on
paf.new_postcode = fav.new_postcode
AND
(
CASE
WHEN ISNULL(paf.new_housenumber,'') <> ''
AND ISNULL(new_flat,'') = ''
THEN paf.new_housenumber = fav.building_no
AND ISNULL(fav.sub_building_name,'') = ''
It should be something like this
on paf.new_postcode = fav.new_postcode
AND
(
(paf.new_housenumber =
CASE
WHEN (ISNULL(paf.new_housenumber,'') <> '' )
AND (ISNULL(new_flat,'') = '' )
THEN fav.building_no END)
AND
(ISNULL(fav.sub_building_name,'') =
CASE WHEN (ISNULL(paf.new_housenumber,'') <> '' )
AND (ISNULL(new_flat,'') = '' )
THEN '' END)
However, this still does not satisfy your condition (when housenumber is not empty AND flat is empty match on housenumber AND flat where possible. But if that isn't satisfied then match on housenumber only).
Your condition is a bit vague.
when housenumber is not empty AND flat is empty match on housenumber AND flat where possible -- flat is empty. which column is to be matched with flat?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply