Re: SQL - Using result fields in a calculation -- MIDRANGE-L

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:

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

.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support

by shopping at

with our affiliate link:

--
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=