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

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
Asked:
Wildone63
  • 2
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
ralmadaCommented:
>>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
 
Wildone63Author Commented:
I still get multiple lines for a customer with both of these.
0
 
ralmadaCommented:
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

Open in new window

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
>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.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now