I've made a view from a (complex) select statement of 4 tables in my database.

lest say I get results like these...
colname1 colname2 colname3 colname4
---------------------------------------------------
patient1 med1 usage1 diagnum
patient1 med2 usage2 diagnum
patient1 med3 usage3 diagnum

I would like to cortrect my query so that the result is the following...
colname1 colname2 colname3 colname4
---------------------------------------------------
patient1 med1 usage1 diagnum
med2 usage2
med3 usage3

since the patient number is the same and the diag num is the same
i want ot be able to avoid the repetition...

this might not help but heres is my original query

CREATE OR REPLACE VIEW PRESCRIPTIONS AS
SELECT DISTINCT d.NoAssMaladie, p.prenompatient || ' ' || UPPER(p.nompatient) AS NomPatient, l.nomedicament AS NoMedic,m.libmedicament AS Libelle, l.quantite || ',' || l.prises || ',' || l.duree AS "Desc. d.usage", l.nodiagnostic AS Diag
FROM lignes_prescriptions l, patients p, diagnostics d, medicaments m
WHERE l.nodiagnostic = d.nodiagnostic AND
d.noassmaladie = p.noassmaladie AND
m.nomedicament = l.nomedicament AND
d.RESULTATDIAGNOSTIC = 'P' AND
d.NoAssMaladie = 'SANL 6005 1218'

where my patient is 'SANL 6005 1218'


And secondly I would like to know how to make a view that will ask for a value that I can apply to my where statement.

lets say I wanted to ask for the patient number 'SANL 6005 1218' instead of putting it into my query directly.

Technically speaking the view you created contains no duplicates, where a duplicate is defined as the row projected from the select statement. If you select the primary key then the rows are already distinct. I don't think you can return 4 columns with different row depths as the dbms would not know what to place in the 'Empty' cells.

You should check your schema and ensure the FD's are correct in your view for example the view you created leeds one to believe that possibly column1, column2, column3 are the key with column4 being dependent on this key.

Hi,
First you cannot give parameters to a VIEW as they are just structures stored with no data.

When you query the view you should include the parameter in the where clause.

As for your requirement, you should use SQL REPORTING utility to get the report in that format.

use the following commands

BREAK ON PATIENT_ID ON DIAGRAMID

SELECT PATIENT_ID, DIAGRAMID ,.....
FROM <<VIEW NAME>> ORDER BY PATIENT_ID, DIAGRAMID

If needed spool the result into a text file.
then use CLEAR BREAKS command to clear the break settings.

Regars
Shelva