Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2013
    Posts
    14

    Unanswered: Scalar fullselect Error with sproc

    I have a stored procedure that I'm having issues with. The stored procedures have 3 parameters category, project, and machine.

    Code:
    CALL schema.stored_procedure ('in_category', 'in_machine_name', 'in_project_name');
    When 'ALL' is brought in as a parameter for project name (in_project_name) the stored procedure should produce all the project results which will generate multiple rows of data.

    Code:
    CALL schema.stored_procedure ('in_category', 'in_machine_name', 'ALL');
    I'm receiving a scallar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row error. The section of my stored procedure causing this error is below. When specifying a specific project the stored procedure will return one row of data as it is supposed to processing code similar to what is posted below with a variation to the if statement executing when in_PROJECT_NAME is <> ALL rather than = ALL, adding a where clause to the select statement setting the project name equal to the in_PROJECT_NAME parameter.

    Code:
    IF (in_PROJECT_NAME = 'ALL' AND in_CATEGORY_NAME = 'CATEGORY_1') THEN
    		BEGIN.....
                      
                  AND DP.PROJECT_NAME =in_PROJECT_NAME
    I'm having trouble troubleshooting how to workaround this error when I need to return more than a single row when the ALL parameter is passed in rather than a specific project name. I'm not very familiar with cursors and I'm not sure if there is an easier way to solve this problem. Help is greatly appreciated. Here is my if statement:



    Code:
    IF (in_PROJECT_NAME = 'ALL' AND in_CATEGORY_NAME = 'CATEGORY_1') THEN
    		BEGIN				
    						
    						DECLARE GLOBAL TEMPORARY TABLE TEMP_DW_1
    						(DIM_PROJECT_ID INT 
    						,PROJECT_NAME VARCHAR (400) 
    						,DIM_MACHINE_ID INT  
    						,MACHINE_NAME VARCHAR(100) 
    						,CATEGORY_NAME VARCHAR(100)  
    						,TOTAL_CAPABILITY INT
    						,BUCKET_A DECIMAL (26,6)
    						,BUCKET_B DECIMAL (26,6)
    						,BUCKET_C DECIMAL (26,6)
    						,TOTAL_PERCENT DECIMAL (10,6)
    						,CAPABILITY_PERCENT_A DECIMAL (10,6)
    						,CAPABILITY_PERCENT_B DECIMAL (10,6)
    						) ON COMMIT DELETE ROWS NOT LOGGED WITH REPLACE;
    						
    					
    					
    					END;
    										
    					SET (v_DIM_PROJECT_ID, v_PROJECT_NAME, v_DIM_MACHINE_ID, v_MACHINE_NAME, v_AWARD_CATEGORY_NAME, v_TOTAL_CAPABILITY, v_BUCKET_A, 
    						v_BUCKET_B, v_BUCKET_C, v_TOTAL_PERC, v_CAPABILITY_BY_PERC_A, v_CAPABILITY_BY_PERC_B) =
    							 		
    							
    							 		(		 SELECT DP.dim_project_id
    									                ,DP.PROJECT_NAME
    									                ,DM.dim_machine_id
    									                ,DM.MACHINE_NAME 
    													,'CATEGORY_1' 
    													,0.0 AS TOTAL_CAPABILITY_HOURS
    									                ,SCHEMA.CALCULATE_PROJECT__A_USAGE(in_MACHINE_NAME,a.project_name, 20140101, 20140512) AS BUCKET_A
    									                ,SCHEMA.CALCULATE_PROJECT_BUCKET_B_USAGE(in_MACHINE_NAME,a.project_name, 20140101, 20140512) AS BUCKET_B
    									                ,SCHEMA.CALCULATE_PROJECT_BUCKET_C_USAGE(in_MACHINE_NAME,a.project_name, 20140101, 20140512) AS BUCKET_C
    									                ,0.0 AS TOTAL_PERCENT
    									                ,0.0 AS CAPABILITY_PERCENT_A
    									                ,0.0 AS CAPABILITY_PERCENT_B   
    										from (SELECT distinct DP.PROJECT_NAME      
    											FROM alcf_warehouse.fact_allocation_transaction AS FAT
    											INNER JOIN WAREHOUSE.DIM_JOB AS DJ on FAT.DIM_JOB_ID = DJ.DIM_JOB_ID
    											INNER JOIN WAREHOUSE.DIM_PROJECT AS DP on FAT.DIM_PROJECT_ID = DP.DIM_PROJECT_ID
    											INNER JOIN WAREHOUSE.DIM_CATEGORY AS DA on FAT.DIM_CATEGORY_ID = DA.DIM_CATEGORY_ID
    											WHERE FAT.ADJUSTED_JOB_DATE_END_ID >= 20140101 AND FAT.ADJUSTED_JOB_DATE_END_ID < 20140512
    											AND DA.category = in_CATEGORY_NAME
    											AND DJ.machine_name=  in_MACHINE_NAME) a
    											left outer join alcf_warehouse.dim_project dp on dp.PROJECT_NAME = a.PROJECT_NAME
    											left outer join alcf_warehouse.dim_machine dm on dm.machine_name = in_MACHINE_NAME
    											order by dp.project_name);
    		
    										INSERT INTO SESSION.TEMP_DW_1
    										VALUES (v_DIM_PROJECT_ID 
    												,v_PROJECT_NAME
    												,v_DIM_MACHINE_ID
    												,v_MACHINE_NAME
    												,v_CATEGORY_NAME
    												,v_TOTAL_CAPABILITY
    												,v_BUCKET_A
    												,v_BUCKET_B
    												,v_BUCKET_C
    												,v_TOTAL_PERC
    												,v_CAPABILITY_BY_PERC_A
    												,v_CAPABILITY_BY_PERC_B );
    Last edited by wtolbert; 05-23-14 at 11:44.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    After reformatting your code(with my way),
    it looks like this...
    Code:
    ...
    ...
    
    SET (  v_DIM_PROJECT_ID
         , v_PROJECT_NAME
         , v_DIM_MACHINE_ID
         , v_MACHINE_NAME
         , v_AWARD_CATEGORY_NAME
         , v_TOTAL_CAPABILITY
         , v_BUCKET_A
         , v_BUCKET_B
         , v_BUCKET_C
         , v_TOTAL_PERC
         , v_CAPABILITY_BY_PERC_A
         , v_CAPABILITY_BY_PERC_B
        )
    = (SELECT DP.dim_project_id
        ...
        ...
        ...
        ...
      )
    ;
    
    INSERT INTO SESSION.TEMP_DW_1
    VALUES
        (  v_DIM_PROJECT_ID 
         , v_PROJECT_NAME
         , v_DIM_MACHINE_ID
         , v_MACHINE_NAME
         , v_CATEGORY_NAME
         , v_TOTAL_CAPABILITY
         , v_BUCKET_A
         , v_BUCKET_B
         , v_BUCKET_C
         , v_TOTAL_PERC
         , v_CAPABILITY_BY_PERC_A
         , v_CAPABILITY_BY_PERC_B
        )
    ;
    I had a question:
    In SET statement, there was a variable
    Code:
         , v_AWARD_CATEGORY_NAME
    While in INSERT statement was
    Code:
         , v_CATEGORY_NAME
    From where came the v_CATEGORY_NAME?

    If that was correct,
    I have no idea now.

    But, if that was a typo(v_AWARD_CATEGORY_NAME and v_CATEGORY_NAME should be same),
    please try to INSERT directry the results of SELECT statement, like...
    Code:
    INSERT INTO SESSION.TEMP_DW_1
       SELECT DP.dim_project_id
        ...
        ...
        ...
        ...
    ;

  3. #3
    Join Date
    Aug 2013
    Posts
    14
    It was a typo. Award_category should be the same.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    It was a typo. Award_category should be the same.
    Then, did you tried to INSERT directry the results of SELECT statement, like this?
    Quote Originally Posted by tonkuma View Post
    ...
    ...

    But, if that was a typo(v_AWARD_CATEGORY_NAME and v_CATEGORY_NAME should be same),
    please try to INSERT directry the results of SELECT statement, like...
    Code:
    INSERT INTO SESSION.TEMP_DW_1
       SELECT DP.dim_project_id
        ...
        ...
        ...
        ...
    ;
    What results did you got?
    Any error messages?
    or, the results were different from your expected results?


    For your reference,
    here is the syntax of INSERT - IBM DB2 9.7 for Linux, UNIX, and Windows

    Syntax

    Code:
    >>-INSERT INTO--+-table-name-------+---------------------------->
                    +-view-name--------+   
                    +-nickname---------+   
                    '-(--fullselect--)-'   
    
    >--+-----------------------+--+---------------------+----------->
       |    .-,-----------.    |  '-| include-columns |-'   
       |    V             |    |                            
       '-(----column-name-+--)-'                            
    
                 .-,----------------------------.              
                 V                              |              
    >--+-VALUES----+-+-expression-+-----------+-+----------+-------->
       |           | +-NULL-------+           |            |   
       |           | '-DEFAULT----'           |            |   
       |           |    .-,--------------.    |            |   
       |           |    V                |    |            |   
       |           +-(----+-expression-+-+--)-+            |   
       |           |      +-NULL-------+      |            |   
       |           |      '-DEFAULT----'      |            |   
       |           '-row-expression-----------'            |   
       '-+-----------------------------------+--fullselect-'   
         |       .-,-----------------------. |                 
         |       V                         | |                 
         '-WITH----common-table-expression-+-'                 
    
    >--+--------------+--------------------------------------------><
       '-WITH--+-RR-+-'   
               +-RS-+     
               +-CS-+     
               '-UR-'

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    This is your problem.
    AND DP.PROJECT_NAME =in_PROJECT_NAME
    in the case of ALL, you should not be equating to ALL, you should be equating to itself or not having the predicate in your SQL.
    Should be:
    Code:
    AND DP.PROJECT_NAME = DP.PROJECT_NAME
    Dave

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •