Invalid column name error

[Lalitha Vydyula]

Hi all,

trying select max(approval_level).. gives java.sql.SQLException: Invalid column name


I have a query like this
select max(approval_level) as maxlevel from fam_quote_header_approval , fam_quote_line_approval where fam_quote_header_approval.header_approval_id = fam_quote_line_approval.header_approval_id and quote_number = 5443 and status = 'Approved'

when I execute the query from SQL prompt its executing fine. but when I execute it from the jdbc it erroring out

try
{
approvedLvlConn = (OracleConnection)getConnection();
approvedOpstmt = (OraclePreparedStatement)
approvedLvlConn.prepareStatement(approvedLevelStr.toString());
approvedSet = approvedOpstmt.executeQuery();
if(approvedSet.next())
maxLevel = approvedSet.getInt(approvedSet.getInt("maxlevel"));
}

Can anyone help. Thanks.

[Scott Selikoff]

Just a thought, but have you tried using table aliases throughout your query such as:

select max(approval.approval_level) as maxlevel
from fam_quote_header_approval header, fam_quote_line_approval approval
where
   header.header_approval_id = approval.header_approval_id 
   and 
   approval.quote_number = 5443
   and approval.status = 'Approved'

I just guessed which aliases some of the columns belong with. Often times its because of a mismatch or a confusion, using table aliases avoids it.

[Dimit Chadha]

HI
It will be better practice to use column numbers instead of column names. This is a tricky solution but it will reduce the error of misspelled column names.

int column=1;
while (rs.next){
int aa= rs.getInt(col++);
String bb=rs.getString(col++);
col+3;
String cc= rs.getString(col++);
}