Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

user defined functions

21 posts in Product Futures Discussion Last posting was on 2002-02-19 10:58:00.0Z
kevbo Posted on 2002-01-16 21:44:12.0Z
From: kevbo
Date: Wed, 16 Jan 2002 16:44:12 -0500
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: user defined functions
Message-ID: <4AD0DAD97BA7709D0077674085256B43.0077675685256B43@webforums>
Lines: 3
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:924
Article PK: 94450

User defined functions - way overdue. You can't always create a #temp
table, and I'm tired of bumping into this. Many times a simple function
will do. There are plenty of built-in functions (object_name(id)), so
what's the problem?


Piotr Posted on 2002-02-19 10:58:00.0Z
Subject: Re: user defined functions
From: Piotr <vpiotr@poczta.onet.pl>
References: <4AD0DAD97BA7709D0077674085256B43.0077675685256B43@webforums>
Organization: BMS
Message-ID: <Xns91BA791C795F3vpiotrpocztaonetpl@199.93.177.77>
Followup-To: poster
User-Agent: Xnews/4.06.22
Newsgroups: sybase.public.ase.product_futures_discussion
Date: Tue, 19 Feb 2002 05:58:00 -0500
Lines: 44
NNTP-Posting-Host: 212.244.167.206
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:761
Article PK: 94292

kevbo wrote in
news:4AD0DAD97BA7709D0077674085256B43.0077675685256B43@webforums:

>
> User defined functions - way overdue. You can't always create a #temp
> table, and I'm tired of bumping into this. Many times a simple function
> will do. There are plenty of built-in functions (object_name(id)), so
> what's the problem?

I dedicate this SQL below to all people that think UDF in ASE is not
necessary (example from my real database):

Regards,
Piotr L.

create view customer_detail as
select
c.customer_id as customer_id,
(
case
when (char_length(rtrim(ltrim(isnull(c.company, '')))) <
(34 - char_length(rtrim(ltrim(isnull(c.name1, '')))) -
char_length(rtrim(ltrim(isnull(c.name1, ''))))))
then
substring(rtrim(ltrim(isnull(c.company, ''))) + ' ' +
rtrim(ltrim(isnull(c.name1, ''))) + ' ' +
rtrim(ltrim(isnull(c.name2, ''))), 1, 35)
else
substring(rtrim(ltrim(isnull(c.company, ''))), 1, 35)
end
) as naw1,
(
case
when (char_length(rtrim(ltrim(isnull(c.company, '')))) <
(34 - char_length(rtrim(ltrim(isnull(c.name1, '')))) -
char_length(rtrim(ltrim(isnull(c.name1, ''))))))
then
substring(
rtrim(ltrim(isnull(c.company, ''))) + ' ' +
rtrim(ltrim(isnull(c.name1, ''))) + ' ' +
rtrim(ltrim(isnull(c.name2, ''))), 36, 35)
else
substring(
rtrim(ltrim(isnull(c.name1, ''))) + ' ' +
rtrim(ltrim(isnull(c.name2, ''))), 1, 35)
end
) as naw2,
c.street as naw3,
(
rtrim(ltrim(isnull(city, ''))) + ' ' +
rtrim(ltrim(isnull(zip_code, '')))
) as naw4
from
customer c
with check option


kevbo Posted on 2002-01-25 17:34:48.0Z
From: kevbo
Date: Fri, 25 Jan 2002 12:34:48 -0500
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: user defined functions
Message-ID: <1E7BCCC6428DF935006091D685256B4C.007AC4D585256B43@webforums>
References: <4AD0DAD97BA7709D0077674085256B43.0077675685256B43@webforums>
Lines: 19
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:855
Article PK: 94383

To clarify, I would like to be able to do the following, same as 1 response
to my initial posting stated: in-line function calls in the select
statement (just like you-know-who-that-starts-with-an-O allows :-)

select col1,
col2,
foo(col3),
col4
from...
where...

My UDF "foo" might be a 5 table-join that calculates some value based on
the input "col3". Perhaps I'm totalling up records to show how much a
customer owes me at this point, and want that info while I'm querying their
name and address. Dig?

If this can be done in java that's at least a start. If someone could
illustrate any hacks/workarounds that sounds good to me too. I've seen some
stuff w/ a "case" statement, but it's not the same.


