best way to dynamically filter SQL statement?
Hi: this is in essence a two-pronged question.
I have a set of 6 checkboxes on a form that I'm trying to query a results set for.
Currently, the 6 checkboxes have different names, but the DB values are either Y or N.
1) I'm wondering, is it better to name them all the same, and assign different values, and make it an array on the ensuing JSP page that returns the result set, or am I better off leaving them w/ different names since the DB values are all either Y or N?
2) what's the best way to filter these in returning the result set? I also am returning about 15 other values, in addition to querying for these 6.
It seems like a very, hairy dynamic SQL statement in the works for verifying if they check boxes 1 & 2, or 1,2, 4, 6, or 3, 4 & 6, etc.
Having trouble with the SQL statement and best approach. Any feedback is welcomed...here is the code.
Statement selstmt = connection.createStatement();
String preparedQuery ="SELECT DISTINCT AID, ACTIVE, REQUESTOR_NAME, REQUESTOR_EMAIL,BUS_CONTACT " +
",DEPT,LOCATION,DATE_REQ,BUSVP,VP_PHONE,VP_DATE,BRANCH,PRIORITY" +
",STATUS1,STATUS2 " +
",EXIST_SYS_FLD_CHG, BUS_RULES_CHG, NEW_CODING, NEW_BUS_RULE,NEW_SYS_FIELD,REQ_TYPE_OTHER " +
" FROM CHANGE_CONTROL_ADMIN a INNER JOIN CHANGE_CONTROL_USER b " +
"ON a.CHANGE_CTRL_ID = b.CHANGE_CTRL_ID " +
" WHERE EXIST_SYS_FLD_CHG = CASE WHEN = 'Y' THEN EXIST_SYS_FLD_CHG ELSE END BUS_RULES_CHG = 'Y' ORDER BY AID";
then
try{
while (rslts.next()){
int aidn = rslts.getInt(1); ....
...
String status2 = rslts.getString(15);
String exis_sys_fld = rslts.getString(16);
String bus_rul_chg = rslts.getString(17);
String reqnewc = rslts.getString(18);
String reqnewbr = rslts.getString(19);
String reqnewsfd = rslts.getString(20);
String reqotr = rslts.getString(21);
Of course I wouldn't be making all the fields like BUS_RULES_CHG = 'Y' but rather dynamically write these to the SQL statement (hopefully!) as the request params were checked off.
Also, this would effect the results set String params created. So that in some cases, if check boxes were left blank for example,
String reqotr = rslts.getString(21) might be String reqotr = rslts.getString(18) another time would it not?
Again, thanks.
***********************************************
I'd thought it might look like this,
" FROM CHANGE_CONTROL_ADMIN a INNER JOIN CHANGE_CONTROL_USER b " +
"ON a.CHANGE_CTRL_ID = b.CHANGE_CTRL_ID WHERE ";
<%if (existfld.equals("Y")){ %>
+"EXIST_SYS_FLD_CHG = 'Y'";
<%} %>
<%if (reqbizrule.equals("Y")){ %>
+"BUS_RULES_CHG = 'Y' ";
<%} %>
<%if (reqnewcode.equals("Y")){ %>
+"NEW_CODING = 'Y' ";
<%} %>
+" ORDER BY AID";
where the existfld, reqnewcode & reqbizrule are set as request.getParameter values of the checkboxes.
I get illegal expression errors trying it this way.
Message was edited by:
bpropes20
