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.

Is there a way to get the base data type for a UDT

4 posts in General Discussion Last posting was on 2011-09-07 23:42:27.0Z
Michael Posted on 2011-09-07 16:24:56.0Z
Sender: 10af.4e6799ac.1804289383@sybase.com
From: Michael
Newsgroups: sybase.public.ase.general
Subject: Is there a way to get the base data type for a UDT
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e679ad8.113f.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 7 Sep 2011 09:24:56 -0700
X-Trace: forums-1-dub 1315412696 10.22.241.41 (7 Sep 2011 09:24:56 -0700)
X-Original-Trace: 7 Sep 2011 09:24:56 -0700, 10.22.241.41
Lines: 7
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30517
Article PK: 72700

Hi,

I know I can use sp_help but can I get the base primitive
type for a particular user defined datatype with straight
SQL?

Using Sybase ASE V15.5


Bret Halford Posted on 2011-09-07 18:09:11.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:6.0.1) Gecko/20110830 Thunderbird/6.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Is there a way to get the base data type for a UDT
References: <4e679ad8.113f.1681692777@sybase.com>
In-Reply-To: <4e679ad8.113f.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e67b347$1@forums-1-dub>
Date: 7 Sep 2011 11:09:11 -0700
X-Trace: forums-1-dub 1315418951 10.22.241.152 (7 Sep 2011 11:09:11 -0700)
X-Original-Trace: 7 Sep 2011 11:09:11 -0700, vip152.sybase.com
Lines: 57
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30518
Article PK: 72695


On 9/7/2011 10:24 AM, Michael wrote:
> Hi,
>
> I know I can use sp_help but can I get the base primitive
> type for a particular user defined datatype with straight
> SQL?
>
> Using Sybase ASE V15.5

select name
from systypes
where usertype < 99 -- as of 15.5, system types are in range -1..80
and type = (select type from systypes where name = "mydatatype")


Example:

1> sp_addtype mytype, "int"
2> go
Type added.

1> select type, usertype, name from systypes where name = "mytype"
2> go
type usertype
name


---- --------
-------------------------------------------------------------
----------------------------------------------------------------------
------------
56 101
mytype



(1 row affected)

1> select name
2> from systypes where usertype < 99
3> and type = (select type from systypes where name = "mytype")
4> go
name


------------------------------------------------------------------
-------------------------------------------------------------------
----
int



(1 row affected)


Michael Posted on 2011-09-07 20:06:07.0Z
Sender: 10af.4e6799ac.1804289383@sybase.com
From: Michael
Newsgroups: sybase.public.ase.general
Subject: Re: Is there a way to get the base data type for a UDT
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e67ceaf.240a.1681692777@sybase.com>
References: <4e67b347$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 7 Sep 2011 13:06:07 -0700
X-Trace: forums-1-dub 1315425967 10.22.241.41 (7 Sep 2011 13:06:07 -0700)
X-Original-Trace: 7 Sep 2011 13:06:07 -0700, 10.22.241.41
Lines: 78
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30519
Article PK: 72699

This doesn't quite do it. This query will return everything
that has the same type. For example a UDT of type nchar will
have the same type as char. Therefore

sp_addtype mytype "nchar" will have an output of

Name
=====
char
nchar

> On 9/7/2011 10:24 AM, Michael wrote:
> > Hi,
> >
> > I know I can use sp_help but can I get the base
> > primitive type for a particular user defined datatype
> > with straight SQL?
> >
> > Using Sybase ASE V15.5
>
>
> select name
> from systypes
> where usertype < 99 -- as of 15.5, system types are in
> range -1..80 and type = (select type from systypes
> where name = "mydatatype")
>
>
> Example:
>
> 1> sp_addtype mytype, "int"
> 2> go
> Type added.
>
> 1> select type, usertype, name from systypes where name =
> "mytype" 2> go
> type usertype
> name
>
>
> ---- --------
>
> ----------------------------------------------------------
> ---
> ----------------------------------------------------------
> ------------ ------------
> 56 101
> mytype
>
>
>
> (1 row affected)
>
> 1> select name
> 2> from systypes where usertype < 99
> 3> and type = (select type from systypes where name =
> "mytype") 4> go
> name
>
>
>
> ----------------------------------------------------------
> --------
> ----------------------------------------------------------
> --------- ----
> int
>
>
>
> (1 row affected)
>
>
>


"Mark A. Parsons" <iron_horse Posted on 2011-09-07 23:42:27.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Is there a way to get the base data type for a UDT
References: <4e67b347$1@forums-1-dub> <4e67ceaf.240a.1681692777@sybase.com>
In-Reply-To: <4e67ceaf.240a.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e680163$1@forums-1-dub>
Date: 7 Sep 2011 16:42:27 -0700
X-Trace: forums-1-dub 1315438947 10.22.241.152 (7 Sep 2011 16:42:27 -0700)
X-Original-Trace: 7 Sep 2011 16:42:27 -0700, vip152.sybase.com
Lines: 94
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30520
Article PK: 72696

If you take a look at the source code for sp_help you'll find that sp_help cheats a little bit by ignoring some systypes
entries, ie, there are a few datatypes that are identical in terms of their systypes entries so sp_help will only show
one of the entries.

'nchar' is one of the systypes entries ignored by sp_help ... otherwise sp_help would show the same 2 entries that
you're getting from running Bret's query.

Unfortunately there is no way to distinguish between some datatypes, eg, 'char' and 'nchar' due to the the fact that
they have the same exact details in systypes (except for the usertype).

If you run sp_help or ddlgen you'll find that your 'nchar' is reverse engineered as a 'char'.

On 09/07/2011 16:06, Michael wrote:
> This doesn't quite do it. This query will return everything
> that has the same type. For example a UDT of type nchar will
> have the same type as char. Therefore
>
> sp_addtype mytype "nchar" will have an output of
>
> Name
> =====
> char
> nchar
>
>
>
>
>
>
>> On 9/7/2011 10:24 AM, Michael wrote:
>>> Hi,
>>>
>>> I know I can use sp_help but can I get the base
>>> primitive type for a particular user defined datatype
>>> with straight SQL?
>>>
>>> Using Sybase ASE V15.5
>>
>>
>> select name
>> from systypes
>> where usertype< 99 -- as of 15.5, system types are in
>> range -1..80 and type = (select type from systypes
>> where name = "mydatatype")
>>
>>
>> Example:
>>
>> 1> sp_addtype mytype, "int"
>> 2> go
>> Type added.
>>
>> 1> select type, usertype, name from systypes where name =
>> "mytype" 2> go
>> type usertype
>> name
>>
>>
>> ---- --------
>>
>> ----------------------------------------------------------
>> ---
>> ----------------------------------------------------------
>> ------------ ------------
>> 56 101
>> mytype
>>
>>
>>
>> (1 row affected)
>>
>> 1> select name
>> 2> from systypes where usertype< 99
>> 3> and type = (select type from systypes where name =
>> "mytype") 4> go
>> name
>>
>>
>>
>> ----------------------------------------------------------
>> --------
>> ----------------------------------------------------------
>> --------- ----
>> int
>>
>>
>>
>> (1 row affected)
>>
>>
>>