Hi
I have created a view using a procedure as below
procedure HTS_VIEW_RESULT_HOR
as
cursor decodes is
select unique result_type,type_desc,data_type from
hts_result_type where data_type !='S';
cursor charres is
select unique result_type,type_desc,data_type from
hts_result_type where data_type ='S';
sqltxt VARCHAR2(32767);
BEGIN
- build hts_view_result_hor_c view text
sqltxt := 'create or replace view hts_view_result_hor as ';
sqltxt := sqltxt || 'select l.compound_id hor_compound_id, ';
sqltxt := sqltxt || 'l.lot_id hor_lot, ';
sqltxt := sqltxt || 'a.assay_name hor_assay, ';
sqltxt := sqltxt || 'p.version_no hor_assay_version, ';
sqltxt := sqltxt || 'r.concentration hor_concentration, ';
sqltxt := sqltxt || 'htsdecode.concunit(r.conc_unit)
hor_conc_unit, ';
sqltxt := sqltxt || 'r.error_flag hor_error_flag, ';
sqltxt := sqltxt || 'r.experiment_date hor_experiment_date, ';
sqltxt := sqltxt || 'htsdecode.notebook(r.notebook_id)
hor_notebook, ';
sqltxt := sqltxt || 'r.notebook_page hor_notebook_page, ';
sqltxt := sqltxt || 'r.operator hor_operator, ';
sqltxt := sqltxt || 'r.comments hor_comments, ';
--add decode statements for result types
for aline in decodes loop
sqltxt := sqltxt ||
'decode(r.result_type,'||aline.result_type||',r.result_value,NULL)'||UPPER(translate((replace(replace(aline.type_desc,'%','Percent
'),'#','')), ' /!£$^&*()-+=[]{}@~:;?">.,<|\', '_'))||', ';
END LOOP;
--add decode statements for result types
for bline in charres loop
sqltxt := sqltxt ||
'decode(r.result_type,'||bline.result_type||',r.result_val_char,NULL)'||UPPER(translate((replace(replace(bline.type_desc,'%','Percent
'),'#','')), ' /!£$^&*()-+=[]{}@~:;?">.,<|\', '_'))||', ';
END LOOP;
- write final lines
sqltxt := sqltxt || 'r.result_id hor_result_id, ';
sqltxt := sqltxt || 'r.point_no hor_point_no, ';
sqltxt := sqltxt || 'htsdecode.pointunit(r.point_unit)
hor_point_unit, ';
sqltxt := sqltxt || 'htsdecode.resultunit(r.result_unit)
hor_result_unit, ';
sqltxt := sqltxt || 'r.status hor_status, ';
sqltxt := sqltxt || 'p.alt_assay_id hor_apid, ';
sqltxt := sqltxt || 't.alias_id hor_alias_id, ';
sqltxt := sqltxt || 'l.total_weight hor_total_weight, ';
sqltxt := sqltxt || 't.alternate_id hor_alternate_id, ';
sqltxt := sqltxt || 'r.experiment_id hor_experiment_id ';
sqltxt := sqltxt || 'from hts_assay_result r, ';
sqltxt := sqltxt || 'hts_compound_lot l, ';
sqltxt := sqltxt || 'hts_assay_protocol p, ';
sqltxt := sqltxt || 'hts_assay a, ';
sqltxt := sqltxt || 'hts_sample t ';
sqltxt := sqltxt || 'where r.sample_id = l.sample_id ';
sqltxt := sqltxt || 'and r.alt_assay_id = p.alt_assay_id ';
sqltxt := sqltxt || 'and p.assay_id = a.assay_id ';
sqltxt := sqltxt || 'and l.sample_id=t.sample_id';
dbms_output.put_line('SQL just before execute: ' ||sqltxt);
EXECUTE IMMEDIATE sqltxt;
sqltxt := 'grant select on hts_view_result_hor to hts_user';
EXECUTE IMMEDIATE sqltxt;
sqltxt := 'create or replace public synonym hts_view_result_hor
for hts_view_result_hor';
EXECUTE IMMEDIATE sqltxt;
END;
This works fine however I have one problem with this it creates my view
like below
sample_id test1 test2 test3
1 1
1 1.25
1 0.23
2 1.25
3 34
3 67
But what I really want is
sample_id test1 test2 test3
1 1 1.25 0.23
2 1.25
3 34 67
I know if I only do the decode part of the view creation script then I
would get this but Iwould not get the other information that I need.
As I am running in 9i and can not for the foreseable future upgrade to
V10 I can not use rowtocol.
any suggestions greatly appreciated.
Dot.
Received on Wed Aug 16 2006 - 10:45:46 CDT