Sherlock, Kevin Posted on 2002-01-25 21:41:00.0Z
Message-ID: <3C51D0D2.A5508398@qwest.com>
Date: Fri, 25 Jan 2002 15:41:00 -0600
From: "Sherlock, Kevin" <ksherlo@qwest.com>
Reply-To: ksherlo@qwest.com
Organization: QWEST DEX
X-Mailer: Mozilla 4.79 (Macintosh; U; PPC)
X-Accept-Language: en,pdf,ko
MIME-Version: 1.0
Subject: Re: user defined functions
References: <4AD0DAD97BA7709D0077674085256B43.0077675685256B43@webforums> <1E7BCCC6428DF935006091D685256B4C.007AC4D585256B43@webforums>
Content-Type: multipart/mixed; boundary="------------C69B0AB1E82B1E29E6FAD66C"
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 43
NNTP-Posting-Host: np-10.dex.uswest.com 199.168.39.10
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:851
Article PK: 94378


kevbo wrote:
>
> To clarify, I would like to be able to do the following, same as 1 response
> to my initial posting stated: in-line function calls in the select
> statement (just like you-know-who-that-starts-with-an-O allows :-)
>
> select col1,
> col2,
> foo(col3),
> col4
> from...
> where...
>
> My UDF "foo" might be a 5 table-join that calculates some value based on
> the input "col3". Perhaps I'm totalling up records to show how much a
> customer owes me at this point, and want that info while I'm querying their
> name and address. Dig?
>
> If this can be done in java that's at least a start. If someone could
> illustrate any hacks/workarounds that sounds good to me too. I've seen some
> stuff w/ a "case" statement, but it's not the same.

