Create "Object Type" in sql server

I'm facing problem while creating object type in sql server.
ex:sql query
"create type student as object ( name varchar2 ( 12) , no number ( 5))" is working fine in Oracle where 'student' can be used in any table as datatype, but its not working in sqlserver. Please can you help me how to create object type in sql server.

Thanks and Regrads,

Suzan:

To create a user defined datatype you need to use the sp_addtype procedure. You might want to look this up in books online. There is an example of use on this page:

http://msdn2.microsoft.com/en-us/library/aa259606(SQL.80).aspx

|||

Thanks , but what i am looking for is different.

i am looking for somting in sql server similar to Oracle OBJECT type

CREATE TYPE Pet_t AS OBJECT (
tag_no INTEGER,
name VARCHAR2(60),
MEMBER FUNCTION set_tag_no (new_tag_no IN INTEGER)
RETURN Pet_t
);

is there a way in sql server to do so....

Thanks

|||

If you're using SQL Server 2005 then you can create CLR user-defined types.

Check out this BOL link for more info:

http://msdn2.microsoft.com/en-us/library/ms131120.aspx

Chris

|||

i have found that this is possible using SQL server 2005, but i need a way to do it in sql server 200.

is it possible to do so?

suzan

|||

Nope.. It is not posible in SQL Server 2000.

SQL SERVER 2000 uses UDT as synonyms for pre-defined types (example - PhoneNumber := Varchar(24) ,etc).

The possible alternate solution is storing your data as BINARY. The issue here is you wont retrive your data on SQL statements (not visible on your QA), you have to depend on your UI/BL to retrive and view the data.

Other simple solution may be storing the data as XML.

|||Thanks ...|||Thanks , but do u have a link to where i can find how to store the data as xml

CR doesnt show what i want

Hi all! I'm a student from Belgium and I need to make several reports in CR, but I'm having some difficulties.. I hope I can get some help here..

I need to make a reports that shows student's grades, from a class and a period (there are 3 periods):
I have 2 tables, the first one (LLADMIN) has the name, class, etc (general fixed data) of the student, the second table (KLEUTER) holds the grades, period, (data that has been inputted)etc..

The reports should look something like this:

Class: LLADMIN.class Period: KLEUTER.period

Name Grade1 Grade2 Grade3
LLAdmin.name KLEUTER.grade1 KLEUTER.grade2 ...
... ... ...

The 2 tables are linked to eachother (visual linking expert) by a unique students-number, with a Right Outer Join from KLEUTER to LLADMIN

I need to show all the students of 1 class, and the according grades, from 1 period.
So i need to put a Select Expert on LLADMIN.class and KLEUTER.period

The Select Experts on LLADMIN.class works perfect, and the report show all the student names and the grades, but also the grades from period 2 and 3
(reports show something like this now:
Name1 |nogrades|
Name2 |nogrades|
Name3 |grade1_period1| |grade2_period1| ...
Name3 |grade1_period2| |grade2_period2| ...
Name3 |grade1_period3| |grade2_period3| ...
Name4 |grade1_period1| |grade2_period1| ...
Name4 |grade1_period2| |grade2_period2| ...
Name5 |nogrades|
...
)

So I put a Select Expert on KLEUTER.period (=1): but then the reports only shows the names of the students who have a grade for that period (where there is a dbase-entry) (not all students have grades for that period)
The reports should still show the names, even if there are no grades present in the dbase.
(reports show something like this now:
Name3 |grade1_period1| |grade2_period1| ...
Name4 |grade1_period1| |grade2_period1| ...
)

I have like 20 students in a class, and as a test i have only given 2 students grades. When I put an Select Expert on KLEUTER.period the reports only shows the names of those 2 students, and their grades. This should not happen! It should show all the names!

I hope you understand my problem! If not, I will give more explantion..

