Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    15

    Unanswered: 'FULL JOIN' and 'MAX()' and ORA:03113 'end of file on communication channel'

    HI,
    When I run following query without MAX() function (used to get latest date)
    the query runs fine, but if I use the MAX() function I get 'ORA-03113: "end of file on comunication channel". I am using TOAD V6.0 to run query. Please
    help me understand the problem. Here are two tables and queries in question.

    Table: DIST_S
    Column: DISTID, Char(4)
    Values: AAAA
    BBBB
    CCCC

    Table: DIST_SD
    Column: DISTID, Char(4),
    STATE, Char(2)
    Row_Update_DT, Date
    Values: AAAA, NJ, 1/1/2004
    AAAA, NY, 2/1/2004
    BBBB, NC, 1/1/2004

    Query that works:
    SELECT *
    FROM DIST_S FULL JOIN
    (SELECT DIST_SD.DistID AS DistID, DIST_SD.STATE AS STATE
    FROM DIST_SD,
    (SELECT DistID, Row_Update_DT AS
    Row_Update_DT
    FROM DIST_SD GROUP BY DistID, Row_update_DT) TT
    WHERE DIST_SD.DistID = TT.DistID AND
    DIST_SD.Row_Update_DT = TT.Row_Update_DT)
    DIST_SD ON (DIST_S.DistID = DIST_SD.DistID);


    Query that causes ORA-03113:
    SELECT *
    FROM DIST_S FULL JOIN
    (SELECT DIST_SD.DistID AS DistID, DIST_SD.STATE AS STATE
    FROM DIST_SD,
    (SELECT DistID, MAX(Row_Update_DT) AS
    Row_Update_DT
    FROM DIST_SD GROUP BY DistID, Row_update_DT) TT
    WHERE DIST_SD.DistID = TT.DistID AND
    DIST_SD.Row_Update_DT = TT.Row_Update_DT)
    DIST_SD ON (DIST_S.DistID = DIST_SD.DistID);

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    don't group by Row_update_DT, just DistID

    anyways, I don't want to rewrite your whole query, but try this:
    PHP Code:
    SELECT *
    FROM DIST_S FULL JOIN
    (SELECT DIST_SD.DistID AS DistIDDIST_SD.STATE AS STATE
    FROM DIST_SD
    ,
    (
    SELECT distinct DistIDMAX(Row_Update_DTover (PARTITION BY DistID) AS Row_Update_DT AS 
    Row_Update_DT 
    FROM DIST_SD
    TT
    WHERE DIST_SD
    .DistID TT.DistID AND 
    DIST_SD.Row_Update_DT TT.Row_Update_DT
    DIST_SD ON (DIST_S.DistID DIST_SD.DistID); 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    BTW - should I bother to ask why you are selecting from the same table twice?

    try this:
    PHP Code:
    SELECT 
      
    *
    FROM 
      DIST_S
    ,
     (
    SELECT DIST_SD.DistIDDIST_SD.Row_Update_DT FROM 
      
    (SELECT *, MAX(Row_Update_DTover (PARTITION BY DistIDSTATE) AS dt
       FROM DIST_SD
    DIST_SD
      WHERE Row_Update_DT 
    dtDIST_SD
    WHERE
      DIST_S
    .DistID DIST_SD.DistID
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Apr 2004
    Posts
    15
    Thanks for your help, Duck.

    I was selecting from the same table twice because I didn't know
    better way of selecting DISTID with MAX(Row_Update_DT).
    I am not very good at writing SQL. I only know Relational Database
    theories.

    Bhargav

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    well, did it work?
    are you all set?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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