Cannot resolve Oracle error in java / spring application

I'm trying to add a new screen to an existing application, and I think everything is set up correctly, but I get an error.

I recently successfully added a very similar screen to another (similar) application, so I thought I'd be able to replicate it. but something is out of whack . . .

Here's the error:
PreparedStatementCallback; bad SQL grammar [select ls.subj_area_cd as subj, [ blah blah blah ], nested exception is java.sql.SQLException: ORA-00904: : invalid identifier

But if I copy the sql as is and run in Oracle, it returns data as expected. I even removed the order by clause but got the same result.

Not sure how to debug this from here . . .

Note that I hard-code a parameter (04202010) so as to eliminate that as the source of the error
========= Full Sql plus error message ============
PreparedStatementCallback; bad SQL grammar [
select ls.subj_area_cd as subj, ls.srce_sys_nm||' - '||ls.srce_sys_cd as source, ls.freq
,    case ops$imedw.ods_daily_schedule (to_char(to_date('04202010', 'mmddyyyy'),'DY'), ls.srce_sys_cd, ls.subj_area_cd) when 'Y' then 'Yes' else 'No' end as expected
,     ps.proc_nm, ps.proc_end_dt as load_dt, ps.rptd_dt, ps.proc_ret_desc,         ps.rpt_mth
, case ls.subj_area_cd             when 'CLM' then s10.actl_mtrc_msr           when 'PCLM' then s10.actl_mtrc_msr 		   when 'MEM' then cf.cntr_62              when 'CLI' then cf.cntr_2 +  cf.cntr_6 + cf.cntr_10 + cf.cntr_14 + cf.cntr_18 + cf.cntr_22 +  cf.cntr_26 + cf.cntr_30 + cf.cntr_34 + cf.cntr_38           else 0 end as number_loaded
,         ps.err_cnt as error_count, ls.sort_order_txt as sort_key, ps.mtrcs_errs_linkg_id as linkg_id        
from ods.subj_area_srce_load_stus ls         
left join ods.proc_stus ps 
  on ps.srce_sys_cd = ls.srce_sys_cd and ps.proc_nm in ('ODS_LOAD','ODS_LOAD_FINAL') and trunc(ps.proc_end_dt) = to_date('04202010','mmddyyyy')        
left join ods.sstedw10_run_mtrc s10 
  on s10.mtrcs_errs_linkg_id = ps.mtrcs_errs_linkg_id and s10.mtrc_msr_cd = 148        
left join stage3.cf_err_metrics cf 
 on cf.mtrcs_errs_linkg_id = ps.mtrcs_errs_linkg_id        where ls.subj_area_cd <> 'IDM' and ls.stus_nm = 'active' and ls.subj_area_cd <> '3RX'        
order by case ops$imedw.ods_daily_schedule (to_char(to_date('04202010', 'mmddyyyy'),'DY')
, ls.srce_sys_cd, ls.subj_area_cd) when 'Y' then 'Yes' else 'No' end desc
, ls.sort_order_txt  
]; 
nested exception is java.sql.SQLException: ORA-00904: : invalid identifier

Open in new window

LVL 1
Alaska CowboyAsked:
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.

Alaska CowboyAuthor Commented:
Here's my controller which all matches up, query to RowMapper
package com.carefirst.edw.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Date;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;

import com.carefirst.edw.domain.ProcessStatus;

public class ProcessStatusDAOImpl extends NamedParameterJdbcDaoSupport implements ProcessStatusDAO {
	private static final String LOAD_DETAILS_SQL = 
	    "select ls.subj_area_cd as subj, ls.srce_sys_nm||' - '||ls.srce_sys_cd as source, ls.freq,  " +
	    "  case ops$imedw.ods_daily_schedule (to_char(to_date('04202010', 'mmddyyyy'),'DY'), ls.srce_sys_cd, ls.subj_area_cd) when 'Y' then 'Yes' else 'No' end as expected,   " +
	    "  ps.proc_nm, ps.proc_end_dt as load_dt, ps.rptd_dt, ps.proc_ret_desc,   " +
	    "      ps.rpt_mth, case ls.subj_area_cd   " +
	    "          when 'CLM' then s10.actl_mtrc_msr " +
	    "          when 'PCLM' then s10.actl_mtrc_msr " +
	    "		   when 'MEM' then cf.cntr_62    " +
	    "          when 'CLI' then cf.cntr_2 +  cf.cntr_6 + cf.cntr_10 + cf.cntr_14 + cf.cntr_18 + cf.cntr_22 +  cf.cntr_26 + cf.cntr_30 + cf.cntr_34 + cf.cntr_38 " +
	    "          else 0 end as number_loaded,   " + 
	    "      ps.err_cnt as error_count, ls.sort_order_txt as sort_key, ps.mtrcs_errs_linkg_id as linkg_id   " +
	    "     from ods.subj_area_srce_load_stus ls   " +
	    "      left join ods.proc_stus ps on ps.srce_sys_cd = ls.srce_sys_cd and ps.proc_nm in ('ODS_LOAD','ODS_LOAD_FINAL') and trunc(ps.proc_end_dt) = to_date('04202010','mmddyyyy')  " +
	    "      left join ods.sstedw10_run_mtrc s10 on s10.mtrcs_errs_linkg_id = ps.mtrcs_errs_linkg_id and s10.mtrc_msr_cd = 148   " +
	    "      left join stage3.cf_err_metrics cf on cf.mtrcs_errs_linkg_id = ps.mtrcs_errs_linkg_id   " +
	    "     where ls.subj_area_cd <> 'IDM' and ls.stus_nm = 'active' and ls.subj_area_cd <> '3RX'   " +
	    "     order by case ops$imedw.ods_daily_schedule (to_char(to_date('04202010', 'mmddyyyy'),'DY'), ls.srce_sys_cd, ls.subj_area_cd) when 'Y' then 'Yes' else 'No' end desc," +
	    "	  		   		ls.sort_order_txt  " ; 
	
    public List getLoadDetails(Date date) {
		MapSqlParameterSource map = new MapSqlParameterSource();
		map.addValue("date", date);
		
		List history = null;
		try {
			history = getNamedParameterJdbcTemplate().query(LOAD_DETAILS_SQL, map, new DailyDetailsRowMapper()); 
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
		return history;
	}

    private class DailyDetailsRowMapper implements RowMapper {
        public Object mapRow(ResultSet rs, int arg1) throws SQLException {
        	ProcessStatus detail = new ProcessStatus();
        	detail.setSubjArea(rs.getString("subj"));
        	detail.setSrceName(rs.getString("source"));
           	detail.setRunFreq(rs.getString("freq"));            
           	detail.setIsLoadExpected(rs.getString("expected"));
           	detail.setProcessName(rs.getString("proc_nm"));
        	detail.setLoadDateTime(rs.getTimestamp("load_dt"));
           	detail.setReportedDate(rs.getDate("rptd_dt"));
           	detail.setLoadStatus(rs.getString("proc_ret_desc"));
           	detail.setLoadMonth(rs.getDate("rpt_mth"));
           	detail.setNumberLoaded(rs.getInt("number_loaded"));
           	detail.setErrorCount(rs.getInt("error_count"));
           	detail.setSortOrder(rs.getString("sort_key"));
           	detail.setLinkingID(rs.getInt("linkg_id"));
        	return detail;
        }
    }
}

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I see nothing wrong direclty with the code ...
but the error states:
ORA-00904: : invalid identifier

so, you must have somewhere specified a wrong table or column name, or the login you use to connect does not have permissions to the table/view used.
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
Alaska CowboyAuthor Commented:
Ok, that makes sense, probably the called procedure is not available to my user, hopefully that's it, checking now.
thanks.
0
Alaska CowboyAuthor Commented:
Well, I should have been able to figure this out, but you sparked my trouble-shooting effort, and voila, it was the called procedure, not accessible for this web user. I'll get that fixed and let you know.
thanks
0
Alaska CowboyAuthor Commented:
yes, that was it, but now I have other issues, I'll open up a new post, I did resolve this initial issue by getting privileges to run a stored proc from the web id
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
Oracle Database

From novice to tech pro — start learning today.