?
Solved

Derived Table Problem

Posted on 2010-01-06
5
Medium Priority
?
521 Views
Last Modified: 2012-05-08
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
Comment
Question by:Wildone63
  • 2
  • 2
5 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26193075
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

by:ralmada
ID: 26193757
>>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

by:Wildone63
ID: 26193981
I still get multiple lines for a customer with both of these.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 26194051
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
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 26194637
>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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
What we learned in Webroot's webinar on multi-vector protection.
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

809 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