1

Actual query looks something like this:

select to_char(first_name||'('||substr(last_name,0,1)||')') from employees order by first_name union select to_char('There are total ' ||count(job_id)||' '||lower(job_id)||'s') from employees group by job_id order by count(job_id),job_id;

individually both queries work but when I add Union to combine the result, I get following errorm in sql developer:

ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause:
*Action: Error at Line: 2 Column: 1

I want to show all the records from the first query then all the records from the second query one after another, each set sorted in the order given above.

Improve this question
5
  • 1
    Can you provide data and the results you want? That query may not be the best way to do what you are trying to do. Commented Mar 13, 2017 at 12:33
  • I just want to combine the records from first query and second query one after another Commented Mar 13, 2017 at 12:45
  • Add 1 to first select and 2 to second. Order by that column. Commented Mar 13, 2017 at 12:53
  • Unrelated, but: the to_char() call does not make any sense. first_name and last_name are already varchars so there is no need to convert them to a varchar using to_char() Commented Mar 13, 2017 at 13:05
  • Looks like your original error is that you have an order by in the first query still. When using union/union all, there is only one order by at the end. Read the answer below, it is pretty much spot on. Commented Mar 13, 2017 at 14:04

3 Answers 3

Reset to default
2

The underlying obstacle is that a SQL dataset is inherently UNordered. As soon as you UNION two datasets you lose any guarantee of ordering previously present.

You can often get away with the following structure, but it is STILL not guaranteed...

SELECT * FROM ( ( select to_char(first_name||'('||substr(last_name,0,1)||')') from employees order by first_name ) Individuals UNION ALL ( select to_char('There are total '||count(job_id)||' '||lower(job_id)||'s') from employees group by job_id order by count(job_id),job_id ) Totals ) Combined ;

In practice you Often get what you want with this structure.

The brackets ensure the ordering is done before the UNION ALL and the database engine PROBABLY won't screw with the ordering.

But it MIGHT. The only way to Guarantee the order of the results is to put an ORDER BY on the outer query. Something like the following normally works fairly well...

SELECT rowdata FROM ( ( select 1 AS myset, rownum AS myrow, to_char(first_name||'('||substr(last_name,0,1)||')') AS rowdata from employees order by first_name ) Individuals UNION ALL ( select 2 AS myset, rownum AS myrow, to_char('There are total '||count(job_id)||' '||lower(job_id)||'s') from employees group by job_id order by count(job_id),job_id ) Totals ) Combined ORDER BY myset, myrow ;

I'm on my phone, so there may be typos, but the sentiment is there...

Use ROWNUM or ROW_NUMBER() to generate an extra field in your data sets. Then union them. Then order by your new field(s).

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

These solutions do not work. The approach causes ORA-00907: missing right parenthesis. This is because ORDER BY is not allowed in the sub-queries of SET operations, which is clearly documented..
2

it should be work 

 select to_char(first_name||'('||substr(last_name,0,1)||')') from (select first_name, last_name from employees order by first_name) union select to_char('There are total '||cnt||' '||lower(job_id)||'s') from (select count(job_id) cnt, job_id from employees group by job_id order by count(job_id), job_id)
Improve this answer

2 Comments

All answer kind of point me in the right direction and since I could only accept one answer as accepted answer. Have selected the one with most details. Thanks a lot of quick answers guys.
Vecchiasignora answer works. The accepted answer does not work. To order before the union, wrap it in another select statement. Just used principle to order the rows and "fetch first 10 rows only" of one query.
2

The way to do that is typically by supplying a column that identifies the "union group" in which a row should appear:

select name from ( select first_name||'('||substr(last_name,0,1)||')' as name, 1 as sort_order, 0 as counter, 0 as job_id from employees union all select 'There are total '||count(job_id)||' '||lower(job_id)||'s', 2, count(job_id), job_id from employees group by job_id ) t order by sort_order, counter, job_id, name;

As you also want to sort the rows inside the second part by a different column, you need to create dummy columns for that, so that you can apply the correct order by on the overall result.

I also remove the useless to_char() calls from your query.

If job_id is in fact a varchar column (which the lower(job_id) seems to indicate), then you need to replace 0 as job_id with some character constant. It doesn't really matter which value it is.

Online example: http://rextester.com/ZUPQ98121

Improve this answer

2 Comments

@MatBailie: you are right, the counter needs to be the second sort column (which doesn't hurt for the first part as that value is the same for all rows there and thus they will be sorted by name)
@MatBailie: As I said, that doesn't matter because the job_id is 0 for all rows in the first set.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.