Re: SQL break ?

On 15 Mar 2013 13:42, CRPence wrote:

On 15 Mar 2013 12:40, fbocch2595@xxxxxxx wrote:

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.

The feature is often called outlining, and is a feature of a report
writer, rather than a feature of the retrieval of data, which is all
that the SQL itself does. IIRC the report writer of QMQRY [STRQM,
STRQMQRY with a *QMFORM], which allows SQL statements, provides
outlining for reports as defined in the report form.

DTNM REPORT SET NAME REPORT NAME
ALEX KAPILF ALLITARB4 ALLITARB4
ANDREW LAJR C34TIPPFZ2 C34TIPPFZ2
ANDREW LAJR X12CVMSF X12CVMSF
ANN MCKE P08RATRF P08RATRF
ANN MCKE C36TIPPF C36TIPPF
ANN MCKE D75LEINF D75LEINF
ANN MCKE P88MAILF P88MAILF
ANN MCKE QSYSPRTX QSYSPRTX
ANN MCKE X24RCVMF X24RCVMF
ANN MCKE P09DUPLF P09DUPLF
ANNE MULDEG C01CAXF C01CAXF
ANNE MULDEG C01DETF C01DETF
ANNE MULDEG C03EMPLF C03EMPLF
ANNE MULDEG C12EMPLF C12EMPLF
ANNE MULDEG C17LOSSF C17LOSSF
ANNE MULDEG C23CLEXF C23CLEXF
ANNE MULDEG C23CLSSF C23CLSSF
ANNE MULDEG C25NODTF C25NODTF
ANNE MULDEG C35TIPPF C35TIPPF

The same could be obtained by the SQL, but probably not a preferable
means to achieve that. Here should be one way (untested):

In an attempt to clarify...

The same *outlining effect* could be obtained with the SQL versus via a report writer, but that is probably not a preferable means to achieve that effect.

select case when theRowNbr=1 then DTNM
/* else '' */
/* use the "else ''" if blank vs the NULL value [presented as a */
/* dash in the query/400 report writer which is used by STRSQL] */
/* then sort using DESC vs ASC. Note: NULL collates highest */
end
, RptSetName
, RptName
from (select DTNM
, RptSetName
, RptName
, Row_Number()
over(partion by DTNM
/* order by ... no apparent order in the sample */
) as theRowNbr
from theLib/theFile ) as S
order by DTNM ASC, 1 ASC /* <ed: correction made to ORDER BY!> */

Sorry, the above query had been incorrectly coded with "ORDER BY 1 ASC". That was since corrected within the above quoted text, to specify "ORDER BY DTNM ASC, 1 ASC" if using the implicit [or explicit ELSE NULL effect in the CASE expression, the first expression of the select-clause] to match the ordering implied by the report sample. Not to suggest that it was tested... just that I noticed the obvious flaw.

If instead choosing to use the ELSE '' in the CASE, then because blanks collate before /printable/ character data, the order-by would be changed to:
order by DTNM ASC, 1 DESC

AFaIK the second specification for the ORDER BY could be theRowNbr, which would be more obvious than using the result of the CASE expression; and theRowNumber would use ASCending, irrespective of the result of the CASE expression. For some reason when I first started coding the query, I thought I would be unable to reference that column in the ORDER BY when using an NTE vs a CTE. Looking at the query now, I can not see why it could not just use:
order by DTNM ASC, theRowNumber ASC