Link to home
Create AccountLog in
Avatar of Metalteck
MetalteckFlag for United States of America

asked on

Invalid Column Name

Hey Guys,

I have this code I"m working with. I thought that I declared Acct_Unit correctly, but it keeps on telling me that it isn't. Can someone shed some light to what I'm missing.

Thanks

Declare @COMPANY AS CHAR(20)
Declare @BUSINESSTYPE CHAR(75)
DECLARE @CONTRACT_TYPE CHAR(50)
Declare @INCENTIVE CHAR(10)
Declare @LOCALGRP1 CHAR(50)
Declare @LOCALGRP2 CHAR(255)
DECLARE @SUBSIDY1 char(10)
DECLARE @SUBSIDY2 char(10)
DECLARE @GADIVISION CHAR(255)
DECLARE @Year int
Declare @Month int

--set @COMPANY ={?@COMPANY}
--set @BUSINESSTYPE=''''+ltrim(rtrim( {?@BUSINESSTYPE}))+''''
set @BUSINESSTYPE='RAD'
--set @CONTRACT_TYPE={?@CONTRACT_TYPE}
--set @INCENTIVE= {?@INCENTIVE}
--set @LOCALGRP1= {?@LOCALGRP1}
--set @LOCALGRP2= {?@LOCALGRP2}
--set @SUBSIDY1= {?@SUBSIDY1}
--set @SUBSIDY2 = {?@SUBSIDY2}
--set @GADIVISION = {?@GADIVISION}
--set @Year = {?@Year}
set @Year = 2013
--set @Month = {?@Month}
set @Month = 1


--set @COMPANY = case when  ltrim(rtrim(@COMPANY)) = ''  then null else @COMPANY end
set @BUSINESSTYPE=case when  ltrim(rtrim(@BUSINESSTYPE)) =''  then null else @BUSINESSTYPE end
--set @CONTRACT_TYPE=case when  ltrim(rtrim(@CONTRACT_TYPE)) = ''  then null else @CONTRACT_TYPE end
--set @INCENTIVE=case when  ltrim(rtrim(@INCENTIVE)) ='' then null else @INCENTIVE end
--set @LOCALGRP2=case when  ltrim(rtrim(@LOCALGRP2)) = '' then null else @LOCALGRP2 end
--set @SUBSIDY1=case when  ltrim(rtrim(@SUBSIDY1)) = '' then null else @SUBSIDY1 end
--set @SUBSIDY2 =case when  ltrim(rtrim(@SUBSIDY2)) = '' then null else @SUBSIDY2 end
--set @GADIVISION =case when  ltrim(rtrim(@GADIVISION)) = '' then null else @GADIVISION end

--set @Year = 2012
--set @Month = 9
Declare @CY_Year as varchar(4)
Declare @PY_Year as varchar(4)
set @CY_Year = @Year
set @PY_Year = @CY_Year - 1
--Declare @SQL_where as varchar(600)
--SET @COMPANY = '31'
--SET @BUSINESSTYPE = '''ANES'''

Declare @Month1 as varchar(25)
Declare @Month2 as varchar(25)
Declare @Month3 as varchar(25)

Declare @Act_YTD as varchar(1000)
Declare @Bud_YTD as varchar(1000)
Declare @Act_YTD_Units as varchar(1000)
Declare @MonthNumber1 as varchar(10)

Declare @SQL1 AS VARCHAR(8000)
Declare @SQL2 AS VARCHAR(8000)
Declare @SQL3 as varchar(8000)
Declare @SQL9 AS VARCHAR(8000)
Declare @SQL_CASES varchar(8000)

set @MonthNumber1 = (SELECT CASE WHEN @Month < = 9 THEN '0' + cast(@Month as varchar(1)) ELSE CAST(@Month AS VARCHAR(2)) END)
+'/1/' + cast(@Year as varchar(4))

set @Month1 = 'CR_AMOUNT_'
+left(CONVERT(char(10),@MonthNumber1,101),2)
+'+DB_AMOUNT_'
+left(CONVERT(char(10),@MonthNumber1,101),2)

set @Month2 = 'CR_AMOUNT_'
+left(CONVERT(char(10),dateadd(mm,-1,@MonthNumber1),101),2)
+'+DB_AMOUNT_'
+left(CONVERT(char(10),dateadd(mm,-1,@MonthNumber1),101),2)

