>Hi,
>
>I have this SQL:
>
>
select dbo.claimitemstatus(clm_forwarded
> ,cit_received
> ,(select com_overduedays
> from companies
> inner join Clients on cli_comfk = com_pk
> inner join Claims subclaims on clm_clifk = cli_pk
> where subclaims.clm_pk = claims.clm_pk)) as statusfk
> from ClaimItems inner join Claims on cit_clmfk = clm_pk
> having statusfk = 3
>
>But I get a syntax error:
>
>Msg 207, Level 16, State 1, Line 9
>Invalid column name 'statusfk'.
>
>How can I refer to the statusfk field?
You have one extra ) Remove it before as
In addition, you can not use this field directly in a query this way.
The better way to re-write is to introduce this field with CROSS APPLY. In this case it will be available for HAVING clause.
If it's not broken, fix it until it is.
My Blog