public int GetEstDeptRefIDByTop1(int estterm_ref_id, int emp_ref_id) { string query = @" SELECT A.EST_DEPT_REF_ID FROM EST_DEPT_INFO A INNER JOIN BSC_EMP_COM_DEPT_DETAIL B ON A.ESTTERM_REF_ID = @ESTTERM_REF_ID AND A.DEPT_REF_ID = B.DEPT_REF_ID AND B.EMP_REF_ID = @EMP_REF_ID ORDER BY A.DEPT_TYPE"; IDbDataParameter[] paramArray = CreateDataParameters(2); paramArray[0] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int); paramArray[0].Value = estterm_ref_id; paramArray[1] = CreateDataParameter("@EMP_REF_ID", SqlDbType.Int); paramArray[1].Value = emp_ref_id; DataSet ds = DbAgentCache.Fill(query, paramArray); //DataSet ds = DbAgent.Fill(query, paramArray); if (ds.Tables[0].Rows.Count > 0) { return(Convert.ToInt32(ds.Tables[0].Rows[0]["EST_DEPT_REF_ID"])); } return(0); }
public int GetEstDeptRefIDByTop1(int estterm_ref_id) { string query = @"SELECT A.EST_DEPT_REF_ID FROM ( SELECT ED.EST_DEPT_REF_ID ,ED.UP_EST_DEPT_ID ,LEVEL as DEPT_LEVEL FROM EST_DEPT_INFO ED WHERE ESTTERM_REF_ID = @ESTTERM_REF_ID AND TEMP_FLAG = 1 START WITH EST_DEPT_REF_ID = (SELECT EST_DEPT_REF_ID FROM EST_DEPT_INFO WHERE NVL(UP_EST_DEPT_ID,0) = 0 AND ROWNUM = 1) CONNECT BY PRIOR EST_DEPT_REF_ID = UP_EST_DEPT_ID )A JOIN (SELECT DISTINCT EST_DEPT_REF_ID FROM BSC_EST_DEPT_ORG_DETAIL WHERE ESTTERM_REF_ID = @ESTTERM_REF_ID) B ON A.EST_DEPT_REF_ID = B.EST_DEPT_REF_ID ORDER BY DEPT_LEVEL" ; query = @" SELECT DISTINCT EST_DEPT_REF_ID FROM BSC_EST_DEPT_ORG_DETAIL WHERE ESTTERM_REF_ID = @ESTTERM_REF_ID "; IDbDataParameter[] paramArray = CreateDataParameters(1); paramArray[0] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int); paramArray[0].Value = estterm_ref_id; DataSet ds = DbAgentCache.Fill(query, paramArray); //DataSet ds = DbAgent.Fill(query, paramArray); if (ds.Tables[0].Rows.Count > 0) { return(Convert.ToInt32(ds.Tables[0].Rows[0]["EST_DEPT_REF_ID"])); } return(0); }