select col1,
col2,
myUDF = (select sum(...) from t1,t2,t3,t4,t5 where t1.colx = col3 ....
col4
from
where


Download VCard ksherlo.vcf


Pablo Sanchez Posted on 2002-01-25 22:18:05.0Z
From: "Pablo Sanchez" <pablo@dev.null>
References: <4AD0DAD97BA7709D0077674085256B43.0077675685256B43@webforums> <1E7BCCC6428DF935006091D685256B4C.007AC4D585256B43@webforums> <3C51D0D2.A5508398@qwest.com>
Subject: Re: user defined functions
Date: Fri, 25 Jan 2002 15:18:05 -0700
Lines: 50
Organization: High-Performance Database Engineering
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <NckQs7epBHA.189@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 207.225.105.222
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:848
Article PK: 94376


"Sherlock, Kevin" <ksherlo@qwest.com> wrote in message
news:3C51D0D2.A5508398@qwest.com...
> kevbo wrote:
> >
> > To clarify, I would like to be able to do the following, same as 1
response
> > to my initial posting stated: in-line function calls in the select
> > statement (just like you-know-who-that-starts-with-an-O allows :-)
> >
> > select col1,
> > col2,
> > foo(col3),
> > col4
> > from...
> > where...
> >
> > My UDF "foo" might be a 5 table-join that calculates some value
based on
> > the input "col3". Perhaps I'm totalling up records to show how
much a
> > customer owes me at this point, and want that info while I'm
querying their
> > name and address. Dig?
> >
> > If this can be done in java that's at least a start. If someone
could
> > illustrate any hacks/workarounds that sounds good to me too. I've
seen some
> > stuff w/ a "case" statement, but it's not the same.
>
> select col1,
> col2,
> myUDF = (select sum(...) from t1,t2,t3,t4,t5 where t1.colx =
col3 ....
> col4
> from
> where

Same problem as before: supportability. If a developer has the same
function implemented multiple times throughout his code, what's the
likelihood of catching all cases? A work-around that I've used, and
will continue, is to preprocess the code.
--
Pablo Sanchez, High-Performance Database Engineering
www.hpdbe.com
Independent Contractor, available for short-term and long-term
contracts


Roger Broadbent Posted on 2002-01-28 16:29:15.0Z
From: "Roger Broadbent" <RBroadbent@wilco-int.com>
References: <4AD0DAD97BA7709D0077674085256B43.0077675685256B43@webforums> <1E7BCCC6428DF935006091D685256B4C.007AC4D585256B43@webforums> <3C51D0D2.A5508398@qwest.com> <NckQs7epBHA.189@forums.sybase.com>
Subject: Re: user defined functions
Date: Mon, 28 Jan 2002 16:29:15 -0000
Lines: 63
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <lTL#jnBqBHA.366@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: wilcohost-180.wilco-int.com 212.36.174.180
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:840
Article PK: 94368

...or you could define a view to reduce the amount of logic in the subquery
itself - most maintenance would be on the view rather than multiple queries.

--
Roger Broadbent
Technical Consultant
Wilco International Ltd

Pablo Sanchez <pablo@dev.null> wrote in message
news:NckQs7epBHA.189@forums.sybase.com...
>
> "Sherlock, Kevin" <ksherlo@qwest.com> wrote in message
> news:3C51D0D2.A5508398@qwest.com...
> > kevbo wrote:
> > >
> > > To clarify, I would like to be able to do the following, same as 1
> response
> > > to my initial posting stated: in-line function calls in the select
> > > statement (just like you-know-who-that-starts-with-an-O allows :-)
> > >
> > > select col1,
> > > col2,
> > > foo(col3),
> > > col4
> > > from...
> > > where...
> > >
> > > My UDF "foo" might be a 5 table-join that calculates some value
> based on
> > > the input "col3". Perhaps I'm totalling up records to show how
> much a
> > > customer owes me at this point, and want that info while I'm
> querying their
> > > name and address. Dig?
> > >
> > > If this can be done in java that's at least a start. If someone
> could
> > > illustrate any hacks/workarounds that sounds good to me too. I've
> seen some
> > > stuff w/ a "case" statement, but it's not the same.
> >
> > select col1,
> > col2,
> > myUDF = (select sum(...) from t1,t2,t3,t4,t5 where t1.colx =
> col3 ....
> > col4
> > from
> > where
>
> Same problem as before: supportability. If a developer has the same
> function implemented multiple times throughout his code, what's the
> likelihood of catching all cases? A work-around that I've used, and
> will continue, is to preprocess the code.
> --
> Pablo Sanchez, High-Performance Database Engineering
> www.hpdbe.com
> Independent Contractor, available for short-term and long-term
> contracts
>
>


Pablo Sanchez Posted on 2002-01-28 18:11:58.0Z
From: "Pablo Sanchez" <pablo@dev.null>
References: <4AD0DAD97BA7709D0077674085256B43.0077675685256B43@webforums> <1E7BCCC6428DF935006091D685256B4C.007AC4D585256B43@webforums> <3C51D0D2.A5508398@qwest.com> <NckQs7epBHA.189@forums.sybase.com> <lTL#jnBqBHA.366@forums.sybase.com>
Subject: Re: user defined functions
Date: Mon, 28 Jan 2002 11:11:58 -0700
Lines: 17
Organization: High-Performance Database Engineering
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <W$ELNgCqBHA.182@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 207.225.105.222
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:839
Article PK: 94367


"Roger Broadbent" <RBroadbent@wilco-int.com> wrote in message
news:lTL#jnBqBHA.366@forums.sybase.com...
> ...or you could define a view to reduce the amount of logic in the
subquery
> itself - most maintenance would be on the view rather than multiple

queries.

The problem still presents itself if you need said function, say, 50
times in a medium size (400 tables?) schema.
--
Pablo Sanchez, High-Performance Database Engineering
www.hpdbe.com
Independent Contractor, available for short-term and long-term
contracts


Pablo Sanchez Posted on 2002-01-16 21:37:50.0Z
From: "Pablo Sanchez" <pablo@dev.null>
References: <4AD0DAD97BA7709D0077674085256B43.0077675685256B43@webforums>
Subject: Re: user defined functions
Date: Wed, 16 Jan 2002 14:37:50 -0700
Lines: 20
Organization: High-Performance Database Engineering
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <fHUtDbtnBHA.189@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 207.225.105.222
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:923
Article PK: 94448


<kevbo> wrote in message
news:4AD0DAD97BA7709D0077674085256B43.0077675685256B43@webforums...
>
> User defined functions - way overdue. You can't always create a
#temp
> table, and I'm tired of bumping into this. Many times a simple
function
> will do. There are plenty of built-in functions (object_name(id)),
so
> what's the problem?

Are you following up on my post? I certainly agree with you. :)
--
Pablo Sanchez, High-Performance Database Engineering
www.hpdbe.com
Independent Contractor, available for short-term and long-term
contracts


Roger Broadbent Posted on 2002-01-17 11:22:16.0Z
From: "Roger Broadbent" <RBroadbent@wilco-int.com>
References: <4AD0DAD97BA7709D0077674085256B43.0077675685256B43@webforums> <fHUtDbtnBHA.189@forums.sybase.com>
Subject: Re: user defined functions
Date: Thu, 17 Jan 2002 11:22:16 -0000
Lines: 36
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <S7cbGo0nBHA.189@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: wilcohost-180.wilco-int.com 212.36.174.180
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:921
Article PK: 94449

Please don't shoot the messenger, but I believe the Sybase line is that UDFs
are provided via Java in the server.

You also have the option of using subqueries, case and/or characteristic
functions which are actually quite flexible in this regard.

--
Roger Broadbent
Technical Consultant
Wilco International Ltd

Pablo Sanchez <pablo@dev.null> wrote in message
news:fHUtDbtnBHA.189@forums.sybase.com...
>
> <kevbo> wrote in message
> news:4AD0DAD97BA7709D0077674085256B43.0077675685256B43@webforums...
> >
> > User defined functions - way overdue. You can't always create a
> #temp
> > table, and I'm tired of bumping into this. Many times a simple
> function
> > will do. There are plenty of built-in functions (object_name(id)),
> so
> > what's the problem?
>
> Are you following up on my post? I certainly agree with you. :)
> --
> Pablo Sanchez, High-Performance Database Engineering
> www.hpdbe.com
> Independent Contractor, available for short-term and long-term
> contracts
>
>


Pablo Sanchez Posted on 2002-01-17 14:04:05.0Z
From: "Pablo Sanchez" <pablo@dev.null>
References: <4AD0DAD97BA7709D0077674085256B43.0077675685256B43@webforums> <fHUtDbtnBHA.189@forums.sybase.com> <S7cbGo0nBHA.189@forums.sybase.com>
Subject: Re: user defined functions
Date: Thu, 17 Jan 2002 07:04:05 -0700
Lines: 21
Organization: High-Performance Database Engineering
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <D8lTLC2nBHA.189@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 207.225.105.222
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:920
Article PK: 94447


"Roger Broadbent" <RBroadbent@wilco-int.com> wrote in message
news:S7cbGo0nBHA.189@forums.sybase.com...
> Please don't shoot the messenger, but I believe the Sybase line is
that UDFs
> are provided via Java in the server.
>
> You also have the option of using subqueries, case and/or
characteristic
> functions which are actually quite flexible in this regard.
>

Holding back .... :)...seriously, these are poor work-arounds. Oh
well.
--
Pablo Sanchez, High-Performance Database Engineering
www.hpdbe.com
Independent Contractor, available for short-term and long-term
contracts


