Error: UniDAC 3.0.0.2 and FastReport 4.8.11

[VAbramyak]

In TfrxUniDACQuery script:

Code: Select all

 DECLARE @daydebt INT, 
@CurrentMonths INT, 
@getdate DATETIME, 
@AccountId INT, 
@IsPlanned BIT, 
@PlannedPeriod INT 
SELECT @daydebt = ( SELECT [s].[value] 
FROM [Services].[Setting] AS s 
WHERE [s].[guid] = '826C4666-F79C-4558-A0BB-2D5A428FCE1B' 
), 
@CurrentMonths = ( SELECT [s].[value] 
FROM [Services].[Setting] AS s 
WHERE [s].[guid] = 'E6AC6284-6983-46E1-9A9D-D110BE68E954' 
), 
@getdate = GETDATE() 
,@AccountId = :AccountId 
,@IsPlanned = :IsPlanned 
,@PlannedPeriod = :PlannedPeriod 

IF EXISTS ( SELECT * 
FROM TempDB..sysobjects 
WHERE id = OBJECT_ID(N'TempDB..#DetailBill') ) 
BEGIN 
DROP TABLE #DetailBill 
END 
CREATE TABLE #DetailBill 
( 
AccountId INT, 
PeriodFrom SMALLDATETIME, 
PeriodTo SMALLDATETIME, 
Summ DECIMAL(16, 2), 
Quantity INT, 
StartScore VARCHAR(30), 
EndScore VARCHAR(30), 
[Type] INT 
) 

IF EXISTS (SELECT * FROM TempDB.sys.objects O WHERE O.Object_id = Object_id(N'TempDB..#TempDetailBill')) 
begin 
drop table #TempDetailBill 
end 