set @Month3 =  'CR_AMOUNT_'
+left(CONVERT(char(10),dateadd(mm,-2,@MonthNumber1),101),2)
+'+DB_AMOUNT_'
+left(CONVERT(char(10),dateadd(mm,-2,@MonthNumber1),101),2)

SET @Act_YTD = RTRIM(REPLACE(REPLACE((select RTRIM(CR_COLUMN)+'+'+RTRIM(DB_COLUMN) FROM dbo.RPT_YTDCOLUMNS WHERE [MONTH] = @Month),'CR','A.CR'),'DB','A.DB'))
SET @Bud_YTD = RTRIM(REPLACE(REPLACE((select RTRIM(CR_COLUMN)+'+'+RTRIM(DB_COLUMN) FROM dbo.RPT_YTDCOLUMNS WHERE [MONTH] = @Month),'CR','B.CR'),'DB','B.DB'))
SET @Act_YTD_Units = REPLACE(REPLACE((SELECT RTRIM(CR_UNITS)+'+'+ RTRIM(DB_UNITS) FROM dbo.RPT_YTDCOLUMNS WHERE [MONTH] = @Month),'CR','U.CR'),'DB','U.DB')


Declare @Timestamp as varchar(20)
set @Timestamp=(select cast(getdate() as varchar(20)))

---------------------------------------------------------------------------------------------

--This part of the code captures number of paramter user selected.

declare @I as int
Declare @par as int
Declare @SQL_fld as varchar(255)
set @par = 0
set @I=1

while @I<=8
begin
set @SQL_fld = (select case when @I = 1 then @BUSINESSTYPE
                      --      when @I = 2 then @CONTRACT_TYPE
                      --      when @I = 3 then @INCENTIVE
                      --      when @I = 4 then @LOCALGRP1
                      --      when @I = 5 then @LOCALGRP2
                      --      when @I = 6 then @SUBSIDY1
                      --      when @I = 7 then @SUBSIDY2
                            --when @I = 8 then @COMPANY
                      --      else @GADIVISION
                      end
                            )
set @par = (case when isnull(@SQL_fld,'')<>'' then @par+1 else @par end )  

set @I = @I+1
end

