Query Error After Removing @Prompt in Derived Table

[former_member419024]

I am getting query error after removing @Prompt in Derived Table. I have no clue at all, only @Prompt is being removing but after validate the query I am getting query error.Below is my @prompt function wanted to remove.

@Prompt('Enter This Year','C','Year Others',mono,constrained)

'Year Others' is the List of Value which I created in IDT.

[Former Member]

What is the error that you are getting? Do you get the error when you parse the derived table or when you run a report that is based on the universe?

[former_member419024]

Sorry for late reply, internet connection is being cut out for few day because of flash flood.

I am getting the error after removing @prompt from derived table query

[former_member419024]

select * from
(select
LONG_NAME,OCCPDESC,MAJORCLSCODE,BRANCH_REPORTTO,
ZPRODUCER,RIDESC,REPTYPE,AGNTNUM,CNTTYPE,REPNUM,RNLTYPE,CLNTNUM,OCCPCODE, CLNTNAME,
CHDRNUM,PREMCL,TRANNO,BATCBRN,BATCACTYR,BATCACTMN,BATCTRCDE,SHORTDESC,GWP
from
(select K.LONG_NAME,J.OCCPDESC,I.MAJORCLSCODE,G.BRANCH_REPORTTO,F.ZPRODUCER,F.RIDESC,E.REPTYPE,E.AGNTNUM,E.CNTTYPE,E.REPNUM,E.RNLTYPE,C.CLNTNUM,C.OCCPCODE, C.CLNTNAME, A.CHDRNUM,A.PREMCL, A.TRANNO,A.BATCBRN, A.BATCACTYR,A.BATCACTMN,A.BATCTRCDE,L.SHORTDESC,sum((-1)*A.TRANAMT01 -A.TRANAMT03) as GWP
FROM [MY_DTA_MIS].[dbo].[VW_NSAP_T_ZBUW] A
inner join [MY_DTA_MIS].[dbo].[CDE_T_CLNT] C on A.CLNTNUM=C.CLNTNUM
inner join [MY_DTA_MIS].[dbo].[SAP_T_CHDR] E
ON A.CHDRNUM=E.CHDRNUM AND A.BATCACTYR=E.BATCACTYR and A.BATCACTMN=E.BATCACTMN AND A.TRANNO=E.TRANNO
inner join [MY_DTA_MIS].[dbo].[CDE_T_AGNT] F
on E.AGNTNUM = F.AGNTNUM
inner join [MY_DTA_MIS].[dbo].[VW_CDE_T_BRANCHGROUPING] G
on A.[New_BRANCH_CDE] = G.[New_BRANCH_CDE]
inner join [MY_DTA_MIS].[dbo].[CDE_T_T3640] H
on A.PREMCL = H.PREMCLS
inner join [MY_DTA_MIS].[dbo].[CDE_V_T7039] I
on H.B5MAJORCLS = I.MAJORCLSCODE
left join [MY_DTA_MIS].[dbo].[CDE_T_T3644] J
on C.OCCPCODE = J.OCCPCODE
inner join [MY_DTA_MIS].[dbo].[CDE_T_T4965] K
on F.ZPRODUCER = K.ZPRODUCER
inner join [MY_DTA_MIS].[dbo].[CDE_V_T1688] L
on A.BATCTRCDE = L.TRANSCODE
where (A.BATCTRCDE='T413' or (A.BATCTRCDE='T405'  and E.REPTYPE in (select REPLCODE from CDE_T_T3586)))and A.SACSCODE in ('FG', 'CO')
and A.BATCACTYR = @Prompt('Enter This Year','C','Year Others',mono,constrained)

group by K.LONG_NAME,J.OCCPDESC,I.MAJORCLSCODE,G.BRANCH_REPORTTO,F.ZPRODUCER,F.RIDESC,E.REPTYPE,E.AGNTNUM,E.CNTTYPE,E.REPNUM,E.RNLTYPE,C.CLNTNUM,
C.OCCPCODE,C.clntname,A.CHDRNUM,A.PREMCL,A.TRANNO,A.BATCBRN,A.BATCACTYR,A.BATCACTMN,A.BATCTRCDE,L.SHORTDESC)b)TY

full outer join