SELECT Bill.AccountId, Bill.PointId, 
case when datediff(day,Bill.Date,@GetDate)>=@daydebt and (Bill.CalcMethod in (1,2) OR Bill.DocumentTypeId = 11) -- борги 
then 1 when Bill.CalcMethod in (3,5,6) then 3 -- тіпо плановий 
when Bill.DocumentTypeId=5 AND Bill.IsIncome = 0 then 7 -- перерахунок по субсидії 
when datediff(day,Bill.Date,@GetDate)0 
AND (Bill.TotalSumm - ISNULL(Used.UsedByPaid,0.00) - ISNULL(Used.UsedBySubsidy,0.00) - ISNULL(Used.UsedByWriteoff,0.00) > 0 
OR datediff(day,Bill.date,@GetDate) 1 
GROUP BY TDB.AccountId 
HAVING SUM(TDB.[UsedSummByPaid]) > 0 
UNION ALL 
SELECT TDB.AccountId , 
NULL , 
NULL , 
-SUM(TDB.[UsedSummBySubsidy]), 
NULL, 
NULL, 
NULL, 
6 AS [Type] 
FROM #TempDetailBill AS TDB 
WHERE [Type]  1 
GROUP BY TDB.AccountId 
HAVING SUM(TDB.[UsedSummBySubsidy]) > 0 
--UNION ALL 
/*SELECT GI.AccountId , 
NULL , 
NULL , 
-GI.[Credit], 
NULL, 
NULL, 
NULL, 
9 AS [Type] 
FROM #GeneralInfo GI WHERE [Credit]>0*/ 

SELECT * FROM #DetailBill 

Error

Code: Select all

Incorrect syntax near '('. Incorrect syntax near the keyword 'AND'.

When viewing Report, in SQL Server Profiler:

Code: Select all

exec sp_executesql N' DECLARE @daydebt INT, 
@CurrentMonths INT, 
@getdate DATETIME, 
@AccountId INT, 
@IsPlanned BIT, 
@PlannedPeriod INT 
SELECT @daydebt = ( SELECT [s].[value] 
FROM [Services].[Setting] AS s 
WHERE [s].[guid] = ''826C4666-F79C-4558-A0BB-2D5A428FCE1B'' 
), 
@CurrentMonths = ( SELECT [s].[value] 
FROM [Services].[Setting] AS s 
WHERE [s].[guid] = ''E6AC6284-6983-46E1-9A9D-D110BE68E954'' 
), 
@getdate = GETDATE() 
,@AccountId = @P1 
,@IsPlanned = @P2 
,@PlannedPeriod = @P3 

IF EXISTS ( SELECT * 
FROM TempDB..sysobjects 
WHERE id = OBJECT_ID(N''TempDB..#DetailBill'') ) 
BEGIN 
DROP TABLE #DetailBill 
END 
CREATE TABLE #DetailBill 
( 
AccountId INT, 
PeriodFrom SMALLDATETIME, 
PeriodTo SMALLDATETIME, 
Summ DECIMAL(16, 2), 
Quantity INT, 
StartScore VARCHAR(30), 
EndScore VARCHAR(30), 
[Type] INT 
) 

IF EXISTS (SELECT * FROM TempDB.sys.objects O WHERE O.Object_id = Object_id(N''TempDB..#TempDetailBill'')) 
begin 
drop table #TempDetailBill 
end 

SELECT Bill.AccountId, Bill.PointId, 
case when datediff(day,Bill.Date,@GetDate)>=@daydebt and (Bill.CalcMethod in (1,2) OR Bill.DocumentTypeId = 11) /* борги*/ 
then 1 when Bill.CalcMethod in (3,5,6) then 3 /* тіпо плановий*/ 
when Bill.DocumentTypeId=5 AND Bill.IsIncome = 0 then 7 /* перерахунок по субсидії*/ 
when datediff(day,Bill.Date,@GetDate)0 
AND (Bill.TotalSumm - ISNULL(Used.UsedByPaid,0.00) - ISNULL(Used.UsedBySubsidy,0.00) - ISNULL(Used.UsedByWriteoff,0.00) > 0 
OR datediff(day,Bill.date,@GetDate) 1 
GROUP BY TDB.AccountId 
HAVING SUM(TDB.[UsedSummByPaid]) > 0 
UNION ALL 
SELECT TDB.AccountId , 
NULL , 
NULL , 
-SUM(TDB.[UsedSummBySubsidy]), 
NULL, 
NULL, 
NULL, 
6 AS [Type] 
FROM #TempDetailBill AS TDB 
WHERE [Type]  1 
GROUP BY TDB.AccountId 
HAVING SUM(TDB.[UsedSummBySubsidy]) > 0 
/*UNION ALL*/ 
/*SELECT GI.AccountId , 
NULL , 
NULL , 
-GI.[Credit], 
NULL, 
NULL, 
NULL, 
9 AS [Type] 
FROM #GeneralInfo GI WHERE [Credit]>0*/ 

SELECT * FROM #DetailBill',N'@P1 int,@P2 smallint,@P3 int,@P4 int',17689,0,0,17689

This occurs when the value stamped in the Master and MasterFields.

why the modified script and error?

[VAbramyak]

I get the answer?

[Plash]

Maybe your script realy contains syntax error. Did you try to execute it in SQL Server Management Studio?

If your SQL script has more that one statement, SQL Server always converts it into one statement with the call to sp_executesql passing your SQL script as a string parameter.
This may not be the cause of the problem.

[VAbramyak]

Maybe your script realy contains syntax error. Did you try to execute it in SQL Server Management Studio?

If your SQL script has more that one statement, SQL Server always converts it into one statement with the call to sp_executesql passing your SQL script as a string parameter.
This may not be the cause of the problem.

Thank you for your reply. A careful look at the script, the script first '(' No, but in a second.

TfsxUniDACQuery script:

Code: Select all

WHERE
PU.IsActive = 1
AND PUBP.Period=@PlannedPeriod
END

insert into #DetailBill
(AccountId, PeriodFrom, PeriodTo, Summ,
Quantity, StartScore, EndScore, [Type])

SELECT TDB.AccountId ,
CASE [Type]
WHEN 3 THEN MIN(ISNULL(TDB.CalculatePeriod, CAST(@CurrentMonths*100+1 AS VARCHAR(10))))
ELSE MIN(TDB.[ConsumptionFrom])
END PeriodFrom ,
CASE [Type]
WHEN 3 THEN MAX(ISNULL(TDB.CalculatePeriod, CAST(@CurrentMonths*100+1 AS VARCHAR(10))))
ELSE MAX(TDB.ConsumptionTo)
END PeriodTo ,
CASE WHEN TDB.[Type] = 1
THEN SUM(TDB.[TotalSumm]) - SUM(TDB.[UsedSummByPaid])
- SUM(TDB.[UsedSummByWriteoff])
- SUM(TDB.[UsedSummBySubsidy])
ELSE SUM(TDB.[TotalSumm])
END [Summ] ,
SUM(TDB.[ConsumptionQuantity]) Quantity ,
CASE WHEN TDB.[Type] = 0
THEN STUFF(( SELECT TOP 1 '/'
+ CAST(REPLACE([I].[Value], '.00000', '') AS VARCHAR)
FROM [Measuring].[Index] I
JOIN [Measuring].[GroupIndex] AS gi2 ON [I].[GroupIndexId] = [gi2].[GroupIndexId]
JOIN [Measuring].[Counter] AS c ON [gi2].[CounterId] = [c].[CounterId]
WHERE [c].[PointId] = [TDB].[PointId]
AND [GI2].[Date] = MIN(TDB.ConsumptionFrom)
FOR
XML PATH('')
), 1, 1, '')
ELSE NULL
END AS StartScore ,
CASE WHEN TDB.[Type] IN ( 0, 1 )
THEN STUFF(( SELECT '/'
+ CAST(REPLACE(MAX(([I].[Value])), '.00000', '') AS VARCHAR)
FROM [Measuring].[Index] I
JOIN [Measuring].[GroupIndex] AS gi2 ON [I].[GroupIndexId] = [gi2].[GroupIndexId]
JOIN [Measuring].[Counter] AS c ON [gi2].[CounterId] = [c].[CounterId]
WHERE [c].[PointId] = [TDB].[PointId]
AND [GI2].[Date] = MAX(TDB.ConsumptionTo)
GROUP BY [GI2].[Date], [I].[Value]
FOR
XML PATH('')
), 1, 1, '')
ELSE NULL
END AS EndScore,
TDB.[Type]
FROM #TempDetailBill AS TDB 

Sql Profiler:

Code: Select all

WHERE (
PU.IsActive = 1
AND PUBP.Period=@PlannedPeriod
END

insert into #DetailBill
(AccountId, PeriodFrom, PeriodTo, Summ,
Quantity, StartScore, EndScore, [Type])

SELECT TDB.AccountId ,
CASE [Type]
WHEN 3 THEN MIN(ISNULL(TDB.CalculatePeriod, CAST(@CurrentMonths*100+1 AS VARCHAR(10))))
ELSE MIN(TDB.[ConsumptionFrom])
END PeriodFrom ,
CASE [Type]
WHEN 3 THEN MAX(ISNULL(TDB.CalculatePeriod, CAST(@CurrentMonths*100+1 AS VARCHAR(10))))
ELSE MAX(TDB.ConsumptionTo)
END PeriodTo ,
CASE WHEN TDB.[Type] = 1
THEN SUM(TDB.[TotalSumm]) - SUM(TDB.[UsedSummByPaid])
- SUM(TDB.[UsedSummByWriteoff])
- SUM(TDB.[UsedSummBySubsidy])
ELSE SUM(TDB.[TotalSumm])
END [Summ] ,
SUM(TDB.[ConsumptionQuantity]) Quantity ,
CASE WHEN TDB.[Type] = 0
THEN STUFF(( SELECT TOP 1 ''/''
+ CAST(REPLACE([I].[Value], ''.00000'', '''') AS VARCHAR)
FROM [Measuring].[Index] I
JOIN [Measuring].[GroupIndex] AS gi2 ON [I].[GroupIndexId] = [gi2].[GroupIndexId]
JOIN [Measuring].[Counter] AS c ON [gi2].[CounterId] = [c].[CounterId]
WHERE [c].[PointId] = [TDB].[PointId]
AND [GI2].[Date] = MIN(TDB.ConsumptionFrom)
FOR
XML PATH('''')
), 1, 1, '''')
ELSE NULL
END AS StartScore ,
CASE WHEN TDB.[Type] IN ( 0, 1 )
THEN STUFF(( SELECT ''/''
+ CAST(REPLACE(MAX(([I].[Value])), ''.00000'', '''') AS VARCHAR)
FROM [Measuring].[Index] I
JOIN [Measuring].[GroupIndex] AS gi2 ON [I].[GroupIndexId] = [gi2].[GroupIndexId]
JOIN [Measuring].[Counter] AS c ON [gi2].[CounterId] = [c].[CounterId]
WHERE [c].[PointId] = [TDB].[PointId]
AND [GI2].[Date] = MAX(TDB.ConsumptionTo)
GROUP BY [GI2].[Date], [I].[Value]
FOR
XML PATH('''')
), 1, 1, '''')
ELSE NULL
END AS EndScore,
TDB.[Type]
FROM #TempDetailBill AS TDB) AND AccountId = @P4 

In place formed the final script execution?

[Plash]

This problem may occur if you are using the query in master-detail.

UniDAC automatically adds AccountId = :AccountId condition to your query. For a complex query UniDAC cannot do this correctly.

You should manually add this condition to your SQL script in the correct place.