Kevin,

scope creep...

so the query your provide is exactly what the scope required, but i'm
trying to tweak it a bit to do the followoing...

Consider this output for the select statement...


"CustFrom" "CustTo"
100,457 1,086,164
1,086,305 1,149,111
1,149,145 1,185,230
1,185,248 1,216,860
1,216,886 2,171,692
2,171,791 2,230,803
2,230,837 3,007,630
3,008,083 7,704,226,567
******** End of data ********

need the output to be adjusted to "override" some of the "true" loan values
to accommodate true ranges.
For example, the above results will look like this...

"CustFrom" "CustTo"
1 1,086,164
1,086,165 1,149,111
1,149,112 1,185,230
1,185,231 1,216,860
1,216,861 2,171,692
2,171,693 2,230,803
2,230,804 3,007,630
3,007,631 9999999999
******** End of data ********

below is the query modification to at least address the very first and very
last "override" values, but not sure exactly how to calculate the in
between...
do you?


select case when ntile = 0
then 1
else ????
end as "CustFrom"
,case when ntile = 8
then 9999999999
else ????
end as "CustTo"

from (select smrn10
,row_number() over (order by smrn10 asc) * 8 /
(select count(*) + 1
from ldatat3x.srvdsr) as ntile
from ldatat3x.srvdsr
order by smrn10) as x
group by ntile
order by ntile

Jay




On Thu, Aug 9, 2018 at 1:47 PM, Jay Vaughn <jeffersonvaughn@xxxxxxxxx>
wrote:

no problems at all Charles... Kevin actually nailed it... thank you Kevin!

On Thu, Aug 9, 2018 at 1:12 PM, Charles Wilt <charles.wilt@xxxxxxxxx>
wrote:

Sounds like a "Gaps & Island" problem...

Take a look here...
https://www.red-gate.com/simple-talk/sql/t-sql-programming/
the-sql-of-gaps-and-islands-in-sequences/

I assume your cust# are supposed to be sequential?

Charles

On Thu, Aug 9, 2018 at 10:41 AM Jay Vaughn <jeffersonvaughn@xxxxxxxxx>
wrote:

say host variable is 8...

need output to be 8 ranges (2 columns each row)...

cust# - cust#
cust# - cust#
etc.

On Thu, Aug 9, 2018 at 12:38 PM, Kevin Bucknum <
Kevin@xxxxxxxxxxxxxxxxxxx>
wrote:

I'm real clear on what you are asking for that might need recursion.
Select min(cust#), max(cust#) where partition = :numOfPartions?




Kevin Bucknum
Senior Programmer Analyst
MEDDATA/MEDTRON
Tel: 985-893-2550

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of
Jay
Vaughn
Sent: Thursday, August 9, 2018 11:32 AM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: sql statement ideas


I'm at a lost on how to create a single sql statement under the
following
scope...

1.) uses one host variable = :numOfPartitions
2.) over a file that contains customer#'s
3.) returns customer# ranges for :numOfPartitions host variable

I'm guessing recursive is sql is required... but hoping not?

anybody?
--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To
subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our
affiliate
link: http://amzn.to/2dEadiD
--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our
affiliate
link: http://amzn.to/2dEadiD

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD




This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].