aircon
asked on
what causes errror Must declare the scalar variable "@Subsidiary".
DECLARE @BeginDate DATETIME = '07/01/2009';
@EndDate DATETIME = '01/05/2010';
@Subsidiary INT = 2;
@Contract VARCHAR(20) = 'KD228';
SET @BeginDate = '07/01/2009';
@EndDate = '01/05/2010';
@Subsidiary = 2;
@Contract= 'KD228';
--AS
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT CASE
WHEN @Subsidiary = 1
AND (RIGHT (RTRIM(R.CSTBTablCode),3)= 'DCF' AND TN.CSTBTablCode != '3')
THEN 'Error'
WHEN @Subsidiary = 1
AND (RIGHT (RTRIM(R.CSTBTablCode),3)= 'TNF' AND TN.CSTBTablCode = '3')
THEN 'Error'
WHEN @Subsidiary = 2
AND (SUBSTRING(R.CSTBTablCode, 4,1)!= 'T' AND TN.CSTBTablCode != '3')
THEN 'Error'
WHEN @Subsidiary = 2
AND (SUBSTRING(R.CSTBTablCode, 4,1) = 'T' AND TN.CSTBTablCode = '3')
THEN 'Error'
WHEN @Subsidiary = 2
AND (C.CSTBTablCode = '01' OR C.CSTBTablCode = '1')
THEN 'Error'
ELSE ''
END AS ErrorFlag,
TN.CSTBTablCode+'-'+TN.CST BTablDesc AS TANFType,
R.CSTBTablCode AS RU,
C.CSTBTablCode AS CostCenter,
CASE
WHEN ((@Subsidiary = 2 AND LEFT (RIGHT (R.CSTBTablCode,6),1) = '1')
OR (@Subsidiary = 1 AND RIGHT (LEFT (R.CSTBTablCode,3),1) = '1')) THEN 'AMH'
WHEN ((@Subsidiary = 2 AND LEFT (RIGHT (R.CSTBTablCode,6),1) = '2')
OR (@Subsidiary = 1 AND RIGHT (LEFT (R.CSTBTablCode,3),1) = '2')) THEN 'ASA'
WHEN ((@Subsidiary = 2 AND LEFT (RIGHT (R.CSTBTablCode,6),1) = '3')
OR (@Subsidiary = 1 AND RIGHT (LEFT (R.CSTBTablCode,3),1) = '3')) THEN 'CMH'
WHEN ((@Subsidiary = 2 AND LEFT (RIGHT (R.CSTBTablCode,6),1) = '4')
OR (@Subsidiary = 1 AND RIGHT (LEFT (R.CSTBTablCode,3),1) = '4')) THEN 'CSA'
END AS Prog,
P.CSTBTablCode AS ProcCode,
P.CSTBStatOvcd AS StatCode,
F.CSTBTablDesc AS Funding,
CONVERT (varchar,T.ARTRTranDate,10 1) AS BatchDate,
CASE
WHEN (T.ARTRDaodFlag = 1 AND T.ARTREditEror = 2) THEN 'Yes'
ELSE 'Error'
END AS Transmit,
--ARTRClntTime,
X.ARCSCustCode,
T.ARTRTranCtrl AS Control,
S.ARSLOldcNumb AS StaffID,
S.ARSLSlmnDesc AS Staff,
R.CSTBStatCntr AS RUContract,
T.ARTRStatCntr AS TransContract
FROM KIS1.dbo.ARTran AS T
INNER Join KIS1.dbo.CSTabl AS R ON R.CSTBRecdCtrl = T.ARTRReptUnit
INNER JOIN KIS1.dbo.CSTabl AS F ON F.CSTBRecdCtrl = R.CSTBSrvcFnd1
INNER Join KIS1.dbo.CSTabl AS P ON P.CSTBRecdCtrl = T.ARTRSrvcCode
INNER JOIN KIS1.dbo.CSTabl AS C ON C.CSTBRecdCtrl = T.ARTRStffCscn
INNER JOIN KIS1.dbo.ARCust AS X ON X.ARCSCustCtrl = T.ARTRCustCtrl
INNER JOIN KIS1.dbo.ARSale AS S ON S.ARSLSlmnCtrl = T.ARTRSaleCtrl
INNER JOIN KIS1.dbo.CSAdmn AS A ON T.ARTRAdmsCtrl = A.CSADRecdCtrl
LEFT JOIN KIS1.dbo.CSTabl AS TN ON TN.CSTBRecdCtrl = A.CSADTanfFlag
WHERE T.ARTRSbsdCtrl = @Subsidiary AND
T.ARTRPendFlag = 2 AND
T.ARTRRecdType = 0 AND
T.ARTREntyType = 191 AND
--T.ARTRSendStat = 1 AND
T.ARTRDaodFlag = 1 AND
((T.ARTRStatCntr = 'KD228') OR (R.CSTBStatCntr LIKE '%KD228%' AND T.ARTRStatCntr Is Null )) AND
T.ARTRTranDate BETWEEN @BeginDate AND @EndDate
ORDER BY 1,4,3,T.ARTRTranCtrl
@EndDate DATETIME = '01/05/2010';
@Subsidiary INT = 2;
@Contract VARCHAR(20) = 'KD228';
SET @BeginDate = '07/01/2009';
@EndDate = '01/05/2010';
@Subsidiary = 2;
@Contract= 'KD228';
--AS
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT CASE
WHEN @Subsidiary = 1
AND (RIGHT (RTRIM(R.CSTBTablCode),3)=
THEN 'Error'
WHEN @Subsidiary = 1
AND (RIGHT (RTRIM(R.CSTBTablCode),3)=
THEN 'Error'
WHEN @Subsidiary = 2
AND (SUBSTRING(R.CSTBTablCode,
THEN 'Error'
WHEN @Subsidiary = 2
AND (SUBSTRING(R.CSTBTablCode,
THEN 'Error'
WHEN @Subsidiary = 2
AND (C.CSTBTablCode = '01' OR C.CSTBTablCode = '1')
THEN 'Error'
ELSE ''
END AS ErrorFlag,
TN.CSTBTablCode+'-'+TN.CST
R.CSTBTablCode AS RU,
C.CSTBTablCode AS CostCenter,
CASE
WHEN ((@Subsidiary = 2 AND LEFT (RIGHT (R.CSTBTablCode,6),1) = '1')
OR (@Subsidiary = 1 AND RIGHT (LEFT (R.CSTBTablCode,3),1) = '1')) THEN 'AMH'
WHEN ((@Subsidiary = 2 AND LEFT (RIGHT (R.CSTBTablCode,6),1) = '2')
OR (@Subsidiary = 1 AND RIGHT (LEFT (R.CSTBTablCode,3),1) = '2')) THEN 'ASA'
WHEN ((@Subsidiary = 2 AND LEFT (RIGHT (R.CSTBTablCode,6),1) = '3')
OR (@Subsidiary = 1 AND RIGHT (LEFT (R.CSTBTablCode,3),1) = '3')) THEN 'CMH'
WHEN ((@Subsidiary = 2 AND LEFT (RIGHT (R.CSTBTablCode,6),1) = '4')
OR (@Subsidiary = 1 AND RIGHT (LEFT (R.CSTBTablCode,3),1) = '4')) THEN 'CSA'
END AS Prog,
P.CSTBTablCode AS ProcCode,
P.CSTBStatOvcd AS StatCode,
F.CSTBTablDesc AS Funding,
CONVERT (varchar,T.ARTRTranDate,10
CASE
WHEN (T.ARTRDaodFlag = 1 AND T.ARTREditEror = 2) THEN 'Yes'
ELSE 'Error'
END AS Transmit,
--ARTRClntTime,
X.ARCSCustCode,
T.ARTRTranCtrl AS Control,
S.ARSLOldcNumb AS StaffID,
S.ARSLSlmnDesc AS Staff,
R.CSTBStatCntr AS RUContract,
T.ARTRStatCntr AS TransContract
FROM KIS1.dbo.ARTran AS T
INNER Join KIS1.dbo.CSTabl AS R ON R.CSTBRecdCtrl = T.ARTRReptUnit
INNER JOIN KIS1.dbo.CSTabl AS F ON F.CSTBRecdCtrl = R.CSTBSrvcFnd1
INNER Join KIS1.dbo.CSTabl AS P ON P.CSTBRecdCtrl = T.ARTRSrvcCode
INNER JOIN KIS1.dbo.CSTabl AS C ON C.CSTBRecdCtrl = T.ARTRStffCscn
INNER JOIN KIS1.dbo.ARCust AS X ON X.ARCSCustCtrl = T.ARTRCustCtrl
INNER JOIN KIS1.dbo.ARSale AS S ON S.ARSLSlmnCtrl = T.ARTRSaleCtrl
INNER JOIN KIS1.dbo.CSAdmn AS A ON T.ARTRAdmsCtrl = A.CSADRecdCtrl
LEFT JOIN KIS1.dbo.CSTabl AS TN ON TN.CSTBRecdCtrl = A.CSADTanfFlag
WHERE T.ARTRSbsdCtrl = @Subsidiary AND
T.ARTRPendFlag = 2 AND
T.ARTRRecdType = 0 AND
T.ARTREntyType = 191 AND
--T.ARTRSendStat = 1 AND
T.ARTRDaodFlag = 1 AND
((T.ARTRStatCntr = 'KD228') OR (R.CSTBStatCntr LIKE '%KD228%' AND T.ARTRStatCntr Is Null )) AND
T.ARTRTranDate BETWEEN @BeginDate AND @EndDate
ORDER BY 1,4,3,T.ARTRTranCtrl
a typo?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.