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?
 
Guy Hengel [angelIII / a3]Connect With a Mentor 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
 
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
 
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
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.