Why does this IIF not work

Transact-SQL
1

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
2

are these columns??

IIF(Alpha IS NULL, Beta, Alpha)

3

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

4

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
5

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
6

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