Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Query Syntax

Posted on 2015-01-13
17
Medium Priority
?
179 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 27

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 27

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 27

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 27

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 27

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 27

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 27

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

636 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