Actually. Your subselects aren't even referring to anything in the parent table, which is unusual, so, if that is the case in production, you might be able to leave of the "lateral" clause in this case.
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Darren Strong
Sent: Monday, March 1, 2021 9:12 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: SQL - Using result fields in a calculation
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://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
--
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
