"select t.opendate as 'OpenDate', t.Transact as 'Transaction', t.NetTotal as 'HeaderNet', isnull(sum(d.Quan*d.CostEach), 0) as 'DetailNet', t.FinalTotal as 'HeaderFinal', t.TenderTotal as 'HowPayAppTotal' " _
& "from dba.posDetail d, " _
& "(select h.opendate, h.Transact, h.NetTotal, h.FinalTotal, sum(p.Tender) TenderTotal " _
& "from dba.posHeader h, dba.howpayapproved p " _
& "where h.Transact = p.Transact and h.opendate between " & lbl_SrchStartDate.Text & " and " & lbl_SrchEndDate.Text & " group by h.opendate, h.Transact, h.NetTotal, h.FinalTotal) t " _
& "where t.Transact *= d.Transact " _
& "and d.prodtype not in (101) " _
& "and opendate between " & lbl_SrchStartDate.Text & " and " & lbl_SrchEndDate.Text & " " _
& "group by t.opendate, t.Transact, t.NetTotal, t.FinalTotal, t.TenderTotal " _
& "order by t.transact"
5. t.TenderTotal is a column, it could not be a row in it.
When possible use parameterized queries or commands. This solves 2. and 3.
select t.Transact, t.NetTotal, t.FinalTotal, isnull(sum(d.Quan*d.CostEach),0) DetailTotal, t.TenderTotal
from dba.posDetail d,
(select h.Transact, h.NetTotal, h.FinalTotal, isnull(sum(p.Tender),0) TenderTotal
from dba.posHeader h, dba.howpayapproved p
where h.Transact *= p.Transact group by h.Transact, h.NetTotal, h.FinalTotal) t
where t.Transact *= d.Transact
and d.prodtype not in (101)
group by t.Transact, t.NetTotal, t.FinalTotal, t.TenderTotal
select t.opendate as 'OpenDate', t.Transact as 'Transaction', t.NetTotal as 'HeaderNet', isnull(sum(d.Quan*d.CostEach), 0) as 'DetailNet', t.FinalTotal as 'HeaderFinal', isnull(t.TenderTotal,0) as 'HowPayAppTotal'
from dba.posDetail d,
(select h.opendate, h.Transact, h.NetTotal, h.FinalTotal, isnull(sum(p.Tender),0) as 'TenderTotal'
from dba.posHeader h, dba.howpayapproved p
where h.Transact = p.Transact group by h.opendate, h.Transact, h.NetTotal, h.FinalTotal) t
where t.Transact *= d.Transact
and d.prodtype not in (101)
group by t.opendate, t.Transact, t.NetTotal, t.FinalTotal, t.TenderTotal
order by t.transact
select t.opendate as 'OpenDate', t.Transact as 'Transaction', t.NetTotal as 'HeaderNet', isnull(sum(d.Quan*d.CostEach), 0) as 'DetailNet', t.FinalTotal as 'HeaderFinal', isnull(t.TenderTotal,0) as 'HowPayAppTotal'
from dba.posDetail d,
(select h.opendate, h.Transact, h.NetTotal, h.FinalTotal, isnull(sum(p.Tender),0) as 'TenderTotal'
from dba.posHeader h, dba.howpayapproved p
where h.Transact *= p.Transact group by h.opendate, h.Transact, h.NetTotal, h.FinalTotal) t
where t.Transact *= d.Transact
and d.prodtype not in (101)
group by t.opendate, t.Transact, t.NetTotal, t.FinalTotal, t.TenderTotal
order by t.transact
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
SQL Help joining two tables | 7 | 48 | |
Query syntax | 10 | 40 | |
How to simplify my SQL statement? | 14 | 50 | |
Alter an update query which rounds | 7 | 33 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
26 Experts available now in Live!