× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. August 2018

Re: sql statement ideas

very cool kevin - we are on 7.2 also... thanks for the elegant
suggestions...

On Fri, Aug 10, 2018 at 9:24 AM, Kevin Bucknum <Kevin@xxxxxxxxxxxxxxxxxxx>
wrote:

And if you are on 7.3, there are new functions you could use that may be
faster. Until we get a new box I'm stuck on 7.2 so I can't test it.
https://www.itjungle.com/2016/05/31/fhg053116-story03/





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

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of
Kevin Bucknum
Sent: Friday, August 10, 2018 8:20 AM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: RE: sql statement ideas

Except I missed the adding of 1 to the x table when I switched back to
a
dummy statement.

SELECT
CASE x.ntile
WHEN 1
THEN 1
ELSE MAX(y.cust#) + 1
END,
CASE x.ntile
WHEN numOfPartions
THEN 9999999999
ELSE MAX(x.cust#)
END
FROM (SELECT cust#,
row_number()
over(ORDER BY cust# ASC) * numOfPartions /
(SELECT COUNT(*) + 1
FROM custTable) + 1
AS ntile
FROM custTable
ORDER BY cust#)
AS x LEFT
JOIN (SELECT cust#,
row_number()
over(ORDER BY cust# ASC) * numOfPartions /
(SELECT COUNT(*) + 1
FROM custTable) + 1
AS ntile
FROM custTable
ORDER BY cust#)
AS y ON x.ntile - 1 = y.ntile
GROUP BY x.ntile,
y.ntile
ORDER BY x.ntile,
y.ntile
;




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

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of
Kevin Bucknum
Sent: Friday, August 10, 2018 8:18 AM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: RE: sql statement ideas

Either that, or join the tables. Run both and see which is faster.
I
added 1 to
the NTILE values because I don't think case statements like math.

SELECT
CASE x.ntile
WHEN 1
THEN 1
ELSE MAX(y.cust#) + 1
END,
CASE x.ntile
WHEN numOfPartions
THEN 9999999999
ELSE MAX(x.cust#)
END
FROM (SELECT cust#,
row_number()
over(ORDER BY cust# ASC) * numOfPartions /
(SELECT COUNT(*) + 1
FROM custTable)
AS ntile
FROM custTable
ORDER BY cust#)
AS x LEFT
JOIN (SELECT cust#,
row_number()
over(ORDER BY cust# ASC) * numOfPartions /
(SELECT COUNT(*) + 1
FROM custTable) + 1
AS ntile
FROM custTable
ORDER BY cust#)
AS y ON x.ntile - 1 = y.ntile
GROUP BY x.ntile,
y.ntile
ORDER BY x.ntile,
y.ntile
;




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: Friday, August 10, 2018 7:49 AM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: sql statement ideas

so i got this to work... but is it the best way?

select case when ntile = 0
then 1
else(select max(smrn10) + 1
from (select smrn10
,row_number() over
(order by smrn10 asc) * 8 /
(select count(*) + 1
from ldatat3x.srvdsr)
as ntilex
from ldatat3x.srvdsr
order by smrn10) as x
where ntilex = ntile - 1
group by ntilex
order by ntilex)
end as "custFrom"
,case when ntile = 8 - 1
then 9999999999
else max(smrn10)
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


"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 9,999,999,999
******** End of data ********








On Fri, Aug 10, 2018 at 8:32 AM, Jay Vaughn
<jeffersonvaughn@xxxxxxxxx>
wrote:

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/t
he-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 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


As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 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].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.