Hi there,
I have a SQL query which I need to add in group by clause, but I have subqueries which I do not want
to group. But technically, I must include all the columns in the select to the group by clause. So, I wish to know what
is the simplest method to do it without group in the subqueries.
Here’s my code (without group by):-
SELECT
T_LC_LICENSE_APPL.APPLICATION_NO AS APPLICATION_NO,
T_LC_LICENSE_APPL.REMARKS AS REMARKS,
T_LC_LICENSE_APPL.USER_OBJECTID AS USER_OBJECTID,
T_LC_LICENSE_APPL_TYPE.OBJECTID AS APPL_TYPE_OBJID,
T_LC_TYPE.LICENSE_TYPE_BM AS LIC_TYPE,
T_LC_LICENSE_APPL.CREATED_DATE AS APPLICATION_DATE,
CASE WHEN T_LC_LICENSE_APPL.NAME = '' OR T_LC_LICENSE_APPL.NAME IS NULL THEN T_LC_USER.NAME ELSE T_LC_LICENSE_APPL.NAME END AS APPLICANT_NAME,
NVL(T_LC_LICENSE_APPL.COR_ADDRESS1,'') AS GUEST_ADDR1,
NVL(T_LC_LICENSE_APPL.COR_ADDRESS2,'') AS GUEST_ADDR2,
NVL(T_LC_LICENSE_APPL.COR_ADDRESS3,'') AS GUEST_ADDR3,
T_LC_LICENSE_APPL.COR_POSTCODE AS GUEST_POSTCODE,
NVL(T_LC_USER.COR_ADDRESS2,'') AS REG_USER_ADDR2,
NVL(T_LC_USER.COR_ADDRESS3,'') AS REG_USER_ADDR3,
T_LC_USER.COR_POSTCODE AS REG_USER_POSTCODE,
(SELECT DESCRIPTION_BM FROM T_REF_COUNTRY WHERE OBJECTID = T_LC_LICENSE_APPL.COR_COUNTRY_OBJECTID) AS GUEST_COUNTRY,
(SELECT DESCRIPTION_BM FROM T_REF_CITY WHERE OBJECTID = T_LC_LICENSE_APPL.COR_CITY_OBJECTID) AS GUEST_CITY,
(SELECT DESCRIPTION_BM FROM T_REF_STATE WHERE OBJECTID = T_LC_LICENSE_APPL.COR_STATE_OBJECTID) AS GUEST_STATE, NVL(T_LC_USER.COR_ADDRESS1,'') AS REG_USER_ADDR1,
(SELECT DESCRIPTION_BM FROM T_REF_COUNTRY WHERE OBJECTID = T_LC_USER.COR_COUNTRY_OBJECTID) AS REG_USER_COUNTRY,
(SELECT DESCRIPTION_BM FROM T_REF_CITY WHERE OBJECTID = T_LC_USER.COR_CITY_OBJECTID) AS REG_USER_CITY,
(SELECT DESCRIPTION_BM FROM T_REF_STATE WHERE OBJECTID = T_LC_USER.COR_STATE_OBJECTID) AS REG_USER_STATE,
NVL((SELECT DISTINCT(T_USER_DEPARTMENT.CODE) FROM T_USER_DEPARTMENT, T_LC_COMMENTER, T_LC_CATEGORY WHERE T_LC_COMMENTER.USER_DEPARTMENT_OBJECTID = T_USER_DEPARTMENT.OBJECTID AND T_USER_DEPARTMENT.CODE IN('JTK', 'PB')),'') AS CATE_DEPART,
NVL((SELECT T_LC_ENDORSE.CODE FROM T_LC_ENDORSE, T_LC_LICENSE_APPL_COMMENT, T_USER_DEPARTMENT WHERE T_LC_LICENSE_APPL_COMMENT.LICENSE_APPL_TYPE_OBJECTID = T_LC_LICENSE_APPL_TYPE.OBJECTID AND T_LC_ENDORSE.OBJECTID = T_LC_LICENSE_APPL_COMMENT.STATUS AND T_LC_LICENSE_APPL_COMMENT.DEPT_ID = T_USER_DEPARTMENT.OBJECTID AND T_USER_DEPARTMENT.CODE IN('JTK', 'PB')), 'TB') AS COMM_STAT,
(SELECT T_LC_LICENSE_APPL_COMMENT.REMARK FROM T_LC_LICENSE_APPL_COMMENT, T_USER_DEPARTMENT WHERE T_LC_LICENSE_APPL_COMMENT.LICENSE_APPL_TYPE_OBJECTID = T_LC_LICENSE_APPL_TYPE.OBJECTID AND T_LC_LICENSE_APPL_COMMENT.DEPT_ID = T_USER_DEPARTMENT.OBJECTID AND T_USER_DEPARTMENT.CODE IN('JTK','PB')) AS COMM_REMARK
FROM
T_LC_LICENSE_APPL LEFT JOIN T_LC_USER ON (T_LC_LICENSE_APPL.USER_OBJECTID = T_LC_USER.OBJECTID), T_LC_LICENSE_APPL_TYPE, T_LC_TYPE
WHERE
T_LC_LICENSE_APPL.OBJECTID = T_LC_LICENSE_APPL_TYPE.LICENSE_APPL_OBJECTID AND T_LC_TYPE.OBJECTID = T_LC_LICENSE_APPL_TYPE.TYPE_OBJECTID AND T_LC_LICENSE_APPL.OBJECTID IN ('013EC99C032B6A1B','013EC152365726A6')
ORDER BY
T_LC_LICENSE_APPL.APPLICATION_NO, T_LC_TYPE.CODE
Please advise.
Thanks