October 3, 2008 at 1:34 am
Hi,
I am running an SQL function below at runtime via Java code. But when i run it, it throws an error for a '('
Below is the function which i have created :
--------------------------------------------------------------
FUNCTION [dbo].[RPT_nSLA_REPORT]
(
-- Add the parameters for the function here
@SLA_Name varchar(100),
@dt_st int,@dt_end int,@grp varchar(69),@priority int,
@class varchar(100), @category varchar(100), @type varchar(100), @item varchar(100)
)
RETURNS TABLE
AS
RETURN
(
SELECT Create_Date Create_Date,
dbo.F_RPT_TO_DATE(Create_Date,0) Create_Date_GMT,
Entry_key,
case when item_type='Service Request' then 1
when item_type='Incident' then 2
when item_type='Problem' then 3
when item_type='Change' then 4
when item_type='Task-General' then 5
when item_type='Task-Vendor' then 5
else 6
end Item_Type_Order,
Item_Type,
ID,
Assigned_To,
Assigned_To_Group,
a.Priority,Priority_Name,P_Priority,
Assigned_To_Group_Date,
dbo.F_RPT_TO_DATE(Assigned_To_Group_Date,0) Assigned_To_Group_GMT,
Ownership_date,
dbo.F_RPT_TO_DATE(Ownership_date,0) Ownership_GMT,
---------------------------------
case when b.Priority_Name='Critical' then Total_Pending_Time
else Total_Pending_Time_BH
end Total_Pending_Time,
---------------------------------
Resolution_Date,
dbo.F_RPT_TO_DATE(Resolution_date,0) Resolution_Date_GMT,
---------------------------------
case when b.Priority_Name='Critical' then Group_Response_Time
else Group_Response_Time_BH
end Group_Response_Time,
---------------------------------
--0=No,1=Yes
case when b.Priority_Name='Critical' then
case when Response_Indicator_Time =0
then 1 else 0 end
else
case when Response_Indicator_Time_BH =0
then 1 else 0 end
end Response_Indicator,
--------------------------------
case when b.Priority_Name='Critical' then
case when Resolution_Indicator_Time =0
then 1 else 0 end
else
case when Resolution_Indicator_Time_BH =0
then 1 else 0 end
end Resolution_Indicator,
---------------------------------
case when b.Priority_Name='Critical' then
case when Total_Resolution_Indicator_Time =0
then 1 else 0 end
else
case when Total_Resolution_Indicator_Time_BH =0
then 1 else 0 end
end Total_Resolution_Indicator,
-----------------------------------
case when b.Priority_Name='Critical' then
Response_Indicator_Time
else
Response_Indicator_Time_BH
end Response_Time,
-----------------------------------
case when b.Priority_Name='Critical' then
Resolution_Indicator_Time
else
Resolution_Indicator_Time_BH
end Resolution_Time,
-----------------------------------
case when b.Priority_Name='Critical' then
Total_Resolution_Indicator_Time
else
Total_Resolution_Indicator_Time_BH
end Total_Resolution_Time,
-----------------------------------
--New 7/24/2008
/*case when b.Priority_Name='Critical' then
Resolution_Indicator_Time_Min_Pending
else
Resolution_Indicator_Time_Min_Pending_BH
end Resolution_Time_Min_Pending,*/
---------------------------------
case when b.Priority_Name='Critical' then
case when Resolution_Indicator_Time_Min_Pending =0
then 1 else 0 end
else
case when Resolution_Indicator_Time_Min_Pending_BH =0
then 1 else 0 end
end Resolution_Indicator_Time_Min_Pending,
-----------------------------------
a.CATEGORIZATION_CLASS,
a.CATEGORIZATION_CATEGORY,
a.CATEGORIZATION_TYPE,
a.CATEGORIZATION_ITEM,
a.DESCRIPTION_OF_ITEM,
a.time_spent
from RPT_SLA_REPORT a,VRPT_SLA_TIMES b
where a.priority=b.priority_join
and b.SLA_Name = @sla_name
and exists (select 1 from history_actions x
where date_time >= @dt_st
and date_time < @dt_end
and status in ('Closed','Resolved')
and a.entry_key=x.ticket_key)
--and create_date >= @dt_st
--and create_date < @dt_end
and a.priority=coalesce(@priority,a.priority)
and assigned_to_group=coalesce(@grp,assigned_to_group)
--and CATEGORIZATION_CLASS like isnull(@class, '%')
--and CATEGORIZATION_CATEGORY like isnull(@category, '%')
--and CATEGORIZATION_TYPE like isnull(@type, '%')
--and CATEGORIZATION_ITEM like isnull(@item, '%')
and CATEGORIZATION_CLASS IN ('@class')
and CATEGORIZATION_CATEGORY IN ('@category')
and CATEGORIZATION_TYPE IN ('@type')
and CATEGORIZATION_ITEM IN ('@item')
)
---------------------------------------------------------------
The SQL exception which gets fired is this :
[Fri Oct 03 12:51:25 GMT+05:30 2008] SQL:
select Create_Date, Item_Type, ID, Assigned_To, Assigned_To_Group, Priority_Name, P_Priority, Assigned_To_Group_Date, Ownership_date, Total_Pending_Time, Resolution_Date, Group_Response_Time,Resolution_Time,Total_Resolution_Time, Response_Indicator, Resolution_Indicator, Total_Resolution_Indicator, Categorization_Class, Categorization_Category, Categorization_Type, Categorization_Item,Description_Of_Item, Time_Spent FROM [InfraDesk_ASP_3].[dbo].[RPT_nSLA_REPORT] ( 'All Priorities',1222799400,1225391399,null,null,('Software'), ('Client/Server Application',' Intel Engineering',' INTERNET'), ('Appliance',' Application'), ('') )ORDER BY Item_Type desc, Assigned_To asc
[Fri Oct 03 12:51:27 GMT+05:30 2008] Pool Query SQLException: [IDL1S]Line 1: Incorrect syntax near '('.
Can anybody please guide me on this ?
Thanks,
Vijoy
October 3, 2008 at 4:40 am
There's no syntax errors with the function. The problem is how you're calling it.
... FROM [InfraDesk_ASP_3].[dbo].[RPT_nSLA_REPORT] ( 'All Priorities',1222799400,1225391399,null,null,('Software'),
('Client/Server Application',' Intel Engineering',' INTERNET'), ('Appliance',' Application'), ('')
Functions take their parameters in a single set of brackets. You have multiple sets of brackets. So, it should be something more like this (if I'm guessing right what you want to do)
FROM [InfraDesk_ASP_3].[dbo].[RPT_nSLA_REPORT] ( 'All Priorities',1222799400,1225391399,null,null,'Software',
'''Client/Server Application'','' Intel Engineering'','' INTERNET'', ''Appliance'','' Application'', '''')
That said, the function's probably not going to do what you want.
and CATEGORIZATION_CLASS IN ('@class')
and CATEGORIZATION_CATEGORY IN ('@category')
and CATEGORIZATION_TYPE IN ('@type')
and CATEGORIZATION_ITEM IN ('@item')
If you pass a comma-delimited list in a variable and then use it in an IN, SQL will not treat it like a list of values, but rather will do an equality. To do what you want requires either dynamic SQL (which isn't allowed in a function) or a split function (check the scripts library here for several good ones)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 3, 2008 at 7:10 am
Thanks for a prompt response.
Can you please give any sample code for splitting the values for the function ? Or I would be very grateful if you could provide me with a link.
October 3, 2008 at 10:55 am
This one's pretty good. Read the comments on the blog post too, as the function needs a table creating.
http://philcart.blogspot.com/2007/06/split-function.html
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 4, 2008 at 6:12 pm
GilaMonster (10/3/2008)
This one's pretty good. Read the comments on the blog post too, as the function needs a table creating.
Teach someone to fish... please see the following URL for how a Tally or Numbers table actually works to do a split. It also shows you how to make one without a bloody While loop! 😉
http://www.sqlservercentral.com/articles/TSQL/62867/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply