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

Problem with Union

I having trouble with a sub-query in a larger call. The calls on either side of the union work fine on their own, but when I combine them with the Union I get a ORA-00933 error.

Here is the call:
SELECT
   test.ts_test_id as test_id,
   test.ts_steps as total_steps,
   max(run.rn_run_id) as run_id
FROM test, run
WHERE  test.ts_test_id = run.rn_test_id
group by test.ts_test_id, test.ts_steps
ORDER BY test_id, run_id DESC
UNION
SELECT
   test.ts_test_id as test_id,
   test.ts_steps as total_steps,
   max(run.rn_run_id) as run_id
FROM test, run
WHERE  test.ts_test_id = run.rn_test_id
and ts_test_id  in  (SELECT distinct ds_test_id FROM DESSTEPS where ds_link_test > 0)
group by test.ts_test_id, test.ts_steps
ORDER BY test_id, run_id DESC


Any thoughts? I feel like I must be doing something utterly stupid here.:)

Thanks in Advance!

Shane
0
NIS_Longfellow
Asked:
NIS_Longfellow
  • 3
  • 2
  • 2
1 Solution
 
NIS_LongfellowAuthor Commented:
Just wanted to add that I know that the call here as it stands doesn't look like the two calls would return anything different, but the larger call causes (should...hopefully) the results to be different.

Shane
0
 
paquicubaCommented:
Order BY cannot be present twice
0
 
paquicubaCommented:
See this simple example:

PAQUI@PROD > SELECT DUMMY FROM DUAL ORDER BY 1 UNION SELECT DUMMY FROM DUAL ORDER BY 1;
SELECT DUMMY FROM DUAL ORDER BY 1 UNION SELECT DUMMY FROM DUAL ORDER BY 1
                                  *
ERROR at line 1:
ORA-00933: SQL command not properly ended


Elapsed: 00:00:00.01
PAQUI@PROD > SELECT DUMMY FROM DUAL UNION SELECT DUMMY FROM DUAL ORDER BY 1;

D
-
X

Elapsed: 00:00:00.00
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
actonwangCommented:
>> Order BY cannot be present twice

     You only can put "order by" in the end not IN the query. "Order BY" always is in the end of query.
0
 
paquicubaCommented:
NIS_Longfellow states << The calls on either side of the union >>

I'm just telling him that the 'Order By' clause cannot be used on both sides of the UNION, and I think that my example clearly shows him why he is getting such error.
0
 
actonwangCommented:
that is right. You said it very clearly.
0
 
NIS_LongfellowAuthor Commented:
I knew it had to be something simple....Thanks
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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