SQL Connect By Level sometimes works, sometimes doesn't can't understand why

Question

I am trying to run the query in Oracle, and if I change the round to 0, I get a result, but anytime there are decimals I am not getting a result back when using the connect by level part. But if I run I my query from after n.n= I get the result.

Reason I am trying to use the connect by level is I have a requirement to put my entire query into the where clause as in the application there is a restriction to do the group by clause I need.

SELECT n.n
FROM 
    (SELECT TO_NUMBER( LEVEL) - 1 n FROM DUAL CONNECT BY LEVEL <= 1000 ) n
WHERE n.n = 
    (subquery)

Examples of values I have which work in HOURS seem to be like whole number, wo when these are summed they are still whole numbers

5 
10 
5 
5 
20

But where I have seen the query not work is where I have decimal values such as:

3.68
2.45
5
10
5

Table:ASSIGNMENTS_M

Table: RESULT_VALUES

Columns: Result_ID, Assignment_ID, Date_Earned, Hours

INSERT INTO RESULT_VALUES(Result_ID, Assignment_ID, Date_Earned, Hours) VALUES(50,123456,to_date('01/02/2020', 'DD/MM/YYYY'),3.68 51,230034,to_date('02/02/2020', 'DD/MM/YYYY'),5 52,123456,to_date('03/02/2020', 'DD/MM/YYYY'),10 53,123456,to_date('04/02/2020', 'DD/MM/YYYY'),5 60,123456,to_date('05/02/2020', 'DD/MM/YYYY'),5 90,123456,to_date('06/02/2020', 'DD/MM/YYYY'),5 2384,123456,to_date('07/02/2020', 'DD/MM/YYYY'),10);

Expected Result = 38.68

Answer 1

Here's one solution, even though it's odd you want to do this:

The adjusted fiddle:

Working test case

This increments by 0.1 to find the matching row:

SELECT n.n
  FROM ( SELECT TO_NUMBER(LEVEL)/10 - 1 n FROM DUAL CONNECT BY LEVEL <= 1000 ) n
 WHERE n.n = (
            SELECT round((sum(P2.HOURS)),1) FTE
              FROM ASSIGNMENTS_M P1, RESULT_HOURS P2
             WHERE P2.date_earned BETWEEN to_date('2020/01/01','YYYY/MM/DD') AND to_date('2020/10/31','YYYY/MM/DD')
               AND P1.ASSIGNMENT_ID = 123456
             GROUP BY P1.ASSIGNMENT_ID
          )
;

This increments by 1 to find the matching row, but adjusts the calculation to allow this:

SELECT n.n / 10
  FROM ( SELECT TO_NUMBER(LEVEL) - 1 n FROM DUAL CONNECT BY LEVEL <= 1000 ) n
 WHERE n.n = (
            SELECT round((sum(P2.HOURS)),1) FTE
              FROM ASSIGNMENTS_M P1, RESULT_HOURS P2
             WHERE P2.date_earned BETWEEN to_date('2020/01/01','YYYY/MM/DD') AND to_date('2020/10/31','YYYY/MM/DD')
               AND P1.ASSIGNMENT_ID = 123456
             GROUP BY P1.ASSIGNMENT_ID
          ) * 10
;

The result:

None of your results match the number sequence generated by the n derived table:

SELECT p1.assignment_id, round((sum(P2.HOURS)),1) FTE
  FROM ASSIGNMENTS_M P1, RESULT_HOURS P2
 WHERE P2.date_earned BETWEEN to_date('2020/01/01','YYYY/MM/DD') AND to_date('2020/10/31','YYYY/MM/DD')
   AND P1.ASSIGNMENT_ID = 123456
 GROUP BY P1.ASSIGNMENT_ID
;

Result:

+---------------=+
| id      | fte  |
+----------------+
| 123456  | 43.7 |
+----------------+

That's the reason. Now how do you want to change this logic?

Do you want an approximate comparison or do you want your sequence to be in 0.1 increments?