?
Solved

SQL Query Syntax

Posted on 2015-01-13
17
Medium Priority
?
176 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Python: Series & Data Frames With Pandas

Learn the basics of Python’s pandas library of series & data frames and how we can use these tools for data manipulation.

 

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
 

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 2000 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

Docker-Compose to Simplify Multi-Container Builds

Our veteran DevOps Author takes you through how to build a multi-container environment, managed with a single utility in order to simplify your deployments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses
Course of the Month7 days, 20 hours left to enroll

765 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