Call Columns to Outer select

Question

Hi,

I am writing below Query required to call the high lighted columns to Outer select from inner Query.But i am getting error invalid column.Please suggest here i am new to sql. How to call those columns to outer Query.

Thanks.

Below is the code:

select PS_ID,
Pn_Key,
Ip_Ref,
Related_Ip_Ref,
UEL_Id,
P_SubClass2
from
(select
'BDX'+'-'+CAST(a.BinderContractID as varchar(50)) PS_ID,
CAST(a.BinderContractID as varchar(50)) Pn_Key,
a.BinderContractReference Ip_Ref,
a.ContractReference Related_Ip_Ref,
a.UWYear Year_Of_Account,
--Sub_InPol.polId, 'Subscribe'+'-'+CAST(Sub_InPol.stpnm As varchar(50)) UEL_Id ,
--'Subscribe'+'-'+CAST(SubClass2.cd As varchar(50)) P_SubClass2,
'BDX' Source_System
from
landing.bdx_tbl_BinderContract a
WHERE a.load_skey = 14507 ) a

Left Join (

select InPol.PolId PolId,InPol.StpNm StpNm from (
(
SELECT PMain.PolId PolId,PMain.WtnDt,PMain.BkrNo,PMain.CtcNm,PMain.Uwr,PMain.EstSgnPctg,PMain.WtnOrdPctg,PMain.WtnLn,PMain.ID
FROM DBO.Sub_PoMain PMain where Load_SKey = 14517) SubPolMain

Left Join (
SELECT SInpol.PolId PolId,case when SInpol.UnitPsu='RTH'and SInpol.StpNm is null then'1414' else SInpol.StpNm end StpNm
FROM DBO.Sub_In SInpol WHERE SInpol.PolId is not null
and SInpol.Load_SKey= 14517
UNION ALL
select PolLyr.PolId PolId, CAST('' As VARCHAR(50)) StpNm

FROM DBO.Sub_Poly PolLyr
where PolLyr.Load_SKey=14517

) InPol ON InPol.PolId = SubPolMain.PolId )

Left Join

(select Polid,cd from DBO.Sub_PolACd where Ty='SUBCLASS2' and Load_SKey= 14517) SubClass2
ON SubPolMain.PolId = SubClass2.PolId

) Sub_InPol ON a.Ip_Ref = Sub_InPol.PolId

Answer 1

You get this error because the SubClass2 alias is defined inside the derived table Sub_InPol, and thus this alias is only visible inside this alias.

Furthermore, you are making the reference to SubClass2 inside the derived table a, and in a derived table, you are blind to what's outside of it.

I can't say how you should rewrite your query, because I don't know your tables of your business logic. I like to give one piece of advice, though: rather than using a number of nested derived tables, which are easy to get lost in with all the parentheses matching etc, use common table expressions (CTE) instead. I find that with CTEs that my queries becomes more structured, and easier to work with.

Answer 2

But i am getting error invalid column.

And which column name is mentioned in the error message?

Your unformated code is hard to read and so difficult to guess it.