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

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

Table:ASSIGNMENTS_M

Columns: Assignment_ID, Assignment_Name, Start_Date, End_Date

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

If your values are not integers, for instance decimals or even more problematic, float / doubles, using `x = y` operations might not find exact matches (without careful massaging) and produce false boolean results. If you can provide some example data and the result you expect, that might help us provide more specific feedback. — Jon Armstrong, Aug 28 '21 at 22:10
You have an `ASSIGNMENTS_M` table and an `RESULT_VALUES` table. What columns do they contain? Can you provide sample data for both tables and the exact result you expect, given that data? We're almost ready to discuss the real problem. :) Feel free to provide only data that produces a result you don't like. But be sure to show the expected result. — Jon Armstrong, Aug 28 '21 at 22:24
That's better, but there's no usable data for the test case, and no corresponding expected output, given that exact data. I'll provide the frame of a test case in a moment. You can add the `INSERT` statements containing the data. — Jon Armstrong, Aug 28 '21 at 22:32
Given the following fiddle and data, what is the exact result you would like to generate? https://dbfiddle.uk/?rdbms=oracle_18&fiddle=4251e66b6ab6a224d36dbbda12dc1a03 — Jon Armstrong, Aug 28 '21 at 22:54
Is there a reason that you actually need to use a recursive query (`connect by`) here? It sure seems like you just want to do a simple aggregate query. — Justin Cave, Aug 28 '21 at 23:00
In Fusion the application I am using, I am restricted on the sql I can use, and I am only able to put the group by into the where clause. I don't mind using an alternative to connect by, if I can still keep the main query in the where clause. — user1660680, Aug 28 '21 at 23:01
It's not clear why you want the recursive sequence, as @JustinCave mentioned. That seems unnecessary. — Jon Armstrong, Aug 28 '21 at 23:04
Is there another way I can use the subquery using a from dual in the 'FROM' clause. — user1660680, Aug 28 '21 at 23:06
See my adjusted SQL, which allows the test to be against a sequence that increments by 0.1. Seems unnecessary. But that's your request. — Jon Armstrong, Aug 28 '21 at 23:09
I've added another version, which increments by 1, but does some work with the calculation to multiply and divide by 10 to make them compare equals as expected. Note: 43.7 will never equal a whole integer, without massaging. — Jon Armstrong, Aug 28 '21 at 23:15
Don't forget, you did the following: `round((sum(P2.HOURS)),1)`, so the answer will never be `38.68`. It will round up to `.7` ... and the data you provided did not result in `38.x` either. Just be aware of that. The solutions provided work for single place decimals. If you want to handle 2 decimal places, you'll need to either increment by 0.01 or otherwise adjust other calculations by 100, just as I showed in the `0.1` examples. Also, to handle 2 decimal places, you'll need to stop rounding to one decimal place or perform some other approximate comparison, not equals. — Jon Armstrong, Aug 28 '21 at 23:39
By the way, `level` is already a number. Also, the issue is not with `connect by` itself (it would be same with a static table of 1000 integers, or `select rownum from xmltable('1 to 1000')` etc). And, your sample INSERT is invalid. — William Robertson, Aug 29 '21 at 09:21
This is a beyond weird requirement, are you sure you’re not missing something? Are you supposed to be able to write your own queries? Anyway, you don’t actually need that group by in your query (you’re filtering on that column with an equality) so do you still need to use this SQL injection technique? If you do need the group by then your query will fail as soon as it has groups as you’re using `= (subquery)` rather than `in (subquery)`. — Andrew Sayer, Aug 30 '21 at 07:25

Jon Armstrong · Accepted Answer · 2021-08-28T23:22:59.490

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?

@user1660680 Correct. If you want the other to multiply by 10, that can work too. — Jon Armstrong, Aug 28 '21 at 23:11
@user1660680 fiddle and answer adjusted. One version of the answer had a typo, but the fiddle had the correct SQL. — Jon Armstrong, Aug 28 '21 at 23:23
@user1660680 Both answers were provided, one which increments by 1 and the other that increments by 0.1. — Jon Armstrong, Aug 28 '21 at 23:24

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

1 Answers1