locked
Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services. RRS feed

  • Question

  • Hello I'm working on SQL 2008 R2

    I have the following query which runs normally on a group by condition and produces the above exception when I change the group by clause!!!

    USE [PayrollDB]
    GO
    /****** Object: UserDefinedFunction [dbo].[Pay_GeneratePayrollJV_SalaryBasicSalaryDepartment]  Script Date: 04/08/2011 15:51:58 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    -- =============================================
    -- Author:		alihijazi
    -- Create date: 11/3/2011
    -- Description:	generates the JV Salary for basic salary for department posting type
    -- =============================================
    ALTER FUNCTION [dbo].[Pay_GeneratePayrollJV_SalaryBasicSalaryDepartment]
    (
    	@headerId int,
    	@companyCode char(6),
    	@planCode int,
    	@postingDate datetime,
    	@JV_Type UDT_JV_Type ,
    	@JV_Posting_Type udt_jv_posting_type,
    	@AccountNo varchar(20),
    	@currencyCode nvarchar(10),
    	@AmountSign UDT_AmountSign,
    	@Description varchar(255),
    	@navisionJournalBatch varchar(10) ,
    	@navisionJournalTemplate varchar(10),
    	@primaryCurrency nvarchar(10),
    	@secondaryCurrency nvarchar(10),
    	@defaultCurrency nvarchar(10)
    )
    RETURNS 
    @result TABLE 
    (
    	[pjv_LineNumber] [int] NULL,
    	[pjv_RecId] [int] NULL,
    	[pjv_CompanyCode] [char](6) NULL,
    	[pjv_PlanCode] [int] NULL,
    	[pjv_PayrollHeaderId] [int] NULL,
    	[pjv_ProvisionHeaderId] [int] NULL,
    	[pjv_Type] [varchar](15) NULL,
    	[pjv_DocumentNumber] [varchar](20) NULL,
    	[pjv_PostingDate] [datetime] NULL,
    	[pjv_AccountType] [dbo].[UDT_JV_Posting_Type] NOT NULL,
    	[pjv_EmployeeCode] [varchar](30) NULL,
    	[pjv_NavEmployeeCode] [varchar](20) NULL,
    	[pjv_NavisionSRSPVendorCode] [varchar](20) NULL,
    	[pjv_NavisionProvisionVendorCode] [varchar](20) NULL,
    	[pjv_AccountNumber] [varchar](20) NULL,
    	[pjv_CurrencyCode] [nvarchar](10) NULL,
    	[pjv_OriginalAmount] [decimal](18, 0) NULL,
    	[pjv_AmountLCY] [decimal](18, 0) NULL,
    	[pjv_AmountACY] [decimal](18, 0) NULL,
    	[pjv_DepartmentCode] [int] NULL,
    	[pjv_SubDepartmentCode] [int] NULL,
    	[pjv_JournalTemplate] [varchar](20) NULL,
    	[pjv_JournalBatch] [varchar](20) NULL,
    	[pjv_Description] [varchar](255) NULL
    )
    AS
    BEGIN
    	declare @multiplier int
    	select @multiplier = case @AmountSign when dbo.Pay_CreditAmountSign() then -1 else 1 end
    	
    		
    	insert into @result 
    	(
    		[pjv_LineNumber],
    		[pjv_RecId] ,
    		[pjv_CompanyCode] ,
    		[pjv_PlanCode] ,
    		[pjv_PayrollHeaderId] ,
    		[pjv_ProvisionHeaderId],
    		[pjv_Type] ,
    		[pjv_DocumentNumber] ,
    		[pjv_PostingDate] ,
    		[pjv_AccountType] ,
    		[pjv_EmployeeCode] ,
    		[pjv_NavEmployeeCode] ,
    		[pjv_NavisionSRSPVendorCode] ,
    		[pjv_NavisionProvisionVendorCode] ,
    		[pjv_AccountNumber] ,
    		[pjv_CurrencyCode] ,
    		[pjv_OriginalAmount],
    		[pjv_AmountLCY] ,
    		[pjv_AmountACY] ,
    		[pjv_DepartmentCode] ,
    		[pjv_SubDepartmentCode],
    		[pjv_JournalTemplate] ,
    		[pjv_JournalBatch] ,
    		[pjv_Description] 
    	)
    	select
    		10000,
    		1,
    		@companyCode,
    		@planCode,
    		@headerId,
    		null,
    		@JV_Type,
    		1,
    		@postingDate,
    		@JV_Posting_Type,
    		null,
    		null,
    		null,
    		null,
    		@AccountNo,
    		@currencyCode,
    		@multiplier * dbo.Pay_ConvertAmount(@companyCode,sum(pcd_BasicSalary),@defaultCurrency,@primaryCurrency, @postingDate),
    		@multiplier * sum(pcd_BasicSalary),
    		@multiplier * dbo.Pay_ConvertAmount(@companyCode, sum(pcd_BasicSalary), @defaultCurrency, @secondaryCurrency, @postingDate),
    		(
    			select	
    				case isnull(pdep_ParentCode,-1) when -1 then pdep_RecID else pdep_ParentCode end
    			from 
    				dbo.Pay_GetEmployeeDepartmentDetails(pcd_EmployeeCode, pcd_CompanyCode)
    		),
    		(
    			select 
    				case isnull(pdep_ParentCode,-1) when -1 then null else pdep_RecID end
    			from
    				dbo.Pay_GetEmployeeDepartmentDetails(pcd_EmployeeCode, pcd_CompanyCode)
    		),
    		@navisionJournalTemplate,
    		@navisionJournalBatch,
    		@Description		
    	from 
    		Pay_Calculation_Details
    			inner join Pay_Employee
    				on pemp_Code = pcd_EmployeeCode
    					and pemp_CompanyCode = pcd_CompanyCode
    	where 
    		pcd_PlanCode = @planCode
    			and pcd_CompanyCode = @companyCode
    			and pcd_PostingDate = @postingDate
    			and pcd_CalculationNumber = @headerId
    	group by pcd_CompanyCode, pemp_DepartmentCode
    	
    			
    
    	RETURN 
    END
    
    
    

    if I group by pcd_employeeCode, pcd_CompanyCode, pemp_departmentCode the query runs normally

    however if i remove the pcd_employeeCode then I get the above exception

     

    Please help


    alihijazi
    Friday, April 8, 2011 1:51 PM

Answers

  • Have you tried simplifying this query? Say, if you get rid (for now) of all these scalar UDFs in your query, does the query work?

    I think you can get a good performance increase if you re-write this scalar UDF to table-valued (inline) UDF that will return both converted amounts at once as a table with one record.

    Your function can also be converted to inline function. Also, what does the other function (GetEmployeeDepartmentDetails return)? Why not OUTER APPLY with that function? (For now remove it also from your function). Also, looks like you call the exact same function and exact same field twice - why?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Marked as answer by Kalman Toth Sunday, May 8, 2011 1:47 PM
    Friday, April 8, 2011 2:07 PM
  • If you remove pcd_EmployeeCode what do you expect to get from this part?:

    (
    select
    case isnull(pdep_ParentCode,-1) when -1 then pdep_RecID else pdep_ParentCode end
    from
    dbo.Pay_GetEmployeeDepartmentDetails(pcd_EmployeeCode, pcd_CompanyCode)
    ),
    (
    select
    case isnull(pdep_ParentCode,-1) when -1 then null else pdep_RecID end
    from
    dbo.Pay_GetEmployeeDepartmentDetails(pcd_EmployeeCode, pcd_CompanyCode)
    )




    Or im missing something?

    MC
    • Proposed as answer by Marko Culo Saturday, April 9, 2011 5:41 AM
    • Marked as answer by Kalman Toth Sunday, May 8, 2011 1:47 PM
    Friday, April 8, 2011 2:09 PM

All replies

  • Have you tried simplifying this query? Say, if you get rid (for now) of all these scalar UDFs in your query, does the query work?

    I think you can get a good performance increase if you re-write this scalar UDF to table-valued (inline) UDF that will return both converted amounts at once as a table with one record.

    Your function can also be converted to inline function. Also, what does the other function (GetEmployeeDepartmentDetails return)? Why not OUTER APPLY with that function? (For now remove it also from your function). Also, looks like you call the exact same function and exact same field twice - why?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Marked as answer by Kalman Toth Sunday, May 8, 2011 1:47 PM
    Friday, April 8, 2011 2:07 PM
  • If you remove pcd_EmployeeCode what do you expect to get from this part?:

    (
    select
    case isnull(pdep_ParentCode,-1) when -1 then pdep_RecID else pdep_ParentCode end
    from
    dbo.Pay_GetEmployeeDepartmentDetails(pcd_EmployeeCode, pcd_CompanyCode)
    ),
    (
    select
    case isnull(pdep_ParentCode,-1) when -1 then null else pdep_RecID end
    from
    dbo.Pay_GetEmployeeDepartmentDetails(pcd_EmployeeCode, pcd_CompanyCode)
    )




    Or im missing something?

    MC
    • Proposed as answer by Marko Culo Saturday, April 9, 2011 5:41 AM
    • Marked as answer by Kalman Toth Sunday, May 8, 2011 1:47 PM
    Friday, April 8, 2011 2:09 PM
  • How about if the same logic is inline-code?  Does it work? Thanks.

     

    If problem remains, you can submit it to Connect:

    http://social.msdn.microsoft.com/Forums/en/transactsql/thread/c40cfbf9-5f06-49b9-9ba6-f15d61ee02e3

     


    Kalman Toth, SQL Server & BI Training; SQL 2008 GRAND SLAM
    Sunday, May 8, 2011 1:49 PM