Solved

SQL Query Syntax

Posted on 2015-01-13
17
151 Views
Last Modified: 2015-01-15
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
Comment
Question by:triphen
  • 9
  • 7
17 Comments
 
LVL 26

Expert Comment

by:wilcoxon
ID: 40548227
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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 40548298
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
 

Author Comment

by:triphen
ID: 40550523
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
 

Author Comment

by:triphen
ID: 40550525
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
 

Author Comment

by:triphen
ID: 40550527
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
 
LVL 26

Expert Comment

by:wilcoxon
ID: 40550539
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
 

Author Comment

by:triphen
ID: 40550542
Error:

---------------------------
PixelPoint Service Utility
---------------------------
[Sybase][ODBC Driver][SQL Anywhere]Derived table 'T' has no name for column 4.
---------------------------
OK  
---------------------------
0
 
LVL 26

Expert Comment

by:wilcoxon
ID: 40550602
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:triphen
ID: 40550769
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
 
LVL 26

Expert Comment

by:wilcoxon
ID: 40551269
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
 

Author Comment

by:triphen
ID: 40551524
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
 
LVL 26

Accepted Solution

by:
wilcoxon earned 500 total points
ID: 40551665
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
 

Author Comment

by:triphen
ID: 40552483
SWEEET!!! THANK YOU!!!
0
 

Author Comment

by:triphen
ID: 40552485
Just for my understanding......is table "t" just an "on the fly view"?
0
 
LVL 26

Expert Comment

by:wilcoxon
ID: 40552509
Pretty much.  The sub-query in the from clause in parens creates the "table" when it runs.
0
 

Author Comment

by:triphen
ID: 40552774
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
 
LVL 26

Expert Comment

by:wilcoxon
ID: 40552789
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now