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.

Does Sybase T-Sql support custom functions like MS Sql

6 posts in Product Futures Discussion Last posting was on 2004-05-26 15:21:24.0Z
GG Posted on 2004-02-27 21:01:11.0Z
From: "GG" <test@test.com>
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Does Sybase T-Sql support custom functions like MS Sql
Lines: 14
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Original-NNTP-Posting-Host: x4034764f.ip.e-nt.net
Message-ID: <403fb030$1@forums-2-dub>
X-Original-Trace: 27 Feb 2004 13:01:36 -0800, x4034764f.ip.e-nt.net
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 27 Feb 2004 13:00:21 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 27 Feb 2004 13:01:11 -0800
X-Trace: forums-1-dub 1077915671 10.22.108.75 (27 Feb 2004 13:01:11 -0800)
X-Original-Trace: 27 Feb 2004 13:01:11 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1515
Article PK: 96515

Can I create a custom function that returns a value that can be used in a
select cmd?
For example
create function getValue(@1stValue int, @2ndValue varchar(8))
returns int
as
return @1stValue + @2stValue

select getValue(t.c1, t.c2) from t

Thanks


Bret Halford Posted on 2004-02-27 21:27:54.0Z
Message-ID: <403FB62A.1F68312F@sybase.com>
From: Bret Halford <bret@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.76 [en] (Windows NT 5.0; U)
X-Accept-Language: en,ja
MIME-Version: 1.0
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Does Sybase T-Sql support custom functions like MS Sql
References: <403fb030$1@forums-2-dub>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bret-pc2.sybase.com
X-Original-NNTP-Posting-Host: bret-pc2.sybase.com
Date: 27 Feb 2004 13:27:54 -0800
X-Trace: forums-1-dub 1077917274 10.21.37.109 (27 Feb 2004 13:27:54 -0800)
X-Original-Trace: 27 Feb 2004 13:27:54 -0800, bret-pc2.sybase.com
Lines: 20
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1516
Article PK: 96521

You can create user-defined functions in ASE using the Java features
(may require an additional license) and use them in this fashion.
See the CREATE FUNCTION command in the ASE 12.5.1 Reference Manual
for more details.

-bret

GG wrote:
>
> Can I create a custom function that returns a value that can be used in a
> select cmd?
> For example
> create function getValue(@1stValue int, @2ndValue varchar(8))
> returns int
> as
> return @1stValue + @2stValue
>
> select getValue(t.c1, t.c2) from t
>
> Thanks


Bill Posted on 2004-03-01 13:45:19.0Z
Sender: 2685.40433dfd.1804289383@sybase.com
From: bill
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Does Sybase T-Sql support custom functions like MS Sql
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <40433e6f.268f.846930886@sybase.com>
References: <403fb030$1@forums-2-dub><403FB62A.1F68312F@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 1 Mar 2004 05:45:19 -0800
X-Trace: forums-1-dub 1078148719 10.22.241.41 (1 Mar 2004 05:45:19 -0800)
X-Original-Trace: 1 Mar 2004 05:45:19 -0800, 10.22.241.41
Lines: 25
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1517
Article PK: 96520

What are the chances of ASE supporting user defined
functions without the Java licence?

Bill

> You can create user-defined functions in ASE using the
> Java features (may require an additional license) and use
> them in this fashion. See the CREATE FUNCTION command in
> the ASE 12.5.1 Reference Manual for more details.
>
> -bret
>
> GG wrote:
> >
> > Can I create a custom function that returns a value that
> > can be used in a select cmd?
> > For example
> > create function getValue(@1stValue int, @2ndValue
> > varchar(8)) returns int
> > as
> > return @1stValue + @2stValue
> >
> > select getValue(t.c1, t.c2) from t
> >
> > Thanks


Rob Verschoor Posted on 2004-03-01 14:07:06.0Z
Reply-To: "Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Newsgroups: sybase.public.ase.product_futures_discussion
References: <403fb030$1@forums-2-dub><403FB62A.1F68312F@sybase.com> <40433e6f.268f.846930886@sybase.com>
Subject: Re: Does Sybase T-Sql support custom functions like MS Sql
Lines: 32
Organization: Sypron B.V.
MIME-Version: 1.0
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Original-NNTP-Posting-Host: a66233.upc-a.chello.nl
Message-ID: <404343ad$1@forums-2-dub>
X-Original-Trace: 1 Mar 2004 06:07:41 -0800, a66233.upc-a.chello.nl
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 1 Mar 2004 06:06:00 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 1 Mar 2004 06:07:06 -0800
X-Trace: forums-1-dub 1078150026 10.22.108.75 (1 Mar 2004 06:07:06 -0800)
X-Original-Trace: 1 Mar 2004 06:07:06 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1518
Article PK: 96518


<bill> wrote in message news:40433e6f.268f.846930886@sybase.com...
> What are the chances of ASE supporting user defined
> functions without the Java licence?
>
> Bill
>

