| Author |
 Topic  |
|
|
mgee
Starting Member
USA
2 Posts |
 Posted - 06/29/2012 : 14:20:40
|
I need to use a value returned from a function in a where clause.
I get the code to compile ok but it runs forever.
Will you please look at this and see if you can help.
Thanks
WHERE FC.CASE_FILING_DT < '05/01/2006'
AND CS.CASE_STATUS_DATE < '05/01/2006'
--the following is the problem code. I have tested the function and --it returns an integer
-- as expected in less than one second.
AND (AOC_RPT.UDFSTATGETCASESTATUS(CS.CASE_ID,'05/01/2006') IN (1000001,1000002,1000006))
AND ( CC.CATEGORY_DESC IN ('CRIMINAL','OUT OF COUNTY CRIMINAL' )) --FELONY&MISD CASE TYPES
AND P.PARTY_ROLE_TYP_ID = 1000071 --DEFENDANT
AND FC.VOID <> 'Y'
AND P.VOID <> 'Y'
Mike Gee
Lead Programmer/Analyst
Arizona Supreme Court
Phoenix, Az
mgee@courts.az.gov |
|
|
yosiasz
Flowing Fount of Yak Knowledge
USA
1608 Posts |
Posted - 06/29/2012 : 14:30:13
|
What happens when you run the query without using the function but using the value returned by the function? (value for AOC_RPT.UDFSTATGETCASESTATUS(CS.CASE_ID,'05/01/2006') )
if result comes back real fast then you know the issue is with your function otherwise do you have indexes on all of these columns you are filtering on
CASE_FILING_DT
CASE_STATUS_DATE
CATEGORY_DESC
PARTY_ROLE_TYP_ID
VOID <> 'Y'
VOID <> 'Y'
are your 'date' columns really date columns or are they varchar fields, if so why not use real datetime columns
are your 'boolean' columns really booleancolumns or are they varchar fields, if so why not use bit columns
<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion |
 |
|
|
Bustaz Kool
Flowing Fount of Yak Knowledge
USA
1430 Posts |
Posted - 06/29/2012 : 18:24:21
|
Could it be that the function is being invoked against the entire dataset and not against the filtered data? Perhaps a subquery that perfroms the filtering first would help:
select subqry.*
from (
select <whatever>
from (tables joined together>
WHERE FC.CASE_FILING_DT < '05/01/2006'
AND CS.CASE_STATUS_DATE < '05/01/2006'
-- -- -- <Not yet> AND (AOC_RPT.UDFSTATGETCASESTATUS(CS.CASE_ID,'05/01/2006') IN (1000001,1000002,1000006))
AND ( CC.CATEGORY_DESC IN ('CRIMINAL','OUT OF COUNTY CRIMINAL' )) --FELONY&MISD CASE TYPES
AND P.PARTY_ROLE_TYP_ID = 1000071 --DEFENDANT
AND FC.VOID <> 'Y'
AND P.VOID <> 'Y'
) subqry
WHERE
AOC_RPT.UDFSTATGETCASESTATUS(subqry.CASE_ID,'05/01/2006') IN (1000001,1000002,1000006))
Just a thought...
=================================================
I don't know what your destiny will be, but one thing I do know: the only ones among you who will be really happy are those who have sought and found how to serve. -Albert Schweitzer |
|
|
|
mgee
Starting Member
USA
2 Posts |
Posted - 06/30/2012 : 20:54:08
|
I think the subquery idea is good.
I will try this and report back.
Thanks for the idea.
Mike Gee
Lead Programmer/Analyst
Arizona Supreme Court
Phoenix, Az
mgee@courts.az.gov |
|
|
| |
Topic |
|
Using a Function in a Where clause
