triphen
asked on
SQL Query Syntax
Hey guys,
I have 3 tables that I would like to query. All of them reference each other using column Trans. Please see attached for an example of the 3 tables and an example of the expected result.
I am using SyBase SQL Anywhere v10
sql.png
I have 3 tables that I would like to query. All of them reference each other using column Trans. Please see attached for an example of the 3 tables and an example of the expected result.
I am using SyBase SQL Anywhere v10
sql.png
Select h1.Trans, h1.NetTotal, h1.FinalTotal,X.DetailTota l, X.TenderTotal From Header h1
inner join
( select h.Trans, sum(d.Quan*d.CostEach) DetailTotal, sum(p.Tender) TenderTotal
from Header h
inner join Detail d on h.Trans = d.Trans
inner join Payments p on h.Trans = p.Trans
group by h.Trans) As X on h1.Trans = X.Trans
inner join
( select h.Trans, sum(d.Quan*d.CostEach) DetailTotal, sum(p.Tender) TenderTotal
from Header h
inner join Detail d on h.Trans = d.Trans
inner join Payments p on h.Trans = p.Trans
group by h.Trans) As X on h1.Trans = X.Trans
ASKER
This is what I currently have.....
"select posheader.opendate as 'OpenDate', " _
& "posheader.transact as 'Trans#', " _
& "'$'+CAST(CONVERT(DECIMAL(30,2),sum(costeach*quan)) AS varchar(20)) as 'NetTotal', " _
& "'$'+CAST(CONVERT(DECIMAL(30,2),sum(posheader.nettotal)) AS varchar(20)) as 'Header-NetTotal', " _
& "'$'+CAST(CONVERT(DECIMAL(30,2),sum(howpayapproved.tender)) AS varchar(20)) as 'HowPaid-Tender', " _
& "'$'+CAST(CONVERT(DECIMAL(30,2),sum(posheader.finaltotal)) AS varchar(20)) as 'Header-FinalTotal' " _
& "from dba.posheader, dba.howpayapproved, dba.posdetail " _
& "where posheader.opendate between " & lbl_SrchStartDate.Text & " and " & lbl_SrchEndDate.Text) & " " _
& "and posdetail.uniqueid in (select uniqueid from dba.posdetail where opendate between " & lbl_SrchStartDate.Text & " and " & lbl_SrchEndDate.Text & " and prodtype not in (101)) " _
& "and posheader.transact = howpayapproved.transact and posheader.transact = posdetail.transact and howpayapproved.transact = posdetail.transact " _
& "group by posheader.opendate, posheader.transact " _
& "order by posheader.opendate, posheader.transact"
ASKER
Pratima Pharande,
When I try to adapat yours.....
Everything is good except that my TenderTotal is multiplied by the number of entries I have in the howpayapproved table. (ie: Payments)
When I try to adapat yours.....
Select h1.Transact, h1.NetTotal, h1.FinalTotal,X.DetailTotal, X.TenderTotal From dba.posHeader h1
inner join
( select h.Transact, sum(d.Quan*d.CostEach) DetailTotal, sum(p.Tender) TenderTotal
from dba.posHeader h
inner join dba.posDetail d on h.Transact = d.Transact
inner join dba.howpayapproved p on h.Transact = p.Transact
group by h.Transact) As X on h1.Transact = X.Transact
Everything is good except that my TenderTotal is multiplied by the number of entries I have in the howpayapproved table. (ie: Payments)
ASKER
wilcoxon,
When I try to adapat yours.....
I get the same thing....Everything is good except that my TenderTotal is multiplied by the number of entries I have in the howpayapproved table. (ie: Payments)
When I try to adapat yours.....
select h.Transact, h.NetTotal, h.FinalTotal, sum(d.Quan*d.CostEach) DetailTotal, sum(p.Tender) TenderTotal
from dba.posHeader h, dba.posDetail d, dba.howpayapproved p
where h.Transact = d.Transact
and h.Transact = p.Transact
group by h.Transact, h.NetTotal, h.FinalTotal
I get the same thing....Everything is good except that my TenderTotal is multiplied by the number of entries I have in the howpayapproved table. (ie: Payments)
Try this. I'm not positive I have the inline query table syntax right (I don't use it often).
select t.Transact, t.NetTotal, t.FinalTotal, sum(d.Quan*d.CostEach) DetailTotal, t.TenderTotal
from dba.posDetail d,
(select h.Transact, h.NetTotal, h.FinalTotal, sum(p.Tender) 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
group by t.Transact, t.NetTotal, t.FinalTotal, t.TenderTotal
ASKER
Error:
-------------------------- -
PixelPoint Service Utility
-------------------------- -
[Sybase][ODBC Driver][SQL Anywhere]Derived table 'T' has no name for column 4.
-------------------------- -
OK
-------------------------- -
--------------------------
PixelPoint Service Utility
--------------------------
[Sybase][ODBC Driver][SQL Anywhere]Derived table 'T' has no name for column 4.
--------------------------
OK
--------------------------
Oops.
select t.Transact, t.NetTotal, t.FinalTotal, sum(d.Quan*d.CostEach) DetailTotal, t.TenderTotal
from dba.posDetail d,
(select h.Transact, h.NetTotal, h.FinalTotal, sum(p.Tender) 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
group by t.Transact, t.NetTotal, t.FinalTotal, t.TenderTotal
ASKER
Very close!!!
I dont want to INCLUDE the sum of quan*costeach in the record has a prodtype of 101.
ie: and d.prodtype not in (101)
I dont want to INCLUDE the sum of quan*costeach in the record has a prodtype of 101.
ie: and d.prodtype not in (101)
select t.Transact, t.NetTotal, t.FinalTotal, sum(d.Quan*d.CostEach) DetailTotal, t.TenderTotal
from dba.posDetail d,
(select h.Transact, h.NetTotal, h.FinalTotal, sum(p.Tender) 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
You could just do d.prodtype != 101 but this way you can just add more prodtypes (comma-separated) if you find others you want to exclude. Also, this assumes prodtype is numeric - if it is a string field, 101 needs quotes around it.
ASKER
I want to exclude prodtype 101 from the value but when I put d.prodtype != 101 it doesn't even include anything with that record. I still want the other columns. Does that make sense?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
SWEEET!!! THANK YOU!!!
ASKER
Just for my understanding......is table "t" just an "on the fly view"?
Pretty much. The sub-query in the from clause in parens creates the "table" when it runs.
ASKER
Another small problem....
(
"select t.opendate as 'OpenDate', t.Transact as 'Transaction', t.NetTotal as 'HeaderNet', sum(d.Quan*d.CostEach) 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"
)
I want to return a 0 if sum(d.Quan*d.CostEach) as 'DetailNet' is Null. Any ideas?
(
"select t.opendate as 'OpenDate', t.Transact as 'Transaction', t.NetTotal as 'HeaderNet', sum(d.Quan*d.CostEach) 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"
)
I want to return a 0 if sum(d.Quan*d.CostEach) as 'DetailNet' is Null. Any ideas?
I'm not positive this will work but it's easy if so - wrap the sum in isnull(... , 0)
isnull(sum(d.Quan*d.CostEach), 0)
Open in new window