SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2011
    Location
    Kuching, Sarawak
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation 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):-

    Code:
    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    all those subqueries in the SELECT clause should be joins in the FROM clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2011
    Location
    Kuching, Sarawak
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    here is the first of the subqueries converted to a join (the part shown in red) --
    Code:
    SELECT ...
         , T_REF_COUNTRY.DESCRIPTION_BM
      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
    INNER
      JOIN T_REF_COUNTRY 
        ON T_REF_COUNTRY.OBJECTID = T_LC_LICENSE_APPL.COR_COUNTRY_OBJECTID        
    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')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Nov 2011
    Location
    Kuching, Sarawak
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    here is the first of the subqueries converted to a join (the part shown in red) --
    Code:
    SELECT ...
         , T_REF_COUNTRY.DESCRIPTION_BM
      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
    INNER
      JOIN T_REF_COUNTRY 
        ON T_REF_COUNTRY.OBJECTID = T_LC_LICENSE_APPL.COR_COUNTRY_OBJECTID        
    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.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •