× 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.



Yep that was what I was just doing :-)

Cheers

Don




From: "Vern Hamberg" <vhamberg@xxxxxxxxxxxxxxx>
To: midrange-l@xxxxxxxxxxxxxxxxxx
Date: 06/03/2021 08:30 AM
Subject: Re: SQL - Using result fields in a calculation
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx>



Hi Don

You've heard it here a few times - Visual Explain can help you determine
which is better, in its environment. Sometimes the actual run-time
environment is enough different, that VE did not come up with what
actually happens.

But it's always a most excellent starting point!

Regards
Vern

On 3/5/2021 4:01 PM, Don Brown via MIDRANGE-L wrote:
Thanks Darren,

Yes that worked.

Now I have 4 different solutions - I hope I can find the time to review
and understand each!!!

Then try and determine if any are better than any other

Cheers


Don



From: "Darren Strong" <darren@xxxxxxxxx>
To: "Midrange Systems Technical Discussion"
<midrange-l@xxxxxxxxxxxxxxxxxx>
Date: 06/03/2021 03:26 AM
Subject: RE: SQL - Using result fields in a calculation
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx>



Glad you were able to get something to help. If the 3913 is coming from
the base table, then, yes, lateral is definitely what you want. In case
you were still curious about this method, its probably a parenthesis
problem as I predicted. I've removed a probable extra one after the
"values".



select npdsdt, npdedt, npdamt, npdsts, npdtno, npdabl, npdnph, npdnpd,
nphnab,
amount, adjust,
npdamt + adjust - amount as avail
from dktnphpf, dktnpdpf
cross join lateral
(values(select sum(ntramt) from dktntrpf where ntrnpd = 3913 and
ntrtty =
'UDD')) L1(amount)
cross join lateral
(values(select sum(ntramt) from dktntrpf where ntrnpd = 3913 and
ntrtty =
'ADJ')) L2(adjust)
where nphcoy = 1 and nphnab = 'ND0001' and nphnph = npdnph;

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Don
Brown via MIDRANGE-L
Sent: Monday, March 1, 2021 4:56 PM
To: Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Don Brown <DBrown@xxxxxxxxxx>
Subject: RE: SQL - Using result fields in a calculation

CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know
the content is safe.


Hi Darren,

While Birgitta has provided 3 solutions that work I am always keen to
look
at other options.

The sub-select does actually refer to the parent table although I have
coded the value of 3913 but this value is also in field npdnpd.
I was making some changes to an enquiry and while developing the sql I
was
minimising the data returned for testing and debugging.

I tried your code and it errors on L1(amount)