thanks in advance and sry for my not-so-good-english :)The problem is that you should use an outer join to your condition as well KLEUTER.period (=1). This can be best accomplished by using the add command property in the database expert and write manually your database query

select t.field1, r.field1, t.field2, r.field2
from tab1 t, tab2 r
where tab1.field3 = tab2.field3 (+)
and tab2.field4 (+) = 1

This works fine using CR version 10, if you are using an older version (8.5) you can type the 'and tab2.field4 (+) = 'A'' part of the condition to the SQL-query (DATABASE, EDIT SQL-query).

- Jukka

Counting votes

Hello, all. We have a student election application where we need to
determine:
- Total votes for each candidate in each category
- Total votes for all candiates for that same category
Here are the tables: tblVotes holds the students' votes; tblNominees holds
the candidates for the various offices; tblValidNomineeCategories which
holds the variouses offices they can run for. One person may be running for
more than one office. Some candidates are write-ins, so they won't show up
in the tblNominees; we want to get an aggregate count of them for each
voting category. We've thought of using a cursor to loop through, but there
must a better way.
Here's the DDL of the three tables,
CREATE TABLE [tblVotes] (
[personID] [int] NOT NULL ,
[nomineeID] [int] NOT NULL ,
[nomineeCategoryID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[isWriteIn] [bit] NULL CONSTRAINT [DF_tblVotes_isWriteIn] DEFAULT (0),
[dateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblVote_dateAdded] DEFAULT
(getdate()),
CONSTRAINT [PK_tblVote] PRIMARY KEY CLUSTERED
(
[personID],
[nomineeID],
[nomineeCategoryID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [tblNominees] (
[personID] [int] NOT NULL , --> ID of student voter
[nomineeCategoryID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[platformFilename] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[isActive] [bit] NULL CONSTRAINT [DF_tblNominees_isActive] DEFAULT (1),
[dateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblNominees_dateAdded]
DEFAULT (getdate()),
[dateLastMod] [smalldatetime] NULL CONSTRAINT [DF_tblNominees_dateLastMod]
DEFAULT (getdate()),
CONSTRAINT [PK_tblNominees] PRIMARY KEY CLUSTERED
(
[personID],
[nomineeCategoryID]
) ON [PRIMARY] ,
CONSTRAINT [FK_tblNominees_tblGeneralPerson] FOREIGN KEY
(
[personID]
) REFERENCES [tblGeneralPerson] (
[personID]
),
CONSTRAINT [FK_tblNominees_tblValidNomineeCategory]
FOREIGN KEY
(
[nomineeCategoryID]
) REFERENCES [tblValidNomineeCategory] (
[nomineeCategoryID]
)
) ON [PRIMARY]
GO
CREATE TABLE [tblValidNomineeCategory] (
[nomineeCategoryID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[nomineeCategoryDesc] [varchar] (95) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[displayOrder] [smallint] NULL ,
[isActive] [bit] NULL CONSTRAINT [DF_tblValidNomineeCategory_isActive]
DEFAULT (1),
[dateAdded] [smalldatetime] NULL CONSTRAINT
[DF_tblValidNomineeCategory_dateAdded] DEFAULT (getdate()),
CONSTRAINT [PK_tblValidNomineeCategory] PRIMARY KEY CLUSTERED
(
[nomineeCategoryID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Here's some sample data in each table,
tblVotes:
personID nomineeID nomineeCategoryID isWriteIn dateAdded
----
4 36 STBPR 0
2/25/2005 5:01:00 PM
tblNominees:
personID nomineeCategoryID platformFilename isActive dateAdded
dateLastMod
----
--
36 STBPR platform_2.doc 1
2/4/2005 2/4/2005 11:22:00 AM
tblValidNomineeCategory:
nomineeCategoryID nomineeCategoryDesc displayOrder isActive
dateAdded
----
--
STBPR Student Body President 10 1
1/24/2005 11:38:00 AMI think I've got a start on this,
select a.nomineeid, count(a.nomineeid) [countPer],
(select count(*) from tblvotes c where c.nomineecategoryid = 'STBPR')
[countAll]
from dbo.tblvotes a join dbo.tblnominees b on a.nomineeid = b.personid
where a.nomineecategoryid = 'STBPR'
group by a.nomineeid
This returns,
nomineeID countPer countAll
36 3 4
37 1 4
"dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
news:OiXjSURHFHA.2616@.tk2msftngp13.phx.gbl...
> Hello, all. We have a student election application where we need to
> determine:
> - Total votes for each candidate in each category
> - Total votes for all candiates for that same category
> Here are the tables: tblVotes holds the students' votes; tblNominees holds
> the candidates for the various offices; tblValidNomineeCategories which
> holds the variouses offices they can run for. One person may be running
> for more than one office. Some candidates are write-ins, so they won't
> show up in the tblNominees; we want to get an aggregate count of them for
> each voting category. We've thought of using a cursor to loop through, but
> there must a better way.
> Here's the DDL of the three tables,
> CREATE TABLE [tblVotes] (
> [personID] [int] NOT NULL ,
> [nomineeID] [int] NOT NULL ,
> [nomineeCategoryID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [isWriteIn] [bit] NULL CONSTRAINT [DF_tblVotes_isWriteIn] DEFAULT (0),
> [dateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblVote_dateAdded] DEFAULT
> (getdate()),
> CONSTRAINT [PK_tblVote] PRIMARY KEY CLUSTERED
> (
> [personID],
> [nomineeID],
> [nomineeCategoryID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [tblNominees] (
> [personID] [int] NOT NULL , --> ID of student voter
> [nomineeCategoryID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [platformFilename] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [isActive] [bit] NULL CONSTRAINT [DF_tblNominees_isActive] DEFAULT (1),
> [dateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblNominees_dateAdded]
> DEFAULT (getdate()),
> [dateLastMod] [smalldatetime] NULL CONSTRAINT [DF_tblNominees_dateLastMod]
> DEFAULT (getdate()),
> CONSTRAINT [PK_tblNominees] PRIMARY KEY CLUSTERED
> (
> [personID],
> [nomineeCategoryID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_tblNominees_tblGeneralPerson] FOREIGN KEY
> (
> [personID]
> ) REFERENCES [tblGeneralPerson] (
> [personID]
> ),
> CONSTRAINT [FK_tblNominees_tblValidNomineeCategory]
FOREIGN KEY
> (
> [nomineeCategoryID]
> ) REFERENCES [tblValidNomineeCategory] (
> [nomineeCategoryID]
> )
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [tblValidNomineeCategory] (
> [nomineeCategoryID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [nomineeCategoryDesc] [varchar] (95) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [displayOrder] [smallint] NULL ,
> [isActive] [bit] NULL CONSTRAINT [DF_tblValidNomineeCategory_isActive]
> DEFAULT (1),
> [dateAdded] [smalldatetime] NULL CONSTRAINT
> [DF_tblValidNomineeCategory_dateAdded] DEFAULT (getdate()),
> CONSTRAINT [PK_tblValidNomineeCategory] PRIMARY KEY CLUSTERED
> (
> [nomineeCategoryID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> Here's some sample data in each table,
> tblVotes:
> personID nomineeID nomineeCategoryID isWriteIn dateAdded
> ----
> 4 36 STBPR 0 2/25/2005
> 5:01:00 PM
> tblNominees:
> personID nomineeCategoryID platformFilename isActive dateAdded
> dateLastMod
> ----
--
> 36 STBPR platform_2.doc 1
> 2/4/2005 2/4/2005 11:22:00 AM
> tblValidNomineeCategory:
> nomineeCategoryID nomineeCategoryDesc displayOrder isActive
> dateAdded
> ----
--
> STBPR Student Body President 10 1
> 1/24/2005 11:38:00 AM
>|||>>Some candidates are write-ins, so they won't show up in the
Nominees;<<
Please stop using that silly tbl- prefix. But to the point, if you
allow write-ins, then by definition they are a nominee. This is an
attribute of a nominee and not of the ballot.
While I am grateful for the DDL, I think that you over did it a bit.
Never use bits or other asembly language things in SQL, we do not need
the audit trail columns that should be handled with a log tool, pick
clearer names for attributes, etc.
CREATE TABLE Ballots
(voter_student_id INTEGER NOT NULL
REFERENCES Students (student_id),
nominee_student_id INTEGER NOT NULL
REFERENCES Nominees (student_id),
office_id VARCHAR(5) NOT NULL
REFERENCES Offices(office_id),
PRIMARY KEY (student_id, nominee_id, office_id));
CREATE TABLE Nominees
(student_id INTEGER NOT NULL
REFERENCES Students (student_id),
office_id VARCHAR (5) NOT NULL,
platform_file_name VARCHAR (500) NULL,
candidancy_type CHAR(1) DEFAULT 'N' NOT NULL -- nominated, write-in
CHECK (is_writein IN ('W', 'N'))
PRIMARY KEY (nominee_student_id, office_id);
CREATE TABLE Offices
(office_id VARCHAR (5) NOT NULL PRIMARY KEY,
office_desc VARCHAR (95) NOT NULL,
officeholder_student_id INTEGER -- null means vacant
REFERENCES Students (student_id));
SELECT nominee_student_id, office_id, COUNT(*) AS votes
FROM Ballots AS B
GROUP BY nominee_student_id, office_id;
SELECT office_id, COUNT(*) AS votes
FROM Ballots
GROUP BY office_id;
You can combine them into one query:
SELECT B1.nominee_student_id, B1.office_id, COUNT(*) AS votes,
(SELECT COUNT(*) AS votes
FROM Ballots AS B2
WHERE B1.office_id = B2.office_id) AS total_cast
FROM Ballots AS B1
GROUP BY nominee_student_id, office_id;|||Thank you, Celko. Your guidance is much appreciated, along with the correct
DDL. Thanks :)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1109550295.981105.207210@.z14g2000cwz.googlegroups.com...
> Nominees;<<
> Please stop using that silly tbl- prefix. But to the point, if you
> allow write-ins, then by definition they are a nominee. This is an
> attribute of a nominee and not of the ballot.
> While I am grateful for the DDL, I think that you over did it a bit.
> Never use bits or other asembly language things in SQL, we do not need
> the audit trail columns that should be handled with a log tool, pick
> clearer names for attributes, etc.
> CREATE TABLE Ballots
> (voter_student_id INTEGER NOT NULL
> REFERENCES Students (student_id),
> nominee_student_id INTEGER NOT NULL
> REFERENCES Nominees (student_id),
> office_id VARCHAR(5) NOT NULL
> REFERENCES Offices(office_id),
> PRIMARY KEY (student_id, nominee_id, office_id));
> CREATE TABLE Nominees
> (student_id INTEGER NOT NULL
> REFERENCES Students (student_id),
> office_id VARCHAR (5) NOT NULL,
> platform_file_name VARCHAR (500) NULL,
> candidancy_type CHAR(1) DEFAULT 'N' NOT NULL -- nominated, write-in
> CHECK (is_writein IN ('W', 'N'))
> PRIMARY KEY (nominee_student_id, office_id);
> CREATE TABLE Offices
> (office_id VARCHAR (5) NOT NULL PRIMARY KEY,
> office_desc VARCHAR (95) NOT NULL,
> officeholder_student_id INTEGER -- null means vacant
> REFERENCES Students (student_id));
>
> SELECT nominee_student_id, office_id, COUNT(*) AS votes
> FROM Ballots AS B
> GROUP BY nominee_student_id, office_id;
>
> SELECT office_id, COUNT(*) AS votes
> FROM Ballots
> GROUP BY office_id;
> You can combine them into one query:
> SELECT B1.nominee_student_id, B1.office_id, COUNT(*) AS votes,
> (SELECT COUNT(*) AS votes
> FROM Ballots AS B2
> WHERE B1.office_id = B2.office_id) AS total_cast
> FROM Ballots AS B1
> GROUP BY nominee_student_id, office_id;
>
>|||Celko, I've got one followup question: Why are bit columns not a good idea
in SQL Server tables? Thanks.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1109550295.981105.207210@.z14g2000cwz.googlegroups.com...
> Nominees;<<
> Please stop using that silly tbl- prefix. But to the point, if you
> allow write-ins, then by definition they are a nominee. This is an
> attribute of a nominee and not of the ballot.
> While I am grateful for the DDL, I think that you over did it a bit.
> Never use bits or other asembly language things in SQL, we do not need
> the audit trail columns that should be handled with a log tool, pick
> clearer names for attributes, etc.
> CREATE TABLE Ballots
> (voter_student_id INTEGER NOT NULL
> REFERENCES Students (student_id),
> nominee_student_id INTEGER NOT NULL
> REFERENCES Nominees (student_id),
> office_id VARCHAR(5) NOT NULL
> REFERENCES Offices(office_id),
> PRIMARY KEY (student_id, nominee_id, office_id));
> CREATE TABLE Nominees
> (student_id INTEGER NOT NULL
> REFERENCES Students (student_id),
> office_id VARCHAR (5) NOT NULL,
> platform_file_name VARCHAR (500) NULL,
> candidancy_type CHAR(1) DEFAULT 'N' NOT NULL -- nominated, write-in
> CHECK (is_writein IN ('W', 'N'))
> PRIMARY KEY (nominee_student_id, office_id);
> CREATE TABLE Offices
> (office_id VARCHAR (5) NOT NULL PRIMARY KEY,
> office_desc VARCHAR (95) NOT NULL,
> officeholder_student_id INTEGER -- null means vacant
> REFERENCES Students (student_id));
>
> SELECT nominee_student_id, office_id, COUNT(*) AS votes
> FROM Ballots AS B
> GROUP BY nominee_student_id, office_id;
>
> SELECT office_id, COUNT(*) AS votes
> FROM Ballots
> GROUP BY office_id;
> You can combine them into one query:
> SELECT B1.nominee_student_id, B1.office_id, COUNT(*) AS votes,
> (SELECT COUNT(*) AS votes
> FROM Ballots AS B2
> WHERE B1.office_id = B2.office_id) AS total_cast
> FROM Ballots AS B1
> GROUP BY nominee_student_id, office_id;
>
>|||Finding thats fairly easy task.:)
http://tinyurl.com/42evx
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
news:ONUN83ZHFHA.3088@.tk2msftngp13.phx.gbl...
> Celko, I've got one followup question: Why are bit columns not a good idea
> in SQL Server tables? Thanks.
>
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1109550295.981105.207210@.z14g2000cwz.googlegroups.com...
>|||Thanks, Roji. Very in-depth and informative. Does Celko have his own web
site?
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:%2349mp%23ZHFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Finding thats fairly easy task.:)
> http://tinyurl.com/42evx
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
> news:ONUN83ZHFHA.3088@.tk2msftngp13.phx.gbl...
>|||Thats more easy :)
http://www.celko.com/
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
news:eThiasaHFHA.1096@.tk2msftngp13.phx.gbl...
> Thanks, Roji. Very in-depth and informative. Does Celko have his own web
> site?
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:%2349mp%23ZHFHA.3332@.TK2MSFTNGP15.phx.gbl...
>|||Thanks, Roji :)
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:%23E4xr4aHFHA.3572@.TK2MSFTNGP14.phx.gbl...
> Thats more easy :)
> http://www.celko.com/
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
> news:eThiasaHFHA.1096@.tk2msftngp13.phx.gbl...
>