Solved

Derived Table Problem

Posted on 2010-01-06
464 Views
I am trying to create a derived table with this query... The query runs fine in SQL but when I put this in Crystal Reports I get the error
"Each Calculated field must have an explicit name...

select custno as custno, company as Company, salesmn as Salesmn, SUM(invamt)  sum_invamt FROM
(
SELECT
armast.custno as custno,
arcust.company as company,
armast.salesmn as salesmn,
armast.invamt as invamt
from armast LEFT JOIN arcust ON arcust.custno = armast.custno
where invdte between '01/01/2009' and '12/31/2009' and artype <> 'R' and arstat <> 'V') d
group by custno, company, salesmn, invamt
0
Question by:Wildone63

LVL 75

Expert Comment

select custno as custno, company as Company, salesmn as Salesmn, SUM(invamt)  sum_invamt FROM
(
SELECT
armast.custno as custno,
arcust.company as company,
armast.salesmn as salesmn,
armast.invamt as invamt
from armast LEFT JOIN arcust ON arcust.custno = armast.custno
where invdte between '01/01/2009' and '12/31/2009' and artype <> 'R' and arstat <> 'V') d
group by custno, company, salesmn
0

LVL 41

Expert Comment

>>Each Calculated field must have an explicit name<<
Try using "AS" after the sum
select custno as custno, company as Company, salesmn as Salesmn, SUM(invamt)  as sum_invamt FROM
(
SELECT
armast.custno as custno,
arcust.company as company,
armast.salesmn as salesmn,
armast.invamt as invamt
from armast LEFT JOIN arcust ON arcust.custno = armast.custno
where invdte between '01/01/2009' and '12/31/2009' and artype <> 'R' and arstat <> 'V') d
group by custno, company, salesmn, invamt
0

LVL 1

Author Comment

I still get multiple lines for a customer with both of these.
0

LVL 41

Expert Comment

Well, that's a different problem :)
Of course you will get multiple lines per customer because your are grouping by
group by custno, company, salesmn
Maybe you're looking for these:

``````select custno as custno, max(company) as Company, max(salesmn) as Salesmn, SUM(invamt)  as sum_invamt FROM

(

SELECT

armast.custno as custno,

arcust.company as company,

armast.salesmn as salesmn,

armast.invamt as invamt

from armast LEFT JOIN arcust ON arcust.custno = armast.custno

where invdte between '01/01/2009' and '12/31/2009' and artype <> 'R' and arstat <> 'V') d

group by custno
``````
0

LVL 75

Accepted Solution

>I still get multiple lines for a customer with both of these.
you have to remove the 'invamt  ' from the group by

select custno as custno, company as Company, salesmn as Salesmn, SUM(invamt)  as sum_invamt FROM
(
SELECT
armast.custno as custno,
arcust.company as company,
armast.salesmn as salesmn,
armast.invamt as invamt
from armast LEFT JOIN arcust ON arcust.custno = armast.custno
where invdte between '01/01/2009' and '12/31/2009' and artype <> 'R' and arstat <> 'V') d
group by custno, company, salesmn
0

Featured Post

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Creating and Managing Databases with phpMyAdmin in cPanel.
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of demâ€¦
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filledâ€¦