select npdsdt, npdedt, npdamt, npdsts, npdtno, npdabl, npdnph, npdnpd,
nphnab,
amount, adjust,
npdamt + adjust - amount as avail
from dktnphpf, dktnpdpf
cross join lateral
(values((select sum(ntramt) from dktntrpf where ntrnpd = 3913 and
ntrtty =
'UDD')) L1(amount)
cross join lateral
(values((select sum(ntramt) from dktntrpf where ntrnpd = 3913 and
ntrtty =
'ADJ')) L2(adjust)
where nphcoy = 1 and nphnab = 'ND0001' and nphnph = npdnph;

SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token L1 was not valid.

Don






From: "Darren Strong" <darren@xxxxxxxxx>
To: "Midrange Systems Technical Discussion"
<midrange-l@xxxxxxxxxxxxxxxxxx>
Date: 02/03/2021 12:12 AM
Subject: RE: SQL - Using result fields in a calculation
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx>



While a CTE gets it done, a lateral table is more elegant and
straightforward for this purpose. I don't have the tables, so, I could
not
validate the syntax. Hopefully my parenthesis line up, but, it gives you
an idea.

select npdsdt, npdedt, npdamt, npdsts, npdtno, npdabl, npdnph, npdnpd,
nphnab,
amount, adjust,
npdamt + adjust - amount as avail
from dktnphpf, dktnpdpf
cross join lateral
(values((select sum(ntramt) from dktntrpf where ntrnpd = 3913 and
ntrtty =
'UDD')) L1(amount)
cross join lateral
(values((select sum(ntramt) from dktntrpf where ntrnpd = 3913 and
ntrtty =
'ADJ')) L2(adjust)
where nphcoy = 1 and nphnab = 'ND0001' and nphnph = npdnph;

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Don
Brown via MIDRANGE-L
Sent: Saturday, February 27, 2021 9:33 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: Don Brown <DBrown@xxxxxxxxxx>
Subject: SQL - Using result fields in a calculation

CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know
the content is safe.


Hi,

I have the following SQL

select npdsdt, npdedt, npdamt, npdsts, npdtno, npdabl, npdnph, npdnpd,
nphnab,
(select sum(ntramt) from dktntrpf where ntrnpd = 3913 and
ntrtty =
'UDD') as amount,
(select sum(ntramt) from dktntrpf where ntrnpd = 3913 and
ntrtty =
'ADJ') as adjust,
npdamt + adjust - amount as avail
from dktnphpf, dktnpdpf
where nphcoy = 1 and nphnab = 'ND0001' and nphnph = npdnph;

I am running it in iACS

It fails indicating the variable "adjust" is not found.

I am obviously not using the correct terms when searching google as I
can't find an example of how to use the results or a sub select.

Any assistance would be appreciated.

Thanks

Don




--
This email has been scanned for computer viruses. Although MSD has taken
reasonable precautions to ensure no viruses are present in this email,
MSD
cannot accept responsibility for any loss or damage arising from the use
of this email or attachments..
--
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://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=lidQIqb6AMZUvO6CJk-voHOnCkkJde8I_LMPtxEIMPg&s=ZsQdohcfkHFeKQqFNUJYRGlW4fEvnCGuqoupAGP-jqI&e=



or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at

https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=lidQIqb6AMZUvO6CJk-voHOnCkkJde8I_LMPtxEIMPg&s=rS0LXbP4tRF-LyWoShlVBEr2lYH_Q6Uywewzqv1KJOo&e=


.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support

https://urldefense.proofpoint.com/v2/url?u=http-3A__midrange.com&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=lidQIqb6AMZUvO6CJk-voHOnCkkJde8I_LMPtxEIMPg&s=-doxXeqOYbbyVrHYGG39vWGAEzmo2WJQTbCfmRyxd5M&e=


by shopping at

https://urldefense.proofpoint.com/v2/url?u=http-3A__amazon.com&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=lidQIqb6AMZUvO6CJk-voHOnCkkJde8I_LMPtxEIMPg&s=zdxsjvyJHAk4dqmps_AVuNysbErgXfnz_O-zTIDEB1Y&e=


with our affiliate link:

https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.com&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=lidQIqb6AMZUvO6CJk-voHOnCkkJde8I_LMPtxEIMPg&s=d36dtrqy_2_PPoMFpSuF0Yyvp_IjjSHNqTuP1KREvso&e=



--
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://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=lidQIqb6AMZUvO6CJk-voHOnCkkJde8I_LMPtxEIMPg&s=ZsQdohcfkHFeKQqFNUJYRGlW4fEvnCGuqoupAGP-jqI&e=



or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at

https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=lidQIqb6AMZUvO6CJk-voHOnCkkJde8I_LMPtxEIMPg&s=rS0LXbP4tRF-LyWoShlVBEr2lYH_Q6Uywewzqv1KJOo&e=


.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support

https://urldefense.proofpoint.com/v2/url?u=http-3A__midrange.com&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=lidQIqb6AMZUvO6CJk-voHOnCkkJde8I_LMPtxEIMPg&s=-doxXeqOYbbyVrHYGG39vWGAEzmo2WJQTbCfmRyxd5M&e=


by shopping at

https://urldefense.proofpoint.com/v2/url?u=http-3A__amazon.com&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=lidQIqb6AMZUvO6CJk-voHOnCkkJde8I_LMPtxEIMPg&s=zdxsjvyJHAk4dqmps_AVuNysbErgXfnz_O-zTIDEB1Y&e=


with our affiliate link:

https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.com&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=lidQIqb6AMZUvO6CJk-voHOnCkkJde8I_LMPtxEIMPg&s=d36dtrqy_2_PPoMFpSuF0Yyvp_IjjSHNqTuP1KREvso&e=







--
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://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=S7TwB876CJfgKIW0KeFewOJ6-tj0ylEHNxWowtIlXoc&s=p0eEWOkQ3N7GIyrgYGxzQlcIav0XmHIWQd_vaZjKA6k&e=


or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at

https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=S7TwB876CJfgKIW0KeFewOJ6-tj0ylEHNxWowtIlXoc&s=BdjSryFAuSILJHmwRBmHuwR_hbmm-xPfcQw-KaKoy44&e=

.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support

https://urldefense.proofpoint.com/v2/url?u=http-3A__midrange.com&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=S7TwB876CJfgKIW0KeFewOJ6-tj0ylEHNxWowtIlXoc&s=hQhAZBCrt_NWRL5-42yMQvS3hAq5PDZCTwBmvFNkayI&e=

by shopping at

https://urldefense.proofpoint.com/v2/url?u=http-3A__amazon.com&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=S7TwB876CJfgKIW0KeFewOJ6-tj0ylEHNxWowtIlXoc&s=RqOrbVniqC87-iRoMd-05LkSdu3IaMdy-ZCz9X3vs7c&e=

with our affiliate link:

https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.com&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=S7TwB876CJfgKIW0KeFewOJ6-tj0ylEHNxWowtIlXoc&s=pY1BLRGm78C3oesTRhjVS5A2VGFy3AJom5gq8ePAlE0&e=




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.