If you generate a new column within the SELECT List (before the FROM
Clause), then you only use the name of this new field outside the
SUB-SELECT.
A SUB-Select can consist of the SELECT, FROM, WHERE, GROUP BY and HAVING
clauses. If you want to reuse the names, you need additional Common Table
Expressions or Nested Sub-Selects
If you change your query as follows, you can use the newly created column
names:
1. With Common Table Expression
With x as (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,
from dktnphpf, dktnpdpf
where nphcoy = 1 and nphnab = 'ND0001' and nphnph = npdnph)
Select x.*, npdamt + adjust - amount as avail
from x;
2. With Nested Sub-Select
Select x.*, npdamt + adjust - amount as avail
From (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
from dktnphpf, dktnpdpf
where nphcoy = 1 and nphnab = 'ND0001' and nphnph = npdnph) x;
3. With CTE and only a single Read of the DKTNTRPF table:
With CTE_Sum as (select sum(Case When Ntrtty = 'UDD' then ntramt Else 0 End)
Amount,
Sum(Case When Ntrtty = 'ADJ' then ntramt Else 0 End)
Adjust
from dktntrpf
where ntrnpd = 3913 and ntrtty in ('UDD', 'ADJ'))
select npdsdt, npdedt, npdamt, npdsts, npdtno, npdabl, npdnph, npdnpd,
nphnab,
Amount, Adjust, npdamt + adjust - amount as avail
from dktnphpf join dktnpdpf on nphnph = npdnph
Cross Join CTE_Sum
where nphcoy = 1 and nphnab = 'ND0001';
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Don
Brown via MIDRANGE-L
Sent: Sonntag, 28. Februar 2021 03:33
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: Don Brown <DBrown@xxxxxxxxxx>
Subject: SQL - Using result fields in a calculation
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
midrange.com
