Link to home
Start Free TrialLog in
Avatar of aircon
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.CSTBTablDesc            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,101)      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
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

a typo?
ASKER CERTIFIED SOLUTION
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial