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=
midrange.com
