SQL - Group By Expression

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

all those subqueries in the SELECT clause should be joins in the FROM clause

There are few subqueries with more than one tables in their ‘FROM’ clause (sub queries) , how do I joined them in the ‘FROM’ clause of the main query?

here is the first of the subqueries converted to a join (the part shown in red) –

SELECT ...
     , [B][COLOR="#FF0000"]T_REF_COUNTRY.DESCRIPTION_BM[/COLOR][/B]
  FROM T_LC_LICENSE_APPL
INNER
  JOIN T_LC_LICENSE_APPL_TYPE
    ON T_LC_LICENSE_APPL_TYPE.LICENSE_APPL_OBJECTID = T_LC_LICENSE_APPL.OBJECTID
INNER
  JOIN T_LC_TYPE
    ON T_LC_TYPE.OBJECTID = T_LC_LICENSE_APPL_TYPE.TYPE_OBJECTID
[COLOR="#FF0000"][B]INNER
  JOIN T_REF_COUNTRY
    ON T_REF_COUNTRY.OBJECTID = T_LC_LICENSE_APPL.COR_COUNTRY_OBJECTID[/B][/COLOR]
LEFT OUTER
  JOIN T_LC_USER
    ON T_LC_USER.OBJECTID = T_LC_LICENSE_APPL.USER_OBJECTID
 WHERE T_LC_LICENSE_APPL.OBJECTID IN ('013EC99C032B6A1B','013EC152365726A6') 

Yes. I did that but I am stuck when come to this lines

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

which having 3 tables.