SQL Query to Oracle

I am converting an application from DB2 to Oracle and I am having problems getting the SQL below to pull data out of Oracle. I have tried at least 30 different little things but still get an error “ORA-00933: SQL command not properly ended”.  
With some of the changes the error would change to missing right parenthesis which I couldn’t make much headway on since all my parenthesis were fine.  Any help would be appreciated. Thanks    The table names are in here because my original error was “Ambiguous column name” and I did not see any so I used all table names rather then aliases.  Here is the SQL  

SELECT MFTACCT.NBR_COMP, MFTACCT.NBR_FUND, MFTACCT.NBR_ACCT, MFTFUND.NME, 6299329.456 AS FUND_SHARES,  '2005-04-29 00:00:00' AS PRICE_DATE, 14.13 AS AMT_NET_ASSET, MFTACCT.SUFFIX_1_4, CASE TMFCD22_ID_RES.CD_CNTRY_ISO  WHEN 'USA' THEN 'US' ELSE TMFCD22_ID_RES.CD_IRS  END AS Country,MFTACCT.ID_DLR, MFTACCTSNAP.SHR_M_END_BAL AS QTY_CURR_BAL,  ' ' AS NME_ACCT_OVR, MFTACCT.CONNECTOR_3, MFTACCT.CONNECTOR_4, MFTACCT.ACCT_DESC_LINE, ((MFTACCTSNAP.SHR_M_END_B
AL / 6299329.456) * 100) AS PERCENT_SHARES, (MFTACCTSNAP.SHR_M_END_BAL * 14.13) AS SETTLED_CASH_VALUE,  'MONTHEND' AS RANK, 412 AS NUM_OPN_ACCTS  

FROM EP1003.MFTACCTSNAP INNER JOIN EP1003.MFTACCT ON MFTACCTSNAP.NBR_COMP = MFTACCT.NBR_COMP AND MFTACCTSNAP.NBR_FUND = MFTACCT.NBR_FUND AND MFTACCTSNAP.NBR_ACCT = MFTACCT.NBR_ACCT INNER JOIN  EP1003.MFTFUND ON MFTACCTSNAP.NBR_COMP = MFTFUND.NBR_COMP AND MFTACCTSNAP.NBR_FUND = MFTFUND.NBR_FUND OUTER JOIN EP1003.TMFCD22_ID_RES ON TMFCD22_ID_RES.ID_RES = MFTACCT.ID_RES  

WHERE MFTACCTSNAP.NBR_COMP = 204 AND MFTACCTSNAP.NBR_FUND =  00551 AND MFTACCTSNAP.DTE_MONTH = 200504 AND MFTACCT.NBR_ACCT IN (4032863040101,4032773640101,4032839840101,4032867030101,4032851460101,4032764650101,4032849210101,4032769880101,4032751240101,4033116270101)  AND ((MFTACCT.ACCT_STAT = 'O') OR  (MFTACCT.ACCT_STAT = 'C' AND MFTACCT.DTE_ACCT_STAT > {ts ' 2005-04-29 00:00:00 '})) ORDER BY MFTFUND.NBR_COMP, MFTFUND.NBR_FUND DESC

ORA-00933: SQL command not properly ended


Thank You

Lance
lva6600311Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
OUTER JOIN EP1003.TMFCD22_ID_RES


you need to specify  LEFT or RIGHT

LEFT OUTER JOIN EP1003.TMFCD22_ID_RES

or

RIGHT OUTER JOIN EP1003.TMFCD22_ID_RES
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Christoffer SwanströmPartnerCommented:
Also, try replacing the curly braces with parentheses
0
sdstuberCommented:
also,  unless this is a placeholder

{ts ' 2005-04-29 00:00:00 '}

should be...

TIMESTAMP '2005-04-29 00:00:00'

0
awking00Information Technology SpecialistCommented:
I'm not sure Oracle recognizes this notation
... AND MFTACCT.DTE_ACCT_STAT > {ts ' 2005-04-29 00:00:00 '} ...
What happens if you change that to
... AND MFTACCT.DTE_ACCT_STAT > to_date( ' 2005-04-29 00:00:00 ','yyyy-mm-dd hh:mi:ss') ...
0
lva6600311Author Commented:
THANK YOU!!  I originally had the LEFT OUTER JOIN in there but when trying to make it work I eliminated that whole part of the SQL and must of only put part of it back when trying other things.

Lance
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.