Bala_Nair Posted on 2002-01-21 22:22:44.0Z
From: Bala_Nair
Date: Mon, 21 Jan 2002 17:22:44 -0500
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: user defined functions
Message-ID: <825DF8C17C5770EE007AEE8585256B48.0053203085256B44@webforums>
References: <4AD0DAD97BA7709D0077674085256B43.0077675685256B43@webforums> <fHUtDbtnBHA.189@forums.sybase.com> <S7cbGo0nBHA.189@forums.sybase.com> <D8lTLC2nBHA.189@forums.sybase.com>
Lines: 3
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:899
Article PK: 94425

It would be very convenient if sybase also provides UDFs like oracle does.
The oracle functions can be used in the SELECT statement. Adaptive Server
Anywhere supports UDFs but it can't be used in a SELECT statement.


Steve Olson Posted on 2002-02-08 17:04:00.0Z
Message-ID: <3C640500.1552510C@sybase.com>
Date: Fri, 08 Feb 2002 12:04:00 -0500
From: Steve Olson <olson@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.79 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: user defined functions
References: <4AD0DAD97BA7709D0077674085256B43.0077675685256B43@webforums> <fHUtDbtnBHA.189@forums.sybase.com> <S7cbGo0nBHA.189@forums.sybase.com> <D8lTLC2nBHA.189@forums.sybase.com> <825DF8C17C5770EE007AEE8585256B48.0053203085256B44@webforums>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 14
NNTP-Posting-Host: 157.133.112.226
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:789
Article PK: 94320

ASE 12.5 supports the notion of 'create function'. This allows the function creator to put a SQL wrapper around java methods. The language used to implement the function can only be java -- for now. ASA allows the implementation language to be SQL or even a C/C++ dll.

Regards,
Steve

Bala_Nair wrote:
>
> It would be very convenient if sybase also provides UDFs like oracle does.
> The oracle functions can be used in the SELECT statement. Adaptive Server
> Anywhere supports UDFs but it can't be used in a SELECT statement.


anil goel Posted on 2002-01-23 18:04:00.0Z
From: "anil goel" <anilgoel@ianywhere.com>
References: <4AD0DAD97BA7709D0077674085256B43.0077675685256B43@webforums> <fHUtDbtnBHA.189@forums.sybase.com> <S7cbGo0nBHA.189@forums.sybase.com> <D8lTLC2nBHA.189@forums.sybase.com> <825DF8C17C5770EE007AEE8585256B48.0053203085256B44@webforums>
Subject: Re: user defined functions
Date: Wed, 23 Jan 2002 13:04:00 -0500
Lines: 15
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4807.1700
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4807.1700
Message-ID: <4DlBgkDpBHA.190@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 172.31.141.177
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:884
Article PK: 94412


