Subquery returned more than 1 value
Hey Guys,
I'm trying to resolve an issue with this code. The code runs successfully, but does not return any value. I checked the messages and it says:
Lookup Error - SQL Server Database Error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Can someone help shed some light on this for me.
Thanks
--Declare @Acct_Unit as varchar(20)
DECLARE @Month AS INT
DECLARE @Y_YEAR AS CHAR(4)
DECLARE @M_MONTH AS VARCHAR(25)
DECLARE @Year AS CHAR(4)
DECLARE @Month1 AS VARCHAR(25)
DECLARE @Month2 AS VARCHAR(25)
DECLARE @Month3 AS VARCHAR(25)
DECLARE @CY_Year AS VARCHAR(4)
DECLARE @PY_Year AS VARCHAR(4)
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 @LastDay AS VARCHAR(20)
/*Declare @Year as int
Declare @Month as int
----''21200'',''72000'')*/
SET @Y_YEAR = CAST(@Y_YEAR AS VARCHAR(4))
IF @Y_YEAR IS NULL
OR @Y_YEAR = ''
BEGIN
SET @Year = ( SELECT YEAR(DATEADD(mm, -1, GETDATE()))
)
END
ELSE
SET @Year = @Y_YEAR
IF @M_MONTH IS NULL
OR @M_MONTH = ''
BEGIN
SET @Month = ( SELECT MONTH(DATEADD(mm, -1, GETDATE()))
)
END
ELSE
SET @Month = @M_MONTH
DECLARE @SQL1 AS VARCHAR(5000)
DECLARE @SQL2 AS VARCHAR(5000)
DECLARE @SQL3 AS VARCHAR(5000)
DECLARE @SQL9 AS VARCHAR(8000)
DECLARE @AUcnt AS INT
DECLARE @AUmax AS INT
--SELECT * FROM RPT_INDIVIDUAL_BURSTING_AU
SET @AUcnt = 1
SET @AUmax = ( SELECT MAX(PRT_ORDER)
FROM RPT_INDIVIDUAL_BURSTING_AU
)
SET @CY_Year = @Year
SET @PY_Year = @Year - 1
SET @MonthNumber1 = ( SELECT CASE WHEN FLOOR(@Month) < = 9
THEN '0'
+ CAST(FLOOR(@Month) AS VARCHAR(1))
ELSE CAST(FLOOR(@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')
DELETE FROM RPT_INDIVIDUAL_BURST
WHILE @AUcnt <= @AUmax
BEGIN
--------------------------
DECLARE @previous AS INT
DECLARE @p AS VARCHAR(100)
DECLARE @pt AS INT
DECLARE @SQL_Acct AS VARCHAR(1000)
DECLARE @SQL_Acct1 AS VARCHAR(1000)
DECLARE @lastrec AS VARCHAR(100)
SET @Year = CAST(@Year AS INT)
SET @LastDay = ( SELECT CAST(DAY(MAX([DATE])) AS VARCHAR(2))
FROM dbo.RPT_BGT_WORKDAYS
WHERE FISCAL_YEAR = @Year
AND [MONTH] = FLOOR(@Month)
)
SET @previous = 0
SET @p = ''
SET @pt = 0
SET @lastrec = ''
IF EXISTS ( SELECT *
FROM tempdb.dbo.sysobjects
WHERE ID = OBJECT_ID('tempdb.dbo.##T_
BEGIN
DROP TABLE tempdb.##T_Acct
END
DECLARE @title AS VARCHAR(255)
SET @title = ''
CREATE TABLE tempdb.##T_Acct
(
Acct_Unit VARCHAR(15) COLLATE Latin1_General_BIN
)
WHILE CHARINDEX(',',
( SELECT ACCT_UNIT
FROM dbo.RPT_INDIVIDUAL_BURSTIN
WHERE PRT_ORDER = @AUcnt
), @pt + 1) > 0
BEGIN
SET @previous = @pt
SET @pt = CHARINDEX(',',
( SELECT ACCT_UNIT
FROM RPT_INDIVIDUAL_BURSTING_AU
WHERE PRT_ORDER = @AUcnt
), ( @pt + 1 ))
IF @pt >= 0
BEGIN
SET @p = CAST(LTRIM(RTRIM(SUBSTRING
ACCT_UNIT
FROM
RPT_INDIVIDUAL_BURSTING_AU
WHERE
PRT_ORDER = @AUcnt
),
( @previous + 1 ),
( ( @pt
- @previous )
- 1 )))) AS VARCHAR)
SET @previous = ( @pt - @previous ) - 1
SET @SQL_Acct = 'insert into tempdb.##T_Acct (Acct_Unit) Select LTRIM(RTRIM('''
+ @p + '''))'
EXEC(@SQL_Acct)
SET @title = SPACE(1) + ISNULL(@title, '')
+ ISNULL(( SELECT TOP 1
SPACE(2)
+ CAST(ACCT_UNIT AS VARCHAR(5))
+ SPACE(1)
+ LTRIM(RTRIM(DESCRIPTION)) AS description
FROM LSPROD.dbo.GLNAMES
WHERE LEVEL_DEPTH = 4
AND ACCT_UNIT = +LTRIM(RTRIM(@p))ORDER BY COMPANY ASC
) + '', '')
END
END
--last record
SET @lastrec = RIGHT(LTRIM(RTRIM(( SELECT ACCT_UNIT
FROM RPT_INDIVIDUAL_BURSTING_AU
WHERE PRT_ORDER = @AUcnt
))),
( LEN(LTRIM(RTRIM(( SELECT ACCT_UNIT
FROM RPT_INDIVIDUAL_BURSTING_AU
WHERE PRT_ORDER = @AUcnt
)))) - @pt ))
SET @SQL_Acct1 = 'insert into tempdb.##T_Acct (Acct_Unit) select LTRIM(RTRIM('''
+ @lastrec + '''))'
EXEC (@SQL_Acct1)
SET @title = SPACE(1) + ISNULL(@title, '')
+ ISNULL(( SELECT TOP 1
SPACE(2) + CAST(ACCT_UNIT AS VARCHAR(5))
+ LTRIM(RTRIM(DESCRIPTION)) AS description
FROM LSPROD.dbo.GLNAMES
WHERE LEVEL_DEPTH = 4
AND ACCT_UNIT = +LTRIM(RTRIM(@lastrec))ORD
), '')
SET @SQL1 = 'SELECT A.ACCOUNT,A.FISCAL_YEAR,A.
, SUM(A.' + REPLACE(@Month1, 'DB', 'A.DB') + ') AS ACTM1
, SUM(A.' + REPLACE(@Month2, 'DB', 'A.DB') + ') AS ACTM2
, SUM(A.' + REPLACE(@Month3, 'DB', 'A.DB') + ') AS ACTM3
, SUM(CASE WHEN A.FISCAL_YEAR IN (' + @CY_Year + ') THEN '
+ @Act_YTD + ' ELSE 0 END ) AS ACT_CYTD
, SUM(CASE WHEN A.FISCAL_YEAR IN (' + @PY_Year + ') THEN '
+ @Act_YTD + ' ELSE 0 END ) AS ACT_PYTD
,0 AS BUDGETM1,0 AS BUDGET_YTD,' + @LastDay
+ ' AS MONTH_LASTDAY
FROM ER_ACCOUNTS E
inner join LSPROD.dbo.GLAMOUNTS A ON A.ACCOUNT = E.ACCOUNT AND A.SUB_ACCOUNT = E.SUB_ACCOUNT WHERE '
+ CASE WHEN @AUcnt = 45
THEN '(A.ACCT_UNIT IN (Select * from tempdb.##T_Acct) and A.COMPANY = ''16'')'
ELSE +' A.ACCT_UNIT IN ( Select * from tempdb.##T_Acct)'
END + 'AND A.FISCAL_YEAR IN (' + @PY_Year + ',' + @CY_Year
+ ')AND A.ACCOUNT <> 4000
GROUP BY A.ACCOUNT,A.FISCAL_YEAR,A.
SET @SQL2 = 'SELECT B.ACCOUNT, B.FISCAL_YEAR,B.SUB_ACCOUN
,0,0,0,0,0 , SUM( CASE WHEN B.ACCOUNT =4000 THEN ' + REPLACE(@Month1,
'AMOUNT',
'UNITS') + '
ELSE B.' + REPLACE(@Month1, 'DB', 'B.DB') + ' END )AS BUDGETM1
, 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 )AS BUDGET_YTD ,'
+ @LastDay
+ ' AS MONTH_LASTDAY
FROM ER_ACCOUNTS E inner join LSPROD.dbo.FBDETAIL B ON B.ACCOUNT = E.ACCOUNT AND B.SUB_ACCOUNT = E.SUB_ACCOUNT
WHERE'
+ +CASE WHEN @AUcnt = 45
THEN '(B.ACCT_UNIT IN (Select * from tempdb.##T_Acct) and B.COMPANY = ''16'')'
ELSE +' B.ACCT_UNIT IN ( Select * from tempdb.##T_Acct)'
END + ' AND B.FISCAL_YEAR IN (' + @PY_Year + ',' + @CY_Year
+ ') AND B.BUDGET_NBR = 1
group by B.ACCOUNT,B.FISCAL_YEAR,B.
SET @SQL3 = 'SELECT U.ACCOUNT,U.FISCAL_YEAR,U.
, SUM(' + REPLACE(REPLACE(@Month1, 'DB_AMOUNT', 'U.DB_UNITS'),
'CR_AMOUNT', 'U.CR_UNITS') + ') AS ACTM1
, SUM(' + REPLACE(REPLACE(@Month2, 'DB_AMOUNT', 'U.DB_UNITS'),
'CR_AMOUNT', 'U.CR_UNITS') + ') AS ACTM2
, SUM(' + REPLACE(REPLACE(@Month3, 'DB_AMOUNT', 'U.DB_UNITS'),
'CR_AMOUNT', 'U.CR_UNITS') + ') AS ACTM3
, SUM(CASE WHEN U.FISCAL_YEAR IN (' + @CY_Year + ') THEN '
+ @Act_YTD_Units + ' ELSE 0 END ) AS ACT_YTD
, SUM(CASE WHEN U.FISCAL_YEAR IN (' + @PY_Year + ')THEN '
+ @Act_YTD_Units + ' ELSE 0 END ) AS ACT_PYTD
,0,0,' + @LastDay + ' AS MONTH_LASTDAY
FROM ER_ACCOUNTS E
inner join LSPROD.dbo.GLUNITS U
ON U.ACCOUNT = E.ACCOUNT
AND U.SUB_ACCOUNT = E.SUB_ACCOUNT
WHERE --U.COMPANY = 1 AND
U.ACCOUNT =4000
AND U.SUB_ACCOUNT =0
AND U.ACCT_UNIT IN ( Select * from tempdb.##T_Acct)
AND U.FISCAL_YEAR IN (' + @PY_Year + ',' + @CY_Year
+ ')
GROUP BY U.ACCOUNT,U.FISCAL_YEAR,U.
'
SET @SQL9 = 'SELECT E.ACCOUNT,E.SUB_ACCOUNT,E.
,ISNULL(FINAL.FISCAL_YEAR,
,CASE WHEN E.ACCOUNT IN (5002,5003) THEN ''TOTAL DIRECT PAYROLL EXPENSES''
WHEN E.ACCOUNT IN (6070,6255,5005) THEN ''TOTAL DIRECT EXPENSES''
ELSE E.GROUPING END
,CASE WHEN E.ACCOUNT IN (6235)THEN ''LEGAL FEES - MALPRACTICE''
WHEN E.ACCOUNT IN (6010)THEN ''EXHIBITIONS''
--WHEN E.ACCOUNT IN (6360,6370,6380) THEN ''PAYROLL - TAXES''
ELSE E.ACCOUNT_DESC_ERDETAIL END
,isnull(FINAL.ACTM1,0)
,isnull(FINAL.ACTM2,0)
,isnull(FINAL.ACTM3,0)
,isnull(FINAL.ACT_CYTD,0),
,isnull(FINAL.BUDGETM1,0) ,isnull(FINAL.BUDGET_YTD,0
,(select ltrim(rtrim(ACCT_UNIT)) from RPT_INDIVIDUAL_BURSTING_AU
+ CAST(@AUcnt AS VARCHAR(3)) + ')
,0
,left(ltrim(rtrim(isnull('
FROM ER_ACCOUNTS E
LEFT OUTER JOIN
(' + @SQL1 + ' UNION ( ' + @SQL3 + ' ) UNION (' + @SQL2 + ') ) AS FINAL
ON E.ACCOUNT = FINAL.ACCOUNT
AND E.SUB_ACCOUNT = FINAL.SUB_ACCOUNT
where E.ACCOUNT NOT IN (6220,4520,6406,6165,6168,
--AND E.FISCAL_YEAR = FINAL.FISCAL_YEAR'
INSERT INTO RPT_INDIVIDUAL_BURST
( ACCOUNT ,
SUB_ACCOUNT ,
SUB_GRP ,
FISCAL_YEAR ,
HEAD_NAME ,
ACCOUNT_DESC ,
ACTM1 ,
ACTM2 ,
ACTM3 ,
CY_ACT_YTD ,
PY_ACT_YTD ,
BUDGETM1 ,
BUDGET_YTD ,
REPORTNAME ,
ACCOUNT_UNIT ,
Title
)
EXEC ( @SQL9
)
--select cast(CURRENT_TIMESTAMP as char(25)) + cast(@AUcnt as char(5))+(@SQL9)
SET @AUcnt = @AUcnt + 1
END
--SELECT @SQL9
SELECT ACCOUNT ,
SUB_ACCOUNT ,
SUB_GRP ,
FISCAL_YEAR ,
HEAD_NAME ,
ACCOUNT_DESC ,
A.COMPANY ,
SUM(ACTM1) AS ACTM1 ,
SUM(ACTM2) AS ACTM2 ,
SUM(ACTM3) AS ACTM3 ,
SUM(CY_ACT_YTD) AS CY_ACT_YTD ,
SUM(PY_ACT_YTD) AS PY_ACT_YTD ,
SUM(BUDGETM1) AS BUDGETM1 ,
SUM(BUDGET_YTD) AS BUDGET_YTD ,
REPORTNAME AS ACCT_UNIT ,
--isnull(A.REPORT_TITLE,Ti
CASE WHEN ISNULL(LTRIM(RTRIM(A.REPOR
THEN B.Title
ELSE LTRIM(RTRIM(A.REPORT_TITLE
END AS Title ,
@LastDay AS MONTH_LASTDAY ,
@Year AS Yr ,
@Month AS Mnth ,
A.PRT_ORDER ,
A.SEQ_NUMBER
FROM RPT_INDIVIDUAL_BURST B
INNER JOIN dbo.RPT_INDIVIDUAL_BURSTIN
GROUP BY ACCOUNT ,
SUB_ACCOUNT ,
SUB_GRP ,
FISCAL_YEAR ,
HEAD_NAME ,
ACCOUNT_DESC ,
A.COMPANY ,
REPORTNAME ,
Title ,
A.REPORT_TITLE ,
A.PRT_ORDER ,
A.SEQ_NUMBER
ORDER BY A.PRT_ORDER
--EXEC (@SQL1)
--set @AUcnt=@AUcnt+1
--end
I'm trying to resolve an issue with this code. The code runs successfully, but does not return any value. I checked the messages and it says:
Lookup Error - SQL Server Database Error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Can someone help shed some light on this for me.
Thanks
--Declare @Acct_Unit as varchar(20)
DECLARE @Month AS INT
DECLARE @Y_YEAR AS CHAR(4)
DECLARE @M_MONTH AS VARCHAR(25)
DECLARE @Year AS CHAR(4)
DECLARE @Month1 AS VARCHAR(25)
DECLARE @Month2 AS VARCHAR(25)
DECLARE @Month3 AS VARCHAR(25)
DECLARE @CY_Year AS VARCHAR(4)
DECLARE @PY_Year AS VARCHAR(4)
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 @LastDay AS VARCHAR(20)
/*Declare @Year as int
Declare @Month as int
----''21200'',''72000'')*/
SET @Y_YEAR = CAST(@Y_YEAR AS VARCHAR(4))
IF @Y_YEAR IS NULL
OR @Y_YEAR = ''
BEGIN
SET @Year = ( SELECT YEAR(DATEADD(mm, -1, GETDATE()))
)
END
ELSE
SET @Year = @Y_YEAR
IF @M_MONTH IS NULL
OR @M_MONTH = ''
BEGIN
SET @Month = ( SELECT MONTH(DATEADD(mm, -1, GETDATE()))
)
END
ELSE
SET @Month = @M_MONTH
DECLARE @SQL1 AS VARCHAR(5000)
DECLARE @SQL2 AS VARCHAR(5000)
DECLARE @SQL3 AS VARCHAR(5000)
DECLARE @SQL9 AS VARCHAR(8000)
DECLARE @AUcnt AS INT
DECLARE @AUmax AS INT
--SELECT * FROM RPT_INDIVIDUAL_BURSTING_AU
SET @AUcnt = 1
SET @AUmax = ( SELECT MAX(PRT_ORDER)
FROM RPT_INDIVIDUAL_BURSTING_AU
)
SET @CY_Year = @Year
SET @PY_Year = @Year - 1
SET @MonthNumber1 = ( SELECT CASE WHEN FLOOR(@Month) < = 9
THEN '0'
+ CAST(FLOOR(@Month) AS VARCHAR(1))
ELSE CAST(FLOOR(@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')
DELETE FROM RPT_INDIVIDUAL_BURST
WHILE @AUcnt <= @AUmax
BEGIN
--------------------------
DECLARE @previous AS INT
DECLARE @p AS VARCHAR(100)
DECLARE @pt AS INT
DECLARE @SQL_Acct AS VARCHAR(1000)
DECLARE @SQL_Acct1 AS VARCHAR(1000)
DECLARE @lastrec AS VARCHAR(100)
SET @Year = CAST(@Year AS INT)
SET @LastDay = ( SELECT CAST(DAY(MAX([DATE])) AS VARCHAR(2))
FROM dbo.RPT_BGT_WORKDAYS
WHERE FISCAL_YEAR = @Year
AND [MONTH] = FLOOR(@Month)
)
SET @previous = 0
SET @p = ''
SET @pt = 0
SET @lastrec = ''
IF EXISTS ( SELECT *
FROM tempdb.dbo.sysobjects
WHERE ID = OBJECT_ID('tempdb.dbo.##T_
BEGIN
DROP TABLE tempdb.##T_Acct
END
DECLARE @title AS VARCHAR(255)
SET @title = ''
CREATE TABLE tempdb.##T_Acct
(
Acct_Unit VARCHAR(15) COLLATE Latin1_General_BIN
)
WHILE CHARINDEX(',',
( SELECT ACCT_UNIT
FROM dbo.RPT_INDIVIDUAL_BURSTIN
WHERE PRT_ORDER = @AUcnt
), @pt + 1) > 0
BEGIN
SET @previous = @pt
SET @pt = CHARINDEX(',',
( SELECT ACCT_UNIT
FROM RPT_INDIVIDUAL_BURSTING_AU
WHERE PRT_ORDER = @AUcnt
), ( @pt + 1 ))
IF @pt >= 0
BEGIN
SET @p = CAST(LTRIM(RTRIM(SUBSTRING
ACCT_UNIT
FROM
RPT_INDIVIDUAL_BURSTING_AU
WHERE
PRT_ORDER = @AUcnt
),
( @previous + 1 ),
( ( @pt
- @previous )
- 1 )))) AS VARCHAR)
SET @previous = ( @pt - @previous ) - 1
SET @SQL_Acct = 'insert into tempdb.##T_Acct (Acct_Unit) Select LTRIM(RTRIM('''
+ @p + '''))'
EXEC(@SQL_Acct)
SET @title = SPACE(1) + ISNULL(@title, '')
+ ISNULL(( SELECT TOP 1
SPACE(2)
+ CAST(ACCT_UNIT AS VARCHAR(5))
+ SPACE(1)
+ LTRIM(RTRIM(DESCRIPTION)) AS description
FROM LSPROD.dbo.GLNAMES
WHERE LEVEL_DEPTH = 4
AND ACCT_UNIT = +LTRIM(RTRIM(@p))ORDER BY COMPANY ASC
) + '', '')
END
END
--last record
SET @lastrec = RIGHT(LTRIM(RTRIM(( SELECT ACCT_UNIT
FROM RPT_INDIVIDUAL_BURSTING_AU
WHERE PRT_ORDER = @AUcnt
))),
( LEN(LTRIM(RTRIM(( SELECT ACCT_UNIT
FROM RPT_INDIVIDUAL_BURSTING_AU
WHERE PRT_ORDER = @AUcnt
)))) - @pt ))
SET @SQL_Acct1 = 'insert into tempdb.##T_Acct (Acct_Unit) select LTRIM(RTRIM('''
+ @lastrec + '''))'
EXEC (@SQL_Acct1)
SET @title = SPACE(1) + ISNULL(@title, '')
+ ISNULL(( SELECT TOP 1
SPACE(2) + CAST(ACCT_UNIT AS VARCHAR(5))
+ LTRIM(RTRIM(DESCRIPTION)) AS description
FROM LSPROD.dbo.GLNAMES
WHERE LEVEL_DEPTH = 4
AND ACCT_UNIT = +LTRIM(RTRIM(@lastrec))ORD
), '')
SET @SQL1 = 'SELECT A.ACCOUNT,A.FISCAL_YEAR,A.
, SUM(A.' + REPLACE(@Month1, 'DB', 'A.DB') + ') AS ACTM1
, SUM(A.' + REPLACE(@Month2, 'DB', 'A.DB') + ') AS ACTM2
, SUM(A.' + REPLACE(@Month3, 'DB', 'A.DB') + ') AS ACTM3
, SUM(CASE WHEN A.FISCAL_YEAR IN (' + @CY_Year + ') THEN '
+ @Act_YTD + ' ELSE 0 END ) AS ACT_CYTD
, SUM(CASE WHEN A.FISCAL_YEAR IN (' + @PY_Year + ') THEN '
+ @Act_YTD + ' ELSE 0 END ) AS ACT_PYTD
,0 AS BUDGETM1,0 AS BUDGET_YTD,' + @LastDay
+ ' AS MONTH_LASTDAY
FROM ER_ACCOUNTS E
inner join LSPROD.dbo.GLAMOUNTS A ON A.ACCOUNT = E.ACCOUNT AND A.SUB_ACCOUNT = E.SUB_ACCOUNT WHERE '
+ CASE WHEN @AUcnt = 45
THEN '(A.ACCT_UNIT IN (Select * from tempdb.##T_Acct) and A.COMPANY = ''16'')'
ELSE +' A.ACCT_UNIT IN ( Select * from tempdb.##T_Acct)'
END + 'AND A.FISCAL_YEAR IN (' + @PY_Year + ',' + @CY_Year
+ ')AND A.ACCOUNT <> 4000
GROUP BY A.ACCOUNT,A.FISCAL_YEAR,A.
SET @SQL2 = 'SELECT B.ACCOUNT, B.FISCAL_YEAR,B.SUB_ACCOUN
,0,0,0,0,0 , SUM( CASE WHEN B.ACCOUNT =4000 THEN ' + REPLACE(@Month1,
'AMOUNT',
'UNITS') + '
ELSE B.' + REPLACE(@Month1, 'DB', 'B.DB') + ' END )AS BUDGETM1
, 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 )AS BUDGET_YTD ,'
+ @LastDay
+ ' AS MONTH_LASTDAY
FROM ER_ACCOUNTS E inner join LSPROD.dbo.FBDETAIL B ON B.ACCOUNT = E.ACCOUNT AND B.SUB_ACCOUNT = E.SUB_ACCOUNT
WHERE'
+ +CASE WHEN @AUcnt = 45
THEN '(B.ACCT_UNIT IN (Select * from tempdb.##T_Acct) and B.COMPANY = ''16'')'
ELSE +' B.ACCT_UNIT IN ( Select * from tempdb.##T_Acct)'
END + ' AND B.FISCAL_YEAR IN (' + @PY_Year + ',' + @CY_Year
+ ') AND B.BUDGET_NBR = 1
group by B.ACCOUNT,B.FISCAL_YEAR,B.
SET @SQL3 = 'SELECT U.ACCOUNT,U.FISCAL_YEAR,U.
, SUM(' + REPLACE(REPLACE(@Month1, 'DB_AMOUNT', 'U.DB_UNITS'),
'CR_AMOUNT', 'U.CR_UNITS') + ') AS ACTM1
, SUM(' + REPLACE(REPLACE(@Month2, 'DB_AMOUNT', 'U.DB_UNITS'),
'CR_AMOUNT', 'U.CR_UNITS') + ') AS ACTM2
, SUM(' + REPLACE(REPLACE(@Month3, 'DB_AMOUNT', 'U.DB_UNITS'),
'CR_AMOUNT', 'U.CR_UNITS') + ') AS ACTM3
, SUM(CASE WHEN U.FISCAL_YEAR IN (' + @CY_Year + ') THEN '
+ @Act_YTD_Units + ' ELSE 0 END ) AS ACT_YTD
, SUM(CASE WHEN U.FISCAL_YEAR IN (' + @PY_Year + ')THEN '
+ @Act_YTD_Units + ' ELSE 0 END ) AS ACT_PYTD
,0,0,' + @LastDay + ' AS MONTH_LASTDAY
FROM ER_ACCOUNTS E
inner join LSPROD.dbo.GLUNITS U
ON U.ACCOUNT = E.ACCOUNT
AND U.SUB_ACCOUNT = E.SUB_ACCOUNT
WHERE --U.COMPANY = 1 AND
U.ACCOUNT =4000
AND U.SUB_ACCOUNT =0
AND U.ACCT_UNIT IN ( Select * from tempdb.##T_Acct)
AND U.FISCAL_YEAR IN (' + @PY_Year + ',' + @CY_Year
+ ')
GROUP BY U.ACCOUNT,U.FISCAL_YEAR,U.
'
SET @SQL9 = 'SELECT E.ACCOUNT,E.SUB_ACCOUNT,E.
,ISNULL(FINAL.FISCAL_YEAR,
,CASE WHEN E.ACCOUNT IN (5002,5003) THEN ''TOTAL DIRECT PAYROLL EXPENSES''
WHEN E.ACCOUNT IN (6070,6255,5005) THEN ''TOTAL DIRECT EXPENSES''
ELSE E.GROUPING END
,CASE WHEN E.ACCOUNT IN (6235)THEN ''LEGAL FEES - MALPRACTICE''
WHEN E.ACCOUNT IN (6010)THEN ''EXHIBITIONS''
--WHEN E.ACCOUNT IN (6360,6370,6380) THEN ''PAYROLL - TAXES''
ELSE E.ACCOUNT_DESC_ERDETAIL END
,isnull(FINAL.ACTM1,0)
,isnull(FINAL.ACTM2,0)
,isnull(FINAL.ACTM3,0)
,isnull(FINAL.ACT_CYTD,0),
,isnull(FINAL.BUDGETM1,0) ,isnull(FINAL.BUDGET_YTD,0
,(select ltrim(rtrim(ACCT_UNIT)) from RPT_INDIVIDUAL_BURSTING_AU
+ CAST(@AUcnt AS VARCHAR(3)) + ')
,0
,left(ltrim(rtrim(isnull('
FROM ER_ACCOUNTS E
LEFT OUTER JOIN
(' + @SQL1 + ' UNION ( ' + @SQL3 + ' ) UNION (' + @SQL2 + ') ) AS FINAL
ON E.ACCOUNT = FINAL.ACCOUNT
AND E.SUB_ACCOUNT = FINAL.SUB_ACCOUNT
where E.ACCOUNT NOT IN (6220,4520,6406,6165,6168,
--AND E.FISCAL_YEAR = FINAL.FISCAL_YEAR'
INSERT INTO RPT_INDIVIDUAL_BURST
( ACCOUNT ,
SUB_ACCOUNT ,
SUB_GRP ,
FISCAL_YEAR ,
HEAD_NAME ,
ACCOUNT_DESC ,
ACTM1 ,
ACTM2 ,
ACTM3 ,
CY_ACT_YTD ,
PY_ACT_YTD ,
BUDGETM1 ,
BUDGET_YTD ,
REPORTNAME ,
ACCOUNT_UNIT ,
Title
)
EXEC ( @SQL9
)
--select cast(CURRENT_TIMESTAMP as char(25)) + cast(@AUcnt as char(5))+(@SQL9)
SET @AUcnt = @AUcnt + 1
END
--SELECT @SQL9
SELECT ACCOUNT ,
SUB_ACCOUNT ,
SUB_GRP ,
FISCAL_YEAR ,
HEAD_NAME ,
ACCOUNT_DESC ,
A.COMPANY ,
SUM(ACTM1) AS ACTM1 ,
SUM(ACTM2) AS ACTM2 ,
SUM(ACTM3) AS ACTM3 ,
SUM(CY_ACT_YTD) AS CY_ACT_YTD ,
SUM(PY_ACT_YTD) AS PY_ACT_YTD ,
SUM(BUDGETM1) AS BUDGETM1 ,
SUM(BUDGET_YTD) AS BUDGET_YTD ,
REPORTNAME AS ACCT_UNIT ,
--isnull(A.REPORT_TITLE,Ti
CASE WHEN ISNULL(LTRIM(RTRIM(A.REPOR
THEN B.Title
ELSE LTRIM(RTRIM(A.REPORT_TITLE
END AS Title ,
@LastDay AS MONTH_LASTDAY ,
@Year AS Yr ,
@Month AS Mnth ,
A.PRT_ORDER ,
A.SEQ_NUMBER
FROM RPT_INDIVIDUAL_BURST B
INNER JOIN dbo.RPT_INDIVIDUAL_BURSTIN
GROUP BY ACCOUNT ,
SUB_ACCOUNT ,
SUB_GRP ,
FISCAL_YEAR ,
HEAD_NAME ,
ACCOUNT_DESC ,
A.COMPANY ,
REPORTNAME ,
Title ,
A.REPORT_TITLE ,
A.PRT_ORDER ,
A.SEQ_NUMBER
ORDER BY A.PRT_ORDER
--EXEC (@SQL1)
--set @AUcnt=@AUcnt+1
--end
ASKER
It says that the error is on line 129, which has this code:
WHILE CHARINDEX(',',
( SELECT ACCT_UNIT
FROM dbo.RPT_INDIVIDUAL_BURSTIN
WHERE PRT_ORDER = @AUcnt
), @pt + 1) > 0
WHILE CHARINDEX(',',
( SELECT ACCT_UNIT
FROM dbo.RPT_INDIVIDUAL_BURSTIN
WHERE PRT_ORDER = @AUcnt
), @pt + 1) > 0
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks for the split. Good luck with your project. -Jim
Chances are that line is one of these statements, where the SELECT is returning more than one value, which will throw that error when it attempts to shove multiple values into a single variable:
SET @some_variable = (SELECT some query)