During the 'ask the experts' session at Techwave 2003, this question
was asked as well. The answers was that it's planned (although they
didn't say for which version exactly). That was a remarkable
difference from earlier years, when the answer to this ever-occurring
question was basically 'no' (although worded more diplomatically). So
there is hope...

HTH,

Rob
-------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5

Author of "Tips, Tricks & Recipes for Sybase ASE" and
"The Complete Sybase ASE Quick Reference Guide"
Online orders accepted at http://www.sypron.nl/shop

mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------


BK Posted on 2004-03-01 14:15:12.0Z
From: "BK" <bill@wpNOSPAMkennedy.com>
Newsgroups: sybase.public.ase.product_futures_discussion
References: <403fb030$1@forums-2-dub><403FB62A.1F68312F@sybase.com> <40433e6f.268f.846930886@sybase.com> <404343ad$1@forums-2-dub>
Subject: Re: Does Sybase T-Sql support custom functions like MS Sql
Lines: 42
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
NNTP-Posting-Host: 144.135.76.124
X-Original-NNTP-Posting-Host: 144.135.76.124
Message-ID: <40434570@forums-1-dub>
Date: 1 Mar 2004 06:15:12 -0800
X-Trace: forums-1-dub 1078150512 144.135.76.124 (1 Mar 2004 06:15:12 -0800)
X-Original-Trace: 1 Mar 2004 06:15:12 -0800, 144.135.76.124
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1519
Article PK: 96522

Thanks Rob.

I keep hinting to folks that it would be a good idea to send me to TechWave.

Bill

"Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote in
message news:404343ad$1@forums-2-dub...
> <bill> wrote in message news:40433e6f.268f.846930886@sybase.com...
> > What are the chances of ASE supporting user defined
> > functions without the Java licence?
> >
> > Bill
> >
>
> During the 'ask the experts' session at Techwave 2003, this question
> was asked as well. The answers was that it's planned (although they
> didn't say for which version exactly). That was a remarkable
> difference from earlier years, when the answer to this ever-occurring
> question was basically 'no' (although worded more diplomatically). So
> there is hope...
>
> HTH,
>
> Rob
> -------------------------------------------------------------
> Rob Verschoor
>
> Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
> and Replication Server 12.5
>
> Author of "Tips, Tricks & Recipes for Sybase ASE" and
> "The Complete Sybase ASE Quick Reference Guide"
> Online orders accepted at http://www.sypron.nl/shop
>
> mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
> http://www.sypron.nl
> Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
> -------------------------------------------------------------
>


bobw Posted on 2004-05-26 15:21:24.0Z
Sender: 4d10.40a3eac8.1804289383@sybase.com
From: bobw
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Does Sybase T-Sql support custom functions like MS Sql
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <40b4b5f4.248d.1681692777@sybase.com>
References: <403fb030$1@forums-2-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 26 May 2004 08:21:24 -0700
X-Trace: forums-1-dub 1085584884 10.22.241.41 (26 May 2004 08:21:24 -0700)
X-Original-Trace: 26 May 2004 08:21:24 -0700, 10.22.241.41
Lines: 82
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1554
Article PK: 96555

I found that user created custom T-SQL functions can be
simulated to a degree using CIS. Instead of calling a
function as "y = fn(x)", the function would be called as
"select y from fn where x = <value>". Not quite as compact
as a true function but does allow you to do anything you can
do in a stored procedure.

I created such a psuedo T-SQL function for one of my
developers that wanted the date displayed in a different
format without having to put a bunch of converts in the
select statement.

It could be used in a select statement as below:

select a.title, (select result from fn_format_date where dt
= a.publication_date) as publication_date
from publications a


The underlying database objects required, working from top
down, to support this pseudo function.

View fn_format_date.

create view fn_format_date (dt, result)
as
select _datetime_value, formatted_result
from format_date (index format_date)
go

I found that the table scan hint was required to force the
optimizer to access the proxy table every time instead of
using reformatting.

Proxy table format_date.

create existing table format_date (
formatted_result char(19) null,
_datetime_value datetime null
)
external procedure
at "loopback.sybsystemprocs.dbo.sp_format_date"
go

Store procedure sp_format_date.

create procedure dp_format_date @datetime_value datetime
as
select convert(varchar,datepart(yy,@dt))+"-"+
case when
char_length(convert(varchar,datepart(mm,@dt))) = 1 then "0"
end +
convert(varchar,datepart(mm,@dt))+"-"+
case when char_length(convert(varchar,datepart(dd,@dt))) =
1 then "0" end +
convert(varchar,datepart(dd,@dt))+"-"+
convert(varchar,@dt,108)
go

This is about as close as I can think of to an user defined
function in Sybase without the java option. This example is
relatively simple but I am sure this technique can be used
to implement any desired function.

Bob Winter
winter12@cox.net

> Can I create a custom function that returns a value that
> can be used in a select cmd?
> For example
> create function getValue(@1stValue int, @2ndValue
> varchar(8))
> returns int
> as
> return @1stValue + @2stValue
>
> select getValue(t.c1, t.c2) from t
>
> Thanks
>
>
>