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?
 
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
 
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
 
awking00Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.