|
Home > Archive > IQ Server > August 2005 > function in a where condition
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
function in a where condition
|
|
|
| Hi,
I am new in tsql. I created a function say mayfunc(some params) returns int
with some cursor inside and in the end it returns 0 or 1. It compiles.
I got an error that something has to be declared when I do select myfunc() .
My question: Is it possible to used the function in where condition in a
select statement, for example:
select something
from sometable(s)
where myfunc()=0 ???
I also want to used this inside a stored proc.
In Oracle/plsql I can do this!
Thanks in advance,
Henk
| |
| Peace Shi 2005-07-26, 3:25 am |
| yes, you can. but remember the customer defined functions are executed in
ASA engine. so the performance is poor. why not use stored-procedure?
"HenkB" <henk@somedutcomp.nl> 写入消息新闻:42e27aa
1@forums-1-dub...
> Hi,
>
> I am new in tsql. I created a function say mayfunc(some params) returns
> int with some cursor inside and in the end it returns 0 or 1. It compiles.
> I got an error that something has to be declared when I do select myfunc()
> .
>
> My question: Is it possible to used the function in where condition in a
> select statement, for example:
>
> select something
> from sometable(s)
> where myfunc()=0 ???
>
> I also want to used this inside a stored proc.
> In Oracle/plsql I can do this!
>
> Thanks in advance,
>
> Henk
>
| |
|
| > yes, you can. but remember the customer defined functions are executed in
Surprising...? I mean the function is NOT a Java function, its a database
function! Like I said if I do a select myfunc() then I get an error while
the function compile without errors!
>why not use stored-procedure?
Is a stored function NOT a stored procedure??? (create function myfunc as
begin ... end)
Greetings,
Henk
"Peace Shi" <peaceshi@gmail.com> wrote in message
news:42e5eb87@forums
-2-dub...
> yes, you can. but remember the customer defined functions are executed in
> ASA engine. so the performance is poor. why not use stored-procedure?
>
> "HenkB" <henk@somedutcomp.nl> 写入消息新闻:42e27aa
1@forums-1-dub...
>
>
| |
| Peace Shi 2005-07-28, 3:25 am |
| in iq12.6 reference guide, p231:(yes, although it is a database user defined
function. )
-----------------------------------------------------------------------
Note User-defined functions are processed by the Adaptive Server Anywhere
portion of the product. They do not take advantage of the performance
features
of Sybase IQ. Queries that include user-defined functions run at least 10
times
slower than queries without them.
In very few cases, differences in semantics between ASA and Sybase IQ can
produce different results for a query if it is issued in a user-defined
function.
For example, IQ treats the CHAR and VARCHAR data types as distinct and
different, while Anywhere treats CHAR data as if it were VARCHAR.
-------------------------------------------------------------------------
"HenkB" <henk@somedutcomp.nl> 写入消息新闻:42e6a87
8@forums-2-dub...
> Surprising...? I mean the function is NOT a Java function, its a database
> function! Like I said if I do a select myfunc() then I get an error while
> the function compile without errors!
>
> Is a stored function NOT a stored procedure??? (create function myfunc as
> begin ... end)
>
> Greetings,
>
> Henk
> "Peace Shi" <peaceshi@gmail.com> wrote in message
> news:42e5eb87@forums
-2-dub...
>
>
| |
|
| Thanks, but I still cannot used the defined function... The function does
some db-acess using a cursor and only in the end it return a 0 or 1. (yes my
client used iq 12.6)
>Queries that include user-defined functions run at least 10 times slower
>than queries without them.
I understand that. But it is always a tradeoff. Another solution is using
temp tables but then again u have to do some inserting and updating and then
join with the temptable(s) which is also not very good for performance...
It would be nice to if u can index on a udf like in Oracle.
Greetings,
Henk
"Peace Shi" <peaceshi@gmail.com> wrote in message
news:42e83cf8@forums
-2-dub...
> in iq12.6 reference guide, p231:(yes, although it is a database user
> defined function. )
> -----------------------------------------------------------------------
> Note User-defined functions are processed by the Adaptive Server Anywhere
> portion of the product. They do not take advantage of the performance
> features
> of Sybase IQ. Queries that include user-defined functions run at least 10
> times
> slower than queries without them.
> In very few cases, differences in semantics between ASA and Sybase IQ can
> produce different results for a query if it is issued in a user-defined
> function.
> For example, IQ treats the CHAR and VARCHAR data types as distinct and
> different, while Anywhere treats CHAR data as if it were VARCHAR.
> -------------------------------------------------------------------------
>
>
> "HenkB" <henk@somedutcomp.nl> 写入消息新闻:42e6a87
8@forums-2-dub...
>
>
| |
| Peace Shi 2005-08-01, 3:25 am |
| can you paste you function scripts?
"HenkB" <henk@somedutcomp.nl> 写入消息新闻:42eb715
5$1@forums-2-dub...
> Thanks, but I still cannot used the defined function... The function does
> some db-acess using a cursor and only in the end it return a 0 or 1. (yes
> my client used iq 12.6)
>
> I understand that. But it is always a tradeoff. Another solution is using
> temp tables but then again u have to do some inserting and updating and
> then join with the temptable(s) which is also not very good for
> performance...
> It would be nice to if u can index on a udf like in Oracle.
>
> Greetings,
>
> Henk
>
> "Peace Shi" <peaceshi@gmail.com> wrote in message
> news:42e83cf8@forums
-2-dub...
>
>
| |
|
| Hi Peace,
Thanks, I found the problem. It lies in specific IQ versus TSQL syntax.
Do u know a solution. When I created a procedure or function with specifix
IQ syntax (closing statements with semicolon ;) I notice when reverse
engineering that all semicolons are gone and my dynamic sql are not valid
anymore. ....How is this possible and how can I solve this? Standard is
quoted identifier off because of many many procs based on tsql syntax. (see
my earlier post about the same problem with pwdesigner with NO reply yet)
Sorry for the trouble.
HenkB
"Peace Shi" <peaceshi@gmail.com> wrote in message
news:42ed87df@forums
-2-dub...
> can you paste you function scripts?
>
> "HenkB" <henk@somedutcomp.nl> 写入消息新闻:42eb715
5$1@forums-2-dub...
>
>
|
|
|
|
|