sybperl-l Archive

From: =?iso-8859-1?Q?J=F6rg=20Th=F6nnes?= <Joerg dot Thoennes at data-sciences dot de>
Subject: Re: avoiding the cross-product
Date: Feb 23 2000 6:57AM

> This is more of a basic sql question (I'm pretty new to sql). I need
> to do a 'dump' of a Sybase database for initializing an LDAP database.
> A person record in the Sybase database spans a number of tables, where
> some of the info is singly-occurring, and other is multiply occurring
> (e.g. the appointment table can have n rows for the same employee, and
> the 'general attribute' table can have m rows for that person.) If I
> do a massive join to get all that info, I get n*m rows for that person in
> the result set.
> 
> Now since this dump might only happen once, a certain amount of
> inefficiency can be tolerated. But not n*m rows per person. So what is
> the right way to do this? Do I just get the singly occurring stuff in
> one query, and then get each of the multiply-occurring stuff in
> separate queries each?

I would suggest this way. Loop through the employee table using a cursor
and 
select the record for the employee. Then, select the n appointment
records and after that select the m general attributes:

	declare @separator varchar(80)

	select  @separator= '-------------'

	declare @crs cursor for select from employee

	open @crs

	fetch @crs into @name, ... -- employee record

	if (@@sqlstatus = 2) begin
		... -- no data found
	end

	while (@@sqlstatus = 0)
	begin
		select @name, ... -- employee record

		select *
		from   appointments
		where  employee = @name	

		select @separator

		select *
		from	general_attributes
		where  employee = @name

		select @separator

		fetch @crs into @name, ... -- employee record
	end

	if (@@sqlstatus = 1) begin
		... -- loop terminated due to error
	end

	close @crs

	deallocate @crs

The result looks like this:

	name, ...
	app1_col_1, app1_col_2, ...
	app2_col_1, ...
	...
	appN_col_1, ...
	----------
	gattr1_col_1, gattr1_col2, ...
	gattr2_col_1, ...
	...
	gattrM_col_1, ...
	----------

Since you do not know the numbers n and m in advance, you can either
make
a select count(*) and put the value immediately after the "name, ..."
record.
Or -- as in the example -- you choose a separator value to mark the end
of 
multiple-record sections.

Perhaps there is an easier or more efficient way than cursors, but in
your case this should help.

Regards, Jörg

-- 
Joerg Thoennes | Joerg.Thoennes@Data-Sciences.DE
Data Sciences  | Sittarder Str. 31, D-52078 Aachen, Germany
+49 241 5295-0 | phone: -111 fax: -100
-------------------------------------------------------------
CSG Computer Service GmbH   ...an IBM Global Services Company