Solved

Subquery returned more than 1 value

Posted on 2013-02-06
5
581 Views
Last Modified: 2013-02-07
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_SPECIALTY

      SET @AUcnt = 1
      SET @AUmax = ( SELECT      MAX(PRT_ORDER)
                           FROM            RPT_INDIVIDUAL_BURSTING_AU_SPECIALTY
                         )

      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

-------------------------- separate accounting units -----------------------------
                  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_Acct') )
                        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_BURSTING_AU_SPECIALTY
                                            WHERE            PRT_ORDER = @AUcnt
                                          ), @pt + 1) > 0
                        BEGIN
                              SET @previous = @pt
                              SET @pt = CHARINDEX(',',
                                                            ( SELECT      ACCT_UNIT
                                                              FROM            RPT_INDIVIDUAL_BURSTING_AU_SPECIALTY
                                                              WHERE            PRT_ORDER = @AUcnt
                                                            ), ( @pt + 1 ))
                              IF @pt >= 0
                                    BEGIN
                                          SET @p = CAST(LTRIM(RTRIM(SUBSTRING(( SELECT
                                                                                            ACCT_UNIT
                                                                                            FROM
                                                                                            RPT_INDIVIDUAL_BURSTING_AU_SPECIALTY
                                                                                            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_SPECIALTY
                                                                     WHERE      PRT_ORDER = @AUcnt
                                                                   ))),
                                                 ( LEN(LTRIM(RTRIM(( SELECT      ACCT_UNIT
                                                                               FROM      RPT_INDIVIDUAL_BURSTING_AU_SPECIALTY
                                                                               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))ORDER BY      COMPANY ASC
                                     ), '')

                  SET @SQL1 = 'SELECT A.ACCOUNT,A.FISCAL_YEAR,A.SUB_ACCOUNT,E.[GROUPING],E.ACCOUNT_DESC_ERDETAIL  AS ACCOUNT_DESC
           , 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.SUB_ACCOUNT,E.[GROUPING],E.ACCOUNT_DESC_ERDETAIL'
           
           
                  SET @SQL2 = 'SELECT B.ACCOUNT, B.FISCAL_YEAR,B.SUB_ACCOUNT,E.[GROUPING],E.ACCOUNT_DESC_ERDETAIL  AS ACCOUNT_DESC
          ,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.SUB_ACCOUNT,E.[GROUPING],E.ACCOUNT_DESC_ERDETAIL'
           
   
                  SET @SQL3 = 'SELECT U.ACCOUNT,U.FISCAL_YEAR,U.SUB_ACCOUNT,E.[GROUPING],E.ACCOUNT_DESC_ERDETAIL  AS ACCOUNT_DESC
           , 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.SUB_ACCOUNT,E.[GROUPING],E.ACCOUNT_DESC_ERDETAIL
          '
           

                  SET @SQL9 = 'SELECT E.ACCOUNT,E.SUB_ACCOUNT,E.SUB_GRP_DIVISIONAL
,ISNULL(FINAL.FISCAL_YEAR,0)
,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.ACT_PYTD,0)
,isnull(FINAL.BUDGETM1,0) ,isnull(FINAL.BUDGET_YTD,0)
,(select ltrim(rtrim(ACCT_UNIT)) from RPT_INDIVIDUAL_BURSTING_AU_SPECIALTY WHERE PRT_ORDER ='
                        + CAST(@AUcnt AS VARCHAR(3)) + ')
,0
,left(ltrim(rtrim(isnull(''' + @title + ''',''''))),255)
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,6700,6701)
--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,Title) as Title,
                  CASE WHEN ISNULL(LTRIM(RTRIM(A.REPORT_TITLE)), '') = ''
                         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_BURSTING_AU_SPECIALTY A ON A.ACCT_UNIT COLLATE DATABASE_DEFAULT = B.REPORTNAME COLLATE DATABASE_DEFAULT
      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
0
Comment
Question by:metalteck
  • 3
5 Comments
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
The error message would likely also tell you a line that the error was thrown.
 
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)
0
 

Author Comment

by:metalteck
Comment Utility
It says that the error is on line 129, which has this code:

WHILE CHARINDEX(',',
                                          ( SELECT      ACCT_UNIT
                                            FROM            dbo.RPT_INDIVIDUAL_BURSTING_AU_SPECIALTY
                                            WHERE            PRT_ORDER = @AUcnt
                                          ), @pt + 1) > 0
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
Comment Utility
Same concept.  The subquery is in the 2nd parameter of CHARINDEX, which expects only one value.

So some @AUcnt value is returning more than one row in the below T-SQL, which you can play with in SSMS until you figure out what that value(s) are, and how to handle them.

Declare @AUcnt int = ? 

SELECT      ACCT_UNIT
FROM            dbo.RPT_INDIVIDUAL_BURSTING_AU_SPECIALTY
 WHERE            PRT_ORDER = @AUcnt

Open in new window

0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
Comment Utility
SELECT ACCT_UNIT FROM RPT_INDIVIDUAL_BURSTING_AU_SPECIALTY WHERE PRT_ORDER = @AUcnt

Can that selection ever produce more than 1 row? (If it did you would have trouble.) e.g.

--last record
                  SET @lastrec = RIGHT(LTRIM(RTRIM(( SELECT      ACCT_UNIT
                                                                     FROM            RPT_INDIVIDUAL_BURSTING_AU_SPECIALTY
                                                                     WHERE      PRT_ORDER = @AUcnt
                                                                   ))),
                                                 ( LEN(LTRIM(RTRIM(( SELECT      ACCT_UNIT
                                                                               FROM      RPT_INDIVIDUAL_BURSTING_AU_SPECIALTY
                                                                               WHERE      PRT_ORDER = @AUcnt
                                                                           )))) - @pt ))

Open in new window

0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Thanks for the split.  Good luck with your project.  -Jim
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video discusses moving either the default database or any database to a new volume.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now