Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    4

    Exclamation Unanswered: row column repetition help

    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.

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    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.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    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.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  4. #4
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •