[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 185
  • Last Modified:

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
0
triphen
Asked:
triphen
  • 9
  • 7
1 Solution
 
wilcoxonCommented:
I think this will work.  If not, it is the portion dealing with Detail (not 100% sure you can do sum on product).
select h.Trans, h.NetTotal, h.FinalTotal, sum(d.Quan*d.CostEach) DetailTotal, sum(p.Tender) TenderTotal
from Header h, Detail d, Payments p
where h.Trans = d.Trans
and h.Trans = p.Trans
group by h.Trans, h.NetTotal, h.FinalTotal

Open in new window

0
 
Pratima PharandeCommented:
Select h1.Trans, h1.NetTotal, h1.FinalTotal,X.DetailTotal, 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
0
 
triphenAuthor Commented:
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"

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
triphenAuthor Commented:
Pratima Pharande,

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

Open in new window



Everything is good except that my TenderTotal is multiplied by the number of entries I have in the howpayapproved table. (ie: Payments)
0
 
triphenAuthor Commented:
wilcoxon,

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

Open in new window



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)
0
 
wilcoxonCommented:
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

Open in new window

0
 
triphenAuthor Commented:
Error:

---------------------------
PixelPoint Service Utility
---------------------------
[Sybase][ODBC Driver][SQL Anywhere]Derived table 'T' has no name for column 4.
---------------------------
OK  
---------------------------
0
 
wilcoxonCommented:
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

Open in new window

0
 
triphenAuthor Commented:
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)
0
 
wilcoxonCommented:
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

Open in new window

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.
0
 
triphenAuthor Commented:
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?
0
 
wilcoxonCommented:
Odd.  It should be working.  It should only be excluding the row in posDetail where prodtype = 101.  Hmm.  Are there entries where all rows in posDetail are prodtype = 101 (eg no rows for a given Transact where prodtype != 101)?  If so, that's the problem.  Try this.
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

Open in new window

0
 
triphenAuthor Commented:
SWEEET!!! THANK YOU!!!
0
 
triphenAuthor Commented:
Just for my understanding......is table "t" just an "on the fly view"?
0
 
wilcoxonCommented:
Pretty much.  The sub-query in the from clause in parens creates the "table" when it runs.
0
 
triphenAuthor Commented:
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?
0
 
wilcoxonCommented:
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

0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now