Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00923 From keyword not found where expected (error when running query in Toad)
ORA-00923 From keyword not found where expected [message #273032] Mon, 08 October 2007 16:08 Go to next message
californiagirl
Messages: 79
Registered: May 2007
Member
I don't understand why I am getting the following message "ORA-00923 From keyword not found where expected"

for the following query:

SELECT    gmsac.project_id,
        gmsac.task_id,
        cdl.PROJECT_BURDENED_COST total_exp,
        DECODE(SUBSTR(et.expenditure_category,1,10),'F & A Cost',0,
        'Cost Share',0,
        NVL(gmsac.burdened_cost,0))) dir_exp,
        DECODE(SUBSTR(et.expenditure_category,1,10),'F & A Cost',NVL(cdl.PROJECT_BURDENED_COST,0),0))) ind_exp,
        DECODE(SUBSTR(et.expenditure_category,1,10),'Cost Share',NVL(cdl.PROJECT_BURDENED_COST,0),0))) cs_exp,
        NVL(gmsac.BILLED_AMOUNT,0)billed,
        NVL(gmsac.REVENUE_AMOUNT,0)rev
FROM    tams.tams_gms_status_actuals gmsac,
        apps.gl_code_combinations gcc,
        apps.PA_COST_DISTRIBUTION_LINES_ALL  cdl,
        apps.pa_expenditure_types et
WHERE    cdl.EXPENDITURE_ITEM_ID (+) = gmsac.expenditure_item_id      
and        cdl.pa_date(+) BETWEEN '1-MAY-07' AND '31-MAY-07'   --'##s_date'  AND '##e_date'
and        gcc.CODE_COMBINATION_ID  = cdl.dr_CODE_COMBINATION_ID
and        gcc.segment3 between nvl('',gcc.segment3) and nvl('',gcc.segment3) --nvl('##7',gcc.segment3) and nvl('##8',gcc.segment3)
and        et.EXPENDITURE_TYPE (+) = gmsac.expenditure_type
Re: ORA-00923 From keyword not found where expected [message #273033 is a reply to message #273032] Mon, 08 October 2007 16:19 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
on a quick glance, I'd say too many close brackets on your decode expression
Re: ORA-00923 From keyword not found where expected [message #273034 is a reply to message #273032] Mon, 08 October 2007 16:22 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Try

SELECT    gmsac.project_id,
        gmsac.task_id,
        cdl.PROJECT_BURDENED_COST total_exp,
        DECODE(SUBSTR(et.expenditure_category,1,10),'F & A Cost',0,
        'Cost Share',0,
        NVL(gmsac.burdened_cost,0)) dir_exp,
        DECODE(SUBSTR(et.expenditure_category,1,10),'F & A Cost',NVL(cdl.PROJECT_BURDENED_COST,0),0) ind_exp,
        DECODE(SUBSTR(et.expenditure_category,1,10),'Cost Share',NVL(cdl.PROJECT_BURDENED_COST,0),0) cs_exp,
        NVL(gmsac.BILLED_AMOUNT,0)billed,
        NVL(gmsac.REVENUE_AMOUNT,0)rev
FROM    tams.tams_gms_status_actuals gmsac,
        apps.gl_code_combinations gcc,
        apps.PA_COST_DISTRIBUTION_LINES_ALL  cdl,
        apps.pa_expenditure_types et
WHERE    cdl.EXPENDITURE_ITEM_ID (+) = gmsac.expenditure_item_id      
and        cdl.pa_date(+) BETWEEN '1-MAY-07' AND '31-MAY-07'   --'##s_date'  AND '##e_date'
and        gcc.CODE_COMBINATION_ID  = cdl.dr_CODE_COMBINATION_ID
and        gcc.segment3 between nvl('',gcc.segment3) and nvl('',gcc.segment3) --nvl('##7',gcc.segment3) and nvl('##8',gcc.segment3)
and        et.EXPENDITURE_TYPE (+) = gmsac.expenditure_type
Re: ORA-00923 From keyword not found where expected [message #273035 is a reply to message #273032] Mon, 08 October 2007 16:24 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
One other thing, stop comparing strings to dates. '31-MAY-07' is not a date. It is a string. Use the to_date function to convert it/them explicitly
Re: ORA-00923 From keyword not found where expected [message #273105 is a reply to message #273035] Tue, 09 October 2007 02:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Also. the Outer join to the table PA_COST_DISTRIBUTION_LINES_AL doesn't do anything. You've got this line here in the where clause:
and        gcc.CODE_COMBINATION_ID  = cdl.dr_CODE_COMBINATION_ID
, and that will remove any rows generated by the outer join.
Re: ORA-00923 From keyword not found where expected [message #273203 is a reply to message #273105] Tue, 09 October 2007 08:19 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
So should I put a right outer join here so that it will not remove any rows?

and    gcc.CODE_COMBINATION_ID  = cdl.dr_CODE_COMBINATION_ID (+)


Re: ORA-00923 From keyword not found where expected [message #273205 is a reply to message #273203] Tue, 09 October 2007 08:24 Go to previous message
californiagirl
Messages: 79
Registered: May 2007
Member
Correction that was a dumb suggestion on my part. A join has to be created on those 2 tables.

Any other suggestions?

Previous Topic: Materialized Views
Next Topic: How to check if list partition exist
Goto Forum:
  


Current Time: Sat Dec 03 16:04:37 CST 2016

Total time taken to generate the page: 0.21838 seconds