<Bala_Nair> wrote in message
news:825DF8C17C5770EE007AEE8585256B48.0053203085256B44@webforums...
> Adaptive Server
> Anywhere supports UDFs but it can't be used in a SELECT statement.

Could you elaborate, please?

I'm not aware of any restrictions in ASA on where UDFs can be used -- a UDF
can be used anywhere an expression is allowed.

-anil
ASA Development


Bala_Nair Posted on 2002-02-07 20:32:12.0Z
From: Bala_Nair
Date: Thu, 7 Feb 2002 15:32:12 -0500
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: user defined functions
Message-ID: <C8D00E7CC4228E160070CFB385256B59.006AF76A85256B4C@webforums>
References: <4AD0DAD97BA7709D0077674085256B43.0077675685256B43@webforums> <fHUtDbtnBHA.189@forums.sybase.com> <S7cbGo0nBHA.189@forums.sybase.com> <D8lTLC2nBHA.189@forums.sybase.com> <825DF8C17C5770EE007AEE8585256B48.0053203085256B44@webforums> <4DlBgkDpBHA.190@forums.sybase.com>
Lines: 12
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:795
Article PK: 94324

Anil,

Is it possible for returning a value from a function as a column.

e.g.,
SELECT col_1, Col_2, some_user_defined_function(col_1)
FROM table_1
WHERE <where_cluase>

I'm not sure this feature is incorporated in the latest versions of ASA.

Bala Nair


anil goel Posted on 2002-02-08 18:38:23.0Z
From: "anil goel" <anilgoel@ianywhere.com>
References: <4AD0DAD97BA7709D0077674085256B43.0077675685256B43@webforums> <fHUtDbtnBHA.189@forums.sybase.com> <S7cbGo0nBHA.189@forums.sybase.com> <D8lTLC2nBHA.189@forums.sybase.com> <825DF8C17C5770EE007AEE8585256B48.0053203085256B44@webforums> <4DlBgkDpBHA.190@forums.sybase.com> <C8D00E7CC4228E160070CFB385256B59.006AF76A85256B4C@webforums>
Subject: Re: user defined functions
Date: Fri, 8 Feb 2002 13:38:23 -0500
Lines: 23
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4807.1700
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4807.1700
Message-ID: <PWc7fCNsBHA.304@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 172.31.141.177
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:787
Article PK: 94319


<Bala_Nair> wrote in message
news:C8D00E7CC4228E160070CFB385256B59.006AF76A85256B4C@webforums...
> Anil,
>
> Is it possible for returning a value from a function as a column.
> e.g.,
> SELECT col_1, Col_2, some_user_defined_function(col_1)
> FROM table_1
> WHERE <where_cluase>

Yes, Bala.

As I said, a function can be used any place that an expression can be.

> I'm not sure this feature is incorporated in the latest versions of ASA.

AFAIK, all versions of ASA have always treated UDFs as expressions...

-anil
ASA Development


Jim Egan Posted on 2002-02-08 04:56:03.0Z
From: Jim Egan <dontspam.dbaguru@eganomics.com>
Subject: Re: user defined functions
Date: Thu, 7 Feb 2002 21:56:03 -0700
Message-ID: <MPG.16cd08506c39b3b298bace@forums.sybase.com>
References: <4AD0DAD97BA7709D0077674085256B43.0077675685256B43@webforums> <fHUtDbtnBHA.189@forums.sybase.com> <S7cbGo0nBHA.189@forums.sybase.com> <D8lTLC2nBHA.189@forums.sybase.com> <825DF8C17C5770EE007AEE8585256B48.0053203085256B44@webforums> <4DlBgkDpBHA.190@forums.sybase.com> <C8D00E7CC4228E160070CFB385256B59.006AF76A85256B4C@webforums>
Reply-To: eganjp@compuserve.com
X-Newsreader: MicroPlanet Gravity v2.50
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 12
NNTP-Posting-Host: 12-252-108-115.client.attbi.com 12.252.108.115
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:794
Article PK: 94323


Bala_Nair wrote...
> Anil,
>
> Is it possible for returning a value from a function as a column.
>
> e.g.,
> SELECT col_1, Col_2, some_user_defined_function(col_1)
> FROM table_1
> WHERE <where_cluase>
>
> I'm not sure this feature is incorporated in the latest versions of ASA.

