Hey everyone,
I have a query that is to return a set of rows depending on its WIPDataValue. Say for example, I have added new WIPDataValue which is 1-9. So obviously the resulting set should have 9 rows and on those 9 rows, the value of each is 1-9. This is running fine. The problem is, when I added a new row, say 10 on the existing 1-9, the query result doubles. So instead of returning 10 rows, it returns 20 rows. Even weirder is that the first 17 rows contain 1-9 and the last three rows has 10 as its value. Below will be my query:
SELECT * FROM
(
SELECT
lol.*
, ROW_NUMBER() OVER (ORDER BY regexp_substr(Wipdatavalue, '^\d+')) AS n
, count(*) OVER() m
FROM
(
SELECT
wipdatavalue
, containername
, l
, q as qtybox
, d
, qtyperbox AS q
, productname
, dt
, dsn
, CASE
WHEN instr(wipdatavalue, '-') = 0 THEN
to_number(wipdatavalue)
ELSE
to_number(substr(wipdatavalue, 1, instr(wipdatavalue, '-') - 1))
END AS una
, CASE
WHEN instr(wipdatavalue, '-') = 0 THEN
to_number(wipdatavalue)
ELSE
to_number(substr(wipdatavalue, instr(wipdatavalue, '-') + 1))
END AS dulo
FROM trprinting_ls
WHERE containername = :lotID
)lol
START WITH instr(Wipdatavalue, '1') > 0
CONNECT BY LEVEL BETWEEN regexp_substr(Wipdatavalue, '^\d+') AND regexp_substr(Wipdatavalue, '\d+$')
)
WHERE n LIKE :n
I know you guys will help so as early as now thank you. :)