(select
LONG_NAMELY,OCCPDESCLY,MAJORCLSCODELY,BRANCH_REPORTTOLY,ZPRODUCERLY,
RIDESCLY,REPTYPELY,AGNTNUMLY,
CNTTYPELY,REPNUMLY,RNLTYPELY,CLNTNUMLY,OCCPCODELY, CLNTNAMELY, CHDRNUMLY,PREMCLLY,
TRANNOLY,BATCBRNLY, BATCACTYRLY,BATCACTMNLY,BATCTRCDELY,SDESC,GWPLY
from
(select K.LONG_NAME as LONG_NAMELY,J.OCCPDESC as OCCPDESCLY,I.MAJORCLSCODE as MAJORCLSCODELY, G.BRANCH_REPORTTO as BRANCH_REPORTTOLY,F.ZPRODUCER as ZPRODUCERLY,F.RIDESC as RIDESCLY,E.REPTYPE as REPTYPELY,E.AGNTNUM as AGNTNUMLY,E.CNTTYPE as CNTTYPELY,E.REPNUM as REPNUMLY,E.RNLTYPE as RNLTYPELY,C.CLNTNUM as CLNTNUMLY,C.OCCPCODE as OCCPCODELY,C.CLNTNAME as CLNTNAMELY,A.CHDRNUM as CHDRNUMLY,A.PREMCL as PREMCLLY, A.TRANNO as TRANNOLY,A.BATCBRN as BATCBRNLY, A.BATCACTYR as BATCACTYRLY,A.BATCACTMN as BATCACTMNLY,A.BATCTRCDE as BATCTRCDELY,L.SHORTDESC as SDESC,sum((-1)*A.TRANAMT01 -A.TRANAMT03) as GWPLY
FROM [MY_DTA_MIS].[dbo].[VW_NSAP_T_ZBUW] A
inner join [MY_DTA_MIS].[dbo].[CDE_T_CLNT] C on A.CLNTNUM=C.CLNTNUM
inner join [MY_DTA_MIS].[dbo].[SAP_T_CHDR] E
ON A.CHDRNUM=E.CHDRNUM AND A.BATCACTYR=E.BATCACTYR and A.BATCACTMN=E.BATCACTMN AND A.TRANNO=E.TRANNO
inner join [MY_DTA_MIS].[dbo].[CDE_T_AGNT] F
on E.AGNTNUM = F.AGNTNUM
inner join [MY_DTA_MIS].[dbo].[VW_CDE_T_BRANCHGROUPING] G
on A.[New_BRANCH_CDE] = G.[New_BRANCH_CDE]
inner join [MY_DTA_MIS].[dbo].[CDE_T_T3640] H
on A.PREMCL = H.PREMCLS
inner join [MY_DTA_MIS].[dbo].[CDE_V_T7039] I
on H.B5MAJORCLS = I.MAJORCLSCODE
left join [MY_DTA_MIS].[dbo].[CDE_T_T3644] J
on C.OCCPCODE = J.OCCPCODE
inner join [MY_DTA_MIS].[dbo].[CDE_T_T4965] K
on F.ZPRODUCER = K.ZPRODUCER
inner join [MY_DTA_MIS].[dbo].[CDE_V_T1688] L
on A.BATCTRCDE = L.TRANSCODE

where (A.BATCTRCDE in ('T413','T405'))and A.SACSCODE in ('FG', 'CO')
and A.BATCACTYR = @Prompt('Enter This Year','C','Year Others',mono,constrained)

group by K.LONG_NAME,J.OCCPDESC,I.MAJORCLSCODE,G.BRANCH_REPORTTO,F.ZPRODUCER,F.RIDESC,E.REPTYPE,E.AGNTNUM,E.CNTTYPE,E.REPNUM,E.RNLTYPE,C.CLNTNUM,
C.OCCPCODE,C.clntname,A.CHDRNUM,A.PREMCL,A.TRANNO,A.BATCBRN,A.BATCACTYR,A.BATCACTMN,A.BATCTRCDE,L.SHORTDESC)b1)LY

on TY.CHDRNUM=LY.CHDRNUMLY and TY.PREMCL=LY.PREMCLLY

Above is the query, when validate after removed @prompt it said query error. But if @prompt is removed and assign A.BATCACTYR=2014 AND A.BATCACTMN = 11 the error disappear.