SQLTeam.com | Weblogs | Forums

Why does this IIF not work

Hi All

This is related to another query I have posted but is a significantly different question and thus worthy of its own thread.

I am using a Front End that allows most querying TSQL terms but not DEFINE, SET, UPDATE and similar.

I have a query as below that works fine until I insert that penultimate IIF statement in it, causing the front end to say 'Invalid column name 'Alpha', invalid column name 'Beta'.

Have I broken TSQL rules or might this be a limitation of the front end system I am using?

Thanks,

Tombo

select

addressline1, 

iif(charindex(',',addressline1)=0 OR charindex(',',addressline1) IS NULL, NULL, left(addressline1,charindex(',',addressline1)-1)) AS Alpha,

iif(charindex(' ',addressline1)=0 OR charindex(' ',addressline1) IS NULL, NULL, left(addressline1,charindex(' ',addressline1)-1)) AS Beta,

IIF(Alpha IS NULL, Beta, Alpha)

from contact

are these columns??

IIF(Alpha IS NULL, Beta, Alpha)

please provide sample data. not much one can do by just looking at your select statement

A column alias ( ... AS column_alias) is not by default available to the rest of the SQL statement.

But you can use a CROSS APPLY to make the alias names available, like this:


select
addressline1, 
Alpha,
Beta,
IIF(Alpha IS NULL, Beta, Alpha)
from contact
cross apply (
    select 
        iif(charindex(',',addressline1)=0 OR charindex(',',addressline1) IS NULL, 
            NULL, left(addressline1,charindex(',',addressline1)-1)) AS Alpha,
        iif(charindex(' ',addressline1)=0 OR charindex(' ',addressline1) IS NULL, 
            NULL, left(addressline1,charindex(' ',addressline1)-1)) AS Beta
) as alias1
1 Like

Thanks for your replies. My front end doesn't accept cross apply but I found a workaround.

My IIF clause was actually more complex than the example I gave [IIF(Alpha IS NULL, Beta, Alpha)]

IIF(Alpha IS NULL, Beta,
(IIF(Beta IS NULL, Alpha,
(IIF(LEN(Alpha)<LEN(Beta), Alpha, Beta)))))

select 
addressline1, 
IIF(iif(charindex(',',addressline1)=0 OR charindex(',',addressline1) IS NULL, NULL, left(addressline1,charindex(',',addressline1)-1)) IS NULL, iif(charindex(' ',addressline1)=0 OR charindex(' ',addressline1) IS NULL, NULL, left(addressline1,charindex(' ',addressline1)-1)), 
(IIF(iif(charindex(' ',addressline1)=0 OR charindex(' ',addressline1) IS NULL, NULL, left(addressline1,charindex(' ',addressline1)-1)) IS NULL, iif(charindex(',',addressline1)=0 OR charindex(',',addressline1) IS NULL, NULL, left(addressline1,charindex(',',addressline1)-1)), 
(IIF(LEN(iif(charindex(',',addressline1)=0 OR charindex(',',addressline1) IS NULL, NULL, left(addressline1,charindex(',',addressline1)-1)))<LEN(iif(charindex(' ',addressline1)=0 OR charindex(' ',addressline1) IS NULL, NULL, left(addressline1,charindex(' ',addressline1)-1))), iif(charindex(',',addressline1)=0 OR charindex(',',addressline1) IS NULL, NULL, left(addressline1,charindex(',',addressline1)-1)) , iif(charindex(' ',addressline1)=0 OR charindex(' ',addressline1) IS NULL, NULL, left(addressline1,charindex(' ',addressline1)-1)))))))
FROM CONTACT

@Tombo - Does your "front end" allow the use of PATINDEX()? If so, we might be able to greatly simplify this code.