--
-- USER DEFINED TYPES and USER DEFINED AGGREGATES
use pubs
go

sp_dropmessage @msgnum=500006
go

sp_addmessage 500006, 16, '%s is not a complex number'
go

-- You MUST manually drop dependent objects 
-- before droppng the UDT
drop table complex_tab
go

drop function add_complex
go

/* Uncomment this if you want to use manual deployment
drop Aggregate ComplexSum
go

drop type ComplexNumber
go

drop assembly ComplexNumber
go

create assembly ComplexNumber 
from 'c:\types\complexnumber.dll'
go


-- CREATE ComplexNumber type
--
create type ComplexNumber 
external name ComplexNumber.[ComplexNumber]
go
*/


--
-- CREATE TABLE WITH UDT COLUMNS
--
create table complex_tab (
	id int identity,
	cnumber ComplexNumber )
go
print 'Building complextab'
print 'Insert 5:5i'
insert INTO complex_tab values(convert(ComplexNumber, '5:5i'))
print 'Insert 4:0i'
insert INTO complex_tab values(convert(ComplexNumber, '4:0i'))
print 'Insert 4.005:2.3i'
insert INTO complex_tab values(convert(ComplexNumber, '4.005:2.3i'))
print 'Insert 7:0.1i'
insert INTO complex_tab values(convert(ComplexNumber, '7:0.1i'))
print 'Insert 4:.2i'
insert INTO complex_tab values(convert(ComplexNumber, '4:.2i'))
go
print 'Insert A:5i is not the proper format for a complex number'
print 'this should produce an error, notice ''bad format'' error string in message'
print 'Insert A:5i'
insert INTO complex_tab values(convert(ComplexNumber, 'A:5i'))
go
print 'complex_tab content'
select 'complex_tab content', * from complex_tab
go

print 'doing compare test'
print 'find row with value = ''5:5i'''
declare @c ComplexNumber
set @c = Convert(ComplexNumber, '5:5i')
declare @b int
if (@c = @c) print 'equal'
print Convert(nvarchar, @c)
set @c = Convert(ComplexNumber, '5:5i')
print 'Compare ='
select 'Compare =', *, cnumber.realpart, cnumber.imagpart from complex_tab where cnumber = @c
print 'Compare ='
select 'Compare =', *, cnumber.realpart, cnumber.imagpart from complex_tab where cnumber = Convert(ComplexNumber, '5:5i')
print 'Compare >'
select 'Compare >', *, cnumber.realpart, cnumber.imagpart from complex_tab where cnumber > @c
print 'Compare >='
select 'Compare >=', *, cnumber.realpart, cnumber.imagpart from complex_tab where cnumber >= @c
print 'Compare <'
select 'Compare <', *, cnumber.realpart, cnumber.imagpart from complex_tab where cnumber < @c
print 'Compare <='
select 'Compare <=', *, cnumber.realpart, cnumber.imagpart from complex_tab where cnumber <= @c
go


print 'select cnumber.realpart, cnumber.imagpart from complex_tab'
select 'select real/image', cnumber.realpart, cnumber.imagpart from complex_tab
go
print 'select using convert to produce string'
select 'select as string', convert(nvarchar, cnumber) from complex_tab
go
print 'select for xml auto'
select * from complex_tab for xml auto
go

print ' create complex add function'
go
create function add_complex(@x ComplexNumber, @y ComplexNumber)
returns ComplexNumber
as
begin
declare @z ComplexNumber
set @z.realpart = @x.realpart + @y.realpart
set @z.imagpart = @x.imagpart + @y.imagpart
return @z
end
go


print 'try add_complex function'
declare @x as ComplexNumber
declare @y as ComplexNumber
declare @z as ComplexNumber

set @x = convert(ComplexNumber, '5:5i')
set @y = convert(ComplexNumber, '10:7i')

print '@x = '  + Convert(nvarchar, @x)
print '@y = '  + Convert(nvarchar, @y)


set @z = dbo.add_complex(@x, @y)
print '@z = '  + Convert(nvarchar, @z)

print @z.realpart
print @z.imagpart
go

/* uncomment this if you want to use manual deployment
create aggregate ComplexSum (@in_complex ComplexNumber)
returns ComplexNumber
external name ComplexNumber.[ComplexSum]
go
*/

declare @cn ComplexNumber
set @cn = Convert(ComplexNumber, 'Null')
print Convert(nvarchar, @cn)
go

print 'use aggregate'
select 'aggregate all', convert(varchar, dbo.ComplexSum(cnumber)) from complex_tab

print 'use aggregate > 4.01:4i'
select 'aggregate > 4.01:4i', convert(varchar, dbo.ComplexSum(cnumber)) from complex_tab where cnumber > Convert(ComplexNumber, '4.01:4i')
go

select 'Check Mutators', cnumber.realpart, cnumber.imagpart from complex_tab
go

update complex_tab set cnumber.SetValue(1,2)  where cnumber = Convert(ComplexNumber, '5:5i')
go
select 'Check SetValue', cnumber.realpart, cnumber.imagpart from complex_tab
go

update complex_tab set cnumber.SetNull() where cnumber = Convert(ComplexNumber, '1:2i')
go
select 'Check SetNull', cnumber.realpart, cnumber.imagpart from complex_tab
go
