select problem (is it possible?)

[Lee2]

i have four tables:

Table 1.)info_firm
------------------------
id | name | class_id |
------------------------
1 | CNN | 1000 |
2 | MTV | 1000 |
3 | Radio1 | 1001 |

FK class_id references info_class(id)

Table 2.)info_class
-------------------
id | name |
-------------------
1000 | television |
1001 | radio |



Table 3.)info_param
-------------------------------------------------------
id | parameter | class_id | type
-------------------------------------------------------
30 | position on market | 1000 | 1
31 | awards | 1000 | 3
32 | hours p. day of broadcasting | 1000 | 2
33 | frequency | 1001 | 2

FK class_id ---> info_class(id)
This table stores parameters for each group(class) of firms
Each parameter may be int or real or string.

type 1 means: value of parameter is integer
type 2 means: value of parameter is float
type 3 means: value of parameter is string


Table 4.)info_stat
------------------------------------------------------------
id | firm_id | param_id | val_int | val_real | val_string
------------------------------------------------------------
50 | 1 | 30 | 1 | NULL | NULL
51 | 1 | 31 | NULL | NULL |bestTV
52 | 1 | 32 | NULL | 23.5 |NULL
53 | 2 | 30 | 2 | NULL | NULL
54 | 2 | 31 | NULL | NULL | none
55 | 2 | 32 | NULL | 22.5 | NULL
56 | 3 | 33 | NULL | 156.6 | NULL

This table stores values for given parameters
Only one of the fields val_int, val_real and val_string is actual, according to value of info_param.type (e.g. if info_param.type is 1, we must look at val_int)


FK firm_id ----> info_firm(id)
FK param_id ----> info_param(id)

_________________________________________

I want a select giving this results:

for given info_class.name (for example television)
result of select:

---------------------------------------------------------
name | pos. on. market | awards | h.p.d broadcast.
---------------------------------------------------------
CNN | 1 | bestTV | 23.5
MTV | 2 | none | 22.5

or result of select for class.name radio
-------------------
name | frequency
-------------------
Radio1 | 156.6

Which means, for given class i want to get all firm names included in that class, and for each firm i want to get all
parameters defined for given class in table info_param with values from info_stat. Is it possible?

 

[Jerrycurl]

This works for me in SQL Server 7.0
This is a pretty procedural method; there may be a more elegant solution. The complexity could be reduced if you stored all your parameter values as strings and then cast them to ints and floats as needed by your program.

CREATE PROCEDURE sp_info @class varchar(50) AS
DECLARE @columnname varchar(50)
DECLARE @columntype int
DECLARE @columntypestr varchar(50)
DECLARE @firm varchar(50)
DECLARE @firm_id int
DECLARE @class_id int
DECLARE @param_name varchar(50)
DECLARE @param_type int
DECLARE @param_id int
DECLARE @param_value varchar(50)
DECLARE @execstring varchar(500)

select @class_id = [id] from info_class
where [name] = @class

create table #info(firm_id int primary key clustered, firm_name varchar(50))

DECLARE info_cursor CURSOR FOR

select parameter,type from info_param
where class_id = @class_id
order by info_param.id

OPEN info_cursor
FETCH NEXT FROM info_cursor INTO @columnname,@columntype
WHILE @@FETCH_STATUS = 0
BEGIN
set @columnname = REPLACE(@columnname,' ','_') --I don't like column names with spaces
IF @columntype = 1
set @columntypestr = 'int'
ELSE IF @columntype = 2
set @columntypestr = 'float'
ELSE
set @columntypestr = 'varchar(50)'

exec('ALTER TABLE #info ADD ' + @columnname + ' ' + @columntypestr)
FETCH NEXT FROM info_cursor INTO @columnname,@columntype
END
CLOSE info_cursor
DEALLOCATE info_cursor

DECLARE firm_cursor CURSOR FOR
SELECT info_firm.name, info_firm.id FROM info_firm
WHERE class_id = @class_id
OPEN firm_cursor
FETCH NEXT FROM firm_cursor INTO @firm,@firm_id
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #info(firm_id,firm_name) VALUES(@firm_id,@firm)

DECLARE param_cursor CURSOR FOR
SELECT info_param.id, info_param.parameter, info_param.type FROM info_param
WHERE class_id = @class_id
OPEN param_cursor
FETCH NEXT FROM param_cursor into @param_id,@param_name,@param_type
WHILE @@FETCH_STATUS = 0
BEGIN
SET @param_name = rtrim(REPLACE(@param_name,' ','_'))
IF @param_type = 1
BEGIN
SELECT @param_value = cast(val_int as varchar(50)) FROM info_stat
WHERE firm_id = @firm_id and param_id = @param_id

set @execstring = 'Update #info set ' + @param_name + ' = ' + cast(@param_value as varchar) + ' where firm_id = ' + cast(@firm_id as varchar)
exec( @execstring)

END
ELSE IF @param_type = 2
BEGIN
SELECT @param_value = cast(val_real as varchar(50)) FROM info_stat
WHERE firm_id = @firm_id and param_id = @param_id
set @execstring = 'Update #info set ' + @param_name + ' = ' + cast(@param_value as varchar) + ' where firm_id = ' + cast(@firm_id as varchar)
exec( @execstring)

END
ELSE
BEGIN
SELECT @param_value = val_string FROM info_stat
WHERE firm_id = @firm_id and param_id = @param_id
set @execstring = 'Update #info set ' + @param_name + ' =''' + cast(@param_value as varchar) + ''' where firm_id = ' + cast(@firm_id as varchar)
exec( @execstring)

END
FETCH NEXT FROM param_cursor into @param_id,@param_name,@param_type
END
CLOSE param_cursor
DEALLOCATE param_cursor

FETCH NEXT FROM firm_cursor into @firm,@firm_id
END
CLOSE firm_cursor
DEALLOCATE firm_cursor


select * from #info

 

[Lee2]

wow! thank you very much. is it possible to implement it in pg7? anyway thank you for your help.

 

[Jerrycurl]

What's pg7? Is that PostgreSQL? I've never used that so I don't know what it's like. There may be a few differences. If that code above doesn't work for you, I can try to help figure out how to modify it.

 

[Lee2]

i have 2 views p1v,p2v

select * from p1v;
id1 | count1
-----+--------
1 | 2
2 | 1
(2 rows)

select * from p2v;
id2 | count2
-----+--------
1 | 1
2 | 1
(2 rows)

is it possible to join views ? i have incorrect results(see bellow. 2's in the column count2 ) how does it work? is it important, how the views were created, or are they considered tables during processing of the select?
database is Postgres7

select * from p1v,p2v;
id1 | count1 | id2 | count2
-----+--------+-----+--------
1 | 2 | 1 | 2
1 | 2 | 2 | 2
2 | 1 | 1 | 1
2 | 1 | 2 | 1
(4 rows)

 

[carp]

Answered in separate posting.