---------------------------- create SELECTz based on the parameter that the user chooses-----------
Declare @var1 as varchar(8000)
Declare @var2 as varchar(8000)
-- OR (CAST(N.COMPANY AS VARCHAR(3)) IN ('+ltrim(rtrim(@COMPANY)) +'))' END
set @var1 = 'select distinct OBJ_ID FROM LSPROD.dbo.AUMXVALUE
where MATRIX_CAT = ''START_DATE''
AND (MX_VALUE <  cast(year(getdate()) as varchar(4)) + ''01'' +''01''
or isnull(MX_VALUE,'''')='''')
and OBJ_ID in (SELECT DISTINCT V1.OBJ_ID FROM LSPROD.dbo.AUMXVALUE V1
WHERE'
+ CASE WHEN isnull(@COMPANY,'') = '' THEN '' ELSE ' OR ( V1.MATRIX_CAT = ''COMPANY-NUMB'' AND CAST(LTRIM(RTRIM(V1.MX_VALUE)) AS INT) IN ('+ltrim(rtrim(@COMPANY))+'))' END
+ CASE WHEN isnull(@BUSINESSTYPE,'') = '' THEN '' ELSE ' OR ( V1.MATRIX_CAT = ''BUSINESSTYPE'' AND LTRIM(RTRIM(V1.MX_VALUE)) IN ('+ltrim(rtrim(@BUSINESSTYPE))+'))' END
+ CASE WHEN isnull(@CONTRACT_TYPE,'') = '' THEN '' ELSE ' OR ( V1.MATRIX_CAT = ''CONTRCT_TYPE'' AND LTRIM(RTRIM(V1.MX_VALUE)) IN ('+ltrim(rtrim(@CONTRACT_TYPE))+'))' END
+ CASE WHEN isnull(@INCENTIVE,'') = '' THEN '' ELSE ' OR ( V1.MATRIX_CAT = ''INCENTIVE'' AND LTRIM(RTRIM(V1.MX_VALUE)) IN ('+ltrim(rtrim(@INCENTIVE))+'))' END
+ CASE WHEN isnull(@LOCALGRP1,'') = '' THEN '' ELSE ' OR ( V1.MATRIX_CAT = ''LOCALGRP1'' AND LTRIM(RTRIM(V1.MX_VALUE)) IN ('+ltrim(rtrim(@LOCALGRP1))+'))' END
+ CASE WHEN isnull(@LOCALGRP2,'') = '' THEN '' ELSE ' OR ( V1.MATRIX_CAT = ''LOCALGRP2'' AND LTRIM(RTRIM(V1.MX_VALUE)) IN ('+ltrim(rtrim(@LOCALGRP2))+'))' END
+ CASE WHEN isnull(@SUBSIDY1,'') = '' THEN '' ELSE ' OR ( V1.MATRIX_CAT = ''SUBSIDY1'' AND LTRIM(RTRIM(V1.MX_VALUE)) IN ('+ltrim(rtrim(@SUBSIDY1))+'))' END
+ CASE WHEN isnull(@SUBSIDY2,'') = '' THEN '' ELSE ' OR ( V1.MATRIX_CAT = ''SUBSIDY2'' AND LTRIM(RTRIM(V1.MX_VALUE)) IN ('+ltrim(rtrim(@SUBSIDY2))+'))' END
+ CASE WHEN isnull(@GADIVISION,'') = '' THEN '' ELSE ' OR ( V1.MATRIX_CAT = ''G&A-DIVISION'' AND LTRIM(RTRIM(V1.MX_VALUE)) IN ('+ltrim(rtrim(@GADIVISION))+'))' END

set @var2 = (SELECT REPLACE (ltrim(rtrim(@var1)), 'WHERE OR ', 'WHERE ')+ 'GROUP BY V1.OBJ_ID HAVING COUNT(V1.OBJ_ID)>=CAST('+CAST(@par AS VARCHAR(1)))+' AS INT))'

--SELECT @var2
---------------------------------------------------------------
set @SQL1 = 'SELECT E.ACCOUNT
,E.ACCT_UNIT
,E.SUB_ACCOUNT
,E.[GROUPING]
,E.ACCOUNT_DESC
,A.FISCAL_YEAR
,''DIVISIONAL'' +'''+@Timestamp+'''
, SUM(A.'+ REPLACE(@Month1,'DB','A.DB') + ')  
           , SUM(A.'+ REPLACE(@Month2,'DB','A.DB') + ')
           , SUM(A.'+ REPLACE(@Month3,'DB','A.DB') + ')
           , SUM(CASE WHEN A.FISCAL_YEAR = '+ @CY_Year+ ' THEN ' + @Act_YTD + ' ELSE 0 END )
           , SUM(CASE WHEN A.FISCAL_YEAR = '+ @PY_Year+ ' THEN ' + @Act_YTD + ' ELSE 0 END )
           
,0
,0
FROM LSPROD.dbo.GLNAMES N
--INNER JOIN LSPROD.dbo.AUMXVALUE V
INNER JOIN ('+ ltrim(rtrim(@var2)) + ') V
ON V.OBJ_ID = N.OBJ_ID
INNER JOIN LSPROD.dbo.GLAMOUNTS A
ON N.COMPANY = A.COMPANY
AND N.ACCT_UNIT = A.ACCT_UNIT
AND N.VAR_LEVELS = A.VAR_LEVELS
INNER JOIN dbo.ER_ACCOUNTS E
ON A.ACCOUNT = E.ACCOUNT
AND A.SUB_ACCOUNT = E.SUB_ACCOUNT
WHERE N.LEVEL_DEPTH = 4
--AND N.ACTIVE_STATUS = ''A''
AND A.FISCAL_YEAR in ('+ @PY_Year + ',' + @CY_Year + ')'
--+ CASE WHEN isnull(@COMPANY,'') = '' THEN '' ELSE 'AND CAST(N.COMPANY AS VARCHAR(3)) IN ('+ltrim(rtrim(@COMPANY)) +')' END
--+'AND V.OBJ_ID IN ('+ ltrim(rtrim(@var2)) + ')'
+ CASE WHEN isnull(@COMPANY,'') = '' THEN '' ELSE 'AND CAST(N.COMPANY AS VARCHAR(3)) IN ('+ltrim(rtrim(@COMPANY)) +')' END
/*+ CASE WHEN isnull(@BUSINESSTYPE,'') = '' THEN '' ELSE 'AND V.MATRIX_CAT = ''BUSINESSTYPE'' AND LTRIM(RTRIM(V.MX_VALUE)) IN ('+ltrim(rtrim(@BUSINESSTYPE))+')' END
+ CASE WHEN isnull(@CONTRACT_TYPE,'') = '' THEN '' ELSE 'AND V.MATRIX_CAT = ''CONTRCT_TYPE'' AND LTRIM(RTRIM(V.MX_VALUE)) IN ('''+ltrim(rtrim(@CONTRACT_TYPE))+''')' END
+ CASE WHEN isnull(@INCENTIVE,'') = '' THEN '' ELSE 'AND V.MATRIX_CAT = ''INCENTIVE'' AND LTRIM(RTRIM(V.MX_VALUE)) IN ('''+ltrim(rtrim(@INCENTIVE))+''')' END
+ CASE WHEN isnull(@LOCALGRP1,'') = '' THEN '' ELSE 'AND V.MATRIX_CAT = ''LOCALGRP1'' AND LTRIM(RTRIM(V.MX_VALUE)) IN ('''+ltrim(rtrim(@LOCALGRP1))+''')' END
+ CASE WHEN isnull(@LOCALGRP2,'') = '' THEN '' ELSE 'AND V.MATRIX_CAT = ''LOCALGRP2'' AND LTRIM(RTRIM(V.MX_VALUE)) IN ('''+ltrim(rtrim(@LOCALGRP2))+''')' END
+ CASE WHEN isnull(@SUBSIDY1,'') = '' THEN '' ELSE 'AND V.MATRIX_CAT = ''SUBSIDY1'' AND LTRIM(RTRIM(V.MX_VALUE)) IN ('''+ltrim(rtrim(@SUBSIDY1))+''')' END
+ CASE WHEN isnull(@SUBSIDY2,'') = '' THEN '' ELSE 'AND V.MATRIX_CAT = ''SUBSIDY2'' AND LTRIM(RTRIM(V.MX_VALUE)) IN ('''+ltrim(rtrim(@SUBSIDY2))+''')' END
+ CASE WHEN isnull(@GADIVISION,'') = '' THEN '' ELSE 'AND V.MATRIX_CAT = ''G&A-DIVISION'' AND LTRIM(RTRIM(V.MX_VALUE)) IN ('''+ltrim(rtrim(@GADIVISION))+''')' END


*/+' GROUP BY E.ACCOUNT
,E.ACCT_UNIT
,E.SUB_ACCOUNT
,E.[GROUPING]
,E.ACCOUNT_DESC
,A.FISCAL_YEAR'
--SELECT (@SQL1)
--DELETE FROM RPT_TEMP_ACCOUNT_NEW WHERE REPORTNAME = 'DIVISIONAL'

INSERT INTO RPT_TEMP_ACCOUNT_NEW
( ACCOUNT
,ACCT_UNIT
,SUB_ACCOUNT
,HEAD_NAME
,ACCOUNT_DESC
,FISCAL_YEAR
,REPORTNAME
,ACTM1
,ACTM2
,ACTM3
,CY_ACT_YTD
,PY_ACT_YTD
,BUDGETM1
,BUDGET_YTD

)

EXEC(@SQL1)
--SELECT @SQL1
-------------------------------------------------------------------------------
set @SQL_CASES = 'SELECT
U.ACCOUNT
,U.ACCT_UNIT
,U.SUB_ACCOUNT
,E.[GROUPING]
,LTRIM(RTRIM(E.ACCOUNT_DESC))
,U.FISCAL_YEAR
,''DIVISIONAL''+'''+@Timestamp+'''
           , SUM('+ REPLACE(REPLACE(@Month1,'DB_AMOUNT','U.DB_UNITS'),'CR_AMOUNT','U.CR_UNITS') + ')
           , SUM('+ REPLACE(REPLACE(@Month2,'DB_AMOUNT','U.DB_UNITS'),'CR_AMOUNT','U.CR_UNITS') + ')
           , SUM('+ REPLACE(REPLACE(@Month3,'DB_AMOUNT','U.DB_UNITS'),'CR_AMOUNT','U.CR_UNITS') + ')
           , SUM(CASE WHEN U.FISCAL_YEAR IN ('+@CY_Year + ') THEN ' + @Act_YTD_Units + ' ELSE 0 END )
           , SUM(CASE WHEN U.FISCAL_YEAR IN ('+@PY_Year + ')THEN ' + @Act_YTD_Units + ' ELSE 0 END )
           ,0,0
           FROM LSPROD.dbo.GLNAMES N
--INNER JOIN LSPROD.dbo.AUMXVALUE V
INNER JOIN ('+ ltrim(rtrim(@var2)) + ') V
ON V.OBJ_ID = N.OBJ_ID
INNER JOIN LSPROD.dbo.GLUNITS U
ON N.COMPANY = U.COMPANY
AND N.ACCT_UNIT = U.ACCT_UNIT
AND N.VAR_LEVELS = U.VAR_LEVELS
INNER JOIN dbo.ER_ACCOUNTS E
ON U.ACCOUNT = E.ACCOUNT
AND U.SUB_ACCOUNT = E.SUB_ACCOUNT
WHERE N.LEVEL_DEPTH = 4
AND U.ACCOUNT =4000
--AND N.ACTIVE_STATUS = ''A''
AND U.FISCAL_YEAR in ('+ @PY_Year + ',' + @CY_Year + ')'
+ CASE WHEN isnull(@COMPANY,'') = '' THEN '' ELSE 'AND CAST(N.COMPANY AS VARCHAR(3)) IN ('+ltrim(rtrim(@COMPANY)) +')' END
+'GROUP BY U.ACCOUNT
,U.ACCT_UNIT
,U.SUB_ACCOUNT
,E.[GROUPING]
,LTRIM(RTRIM(E.ACCOUNT_DESC))
,U.FISCAL_YEAR'

INSERT INTO RPT_TEMP_ACCOUNT_NEW
( ACCOUNT
,ACCT_UNIT
,SUB_ACCOUNT
,HEAD_NAME
,ACCOUNT_DESC
,FISCAL_YEAR
,REPORTNAME
,ACTM1
,ACTM2
,ACTM3
,CY_ACT_YTD
,PY_ACT_YTD
,BUDGETM1
,BUDGET_YTD

)
EXEC(@SQL_CASES)

----------------------------------------------------------BGT------------------------------------
SET @SQL2 = 'SELECT
B.ACCOUNT
,B.ACCT_UNIT
,B.SUB_ACCOUNT
,E.[GROUPING]
,LTRIM(RTRIM(E.ACCOUNT_DESC))
,B.FISCAL_YEAR
,''DIVISIONAL''+'''+@Timestamp+'''
          ,0,0,0,0,0
          , SUM( CASE WHEN B.ACCOUNT =4000 THEN '+ REPLACE(@Month1,'AMOUNT','UNITS') +'
              ELSE B.'+ REPLACE(@Month1,'DB','B.DB') + ' END
                  )
          , SUM(CASE WHEN B.FISCAL_YEAR IN ('+ @CY_Year+ ') THEN
               CASE WHEN B.ACCOUNT =4000 THEN '+ REPLACE(@Bud_YTD,'AMOUNT','UNITS')+'
               ELSE '+ @Bud_YTD + ' END ELSE 0 END )
          FROM LSPROD.dbo.GLNAMES N
--INNER JOIN LSPROD.dbo.AUMXVALUE V
INNER JOIN ('+ ltrim(rtrim(@var2)) + ') V
ON V.OBJ_ID = N.OBJ_ID
INNER JOIN LSPROD.dbo.FBDETAIL B
ON N.COMPANY = B.COMPANY
AND N.ACCT_UNIT = B.ACCT_UNIT
AND N.VAR_LEVELS = B.VAR_LEVELS
INNER JOIN dbo.ER_ACCOUNTS E
ON B.ACCOUNT = E.ACCOUNT
AND B.SUB_ACCOUNT = E.SUB_ACCOUNT
WHERE N.LEVEL_DEPTH = 4
--AND N.ACTIVE_STATUS = ''A''
AND B.FISCAL_YEAR in ('+ @PY_Year + ',' + @CY_Year + ')'
+ CASE WHEN isnull(@COMPANY,'') = '' THEN '' ELSE 'AND N.COMPANY  IN ('+ltrim(rtrim(@COMPANY)) +')' END
+'GROUP BY B.ACCOUNT
,B.ACCT_UNIT
,B.SUB_ACCOUNT
,E.[GROUPING]
,LTRIM(RTRIM(E.ACCOUNT_DESC))
,B.FISCAL_YEAR'

INSERT INTO RPT_TEMP_ACCOUNT_NEW
( ACCOUNT
,ACCT_UNIT
,SUB_ACCOUNT
,HEAD_NAME
,ACCOUNT_DESC
,FISCAL_YEAR
,REPORTNAME
,ACTM1
,ACTM2
,ACTM3
,CY_ACT_YTD
,PY_ACT_YTD
,BUDGETM1
,BUDGET_YTD

)
EXEC(@SQL2)


SELECT @BUSINESSTYPE,E.ACCOUNT,E.SUB_ACCOUNT,SUB_GRP_DIVISIONAL
,'ACCOUNT_DESC'= CASE  WHEN E.ACCOUNT IN (6080) THEN 'DEPRECIATION EXPENSE'
                       WHEN E.ACCOUNT IN (6168) THEN 'INCOME TAX EXPENSE - CURRENT'
                       WHEN E.ACCOUNT IN (6700,6701) THEN 'NON-RECURRING CHARGES'
                       WHEN E.ACCOUNT IN (6220,4520) THEN 'INTEREST EXPENSE, NET'
                       WHEN E.ACCOUNT IN (6081) THEN 'AMORTIZATION EXPENSE'
                       ELSE E.ACCOUNT_DESC_DIVISIONAL END
,'HEAD_NAME'=CASE WHEN E.ACCOUNT<5000 AND E.ACCOUNT <> 4000 and  E.ACCOUNT <> 4520  THEN 'TOTAL INCOME'
                  WHEN E.ACCOUNT BETWEEN 5000 AND 5004 THEN 'NET REVENUE'
                  WHEN E.ACCOUNT BETWEEN 5006 AND 5999 THEN 'NET REVENUE'
                  WHEN E.ACCOUNT in (5005,6070) THEN 'TOTAL DIRECT EXPENSES' -- THIS CHANGE IS BASED ON Elizabth's request 05/24/2011
                  WHEN E.ACCOUNT in (6220,4520,6406,6165,6168,6700,6701)  THEN 'INDIRECT EXPENSES'
                  ELSE E.GROUPING_DIVISIONAL END
                                     
,N.ACTM1,N.ACTM2,N.ACTM3,N.CY_ACT_YTD
,N.PY_ACT_YTD,N.BUDGETM1,N.BUDGET_YTD
,cast(@PY_Year as int) as FISCAL_YEAR
FROM LawsonRpt.dbo.ER_ACCOUNTS E
LEFT OUTER JOIN (SELECT * FROM RPT_TEMP_ACCOUNT_NEW WHERE REPORTNAME = 'DIVISIONAL'+@Timestamp
AND FISCAL_YEAR = @PY_Year)N
ON E.ACCOUNT = N.ACCOUNT
AND E.SUB_ACCOUNT = N.SUB_ACCOUNT
--WHERE E.ACCOUNT NOT IN (6328)                           --05/31/11 aS PER lIZ, SOME ACCOUNTS NEEDS TO BE EXCLUDED FROM DIVISIONAL RPTs, not from CONSOLIDATED
UNION
SELECT @BUSINESSTYPE,E.ACCOUNT,E.SUB_ACCOUNT,SUB_GRP_DIVISIONAL
,'ACCOUNT_DESC'= CASE  WHEN E.ACCOUNT IN (6080) THEN 'DEPRECIATION EXPENSE'
                       WHEN E.ACCOUNT IN (6168) THEN 'INCOME TAX EXPENSE - CURRENT'
                       WHEN E.ACCOUNT IN (6700,6701) THEN 'NON-RECURRING CHARGES'
                       WHEN E.ACCOUNT IN (6220,4520) THEN 'INTEREST EXPENSE, NET'
                       WHEN E.ACCOUNT IN (6081) THEN 'AMORTIZATION EXPENSE'
                       --WHEN E.ACCOUNT IN (6335,6337,6338,6332,6329,6364,6336,6334) THEN 'PHYS REGULAR'
                       --WHEN E.ACCOUNT IN (6330) THEN
                       --     CASE WHEN E.SUB_ACCOUNT IN (0,1,2,9) THEN 'PHYS REGULAR' ELSE E.ACCOUNT_DESC_ERCONSOLIDATED END
                       ELSE E.ACCOUNT_DESC_DIVISIONAL END
,'HEAD_NAME'=CASE WHEN E.ACCOUNT<5000 AND E.ACCOUNT <> 4000 and  E.ACCOUNT <> 4520  THEN 'TOTAL INCOME'
                  WHEN E.ACCOUNT BETWEEN 5000 AND 5004 THEN 'NET REVENUE'
                  WHEN E.ACCOUNT BETWEEN 5006 AND 5999 THEN 'NET REVENUE'
                  WHEN E.ACCOUNT in (5005,6070) THEN 'TOTAL DIRECT EXPENSES'
                  WHEN E.ACCOUNT in (6220,4520,6406,6165,6168,6700,6701)  THEN 'INDIRECT EXPENSES'
                  --WHEN E.ACCOUNT = 6025 THEN 'TOTAL DIRECT EXPENSES' -- THIS CHANGE IS BASED ON Elizabth's request 05/24/2011
                  ELSE E.GROUPING_DIVISIONAL END
 ,N.ACTM1,N.ACTM2,N.ACTM3,N.CY_ACT_YTD
,N.PY_ACT_YTD,N.BUDGETM1,N.BUDGET_YTD
,cast(@CY_Year as int) as FISCAL_YEAR
 FROM LawsonRpt.dbo.ER_ACCOUNTS E
LEFT OUTER JOIN (SELECT * FROM RPT_TEMP_ACCOUNT_NEW WHERE REPORTNAME = 'DIVISIONAL'+@Timestamp
AND FISCAL_YEAR = @CY_Year)N
ON E.ACCOUNT = N.ACCOUNT
AND E.SUB_ACCOUNT = N.SUB_ACCOUNT
--WHERE E.ACCOUNT NOT IN (6328)                         --10/26/11 - as per Liz today, email 10/26-12:44 uncomenting /  05/31/11 aS PER lIZ, SOME ACCOUNTS NEEDS TO BE EXCLUDED FROM DIVISIONAL RPTs, not from CONSOLIDATED
DELETE FROM RPT_TEMP_ACCOUNT_NEW WHERE REPORTNAME = 'DIVISIONAL'+@Timestamp
Avatar of momi_sabag
momi_sabag
Flag of United States of America image

can you post the exact error message that you get?
You are selecting ACCT_UNIT from the following tables. If you're getting "invalid column name" I suspect one of them doesn't contain that column, or perhaps contains the column with a slightly different spelling:

ER_ACCOUNTS
GLNAMES
GLAMOUNTS
GLUNITS
FBDETAIL
Avatar of Metalteck

ASKER

This is the error message I get.
Lookup Error - SQL Server Database Error: Invalid column name 'ACCT_UNIT'.
I recommend that you run each of these individually, and see if you get that same error from any of them:

SELECT TOP 10 ACCT_UNIT FROM ER_ACCOUNTS

SELECT TOP 10 ACCT_UNIT FROM GLAMOUNTS

SELECT TOP 10 ACCT_UNIT FROM GLUNITS

SELECT TOP 10 ACCT_UNIT FROM FBDETAIL

SELECT TOP 10 ACCT_UNIT FROM GLNAMES
The only table that doesn't have Acct_Unit is ER_Accounts.
ASKER CERTIFIED SOLUTION
Avatar of Simone B
Simone B
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Yes, the other fields appear in the ER_Accounts table.
Very strange, I posted another comment and it seems to have vanished.

In any case, while this may solve your "invalid column name" error, I believe that you will have other issues in executing your code.

When using dynamic sql, you need to be able to take the text out of the single quotes, and what remains should be executable on its own (provided of course you plug in values for the variables.) The only thing that is normally outside of the single quotes would be the variables.

If you can give me some background and context on what you are trying to achieve, perhaps I can help a little more.
The current code works if I remove all instances of the Acct_Unit. The problem is that there is a particular acct_unit that is causing the financial numbers to be skewed and I'm trying to add acct_unit to the report so that I can troubelshoot.

I did not write this code and have been trying to figure it out.
If it works that's great. Then that should solve the invalid column problem.