0

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. :)

2
Contributors
1
Reply
12
Views
4 Years
Discussion Span
Last Post by pritaeas
0

I don't have Oracle to test with, but I think anyone that has will need the table structure and sample data to test with.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.