-1

on SQL Server 2012+ I am attempting to use IIF resulted column into another IIF as shown in SQL query below but it is gives me error invalid column. Count table has columns CountId and CountedQty,

SELECT  CountId, IIF(CountId<5, 3,2) as MyGroup, IIF([MyGroup]<5, 3,2) as GName FROM Count

Error shown for this query execution is Invalid column name 'MyGroup' if there any way to use IIF resulted column into another IIF in same query

similarly for following query it shows invalid column names errors

SELECT
  Count.StockCountId,
  Count.CountedQty,
  IIf(Count.CountedQty > 0, 1, IIf(Count.CountedQty = 0, 3, 2)) AS MyGroup,
  IIf([MyGroup] = 1, "Additional products counted", IIf([MyGroup]  =2, "Insufficient products counted", "Matched Count")) AS GName
FROM Count

it returns following errors

Invalid column name 'MyGroup'.
Invalid column name 'Additional products counted'.
Invalid column name 'MyGroup'.
Invalid column name 'Insufficient products counted'.
Invalid column name 'Matched Count'.

what changes are needed to use IIF resulted columns into above query to resolve errors and get intended output

Charlieface
  • 52,284
  • 6
  • 19
  • 43
Curious San
  • 15
  • 8

1 Answers1

0

You cannot reference a column alias like that, at the time query execution reaches select it does not yet exist.

You can use a derived table or CTE and then reference the column in an outer query, you can repeat the expression, or in SQL Server you could use apply.

Your logic seems a little odd however as you'll always return 3 since MyGroup is always < 5 in this example.

select CountId, Iif(MyGroup < 5, 3, 2) as GName
from [Count]
cross apply(values(Iif(CountId < 5, 3, 2)))c(MyGroup);
Stu
  • 30,392
  • 6
  • 14
  • 33
  • yes use of cross apply as mentioned in your example resolves errors in first query. pl let me how cross apply can be used for second query – Curious San May 29 '22 at 14:01