In MySQL, both distinct and group are working fine but in Oracle, it is not working providing below errors:
For Distinct:
ORA-01791: not a SELECTed expression
For Group By:
ORA-00979: not a GROUP BY expression
My Code is:
public function getOrgModules() {
$session_info = $this->session->userdata('logged_in');
$org = $session_info['ORG_ID'];
$org_group = $session_info['USERGRP_ID'];
$org_group_level = $session_info['USERLVL_ID'];
$user = $session_info['USER_ID'];
//$this->db->distinct('SA_ORG_MODULES.SA_MODULE_ID');
$this->db->select('SA_ORG_MODULES.SA_MODULE_NAME,ATI_MODULES.MODULE_NAME_BN,ATI_MODULES.MODULE_ICON, SA_ORG_MODULES.SA_MODULE_ID, ATI_MODULES.CATEGORY');
$this->db->from('SA_UGLW_MLINK');
$this->db->join('SA_ORG_MODULES', 'SA_UGLW_MLINK.SA_MODULE_ID = SA_ORG_MODULES.SA_MODULE_ID', 'left');
$this->db->join('ATI_MODULES', 'SA_ORG_MODULES.MODULE_IDS = ATI_MODULES.MODULE_ID', 'left');
$this->db->where('SA_UGLW_MLINK.USERGRP_ID', $org_group);
$this->db->where('SA_UGLW_MLINK.UG_LEVEL_ID', $org_group_level);
$this->db->or_where('SA_UGLW_MLINK.USER_ID', $user);
$this->db->where('SA_UGLW_MLINK.ORG_ID', $org);
$this->db->or_where('SA_UGLW_MLINK.CREATE_', "1");
$this->db->or_where('SA_UGLW_MLINK.READ', "1");
$this->db->or_where('SA_UGLW_MLINK.UPDATE_', "1");
$this->db->or_where('SA_UGLW_MLINK.DELETE_', "1");
$this->db->or_where('SA_UGLW_MLINK.STATUS', "1");
$this->db->group_by('SA_ORG_MODULES.SA_MODULE_ID');
$this->db->order_by("ATI_MODULES.SL_NO", "asc");
return $this->db->get()->result();
}
Please help me. Thanks in advance
Bob Jarvis - Слава Україні :
The problem is that you're grouping by SA_ORG_MODULES.SA_MODULE_ID, but you're fetching other fields as well (SA_ORG_MODULES.SA_MODULE_NAME,ATI_MODULES.MODULE_NAME_BN,ATI_MODULES.MODULE_ICON, ATI_MODULES.CATEGORY). In a GROUP BY query, Oracle insists that values which are retrieved are either fields which appear in the GROUP BY statement, or are the result of an aggregate function such as SUM, AVG, etc. Other databases do not enforce this requirement. So it's entirely possible that your query might work in MySQL and provoke an exception in Oracle.",
2018-07-21T23:15:08