Yes, ASA can do this. With ASE it requires the use of a Java method.
--
Jim Egan [TeamSybase]
Senior Consultant
Sybase Professional Services


Sherlock, Kevin Posted on 2002-01-18 17:55:53.0Z
Message-ID: <3C48619B.EB2CBF9E@qwest.com>
Date: Fri, 18 Jan 2002 11:55:53 -0600
From: "Sherlock, Kevin" <ksherlo@qwest.com>
Reply-To: ksherlo@qwest.com
Organization: QWEST DEX
X-Mailer: Mozilla 4.79 (Macintosh; U; PPC)
X-Accept-Language: en,pdf,ko
MIME-Version: 1.0
Subject: Re: user defined functions
References: <4AD0DAD97BA7709D0077674085256B43.0077675685256B43@webforums> <fHUtDbtnBHA.189@forums.sybase.com> <S7cbGo0nBHA.189@forums.sybase.com> <D8lTLC2nBHA.189@forums.sybase.com>
Content-Type: multipart/mixed; boundary="------------5B5D4CF892641C3F4BF6C425"
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 60
NNTP-Posting-Host: np-10.dex.uswest.com 199.168.39.10
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:914
Article PK: 94439


Pablo Sanchez wrote:
>
> "Roger Broadbent" <RBroadbent@wilco-int.com> wrote in message
> news:S7cbGo0nBHA.189@forums.sybase.com...
> > Please don't shoot the messenger, but I believe the Sybase line is
> that UDFs
> > are provided via Java in the server.
> >
> > You also have the option of using subqueries, case and/or
> characteristic
> > functions which are actually quite flexible in this regard.
> >
>
> Holding back .... :)...seriously, these are poor work-arounds. Oh
> well.

A rather smug response Pablo.

Some of us happen to agree with Roger here. Obviously much depends on
what the requirement is for UDF's, but many of the questions I've seen
stating the lack of UDF's could be answered (in general terms) with some
of the solutions that Roger mentions. This is especially true for data
presentation or transformation types of requirements.

If you want a dataserver that serves a purpose other than simply serving
data, then you start getting into extensions like Java in the server, or
even an application server for that purpose. I think the line really
starts to blur when customers start demanding functionality from the
server side that really belongs on the client side. Do I really want my
server resources spending lots of time with data presentation tasks?
Isn't that exactly what belongs on the client end?

UDF's that really can't be expressed with the kinds of mechanisms that
Roger mentions are typically very complex anyway.

Yes, some ability to write UDF's are probably warranted, but I wouldn't
catagorize what's already available as "poor work-arounds".


Download VCard ksherlo.vcf


Pablo Sanchez Posted on 2002-01-18 18:49:47.0Z
From: "Pablo Sanchez" <pablo@dev.null>
References: <4AD0DAD97BA7709D0077674085256B43.0077675685256B43@webforums> <fHUtDbtnBHA.189@forums.sybase.com> <S7cbGo0nBHA.189@forums.sybase.com> <D8lTLC2nBHA.189@forums.sybase.com> <3C48619B.EB2CBF9E@qwest.com>
Subject: Re: user defined functions
Date: Fri, 18 Jan 2002 11:49:47 -0700
Lines: 76
Organization: High-Performance Database Engineering
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <701EhGFoBHA.189@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 207.225.105.222
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:912
Article PK: 94437


"Sherlock, Kevin" <ksherlo@qwest.com> wrote in message
news:3C48619B.EB2CBF9E@qwest.com...
> Pablo Sanchez wrote:
> >
> > "Roger Broadbent" <RBroadbent@wilco-int.com> wrote in message
> > news:S7cbGo0nBHA.189@forums.sybase.com...
> > > Please don't shoot the messenger, but I believe the Sybase line
is
> > that UDFs
> > > are provided via Java in the server.
> > >
> > > You also have the option of using subqueries, case and/or
> > characteristic
> > > functions which are actually quite flexible in this regard.
> > >
> >
> > Holding back .... :)...seriously, these are poor work-arounds. Oh
> > well.
>
> A rather smug response Pablo.

Sorry you perceived it that way, definitely not my intention. Roger
says "please don't shoot the messenger" and I say "holding back" with
a smiley. How much more do you want to see in terms of friendliness?
As for my comment that these are poor work-arounds, I don't apologize
for that. They are. See more below.

> Obviously much depends on
> what the requirement is for UDF's, but many of the questions I've
seen
> stating the lack of UDF's could be answered (in general terms) with
some
> of the solutions that Roger mentions. This is especially true for
data
> presentation or transformation types of requirements.

