outer join on subquery

[Kwj]

Hi everybody --

Please bear with me as I haven't been at this very long.

How can I get around this error?

  and m.dte_occurred = (select max(dte_occurred) from co_clarification aaa where aaa.sak_csr = a.sak_csr)(+)
                                                                                                         *
ERROR at line 10:
ORA-00933: SQL command not properly ended

Thanks a heap!!

[tamilselvan]

Post the entire SQL statement.

Tamil

[Kwj]

SELECT 
  a.sak_csr as EXTERNAL_ID,
  SUBSTR(m.dsc,1,50) as clar 
FROM 
  co a,
  co_clarification m 
WHERE 
  a.sak_csr = 1340
  and a.sak_csr = m.sak_csr
  and m.dte_occurred = (select max(dte_occurred) from co_clarification aaa where aaa.sak_csr = a.sak_csr)(+)  
ORDER BY external_id

Thanks!

[Kwj]

SELECT 
  a.sak_csr as EXTERNAL_ID,
  SUBSTR(m.dsc,1,50) as clar 
FROM 
  co a,
  co_clarification m 
WHERE 
  a.sak_csr = 1340
  and a.sak_csr = m.sak_csr
  and m.dte_occurred = (select max(dte_occurred) from co_clarification aaa where aaa.sak_csr = a.sak_csr)(+)  
ORDER BY external_id

Thanks!

[slimdave]

can you describe what you are trying to do?

[Kwj]

Sure!

I'm trying to select an id and a date that is associated to it, if there is one. In table m, there can be multiple dates associated with one id, so I want the latest one available.

The reason that I'm joining two tables, even though the id is a column in table m, is that this is part of a bigger query (that currently works).

I think this is an outer join, because I still want to return the id, even if there is no date for it in table m. But since table m can have more than one row with the same id and I just want the most recent one, I need my subquery. Right? Isn't that what the error msg is saying? How can you do an outer join in conjunction with a subquery?

Am I totally off?

Thanks!!

[BTW, sorry about posting the same response twice. I hit the back button on the browser and it resubmitted my reply. :( ]

[slimdave]

This would do it, i think.

Select 
   co.sak_csr as external_id,
   (select
      max(dte_occurred)
    from
      co_clarification cc
    where cc.sak_csr = co.sak_csr) as clar 
From 
  co
Where 
  co.sak_csr = 1340
Order By
  external_id
/

[Kwj]

slimdave --

You're absolutely right -- that's it. Thanks so much!!