Problem with SQL function

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » Problem with SQL function

Problem with SQL function

Rate Topic

Display Mode

Topic Options

Author

Message

Vijoy Kolady

Posted Friday, October 03, 2008 1:34 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, December 09, 2008 4:07 AM
Points: 17, Visits: 60

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

Post #580109

GilaMonster

Posted Friday, October 03, 2008 4:40 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 37,738, Visits: 30,011

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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #580163

Vijoy Kolady

Posted Friday, October 03, 2008 7:10 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, December 09, 2008 4:07 AM
Points: 17, Visits: 60

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.

Post #580246

GilaMonster

Posted Friday, October 03, 2008 10:55 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 37,738, Visits: 30,011

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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #580453

Jeff Moden

Posted Saturday, October 04, 2008 6:12 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:14 AM
Points: 32,910, Visits: 26,802

GilaMonster (10/3/2008)

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

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

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #580789

« Prev Topic | Next Topic »

Permissions