Re: problem with creating a view translating rows to columns

From: 0185 <dj_bodle_at_yahoo.co.uk>
Date: 22 Aug 2006 04:55:43 -0700
Message-ID: <1156247743.323939.73170@m73g2000cwd.googlegroups.com>

HI Bernard,

tried that and it does not work as the group by clause requires that you have an agregating function ie count, max. and all the non aggregating functions are in the group by, which if I do that gives me the same thing ie

SQL> create or replace view hts_view_result_hor_test as   2 select l.compound_id hor_compound_id,

  3      l.lot_id        hor_lot,
  4           a.assay_name    hor_assay,
  5           p.version_no    hor_assay_version,
  6           r.concentration hor_concentration,
  7           htsdecode.concunit(r.conc_unit)     hor_conc_unit,
  8           r.error_flag    hor_error_flag,
  9           r.experiment_date hor_experiment_date,
 10           htsdecode.notebook(r.notebook_id)   hor_notebook,
 11           r.notebook_page hor_notebook_page,
 12           r.operator      hor_operator,
 13     htsdecode.resultunit(r.result_unit)   hor_result_unit,
 14           r.point_no             hor_point_no,
 15          htsdecode.pointunit(r.point_unit)    hor_point_unit,
 16           r.status        hor_status,
 17           p.alt_assay_id  hor_apid,
 18           t.alias_id hor_alias_id,
 19           l.total_weight hor_total_weight,
 20      r.experiment_id hor_experiment_id,
 21      t.alternate_id hor_alternate_id,
 22      decode(r.result_type,1,r.result_value,NULL)IC50,
 23           r.experiment_id hor_experiment_id
 24      from hts_assay_result r,
 25           hts_compound_lot l,
 26           hts_assay_protocol p,
 27           hts_assay a,
 28           hts_sample t
 29     where r.sample_id = l.sample_id
 30       and r.alt_assay_id = p.alt_assay_id
 31       and p.assay_id = a.assay_id
 32       and l.sample_id=t.sample_id
 33           group by t.sample_id
 34          ;
  select l.compound_id   hor_compound_id,
         *

ERROR at line 2:
ORA-00979: not a GROUP BY expression

and if used all non aggreatgate ing columns then get view with results like

sample_id test1 test2 test3

1          1
1                  1.25
1                        0.23
2                  1.25
3          34
3                         67

Received on Tue Aug 22 2006 - 06:55:43 CDT