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://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
