none
Using all results in a multiple row resultset returned by a subquery

    Question

  • I solved the problem of splitting up the dimension fields for each account code as referenced in my first post.  Now I am needing to return the budget amounts of each account code (some account codes return multiple amounts).  I need to do this for five sets of years so that I show each year in its own column (a Proposed Budget Year, a Current Budget Year, and three historical budget years).

    Right now I am attempting to use joins to the three tables that show the different types of amounts.  Some account codes will return multiple amount values for the same account code in the same year.  I need all of these amounts in a row associated with the account code so that I can group and sum them later on by differing criteria from the dimension fields.  Here is what I have so far (it returns the account codes and dimension fields correctly, but the amounts aren't working and I can't figure out how to make them work.

    USE [Financial]
    
    -- Input variables from web page
    DECLARE	@pkYear			INT;
    DECLARE	@Fund			INT;
    DECLARE	@Function		INT;
    DECLARE	@FundingSource		INT;
    DECLARE	@OperationalUnit	INT;
    DECLARE	@JobClass		INT;
    DECLARE	@Version		INT;
    DECLARE	@Object			INT;
    DECLARE	@Instorg		INT;
    DECLARE	@SubMat			INT;
    DECLARE	@SCC			INT;
    DECLARE	@ExcludeNoAct		INT;
    DECLARE	@CurrentUser		INT;
    
    -- Variables to calculate budget years
    DECLARE	@PeriodStartDate	VARCHAR(256); 
    DECLARE	@PeriodEndDate		VARCHAR(256);
    DECLARE	@ProposedBudgetYear	INT;
    DECLARE	@CurrentBudgetYear	INT;
    DECLARE	@BudgetYear1		INT;
    DECLARE	@BudgetYear2		INT;
    DECLARE	@BudgetYear3		INT;
    DECLARE	@YearStartDateYear	INT;
    DECLARE	@ProposedYearStartDateYear	INT;
    DECLARE	@Year1StartDateYear	INT;
    DECLARE	@Year2StartDateYear	INT;
    DECLARE	@Year3StartDateYear	INT;
    DECLARE @YearStartDate		DATE;
    
    -- Set variables to test query
    SET @pkYear = 5;
    SET @ExcludeNoAct = 0;
    SET @CurrentUser = 1;
    SET @YearStartDate = (SELECT TOP 1 BeginDate FROM fiYear Y
                              Where Y.pkYear = @pkYear);
    
    PRINT @pkYear;
    PRINT @YearStartDate;
    
    -- Set variables for budget years
    SET @YearStartDateYear = YEAR(@YearStartDate);
    SET @ProposedYearStartDateYear = @YearStartDateYear + 1;
    SET @Year1StartDateYear = @YearStartDateYear - 1;
    SET @Year2StartDateYear = @YearStartDateYear - 2;
    SET @Year3StartDateYear = @YearStartDateYear - 3;
    
    PRINT @YearStartDateYear;
    PRINT @ProposedYearStartDateYear;
    PRINT @Year1StartDateYear;
    PRINT @Year2StartDateYear;
    PRINT @Year3StartDateYear;
    
    -- Set budget years based on year input parameter
    SET @ProposedBudgetYear = (SELECT pkYear
    FROM fiYear
    WHERE YEAR(fiYear.BeginDate) = @ProposedYearStartDateYear)
    
    SET @CurrentBudgetYear = (SELECT pkYear
    FROM fiYear
    WHERE YEAR(fiYear.BeginDate) = @YearStartDateYear)
    
    SET @BudgetYear1 = (SELECT pkYear
    FROM fiYear
    WHERE YEAR(fiYear.BeginDate) = @Year1StartDateYear)
    
    SET @BudgetYear2 = (SELECT pkYear
    FROM fiYear
    WHERE YEAR(fiYear.BeginDate) = @Year2StartDateYear)
    
    SET @BudgetYear3 = (SELECT pkYear
    FROM fiYear
    WHERE YEAR(fiYear.BeginDate) = @Year3StartDateYear)
    
    PRINT @ProposedBudgetYear;
    PRINT @CurrentBudgetYear;
    PRINT @BudgetYear1;
    PRINT @BudgetYear2;
    PRINT @BudgetYear3;
    
    -- Query building exercise
    
    
    DECLARE @fkState		INT
    DECLARE @fkDimPosFund	INT
    DECLARE @fkDimPosFunction INT
    DECLARE @fkDimPosObject INT
    DECLARE @fkDimPosFundingSource INT
    DECLARE @fkDimPosInstructionalOrganization INT
    DECLARE @fkDimPosOperationalUnit INT
    DECLARE @fkDimPosSubjectMatter INT
    DECLARE @fkDimPosJobClass INT
    DECLARE @fkDimPosSpeclCostCntr INT
    
    SET @fkState = 40
    SET @Version = 11
    --Get the position of the fund account code dimensions determined by state
    	SELECT @fkDimPosFund = pkDimensionPosition FROM fiDimensionPosition WHERE Title = 'FUND' AND @fkState = fiDimensionPosition.fkState
    	SELECT @fkDimPosFunction = pkDimensionPosition FROM fiDimensionPosition WHERE Title = 'FUNCTION' AND @fkState = fiDimensionPosition.fkState
    	SELECT @fkDimPosObject = pkDimensionPosition FROM fiDimensionPosition WHERE Title = 'OBJECT' AND @fkState = fiDimensionPosition.fkState
    	SELECT @fkDimPosFundingSource = pkDimensionPosition FROM fiDimensionPosition WHERE Title = 'FUNDING SOURCE' AND @fkState = fiDimensionPosition.fkState
    	SELECT @fkDimPosInstructionalOrganization = pkDimensionPosition FROM fiDimensionPosition WHERE Title = 'INSTRUCTIONAL ORGANIZATION' AND @fkState = fiDimensionPosition.fkState
    	SELECT @fkDimPosOperationalUnit = pkDimensionPosition FROM fiDimensionPosition WHERE Title = 'OPERATIONAL UNIT' AND @fkState = fiDimensionPosition.fkState
    	SELECT @fkDimPosSubjectMatter = pkDimensionPosition FROM fiDimensionPosition WHERE Title = 'SUBJECT MATTER' AND @fkState = fiDimensionPosition.fkState
    	SELECT @fkDimPosJobClass = pkDimensionPosition FROM fiDimensionPosition WHERE Title = 'JOB CLASS' AND @fkState = fiDimensionPosition.fkState
    	SELECT @fkDimPosSpeclCostCntr = pkDimensionPosition FROM fiDimensionPosition WHERE Title = 'SPECIAL COST CENTER' AND @fkState = fiDimensionPosition.fkState
    
    PRINT @fkDimPosFund
    PRINT @fkDimPosFunction
    PRINT @fkDimPosObject
    PRINT @fkDimPosFundingSource
    PRINT @fkDimPosInstructionalOrganization
    PRINT @fkDimPosOperationalUnit
    PRINT @fkDimPosSubjectMatter
    PRINT @fkDimPosJobClass
    PRINT @fkDimPosSpeclCostCntr
    
    SELECT ACCTCODE.fkYear
    ,Y.YearID
    ,ACCTCODE.AccountCode
    ,DIMFUND.Dimension AS [Fund]
    ,DIMFUNCTION.Dimension AS [Function]
    ,DIMOBJECT.Dimension AS [Object]
    ,(SELECT LEFT ( DIMOBJECT.Dimension , 1 )) AS [MAJOR OBJECT] 
    ,DIMFUNDINGSOURCE.Dimension AS [Funding Source]
    ,DIMFUNDINGINSTRUCTIONALORG.Dimension AS [Instructional Organization]
    ,DIMOPUNIT.Dimension AS [Operational Unit]
    ,DIMSUBMATTER.Dimension AS [Subject Matter]
    ,DIMJOBCLASS.Dimension AS [Job Classification]
    ,DIMSPECLCOSTCNTR.Dimension AS [Special Cost Center]
    ,BP.Amount AS Proposed
    ,B.Amount AS [Current]
    ,T1.Amount AS [Historical]
    FROM fiAccountCode ACCTCODE
    JOIN fiDimension DIMFUND 			
    				ON DIMFUND.fkDimensionPosition = @fkDimPosFund	
    				AND (DIMFUND.pkDimension = ACCTCODE.fkDimension1
    				OR DIMFUND.pkDimension = ACCTCODE.fkDimension2
    				OR DIMFUND.pkDimension = ACCTCODE.fkDimension3
    				OR DIMFUND.pkDimension = ACCTCODE.fkDimension4
    				OR DIMFUND.pkDimension = ACCTCODE.fkDimension5
    				OR DIMFUND.pkDimension = ACCTCODE.fkDimension6
    				OR DIMFUND.pkDimension = ACCTCODE.fkDimension7
    				OR DIMFUND.pkDimension = ACCTCODE.fkDimension8
    				OR DIMFUND.pkDimension = ACCTCODE.fkDimension9
    			)
    JOIN fiDimension DIMFUNCTION
    			ON DIMFUNCTION.fkDimensionPosition = @fkDimPosFund	
    			AND (DIMFUNCTION.pkDimension = ACCTCODE.fkDimension1
    				OR DIMFUNCTION.pkDimension = ACCTCODE.fkDimension2
    				OR DIMFUNCTION.pkDimension = ACCTCODE.fkDimension3
    				OR DIMFUNCTION.pkDimension = ACCTCODE.fkDimension4
    				OR DIMFUNCTION.pkDimension = ACCTCODE.fkDimension5
    				OR DIMFUNCTION.pkDimension = ACCTCODE.fkDimension6
    				OR DIMFUNCTION.pkDimension = ACCTCODE.fkDimension7
    				OR DIMFUNCTION.pkDimension = ACCTCODE.fkDimension8
    				OR DIMFUNCTION.pkDimension = ACCTCODE.fkDimension9
    			)
    JOIN fiDimension DIMOBJECT
    			ON DIMOBJECT.fkDimensionPosition = @fkDimPosFund	
    			AND (DIMOBJECT.pkDimension = ACCTCODE.fkDimension1
    				OR DIMOBJECT.pkDimension = ACCTCODE.fkDimension2
    				OR DIMOBJECT.pkDimension = ACCTCODE.fkDimension3
    				OR DIMOBJECT.pkDimension = ACCTCODE.fkDimension4
    				OR DIMOBJECT.pkDimension = ACCTCODE.fkDimension5
    				OR DIMOBJECT.pkDimension = ACCTCODE.fkDimension6
    				OR DIMOBJECT.pkDimension = ACCTCODE.fkDimension7
    				OR DIMOBJECT.pkDimension = ACCTCODE.fkDimension8
    				OR DIMOBJECT.pkDimension = ACCTCODE.fkDimension9
    			)
    JOIN fiDimension DIMFUNDINGSOURCE
    			ON DIMFUNDINGSOURCE.fkDimensionPosition = @fkDimPosFund	
    			AND (DIMFUNDINGSOURCE.pkDimension = ACCTCODE.fkDimension1
    				OR DIMFUNDINGSOURCE.pkDimension = ACCTCODE.fkDimension2
    				OR DIMFUNDINGSOURCE.pkDimension = ACCTCODE.fkDimension3
    				OR DIMFUNDINGSOURCE.pkDimension = ACCTCODE.fkDimension4
    				OR DIMFUNDINGSOURCE.pkDimension = ACCTCODE.fkDimension5
    				OR DIMFUNDINGSOURCE.pkDimension = ACCTCODE.fkDimension6
    				OR DIMFUNDINGSOURCE.pkDimension = ACCTCODE.fkDimension7
    				OR DIMFUNDINGSOURCE.pkDimension = ACCTCODE.fkDimension8
    				OR DIMFUNDINGSOURCE.pkDimension = ACCTCODE.fkDimension9
    			)
    JOIN fiDimension DIMFUNDINGINSTRUCTIONALORG
    			ON DIMFUNDINGINSTRUCTIONALORG.fkDimensionPosition = @fkDimPosFund	
    			AND (DIMFUNDINGINSTRUCTIONALORG.pkDimension = ACCTCODE.fkDimension1
    				OR DIMFUNDINGINSTRUCTIONALORG.pkDimension = ACCTCODE.fkDimension2
    				OR DIMFUNDINGINSTRUCTIONALORG.pkDimension = ACCTCODE.fkDimension3
    				OR DIMFUNDINGINSTRUCTIONALORG.pkDimension = ACCTCODE.fkDimension4
    				OR DIMFUNDINGINSTRUCTIONALORG.pkDimension = ACCTCODE.fkDimension5
    				OR DIMFUNDINGINSTRUCTIONALORG.pkDimension = ACCTCODE.fkDimension6
    				OR DIMFUNDINGINSTRUCTIONALORG.pkDimension = ACCTCODE.fkDimension7
    				OR DIMFUNDINGINSTRUCTIONALORG.pkDimension = ACCTCODE.fkDimension8
    				OR DIMFUNDINGINSTRUCTIONALORG.pkDimension = ACCTCODE.fkDimension9
    			)
    JOIN fiDimension DIMOPUNIT
    			ON DIMOPUNIT.fkDimensionPosition = @fkDimPosFund	
    			AND (DIMOPUNIT.pkDimension = ACCTCODE.fkDimension1
    				OR DIMOPUNIT.pkDimension = ACCTCODE.fkDimension2
    				OR DIMOPUNIT.pkDimension = ACCTCODE.fkDimension3
    				OR DIMOPUNIT.pkDimension = ACCTCODE.fkDimension4
    				OR DIMOPUNIT.pkDimension = ACCTCODE.fkDimension5
    				OR DIMOPUNIT.pkDimension = ACCTCODE.fkDimension6
    				OR DIMOPUNIT.pkDimension = ACCTCODE.fkDimension7
    				OR DIMOPUNIT.pkDimension = ACCTCODE.fkDimension8
    				OR DIMOPUNIT.pkDimension = ACCTCODE.fkDimension9
    			)
    JOIN fiDimension DIMSUBMATTER
    			ON DIMSUBMATTER.fkDimensionPosition = @fkDimPosFund	
    			AND (DIMSUBMATTER.pkDimension = ACCTCODE.fkDimension1
    				OR DIMSUBMATTER.pkDimension = ACCTCODE.fkDimension2
    				OR DIMSUBMATTER.pkDimension = ACCTCODE.fkDimension3
    				OR DIMSUBMATTER.pkDimension = ACCTCODE.fkDimension4
    				OR DIMSUBMATTER.pkDimension = ACCTCODE.fkDimension5
    				OR DIMSUBMATTER.pkDimension = ACCTCODE.fkDimension6
    				OR DIMSUBMATTER.pkDimension = ACCTCODE.fkDimension7
    				OR DIMSUBMATTER.pkDimension = ACCTCODE.fkDimension8
    				OR DIMSUBMATTER.pkDimension = ACCTCODE.fkDimension9
    			)
    JOIN fiDimension DIMJOBCLASS
    			ON DIMJOBCLASS.fkDimensionPosition = @fkDimPosFund	
    			AND (DIMJOBCLASS.pkDimension = ACCTCODE.fkDimension1
    				OR DIMJOBCLASS.pkDimension = ACCTCODE.fkDimension2
    				OR DIMJOBCLASS.pkDimension = ACCTCODE.fkDimension3
    				OR DIMJOBCLASS.pkDimension = ACCTCODE.fkDimension4
    				OR DIMJOBCLASS.pkDimension = ACCTCODE.fkDimension5
    				OR DIMJOBCLASS.pkDimension = ACCTCODE.fkDimension6
    				OR DIMJOBCLASS.pkDimension = ACCTCODE.fkDimension7
    				OR DIMJOBCLASS.pkDimension = ACCTCODE.fkDimension8
    				OR DIMJOBCLASS.pkDimension = ACCTCODE.fkDimension9
    			)
    JOIN fiDimension DIMSPECLCOSTCNTR
    			ON DIMJOBCLASS.fkDimensionPosition = @fkDimPosFund	
    			AND (DIMJOBCLASS.pkDimension = ACCTCODE.fkDimension1
    				OR DIMSPECLCOSTCNTR.pkDimension = ACCTCODE.fkDimension2
    				OR DIMSPECLCOSTCNTR.pkDimension = ACCTCODE.fkDimension3
    				OR DIMSPECLCOSTCNTR.pkDimension = ACCTCODE.fkDimension4
    				OR DIMSPECLCOSTCNTR.pkDimension = ACCTCODE.fkDimension5
    				OR DIMSPECLCOSTCNTR.pkDimension = ACCTCODE.fkDimension6
    				OR DIMSPECLCOSTCNTR.pkDimension = ACCTCODE.fkDimension7
    				OR DIMSPECLCOSTCNTR.pkDimension = ACCTCODE.fkDimension8
    				OR DIMSPECLCOSTCNTR.pkDimension = ACCTCODE.fkDimension9
    			)			
    JOIN faBudgetPrep BP ON ACCTCODE.pkAccountCode = BP.fkAccountCode
    JOIN fiBudget B ON B.fkAccountCode = ACCTCODE.pkAccountCode
    JOIN faTransaction T1 ON T1.fkAccountCode = ACCTCODE.pkAccountCode
    JOIN fiYear Y ON Y.pkYear = ACCTCODE.fkYear
    ORDER BY Y.YearID
    If I run the query without the amounts included, I get all the account codes and dimension fields fine.  If I try to include the amounts I get an empty result set.  I tried using a subquery to get the amounts, but it erred out due to returning multiple results for each account code on a given amount type in a given year.  I'm stuck.  Please help.


    RandyHJ1957

    Thursday, August 07, 2014 6:01 PM

All replies

  • I'm not really sure where the amounts are in this query. And since I don't know the tables, I am not sure that it would help if I did.

    You will need to debug the query piece by piece. For instance, use SELECT INTO to save the data you get from the query without the amounts, and the play with that temp table to see why it does not join to the other tables.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, August 07, 2014 10:04 PM
  • The amounts are coming from three other tables that are not yet included in the existing query.  Depending on the year being referenced, the amounts come from the following tables:

    ProposedBudget amounts come from the BudgetPrep table (BudgetPrep.Amount).

    CurrentBudget amounts come from the Budget table (Budget.Amount)

    Each of the the historical years of budget amounts come from the Transactions table, which I call three times with different table aliases (T1, T2, T3) so that I use the following columns T1.Amount, T2.Amount, and T3.Amount for each respective historical year.

    For example, I want my years to appear as columns in the result set:

    ProposedBudget     Current Budget     Historical 1     Historical 2     Historical 3

    2015                       2014                    2013              2012               2011

    I need to join the amounts to the account codes for each year I am dealing with.

    Sometimes there are several results for the amount per account code in a year (representing different fund divisions) and I need to display all the amounts so I can group and sum them later in a report.  I'm trying to gather all the amounts for each account code (and its respective line items) for each of the five years so I can present the data in a report.


    RandyHJ1957

    Friday, August 08, 2014 12:58 PM
  • The amounts are coming from three other tables that are not yet included in the existing query.  Depending on the year being referenced, the amounts come from the following tables:

    OK, so I am supposed to tell what is wrong with a query that I don't even see?

    ProposedBudget amounts come from the BudgetPrep table (BudgetPrep.Amount).

    CurrentBudget amounts come from the Budget table (Budget.Amount)

    OK, so these columns are in the table, but in your previous post you say: but the amounts aren't working and I can't figure out how to make them work. In what whey are they not working? Do you get the correct number of rows, but incorrect values? Do you get too many rows, too few rows?

    Unfortunately, it is very difficult to help, because there is so much I don't know.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, August 08, 2014 9:50 PM