Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2006
    Posts
    3

    Unanswered: Creating a Generic Query

    Hi ,

    I have a dynamic query , even it done using bind variables , since it
    is dynamic every time the condition keeps changing and the query
    parses , loading the db.
    How to make this as a Generic Query to aviod parsing

    Thanks
    Pree

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Show us the query. While reducing the amount of unnecessary parsing is a good thing, you can take it too far. For example, sometimes users search for employees by name, sometimes by department, and sometimes by employee number:

    select * from emp where ename = :n;
    select * from emp where deptno = :d;
    select * from emp where empno = :e;

    You could create a generic query to answer all 3 questions:

    select * from emp
    where ename = NVL(:n,ename)
    and deptno = NVL(:d,deptno)
    and empno = NVL(:e,empno);

    This means you will only parse once rather than 3 times, but the much bigger downside is that all your queries will be slower and use more resources. The cost of this will far outweigh the cost of the 2 extra parses if the queries are used a few times.

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    If its dynamic but you are using bind variables for all the criteria which the user can change then that is the best you can do. Is the parsing really the problem or is it that the query runs inefficiently?

    Alan

  4. #4
    Join Date
    Dec 2006
    Posts
    3

    Re : The Dynamic Query

    Hi All,

    This is the dynamic query.

    sqlQuery.append("SELECT CA.CA_ID_CONTR_ATTEST CA_CONTR_ATTEST FROM CDL_MA_ASSEGN_CONTR AC, CDL_MA_CONTROLLI CO, CDL_TR_CONTR_ATTEST CA ");
    sqlQuery .append("WHERE CA.CA_ID_CONTR_ASSEGN = AC.AC_ID_ASSEGN_CONTR AND AC.AC_ID_CONTROLLO = CO.CO_ID_CONTROLLO ");
    if (searchConditionHt.containsKey("CodiceControlo")) {
    sqlQuery.append(" AND CO.CO_COD_CONTROLLO LIKE ?||'%' ");
    paramValueMap.put(new Long(++paramCount),searchConditionHt.get("CodiceCo ntrolo").toString().trim().toUpperCase());
    }
    if (searchConditionHt.containsKey("Oggetto")) {
    sqlQuery.append(" AND CO.CO_OGGETTO LIKE ?||'%' ");
    paramValueMap.put(new Long(++paramCount),searchConditionHt.get("Oggetto" ).toString().trim().toUpperCase());
    }
    if (searchConditionHt.containsKey("Periodicita")) {
    sqlQuery.append(" AND CO.CO_PERIODICITA = ? ");
    paramValueMap.put(new Long(++paramCount),searchConditionHt.get("Periodic ita"));
    }
    if (searchConditionHt.containsKey("Incaricato")) {
    sqlQuery.append(" AND CO.CO_INCARICATO = ? ");
    paramValueMap.put(new Long(++paramCount),searchConditionHt.get("Incarica to"));
    }
    if (searchConditionHt.containsKey("LinkedCdrs")) {
    sqlQuery.append(" AND AC.AC_CDR IN (SELECT COLUMN_VALUE FROM TABLE (cdl_pa_internal_interface.CDL_FN_GET_IN_LIST( ? ) ) ) ");
    paramValueMap.put(new Long(++paramCount),searchConditionHt.get("LinkedCd rs"));
    }
    if (searchConditionHt.containsKey("FromDate") && searchConditionHt.containsKey("ToDate")) {
    sqlQuery .append(" AND ( AC.AC_FINE_VALID IS NULL OR (Trunc (AC.AC_FINE_VALID) >TO_DATE( ? , 'DD/MM/YYYY') ");
    sqlQuery.append(" AND Trunc(AC.AC_FINE_VALID)> TO_DATE( ?, 'DD/MM/YYYY'))) ");
    paramValueMap.put(new Long(++paramCount),searchConditionHt.get("FromDate "));
    paramValueMap.put(new Long(++paramCount),searchConditionHt.get("ToDate") );
    } else if (searchConditionHt.containsKey("FromDate")) {
    sqlQuery.append(" AND AC.AC_FINE_VALID IS NULL ");
    }
    if (searchConditionHt.containsKey("Categoria")) {
    sqlQuery.append(" AND CO.CO_CATEGORIA = ? ");
    paramValueMap.put(new Long(++paramCount),searchConditionHt.get("Categori a"));
    }
    if (searchConditionHt.containsKey("ControlliType")) {
    sqlQuery.append(" AND CO.CO_TYPE IN (SELECT COLUMN_VALUE FROM TABLE (cdl_pa_internal_interface.CDL_FN_GET_IN_LIST( ? ) ) ) ");
    paramValueMap.put(new Long(++paramCount),searchConditionHt.get("Controll iType"));

    }
    if (searchConditionHt.containsKey("FromDate")) {
    if (searchConditionHt.containsKey("DEFAULT")) {
    if (searchConditionHt.containsKey("CDLBUSINESSCALENDA R")) {
    paramCount = setDefaultHolidayCondition(searchConditionHt, sqlQuery, paramCount,paramValueMap);
    }else {
    sqlQuery.append(" AND ( CA.CA_ULTIMA_DATA_ATTEST = Trunc(SYSDATE)) ");
    }
    } else {
    if (searchConditionHt.containsKey("ToDate")) {
    sqlQuery .append(" AND CA.CA_ULTIMA_DATA_ATTEST >= TO_DATE( ? ,'DD/MM/YYYY' ) ");
    sqlQuery.append(" AND CA.CA_ULTIMA_DATA_ATTEST <= TO_DATE( ? , 'DD/MM/YYYY' ) ");
    paramValueMap.put(new Long(++paramCount),searchConditionHt.get("FromDate "));
    paramValueMap.put(new Long(++paramCount),searchConditionHt.get("ToDate") );
    } else {
    sqlQuery.append(" AND CA.CA_ULTIMA_DATA_ATTEST >= TO_DATE( ? , 'DD/MM/YYYY') ");
    paramValueMap.put(new Long(++paramCount),searchConditionHt.get("FromDate "));
    }
    }
    }
    if (searchConditionHt.containsKey("Attestai")) {
    if ("SI".equals(searchConditionHt.get("Attestai").toS tring().toUpperCase())) {
    sqlQuery .append(" AND EXISTS (SELECT CD_ID_CONTR_ATTEST FROM CDL_TR_DETT_CONTR_ATTEST CAIN where CAIN.CD_ID_CONTR_ATTEST=CA.CA_ID_CONTR_ATTEST ) ");
    }else if ("NO".equals(searchConditionHt.get("Attestai").toS tring().toUpperCase())) {sqlQuery .append(" AND NOT EXISTS (SELECT CD_ID_CONTR_ATTEST FROM CDL_TR_DETT_CONTR_ATTEST CAIN WHERE CAIN.CD_ID_CONTR_ATTEST=CA.CA_ID_CONTR_ATTEST) ");
    }
    }
    sqlQuery .append(" ORDER BY AC.AC_CDR, CA.CA_ULTIMA_DATA_ATTEST,CO.CO_COD_CONTROLLO ");
    try {
    conn = DbConnector.getInstance().getConnection();
    pstmt = conn.prepareStatement(sqlQuery.toString());

    if( paramValueMap.size() > 0 ) {
    Iterator paramIterator = paramValueMap.keySet().iterator();
    Long paramKeyIndex = null;

    for (int i = 0; i < paramCount; i ++) {
    paramKeyIndex = (Long) paramIterator.next();
    pstmt.setString(paramKeyIndex.intValue(),String.va lueOf(paramValueMap.get(paramKeyIndex)));
    }
    }

    rs = pstmt.executeQuery();

    while (rs.next()) {
    attestedControlVector.add(new Long(rs.getLong("CA_CONTR_ATTEST")));
    }

    attestedControlVector.add(attestedControlVector.si ze(), searchConditionHt);

    Thanks
    Preetha

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I haven't looked hard at the details, but that looks just fine as a concept - it is something I often do myself. You have a search screen where the user can choose from lots of possible criteria. Rather than have a single query that tries to handle everything, with an inevitable full table scan regardless of how selective the criteria given are, you build the most appropriate query for the criteria given.

    The number of different queries generated can be fairly high, but it is finite - and they really are different queries. This is not like failing to use bind variables, in which case the number of different queries is effectively infinite.

    Oracle's own client software like Oracle Forms builds queries dynamically, just like this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •