Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2013
    Location
    Germany
    Posts
    2

    Post Unanswered:

    Hello my Oracle SQL is not my favorite language, but I hope you can help me.

    The result of my select has more than one entry to the PAT ID. But i only need one entry (the last).

    I tried a lot with the max function but it dosn´t work.

    THANKS

    View Image

    Hello my Oracle SQL is not my favorite language, but I hope you can help me.

    The result of my select has more than one entry to the PAT ID. But i only need one entry (the last).

    I tried a lot with the max function but it dosn´t work.

    THANKS

    View Image

    Hello I have a big problem with oracle. I need to do an report because an college of me is in holydays. Also it is my first time to do something with oracle.

    Sorry to ask this \baby question\ to you...

    My problem is that I need for every id (pat) only one entry. I tried a lot with the max function but it don't work.

    In this forum are a lot of threads with this theme but I don't understanding it.
    Sample one

    Here is my problem
    Attached Thumbnails Attached Thumbnails sql.png  
    Last edited by healdem; 07-02-13 at 14:42. Reason: threads merged

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by Sofahocker View Post
    Hello my Oracle SQL is not my favorite language, but I hope you can help me.
    . . . E t c . . .

    Here is my problem
    The following will generate a sequential number within each "PAT.PAT" in descending order of PAT.ADMD; by selecting the first one, you get the entry corresponding to the MAX(PAT.ADMD):
    Code:
    SELECT *
      FROM (SELECT pat.pat
                 , pat.typ
                 , pat.admd
                 , sta.pat
                 , sav.docunit
                 , sav.crd
                 , sav.chd
                 , ROW_NUMBER () OVER ( PARTITION BY pat.pat ORDER BY pat.admd DESC) rn
            FROM x1100pat pat, x1102sta sta, b2000save sav
           WHERE pat.typ = 'S'
             AND pat.admd >= TO_DATE ( '30-JAN-2012', 'DD-MON-YYYY')
             AND pat.pat = sta.pat
             AND sta.wds = '213'
             AND pt.pat = sav.docunit
             AND sav.frm = 'GASDIASCORE')
     WHERE rn = 1;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jul 2013
    Location
    Germany
    Posts
    2

    Red face Ora-00918

    Hi thank you, for this learning lesson.
    Foot control loops I've never seen this in SQL. But I had some trouble with the columns.

    [Err] ORA-00918: column ambiguously defined
    After editing it works

    SELECT *
    FROM (SELECT pat.pat as PatID
    , pat.typ
    , pat.admd
    , sta.pat
    , sav.docunit
    , sav.crd
    , sav.chd
    , ROW_NUMBER () OVER ( PARTITION BY pat.pat ORDER BY pat.admd DESC) rn
    FROM x1100pat pat, x1102sta sta, b2000sav sav
    WHERE pat.typ = 'S'
    AND pat.admd >= TO_DATE ( '30-JAN-2012', 'DD-MON-YYYY')
    AND pat.pat = sta.pat
    AND sta.wds = '213'
    AND pat.pat = sav.docunit
    AND sav.frm = 'GASDIASCORE')
    WHERE rn = 1;

Posting Permissions

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