Solved

SQL Query Syntax

Posted on 2015-01-13
17
166 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
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!

 

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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

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…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

733 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