Friday 14 October 2011

How to add where condition to the sql stament....?

public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
  {
    super.processFormRequest(pageContext, webBean);
    String resourceId=pageContext.getParameter("ResourceId");
    OAApplicationModule am = pageContext.getRootApplicationModule();
     String projectId = (String)pageContext.getSessionValue("paProjectId");
    String sWhereClauseValue = "";

    String dynVoQuery = "SELECT ppp.person_id, ppp.start_date_active, ppp.end_date_active  FROM pa_project_players ppp, pa_project_role_types ppr,pa_project_parties pp WHERE ppp.project_id = :1 and pp.project_id=ppp.project_id   AND ppp.project_role_type = ppr.project_role_type AND ppr.description = 'Delivery Manager' AND pp.project_role_id =ppr.PROJECT_ROLE_TYPE AND NVL (ppp.end_date_active, NVL (pp.START_DATE_ACTIVE, SYSDATE)) BETWEEN NVL (pp.START_DATE_ACTIVE, SYSDATE) AND NVL (pp.END_DATE_ACTIVE, TO_DATE ('01/01/4712', 'DD/MM/YYYY'))";
    ViewObject dynViewObject = am.findViewObject("ExistingDeliveryMgrVO");
    if(dynViewObject == null)
    {
      dynViewObject = am.createViewObjectFromQueryStmt("ExistingDeliveryMgrVO", dynVoQuery);
//      ExistingDeliveryMgrVORowImpl row=null;
      dynViewObject.setWhereClauseParams(null);
      dynViewObject.setWhereClauseParam(0,projectId);
      dynViewObject.executeQuery();
      if(dynViewObject.getRowCountInRange() >0)
      {
        oracle.jbo.Row row1 = dynViewObject.first();
        if(row1 != null)
        {
          String existingDeliveryMgr = row1.getAttribute(0).toString();
          if(existingDeliveryMgr != resourceId)
          {
            throw new OAException("Duplicate Delivery Manager");
          }
        }
      }

No comments:

Post a Comment