|
|
Posted by Carsten on 07/20/07 16:39
Hello Folks,
I encountered a problem with SQL server 2000 and UDFs.
I have a scalar UDF and a table UDF where I would like the scalar UDF
to provide the argument for the table UDF like in:
SELECT
*
FROM
transaction_t
WHERE
trxn_gu_id in (
select get_trxns_for_quarter(get_current_quarter( GetDate() ) )
)
'get_current_quarter' returns an integer which is a GUID in a table
containing business quarter definitions, like start date, end date.
'get_current_quarter' is a scalar UDF.
'get_trxns_for_quarter' will then get all transctions that fall into
that quarter and return their GUID's in a table.
'get_trxns_for_quarter' is a table UDF.
This doesn't seem to work at all. Regardless whether I provide the
namespace (schema) calling the scalar UDF or not. Error message is
just different.
Both functions operate correctly invoked un-nested.
The whole expression does work fine if I turn 'get_trxns_for_quarter'
into a scalar UDF as well, e.g. by returning just one trxn_gu_id with
e.g. MAX() in a scalar datatype. But of course that's no good to me.
It also works fine if I select the result of 'get_current_quarter'
into a variable and pass that variable into 'get_trxns_for_quarter'.
But that's no good to me either since then I cannot use the whole
thing embedded into other SELECT clauses.
Both UDF's are non-deterministic but I couldnt see how that would have
an impact anyway.
Never mind the syntax on that example or anyhting, I tried all the
obvious and not so obvious stuff and it really seems to come down to
the fact that one UDF is scalar and the other one is not. However, I
did not come across any type of information saying that this cannot be
done.
Have you any ideas?
Any help would be greatly appreciated.
Carsten
Navigation:
[Reply to this message]
|