I have the following table in Oracle:
ID START END _ID01 AB050 AB100 _ID01 AB101 AB150 _ID02 AB0251 AB0300 _ID01 AB00301 AB00302
I want to generate as many rows and every records defines. So the first need 50 more with a new col start with AB50 up to AB100. Everything works fine, but if I don't add that junk row, the sql developer can't solve the problem, just loading.
I tried with some random SQL hint but none of them worked. Need some advice or hint what's "wrong" with my working query, because I don't think that's normal behavior.
And my SQL query is the following.
with m as ( select to_number(substr(t.START,3)) as num from FCS_FP_TOMB t where t.START ='AB0251' --<--- THIS ROW IS A JUNK BUT DOESNT WORK WITHOUT IT ), n as ( select level - 1 as n from m connect by level <= m.num + 1 ) select ft0.*, regexp_substr(ft0.START,'([A-Z])+')||to_char(to_number(substr(ft0.START,3)) + n.n) as "bizonylat" from FCS_FP_TOMB ft0 join n on to_number(substr(ft0.START,3)) + n.n <= to_number(substr(ft0.END,3)) order by 2,3,7 ;
The out put:
... _ID01 AB050 AB100 AB098 _ID01 AB050 AB100 AB099 _ID01 AB050 AB100 AB100 _ID01 AB101 AB150 AB101 _ID01 AB101 AB150 AB102 ...
EDIT: I tried
INLINE hints, and my Oracle server is 10g.