Here's what I previously posted on what I see are the issues with
these work-arounds. You clearly disagree with me and frankly, that's
fine. That's why this NG is about future discussions. Also, not only
do you disagree with me but even Sybase Management has disagreed with
me thus far. I still hold true to my position. I'm lazy. I don't
want to incur the cost of hacking when something so elegant can be
done. Using Oracle, it's a piece of cake. Not trying to bate you but
stating that once someone experiences the beauty of UDF's in indexes,
it's cake. That's my point: UDF's and UDF's in Indexes are a good
thing.

Here's my post on why it's A Good Thing to have UDF's in index
specification.
==========
The above solution is the 'old' method to solve the problem function
based problem. The issue though, as you start to point out, is that
it requires a lot more extra work to support and run it. Minimally,
we're looking at:

* Additional code at the PL/SQL and trigger level
* Maintenance support
* Storage space
* Additional log data (bad in a high-performance situation)

Looking at the cost-analysis, I'd rather invest my developer budget
on:

* create index

Nice and clean.
========
--
Pablo Sanchez, High-Performance Database Engineering
www.hpdbe.com
Independent Contractor, available for short-term and long-term
contracts


Mike Harrold Posted on 2002-01-24 19:06:36.0Z
Subject: Re: user defined functions
References: <4AD0DAD97BA7709D0077674085256B43.0077675685256B43@webforums> <D8lTLC2nBHA.189@forums.sybase.com> <3C48619B.EB2CBF9E@qwest.com> <701EhGFoBHA.189@forums.sybase.com>
X-Newsreader: trn 4.0-test75 (Feb 13, 2001)
From: ao@shell.core.com (Mike Harrold)
Originator: ao@shell.core.com (Mike Harrold)
Message-ID: <YvsB$oQpBHA.313@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
Date: Thu, 24 Jan 2002 14:06:36 -0500
Lines: 80
NNTP-Posting-Host: shell.voyager.net 169.207.1.89
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:868
Article PK: 94394

In article <701EhGFoBHA.189@forums.sybase.com>,

Pablo Sanchez <pablo@dev.null> wrote:
>
>
>"Sherlock, Kevin" <ksherlo@qwest.com> wrote in message
>news:3C48619B.EB2CBF9E@qwest.com...
>> Pablo Sanchez wrote:
>> >
>> > "Roger Broadbent" <RBroadbent@wilco-int.com> wrote in message
>> > news:S7cbGo0nBHA.189@forums.sybase.com...
>> > > Please don't shoot the messenger, but I believe the Sybase line
>is
>> > that UDFs
>> > > are provided via Java in the server.
>> > >
>> > > You also have the option of using subqueries, case and/or
>> > characteristic
>> > > functions which are actually quite flexible in this regard.
>> > >
>> >
>> > Holding back .... :)...seriously, these are poor work-arounds. Oh
>> > well.
>>
>> A rather smug response Pablo.
>
>Sorry you perceived it that way, definitely not my intention. Roger
>says "please don't shoot the messenger" and I say "holding back" with
>a smiley. How much more do you want to see in terms of friendliness?
>As for my comment that these are poor work-arounds, I don't apologize
>for that. They are. See more below.
>
>> Obviously much depends on
>> what the requirement is for UDF's, but many of the questions I've
>seen
>> stating the lack of UDF's could be answered (in general terms) with
>some
>> of the solutions that Roger mentions. This is especially true for
>data
>> presentation or transformation types of requirements.
>
>Here's what I previously posted on what I see are the issues with
>these work-arounds. You clearly disagree with me and frankly, that's
>fine. That's why this NG is about future discussions. Also, not only
>do you disagree with me but even Sybase Management has disagreed with
>me thus far. I still hold true to my position. I'm lazy. I don't
>want to incur the cost of hacking when something so elegant can be
>done. Using Oracle, it's a piece of cake. Not trying to bate you but
>stating that once someone experiences the beauty of UDF's in indexes,
>it's cake. That's my point: UDF's and UDF's in Indexes are a good
>thing.
>
>Here's my post on why it's A Good Thing to have UDF's in index
>specification.
>==========
>The above solution is the 'old' method to solve the problem function
>based problem. The issue though, as you start to point out, is that
>it requires a lot more extra work to support and run it. Minimally,
>we're looking at:
>
>* Additional code at the PL/SQL and trigger level
>* Maintenance support
>* Storage space
>* Additional log data (bad in a high-performance situation)
>
>Looking at the cost-analysis, I'd rather invest my developer budget
>on:
>
>* create index
>
>Nice and clean.

