Error (WIS 00022)

[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

What is the possible cause?It said SQL Query entered incorrect. This message came out if I remove the bolded row which contain prompt.

Update: I click yes to continue, all column inside my table gone and running row count i get this error "The query does not reference any table when attempting to build the WHERE clause.  (WIS 00022)".

[nscheaffer · Accepted solution]

Where are you putting this SQL?  In a derived table?

Check out my blog post on

Does that help?

Noel

[former_member419024 · Accepted solution]

Thank for your reply, I place the @prompt function in derived table before and it working fine. Now I wanted to remove it but it appear query entered incorrect (WIS 00022).