1

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 MATERIALIZE and INLINE hints, and my Oracle server is 10g.

  • There were several issues with CTEs in Oracle. You should mention exact version you use. I'd recommend you to use hins MATERIALIZE and INLINE on factored sub-queries to see if query output changes. I also saw situation where dropping functional index on the table, changed the result of the query. – ibre5041 May 20 '15 at 11:41
  • I tried both of these hints none of them worked :/ – SüniÚr May 20 '15 at 12:04
  • What happens if you replace the CTE with: with n as (select level-1 as n from dual connect by level <= 100) select ft0.* ... (the rest remains unchanged, no m at all). – ypercubeᵀᴹ May 20 '15 at 12:17
3

You have an error in your query. The CTE m will return not a single row, but as many rows as in FCS_FP_TOMB. Then you are putting all of those rows into the row generator CTE n, whereas a row generator construct like you're using should get a single row.

What I would do is to define m to return a single row with the minimum and maximum values, and then subtract these in n to get the number of rows you need to generate.

Something like (untested):

with m as (
  select max(to_number(substr(t.END,3))) - min(to_number(substr(t.START,3))) as num
  from FCS_FP_TOMB t
), n as (
  select level - 1 as n
  from m
  connect by level <= m.num + 1
)
select ft0.*, regexp_substr(ft0.START,'([A-])+')||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;
  • Shouldn't that be max(to_number(substr(t.END,3))) - min(...t.START...) ? – ypercubeᵀᴹ May 20 '15 at 12:38
  • Or max( to_number(substr(t.END,3)) - to_number(substr(t.START,3)) ) – ypercubeᵀᴹ May 20 '15 at 12:39
  • Something like that... my brain hurts! If you have 1-2 and 99-100, you'd only need 2 generated rows -- but you'd have to add the row numbers to the start value when generating the labels. – Colin 't Hart May 20 '15 at 12:46
  • thanks Colin, its actually fix my problem. Can anyone explain it why i need that max-min? :O. I Try to understand but I simply can't – SüniÚr May 20 '15 at 13:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.