Except you're only thinking as a developer. As a DBA let me tell you
something. It takes ten times (and more) as long to upgrade an 'Orable
database than it does ASE. Why? Because 'Orable is full of crud that
customers have wanted because it makes their life SO much easier. The
problem is that said crud means bloat in the server, which trickles
all the way down to us DBAs.

Then the developers have the gall to complain about how long the DB is
down for upgrades. Heh.

/Mike


Pablo Sanchez Posted on 2002-01-24 19:50:10.0Z
From: "Pablo Sanchez" <pablo@dev.null>
References: <4AD0DAD97BA7709D0077674085256B43.0077675685256B43@webforums> <D8lTLC2nBHA.189@forums.sybase.com> <3C48619B.EB2CBF9E@qwest.com> <701EhGFoBHA.189@forums.sybase.com> <YvsB$oQpBHA.313@forums.sybase.com>
Subject: Re: user defined functions
Date: Thu, 24 Jan 2002 12:50:10 -0700
Lines: 37
Organization: High-Performance Database Engineering
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <pRnXWERpBHA.123@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 207.225.105.222
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:867
Article PK: 94395


"Mike Harrold" <ao@shell.core.com> wrote in message
news:YvsB$oQpBHA.313@forums.sybase.com...
> In article <701EhGFoBHA.189@forums.sybase.com>,
> Pablo Sanchez <pablo@dev.null> wrote:
>
> Except you're only thinking as a developer.

Actually, I had my 'mangler' hat on... although at first view, it did
look like a development manager. My idea is to reduce costs: hard
dollars as well as support dollars.

> As a DBA let me tell you
> something. It takes ten times (and more) as long to upgrade an
'Orable
> database than it does ASE. Why? Because 'Orable is full of crud that
> customers have wanted because it makes their life SO much easier.
The
> problem is that said crud means bloat in the server, which trickles
> all the way down to us DBAs.

ahm, having upgraded Oracle instances (as recent as last month), I'll
beg to differ. Oracle's links libraries into their executables so
that's about as 'tricky' as it gets. The upgrade took 10m or so.

> Then the developers have the gall to complain about how long the DB
is
> down for upgrades. Heh.

<g>
--
Pablo Sanchez, High-Performance Database Engineering
www.hpdbe.com
Independent Contractor, available for short-term and long-term
contracts


Mike Harrold Posted on 2002-01-24 19:58:07.0Z
Subject: Re: user defined functions
References: <4AD0DAD97BA7709D0077674085256B43.0077675685256B43@webforums> <701EhGFoBHA.189@forums.sybase.com> <YvsB$oQpBHA.313@forums.sybase.com> <pRnXWERpBHA.123@forums.sybase.com>
X-Newsreader: trn 4.0-test75 (Feb 13, 2001)
From: ao@shell.core.com (Mike Harrold)
Originator: ao@shell.core.com (Mike Harrold)
Message-ID: <KSOVxFRpBHA.140@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
Date: Thu, 24 Jan 2002 14:58:07 -0500
Lines: 34
NNTP-Posting-Host: shell.voyager.net 169.207.1.89
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:866
Article PK: 94391

In article <pRnXWERpBHA.123@forums.sybase.com>,

Pablo Sanchez <pablo@dev.null> wrote:
>
>
>"Mike Harrold" <ao@shell.core.com> wrote in message
>news:YvsB$oQpBHA.313@forums.sybase.com...
>> In article <701EhGFoBHA.189@forums.sybase.com>,
>> Pablo Sanchez <pablo@dev.null> wrote:
>>
>> Except you're only thinking as a developer.
>
>Actually, I had my 'mangler' hat on... although at first view, it did
>look like a development manager. My idea is to reduce costs: hard
>dollars as well as support dollars.
>
>> As a DBA let me tell you
>> something. It takes ten times (and more) as long to upgrade an
>'Orable
>> database than it does ASE. Why? Because 'Orable is full of crud that
>> customers have wanted because it makes their life SO much easier.
>The
>> problem is that said crud means bloat in the server, which trickles
>> all the way down to us DBAs.
>
>ahm, having upgraded Oracle instances (as recent as last month), I'll
>beg to differ. Oracle's links libraries into their executables so
>that's about as 'tricky' as it gets. The upgrade took 10m or so.

10 months? ;-)

How the h*ll do you manage to run the catalog and catproc scripts in
10 minutes? They take hours. Unless your database is tiny I guess.

Btw, I never said tricky, just time consuming.

/Mike