Having clause not working

Question

i make this query in oracle 11g database

     SELECT   DISTINCT JOC_FIN_CLTH_DFCT_LOT.LOT_NO,
                    I.ISSUE_DATE,
                    R.PROC_DESC,
                    R.RECV_DATE,
                    M.DFCT_DATE,
                    JOC_FIN_CLTH_DFCT_LOT.FCD_MAIN_ID
    FROM   JOC_FIN_CLTH_DFCT_LOT,
           JOC_FIN_CLTH_DFCT_MAIN M,
           JOC_DAILY_FABRC_RECV_FOLD R,
           JOC_LOT_ISSUE_REG I
   WHERE       M.FCD_MAIN_ID = JOC_FIN_CLTH_DFCT_LOT.FCD_MAIN_ID
           AND R.LOT_NO = JOC_FIN_CLTH_DFCT_LOT.LOT_NO
           AND I.LOT_NO = R.LOT_NO
           AND I.LOT_YEAR = R.LOT_YEAR
           AND JOC_FIN_CLTH_DFCT_LOT.LOT_YEAR = R.LOT_YEAR
           AND JOC_FIN_CLTH_DFCT_LOT.LOT_YEAR = '1213'
           AND JOC_FIN_CLTH_DFCT_LOT.FCDL_ID IN
                    (    SELECT   MIN (DFCT_LOT.FCDL_ID)
    FROM   JOC_FIN_CLTH_DFCT_LOT DFCT_LOT, JOC_FIN_CLTH_DFCT_MAIN DFT_MAIN
   WHERE   DFCT_LOT.FCD_MAIN_ID IN (DFT_MAIN.FCD_MAIN_ID)
GROUP BY   DFCT_LOT.FCD_MAIN_ID)
ORDER BY   JOC_FIN_CLTH_DFCT_LOT.FCD_MAIN_ID

it retrieve data within 2 sec no. of rows=5100 but when i use this query in my front end application it takes too much times so after troubleshooting i find subquery cause problem when data retrieve so i simplify this query

  SELECT   DISTINCT DFCT_LOT.LOT_NO,
                    I.ISSUE_DATE,
                    R.PROC_DESC,
                    R.RECV_DATE,
                    M.DFCT_DATE,
                    DFCT_LOT.FCD_MAIN_ID
    FROM   JOC_FIN_CLTH_DFCT_LOT DFCT_LOT,
           JOC_FIN_CLTH_DFCT_MAIN M,
           JOC_DAILY_FABRC_RECV_FOLD R,
           JOC_LOT_ISSUE_REG I,
           JOC_FIN_CLTH_DFCT_MAIN DFT_MAIN
   WHERE       M.FCD_MAIN_ID = DFCT_LOT.FCD_MAIN_ID
           AND R.LOT_NO = DFCT_LOT.LOT_NO
           AND I.LOT_NO = R.LOT_NO
           AND I.LOT_YEAR = R.LOT_YEAR
           AND DFCT_LOT.LOT_YEAR = R.LOT_YEAR
           AND DFCT_LOT.LOT_YEAR = '1213'
           AND DFCT_LOT.FCD_MAIN_ID IN (DFT_MAIN.FCD_MAIN_ID)

GROUP BY   DFCT_LOT.FCDL_ID,
           DFCT_LOT.FCD_MAIN_ID,
           DFCT_LOT.LOT_NO,
           I.ISSUE_DATE,
           R.PROC_DESC,
           R.RECV_DATE,
           M.DFCT_DATE,
           DFCT_LOT.FCD_MAIN_ID
 HAVING   DFCT_LOT.FCDL_ID in MIN (DFCT_LOT.FCDL_ID)
ORDER BY   DFCT_LOT.FCD_MAIN_ID

this is simplified form of above query but number of rows increase no.of rows=5578 but i know the actual no. of rows=5100 having clause not working here kindly look into my query and guide me

Answer 1

In your second query you are joining the table JOC_FIN_CLTH_DFCT_MAIN a second time, once as M and once as DFCT_LOT. In the second query's SELECT list you take the first column from M and the last one from DFCT_LOT.

But in the first query they are both from the same M table. When you have more than one record in JOC_FIN_CLTH_DFCT_MAIN for the same FCD_MAIN_ID, then this will result in more combinations in the second query, and this explains why you have more results with it.

But there are several other differences. In the second query you group by many more columns than in the first. Moreover, MIN (DFCT_LOT.FCDL_ID) does not really make sense in the second query, as it is already grouped by, so it is exactly the same as just DFCT_LOT.FCDL_ID. As a consequence, the HAVING clause is just a plain tautology, and you could just as well leave it out and still get the same results.

If you are sure the first query gives the results you want, then I would suggest a different way to achieve a possible optimisation of it:

SELECT      DISTINCT 
            L.LOT_NO,
            I.ISSUE_DATE,
            R.PROC_DESC,
            R.RECV_DATE,
            L.DFCT_DATE,
            L.FCD_MAIN_ID,
FROM        (SELECT     L.FCD_MAIN_ID,
                        L.LOT_NO,
                        L.LOT_YEAR,
                        M.DFCT_DATE,
                        ROW_NUMBER() OVER (PARTITION BY L.FCD_MAIN_ID 
                                           ORDER BY L.FCDL_ID) AS RN
            FROM        JOC_FIN_CLTH_DFCT_LOT   L, 
            INNER JOIN  JOC_FIN_CLTH_DFCT_MAIN  M
                    ON  M.FCD_MAIN_ID = L.FCD_MAIN_ID
            ) L
INNER JOIN  JOC_DAILY_FABRC_RECV_FOLD R
        ON  R.LOT_NO = L.LOT_NO
       AND  R.LOT_YEAR = L.LOT_YEAR
INNER JOIN  JOC_LOT_ISSUE_REG I
        ON  I.LOT_NO = R.LOT_NO
        AND I.LOT_YEAR = R.LOT_YEAR
WHERE       L.LOT_YEAR = '1213'
        AND L.RN = 1
ORDER BY    L.FCD_MAIN_ID

Note that I have used the ANSI/ISO syntax for joins, which I would strongly advise you to do. Defining join conditions in the WHERE clause is something of the eighties; don't do it. Queries becomes much more readable once you are used to the ANSI/ISO syntax.

The suggested query selects all the columns that are needed from both JOC_FIN_CLTH_DFCT_LOT and JOC_FIN_CLTH_DFCT_MAIN in the sub-query, that way you don't have to include those tables again.

The major trick is the use of the ROW_NUMBER window function, which gives an sequence number according to the PARTITION clause. The outer query then filters for only those records which got number 1, which are the records where the value of FCD_MAIN_ID is minimal for a given FCDL_ID.