Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.
"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