General information
Category:
Coding, syntax & commands
I think you have to use two queries:
after your first select sql statement, try:
select cnt(jobs) as jobs;
from JobTot;
into cursor JobTot2
Florin
>I have two identical tables, one for jobs we are currently working on called OpenJobs >and another containing invoiced jobs called DoneJobs.
>There are many times when I need to get the total number of jobs that match some >criteria from both tables. In the following example, I simply need the total number of >jobs within a date range:
>select cnt(Job) as Jobs from OpenJobs;
>where between(GetJob("MaxDate", Job), BDate, EDate) and !deleted();
>union all select cnt(Job) as Jobs from DoneJobs;
>where between(GetJob("MaxDate", Job), BDate, EDate) and !deleted();
>group by 1;
>into cursor JobTot
>I am trying to get a single record with the total number of jobs. The group by clause >here is illegal. You can't group by an aggragate column. So I add an additional field >to both selects: "X" as X and group by that column. It produces two records one from >OpenJobs and one from DoneJobs.
>What syntax can I use to get one record with the total of both tables?
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only