Sub Query in Select List truncation error

[bajo71]

Hello,

I have an extremely long SQL statement that I'm trying to write in the Select List. Basically,it's the following repeated three times(with different tables each time). Problem is, I cannot get around having to write the entire Select statement per the conditional argument. If there was a way, this query would be much smaller.

Any ideas would be greatly appreciated.

IIf(Exists (SELECT TOP 1 [COST_PER_TREATMENT] FROM refZeroBalClinicLookup rzc WHERE [tblDSI].[FAC_CODE]=rzc.[CLINIC_ZERO_INS_CODE] AND [tblDSI].[PRIM_INS]=rzc.[CLINIC_ZERO_INS_NAME]),(SELECT TOP 1 [COST_PER_TREATMENT] FROM refZeroBalClinicLookup rzc WHERE [tblDSI].[FAC_CODE]=rzc.[CLINIC_ZERO_INS_CODE] AND [tblDSI].[PRIM_INS]=rzc.[CLINIC_ZERO_INS_NAME]),...

 

[PWise]

use left joins

Select * ,nz(rsz.COST_PER_TREATMENT,Sot.COST_PER_TREATMENT)
from tblDSI nz(COST_PER_TREATMENT,)
left join refZeroBalClinicLookup Rzc
on [tblDSI].[PRIM_INS]=rzc.[CLINIC_ZERO_INS_NAME]
left join SomeOtherTable SOT
on [tblDSI].[PRIM_INS]=Sot.[CLINIC_ZERO_INS_NAME]

what dose this give you