• Status: Solved
• Priority: Medium
• Security: Public
• Views: 552

# Derived Table Problem

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
Wildone63
• 2
• 2
1 Solution

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

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

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

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

>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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.