Michael Peppler
Sybase Consulting
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
Sybase on Linux FAQ
Sybperl FAQ
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

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
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

	while (@@sqlstatus = 0)
		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

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

	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
a select count(*) and put the value immediately after the "name, ..."
Or -- as in the example -- you choose a separator value to mark the end
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 IBM Global Services Company