I'm having a problem with selecting columns from a query using datarows.
The original SQL statement was written as follows:
String sql = " select bvd.*, level, r.route_id, r.int_material_number,
r.plant_id, p.plant_number" +
" from bomview_detail bvd,
routing r, manufacturing_plant p" +
" where bvd.bomview_id = " +
viewId +
" and r.plant_id=p.plant_id"+
" and
r.bomview_detail_id=bvd.bomview_detail_id"+
" connect by prior
bvd.bomview_detail_id = bvd.parent_id" +
" start with bvd.parent_id is
null ";
SQLTemplate query = new SQLTemplate(BomviewDetail.class, sql, true);
query.setFetchingDataRows(true);
List result = context.performQuery(query);
Which worked correctly for a while, but as of yesterday, we started
getting ClassCastExceptions (java.math.BigDecimal) on some of the Integers
that are stored in the bvd table. I'd used the #result directive before,
so we changed the query to read:
String sql = "select * from (" +
"select " +
"#result('BOMVIEW_DETAIL_ID' 'Integer'), " +
"#result('BOMVIEW_ID' 'Integer'), " +
"#result('MATERIAL_NUMBER' 'VARCHAR'), " +
"#result('MATERIAL_DESCRIPTION' 'VARCHAR'), " +
"#result('PARENT_ID' 'Integer'), " +
"#result('AUTO_DRILL' 'Integer'), " +
"#result('WEEKLY_GOAL' 'Integer'), " +
"#result('DAILY_GOAL' 'Integer'), " +
"#result('MONTHLY_GOAL' 'Integer'), " +
" level" +
" from bomview_detail" +
" where bomview_id = " + viewId +
" connect by prior bomview_detail_id = parent_id"
+
" start with parent_id is null) bvd" +
" left join routing r on r.bomview_detail_id =
bvd.bomview_detail_id" +
" left join manufacturing_plant p on p.plant_id =
r.plant_id";
where we still want to get the same columns but also need to specify the
data types. Now, for some reason unknown to me, we only get a subset of
the columns we need. As a matter of fact, the only columns that come back
are those that are specified in the bvd table but not in the associated
join tables. I've tried removing the 'level' attribute from the query as
it is not stored in the DB but is actually a derived column from the
"connect by" clause, but had no luck retrieving the columns from any of
the other tables. Additionally, the #result directive already contains all
the columns from the bvd table.
Can you shed any light on this column issue as well as why the original
code worked for so long using the correct 'Integer' datatype? There have
been no changes to the underlying database or the Cayenne mapping files.
If it would help to see the basic DB structure, please let me know.
