|
sachavieuxroy
Starting Member
Canada
1 Posts |
 Posted - 06/18/2010 : 03:02:27
|
AFTER USING 3rd APPLY STATEMENT I GOT FOLLOWING ERROR :
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
IT SEEMS THAT I CAN'T PASS A SCALAR DERIVED FROM THE PREVIOUS APPLY STATEMENT, IF THAT SCALAR WAS CALCULATED USING SCALAR FUNCTION.
ALTER FUNCTION [dbo].[f_GetCOM]
(
@Division Int,
@Commission decimal(18,5),
@Section Int
)
--RETURNS decimal(18,5)
RETURNS Int
AS
BEGIN
-- DECLARE @Return decimal(18,5),@Division_Fiesta Int
-- SET @Division_Fiesta = 2
-- SET @Return =
-- CASE
-- WHEN @Section IN (24,26,27,28,-1) THEN 0
-- WHEN @Division=@Division_Fiesta AND @Commission<>0 AND @Section IN (6,17) THEN @Commission/5
-- ELSE @Commission
-- END
-- RETURN COALESCE(@Return,0)
RETURN 0
END
SELECT
[J].[ID]
,[J].[FullName]
,[J].[Category_TypeDePayroll_ID]
,[J].[Travaille_de_Jour]
,[J].[Heur_Supplementaire_Travaille]
,[J].[prime_transport]
,[J].[Tarif]
,[CM].[IsPayed]
,[J].[Jour_Travaille]
,[J].[Montant_Supplementaire_Par_Heur]
,[J].[Salair_par_Jour]
,[J].[Salair_Minimum]
,[J].[TypeDePresence]
,[J].[Date_de_Presence]
,[J].[DD]
,[J].[Section]
,[J].[Personel_ID]
,0 AS [Assurance]
,0 AS [Pret_Capital]
,0 AS [Dash]
,0 AS [Avance]
,0 AS [Dette]
,0 AS [Penalite]
,0 AS [Short]
,0 AS [Pret_Auto]
,[J].[Category_Division_ID]
,[J].[Tarif_Par_Jour]
,[J].[Produit_sur_Quota_Produit_ID]
,[J].[Quinzaine]
,[J].[Category_Department_ID]
,[J].[Salaire_de_Base]
,[J].[Division]
,[DC].[CaisseOrCash]
,[COALESCE_A].[ADJ]
,[COALESCE_A].[Commission]
,[COALESCE_A].[Nom_du_camillion]
,[COALESCE_A].[HS]
,[CALC1_A].[PT]
,[CALC1_A].[JR]
,[CALC2_A].[COM]
,[CALC1_A].[TEST]
FROM
.[dbo].[vwDistributionReport_Join_Indexed_2] AS [J]
LEFT OUTER JOIN
.[dbo].[Category_Motif] AS [CM] ON
[J].[Category_Motif_ID] = [CM].[ID]
LEFT OUTER JOIN
.[dbo].[PayrollAdjustments] AS [PA] ON
[J].[Personel_ID]=[PA].[Personel_ID]
AND [J].[Date_de_Presence]=[PA].[Date_of_Adjustment]
LEFT OUTER JOIN
.[dbo].[DetailDeVenteCalcUnionIndexed] [DC] ON
[J].[Personel_ID]=[DC].[Personel_ID]
AND [J].[Date_de_Presence]=[DC].[Date_Vendue]
AND [J].[Quinzaine_ID]=[DC].[QuinzaineID]
LEFT OUTER JOIN
.[dbo].[CustomValues] [CV] ON
[J].[Personel_ID]=[CV].[RecordID]
AND [CV].[TableID]=1
AND [CV].[CategoryID]=1
CROSS APPLY ( SELECT
COALESCE([PA].[Montant],0)
,COALESCE([DC].[Commission],0)
,COALESCE([CV].[StringValue],'')
,COALESCE ([J].[Heur_Supplementaire_Travaille], 0)
) COALESCE_A (ADJ,Commission,Nom_du_camillion,HS)
CROSS APPLY (
SELECT
.[dbo].[f_GetTypeDePayrollID]([J].[Section],[J].[Category_TypeDePayroll_ID])
,
.[dbo].[f_GetPT]([J].[TypeDePresence], [J].[prime_transport])
,CAST(
.[dbo].[f_GetSectionID]([J].[Section],[J].[Section_ID]) As Int)
,
.[dbo].[f_GetJR]([J].[TypeDePresence])
,([COALESCE_A].[HS] *
[J].[Montant_Supplementaire_Par_Heur])
) CALC1_A (CALC_TypeDePayrollID,PT,TEST,JR,MS)
CROSS APPLY (
SELECT
.[dbo].[f_GetCOM]([J].[Category_Division_ID], [DC].[Commission],[CALC1_A].[JR])
) CALC2_A (COM)
WHERE
([CM].[IsPayed] IS NULL
OR [CM].[IsPayed] = 1
OR ([CM].[IsPayed]=0 AND [J].[Section] Like '%Demarcheur%'))
AND ([J].[ID] IS NOT NULL) |
|
Error: 17310, Severity: 20, State: 1
