General information
Category:
Coding, syntax & commands
Title:
Unions in SQL Select
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?
Next
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