Re: All-of-a-sudden problem with self JOINs and ROW_NUMBER()

Vielen Dank!

The Partition By did the trick - the 2nd idea is close and is preferable, but I got a couple messages about problems - I have to respond to a user report of the bad report, so I'll go with what works right away - maybe look at the other later.

Regards
Vern

On 10/6/2020 12:28 AM, Birgitta Hauser wrote:

Hi Vern,

if I understand correctly what you want to archive, you need to add a Partition By Clause in your OVER()
... Over(Partition By RefCod Order By Change_TS)

If it worked until now, I'd suspect your data changed and you'll get somehow duplicates.

But why using a self join? Wouldn't the following query return the same result?
Select RefCod, Nbr,
Max(Case When QSTRDS = 10 Then QSTRDS End) #po,
Max(Case When QSTRDS = 20 Then QSTRDS End) #wo,
Max(Case When QSTRDS = 30 Then QSTRDS End) Saddress
from (Select Row_Number() Over(Partition By RefCod, QSTRDS Order Change_Ts) Nbr, RefCod, Qstrds
from SVYQUE
Where QStIdx = 88 and QSTRDS in (10, 20, 30)
Group By RefCod, Nbr;

Mit freundlichen Grüßen / Best regards

Birgitta Hauser


"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
„Train people well enough so they can leave, treat them well enough so they don't want to.“ (Richard Branson)


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Vernon Hamberg
Sent: Montag, 5. Oktober 2020 20:43
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: All-of-a-sudden problem with self JOINs and ROW_NUMBER()

Hi all

I have an SQLRPGLE program that we've been running over 2 years - all of a sudden, it is not working right. I wonder what changed - I believe no PTFs were applied last Thursday.

I do used NTEs (nested tables expressions) inside the CTE - I did try making them CTEs, but that didn't fix it - but that doesn't explain the changed behavior

The SQL SELECT statement includes a 3-way self JOIN - here's what the data structure looks like in table SVYQUE (survey questions) -

Reference code (REFCOD) - char(8) - this is a reference that is common to several tables related to invoices.
Question index (QSTIDX) - zoned(3, 0)
Question number (QSTNBR) - zoned(3, 0)
Response text (QSTRDS) - char(60)

The reference code is a unique identifier for an invoice, say, and the others refer to a set of survey questions. This survey (#88) has 3 questions, and question numbers are 10, 20, and 30. There is supposed to be only one of each kind question, but I wonder if we didn't have some kind of duplicates - can't remember, not - but that would be why I used ROW_NUMBER, perhaps.

This has worked fine for over 2 years - then Friday and this morning, it did not handle this right - there are 3 CTEs - Q10 is the REFCOD, ROWNUM, and QSTRDS for question number 10, same for 20 and 30. ROWNUM has always been 1, so far as I know, in these last 2+ years.

Now if I run the CTEs separately, ROWNUM is actually 1 in each - but if JOINed as here, there is no record returned for most values of REFCOD - there was 1 REFCOD for which values were returned.

The desired output is like this -
REFCOD Reply#1 Reply#2 Reply#3

I have focused on ROWNUM as not matching - but it might be REFCOD - that would be extremely strange, since it is coming from the exact same column in the exact same row in the exact same table.

SELECT q10.refcod, q10.qstrds po#, q20.qstrds wo#,
q30.qstrds saddress
FROM (SELECT refcod, ROW_NUMBER()
OVER(ORDER BY refcod, change_ts) rownum,
qstrds
FROM svyque
WHERE qstidx = 88 AND qstnbr = 10) q10
JOIN (SELECT refcod, ROW_NUMBER()
OVER(ORDER BY refcod, change_ts) rownum,
qstrds
FROM svyque
WHERE qstidx = 88 AND qstnbr = 20) q20
ON q10.refcod = q20.refcod AND
q10.rownum = q20.rownum
JOIN (SELECT refcod, ROW_NUMBER()
OVER(ORDER BY refcod, change_ts) rownum,
qstrds
FROM svyque
WHERE qstidx = 88 AND qstnbr = 30) q30
ON q10.refcod = q30.refcod AND
q10.rownum = q30.rownum where q10.refcod = 'E8755216'; -- I put the WHERE caluse here for testing individual items - it's not part of the running code.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com