SQL query question

Hi,

I have an existing table that looks like this....

• =======================
• Create TARGET table
• ======================= CREATE TABLE "TARGET"(
  "ID" NUMBER(11),
  "NOTETITLE" VARCHAR2(254),
  "DESCRIPTION" VARCHAR2(254),
  "RELNOTES" VARCHAR2(254),
  "IMPLEMENT" VARCHAR2(254),
  "COMMENTS" VARCHAR2(254),
  CONSTRAINT "ID" PRIMARY KEY(
  "ID" ))
  /
• =======================

I want to have a view that will look like this...... [Data only, as I dont know how to code this view] ID NOTE_TYPE NOTE

------------- ---------------- --------------------
1             Description      This is the description of the target
1             Release Notes    These are the release notes
1             Implementation   These are implementation notes
1             Comments         Comments made by the editor
2             Description      This is the description of the target number
two
2             Release Notes    These are the release notes for number two
2             Implementation   These are implementation notes for number two
2             Comments         Comments made by the editor fornumber two

.... and this is my interpretation of this in a create view block.....

• =======================
• Create TARGETNOTE view
• ======================= create or replace view TARGETNOTE( "ID", "NOTE_TYPE", "NOTE") as ( select ID, [literal data depending on what column is the next one we want], [fetched data depending on what column is the next one we want] from TARGET) /
• =======================

...So, for each TARGET record, there will be four notes records. Each one expecting data from different columns.

The NOTE_TYPE column will be coded to depend upon the column being used... EG, when the column is "TARGET.RELNOTES", then the NOTE_TYPE data will be "Release Notes".

Please keep inmind that I have two restrictions....

A). The structure of the TARGET table must not be changed, as it is being used by existing applications.
B). The structure of the TARGETNOTE view must not be changed, as it is being used by existing applications.

Any help on how to do this will be greately appreciated.

Thanks Received on Tue Feb 25 2003 - 19:54:32 CST