Re: SQL break ?

On 15 Mar 2013 13:38, Tom E Stieger wrote:

fbocch2595 onFriday, March 15, 2013 12:40 PM wrote:

Hi Folks, when I run this statement;
SELECT RDDTNM, RDRSET, RDRPTN FROM REPRD ORDER BY rddtnm
I get the screen below but how can I remove the duplicate names
and only show one name for every report set? I know it's doable
with WRKQRY but I'd like to use SQL. <<SNIP>>

This will probably work for you.
As long as you are above v5r4

With
rowcount as
( SELECT
RDDTNM
, RDRSET
, RDRPTN
, ROW_NUMBER() OVER(PARTITION BY RDDTNM
ORDER BY RDDTNM, RDRSET, RDRPTN
) as rowno
FROM REPRD
ORDER BY RDDTNM
)
Select
CASE ROWNO when 1 then RDDTNM else '' end
, RDRSET
, RDRPTN
from rowcount

For the OP to consider about that query... if indeed one were to use the SQL instead of a report writer for the task of mimicking the /outlining/ feature of a report writer.

I believe for lack of an ORDER BY in the final\outer-most SELECT, the collation of rows is unpredictable [even if the rows are /likely/ to appear ordered as desired, merely because there was an ORDER BY in the CTE]. Thus I would move the ORDER BY to the final SELECT, and add the ROWNO to that ordering of the final result so the first row with the actual value of RDDTNM will appear before each row where the empty string had replaced the value of RDDTNM using the CASE expression.

And FWiW the sample report given in the OP used only "ORDER BY RDDTNM" so there was no ordering by any other columns. Thus one might presume that the partitioning need not include additional columns in its ordering either.? Thus I believe the window-order-clause of the window-partition-clause may be unnecessary to effect what expressed was desired.

With those modifications to Tom's query:

With
rowcount as
( SELECT
RDDTNM
, RDRSET
, RDRPTN
, ROW_NUMBER() OVER(PARTITION BY RDDTNM
) as rowno
FROM REPRD
)
Select
CASE ROWNO when 1 then RDDTNM else '' end
, RDRSET
, RDRPTN
from rowcount
order by RDDTNM, ROWNO

The query I offered in another reply [and a correction in a reply to that] is effectively the same as the above query, but uses a Nested Table Expression (NTE) instead of the Common Table Expression (CTE) used in the above query request.