Oracle ORDER BY in UNION
up vote
0
down vote
favorite
I have this query:
SELECT p.prenume||' '||p.nume ||' '||LOWER(REVERSE(c.titlu_curs)) AS "Info"
from profesori p JOIN didactic d ON d.id_prof = p.id_prof
JOIN cursuri c ON d.id_curs = c.id_curs
UNION
SELECT p.prenume||' '||p.nume||' ' AS "Info" from profesori p
JOIN didactic d ON p.id_prof NOT IN
(SELECT id_prof from didactic)
JOIN cursuri c ON c.id_curs NOT IN (SELECT id_curs from didactic) ORDER BY p.nume
How can I ORDER BY p.nume ? I get this error 'P"."NUME": invalid identifier'
I know I can order by Alias "Info" , but how can I order only by p.nume ?
oracle
edited Nov 11 at 6:26
Madhur Bhaiya
19.2k62236
asked Nov 11 at 0:16
Robi_d
41
|
show 2 more comments
up vote
0
down vote
favorite
I have this query:
SELECT p.prenume||' '||p.nume ||' '||LOWER(REVERSE(c.titlu_curs)) AS "Info"
from profesori p JOIN didactic d ON d.id_prof = p.id_prof
JOIN cursuri c ON d.id_curs = c.id_curs
UNION
SELECT p.prenume||' '||p.nume||' ' AS "Info" from profesori p
JOIN didactic d ON p.id_prof NOT IN
(SELECT id_prof from didactic)
JOIN cursuri c ON c.id_curs NOT IN (SELECT id_curs from didactic) ORDER BY p.nume
How can I ORDER BY p.nume ? I get this error 'P"."NUME": invalid identifier'
I know I can order by Alias "Info" , but how can I order only by p.nume ?
oracle
edited Nov 11 at 6:26
Madhur Bhaiya
19.2k62236
asked Nov 11 at 0:16
Robi_d
41
try SELECT * FROM (your query) ORDER BY Info
– BigMike
Nov 11 at 0:20
I get the same error .
– Robi_d
Nov 11 at 0:25
select also p.nume as "Nume" in your query execute in sql*plus (or any other program) the query and check how Oracle is naming the column, then add the order by.
– BigMike
Nov 11 at 0:30
I can't do that , its for a task . I need to select only 'p.prenume||' '||p.nume ||' '||LOWER(REVERSE(c.titlu_curs))' . I can't select p.nume too .. this is the problem
– Robi_d
Nov 11 at 0:32
1
Thanks man , OUTER JOIN was the solution .
– Robi_d
Nov 11 at 0:49
|
show 2 more comments
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have this query:
SELECT p.prenume||' '||p.nume ||' '||LOWER(REVERSE(c.titlu_curs)) AS "Info"
from profesori p JOIN didactic d ON d.id_prof = p.id_prof
JOIN cursuri c ON d.id_curs = c.id_curs
UNION
SELECT p.prenume||' '||p.nume||' ' AS "Info" from profesori p
JOIN didactic d ON p.id_prof NOT IN
(SELECT id_prof from didactic)
JOIN cursuri c ON c.id_curs NOT IN (SELECT id_curs from didactic) ORDER BY p.nume
How can I ORDER BY p.nume ? I get this error 'P"."NUME": invalid identifier'
I know I can order by Alias "Info" , but how can I order only by p.nume ?
oracle
edited Nov 11 at 6:26
Madhur Bhaiya
19.2k62236
asked Nov 11 at 0:16
Robi_d
41
I have this query:
SELECT p.prenume||' '||p.nume ||' '||LOWER(REVERSE(c.titlu_curs)) AS "Info"
from profesori p JOIN didactic d ON d.id_prof = p.id_prof
JOIN cursuri c ON d.id_curs = c.id_curs
UNION
SELECT p.prenume||' '||p.nume||' ' AS "Info" from profesori p
JOIN didactic d ON p.id_prof NOT IN
(SELECT id_prof from didactic)
JOIN cursuri c ON c.id_curs NOT IN (SELECT id_curs from didactic) ORDER BY p.nume
How can I ORDER BY p.nume ? I get this error 'P"."NUME": invalid identifier'
I know I can order by Alias "Info" , but how can I order only by p.nume ?
oracle
oracle
edited Nov 11 at 6:26
Madhur Bhaiya
19.2k62236
asked Nov 11 at 0:16
Robi_d
41
edited Nov 11 at 6:26
Madhur Bhaiya
19.2k62236
asked Nov 11 at 0:16
Robi_d
41
edited Nov 11 at 6:26
Madhur Bhaiya
19.2k62236
edited Nov 11 at 6:26
Madhur Bhaiya
19.2k62236
edited Nov 11 at 6:26
Madhur Bhaiya
19.2k62236
19.2k62236
asked Nov 11 at 0:16
Robi_d
41
asked Nov 11 at 0:16
Robi_d
41
asked Nov 11 at 0:16
Robi_d
41
41
try SELECT * FROM (your query) ORDER BY Info
– BigMike
Nov 11 at 0:20
I get the same error .
– Robi_d
Nov 11 at 0:25
select also p.nume as "Nume" in your query execute in sql*plus (or any other program) the query and check how Oracle is naming the column, then add the order by.
– BigMike
Nov 11 at 0:30
I can't do that , its for a task . I need to select only 'p.prenume||' '||p.nume ||' '||LOWER(REVERSE(c.titlu_curs))' . I can't select p.nume too .. this is the problem
– Robi_d
Nov 11 at 0:32
1
Thanks man , OUTER JOIN was the solution .
– Robi_d
Nov 11 at 0:49
|
show 2 more comments
try SELECT * FROM (your query) ORDER BY Info
– BigMike
Nov 11 at 0:20
I get the same error .
– Robi_d
Nov 11 at 0:25
select also p.nume as "Nume" in your query execute in sql*plus (or any other program) the query and check how Oracle is naming the column, then add the order by.
– BigMike
Nov 11 at 0:30
I can't do that , its for a task . I need to select only 'p.prenume||' '||p.nume ||' '||LOWER(REVERSE(c.titlu_curs))' . I can't select p.nume too .. this is the problem
– Robi_d
Nov 11 at 0:32
1
Thanks man , OUTER JOIN was the solution .
– Robi_d
Nov 11 at 0:49
try SELECT * FROM (your query) ORDER BY Info
– BigMike
Nov 11 at 0:20
try SELECT * FROM (your query) ORDER BY Info
– BigMike
Nov 11 at 0:20
I get the same error .
– Robi_d
Nov 11 at 0:25
I get the same error .
– Robi_d
Nov 11 at 0:25
select also p.nume as "Nume" in your query execute in sql*plus (or any other program) the query and check how Oracle is naming the column, then add the order by.
– BigMike
Nov 11 at 0:30
select also p.nume as "Nume" in your query execute in sql*plus (or any other program) the query and check how Oracle is naming the column, then add the order by.
– BigMike
Nov 11 at 0:30
I can't do that , its for a task . I need to select only 'p.prenume||' '||p.nume ||' '||LOWER(REVERSE(c.titlu_curs))' . I can't select p.nume too .. this is the problem
– Robi_d
Nov 11 at 0:32
I can't do that , its for a task . I need to select only 'p.prenume||' '||p.nume ||' '||LOWER(REVERSE(c.titlu_curs))' . I can't select p.nume too .. this is the problem
– Robi_d
Nov 11 at 0:32
1
1
Thanks man , OUTER JOIN was the solution .
– Robi_d
Nov 11 at 0:49
Thanks man , OUTER JOIN was the solution .
– Robi_d
Nov 11 at 0:49
|
show 2 more comments
1 Answer
1
active
oldest
votes
up vote
1
down vote
How can I ORDER BY p.nume ?
With UNION queries we can only order by named columns in the projection of the first subquery. Your query only has nume as a part of a concatenated column, hence the ORA-00904 error.
Sorting the UNION query by the columns in the result set means you can sort by your column alias...
order by "Info"
...which means it will effectively sort by prenume, nume, LOWER(REVERSE(c.titlu_curs)).
The alternative solution - useful when you're not aliasing the sorting column - is to use the positional notation
order by 1
answered Nov 11 at 10:06
APC
117k15116229
1
Alternatively,order by 1would also work (meaning "sort by the first column" (which is "Info"). Though, that's probably not the best idea because people add columns and remove them, so "1" doesn't have to be the same column as it once was. I'd suggestorder by "Info".
– Littlefoot
Nov 11 at 13:03
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53244696%2foracle-order-by-in-union%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
How can I ORDER BY p.nume ?
With UNION queries we can only order by named columns in the projection of the first subquery. Your query only has nume as a part of a concatenated column, hence the ORA-00904 error.
Sorting the UNION query by the columns in the result set means you can sort by your column alias...
order by "Info"
...which means it will effectively sort by prenume, nume, LOWER(REVERSE(c.titlu_curs)).
The alternative solution - useful when you're not aliasing the sorting column - is to use the positional notation
order by 1
answered Nov 11 at 10:06
APC
117k15116229
1
Alternatively,order by 1would also work (meaning "sort by the first column" (which is "Info"). Though, that's probably not the best idea because people add columns and remove them, so "1" doesn't have to be the same column as it once was. I'd suggestorder by "Info".
– Littlefoot
Nov 11 at 13:03
add a comment |
up vote
1
down vote
How can I ORDER BY p.nume ?
With UNION queries we can only order by named columns in the projection of the first subquery. Your query only has nume as a part of a concatenated column, hence the ORA-00904 error.
Sorting the UNION query by the columns in the result set means you can sort by your column alias...
order by "Info"
...which means it will effectively sort by prenume, nume, LOWER(REVERSE(c.titlu_curs)).
The alternative solution - useful when you're not aliasing the sorting column - is to use the positional notation
order by 1
answered Nov 11 at 10:06
APC
117k15116229
1
Alternatively,order by 1would also work (meaning "sort by the first column" (which is "Info"). Though, that's probably not the best idea because people add columns and remove them, so "1" doesn't have to be the same column as it once was. I'd suggestorder by "Info".
– Littlefoot
Nov 11 at 13:03
add a comment |
up vote
1
down vote
up vote
1
down vote
How can I ORDER BY p.nume ?
With UNION queries we can only order by named columns in the projection of the first subquery. Your query only has nume as a part of a concatenated column, hence the ORA-00904 error.
Sorting the UNION query by the columns in the result set means you can sort by your column alias...
order by "Info"
...which means it will effectively sort by prenume, nume, LOWER(REVERSE(c.titlu_curs)).
The alternative solution - useful when you're not aliasing the sorting column - is to use the positional notation
order by 1
answered Nov 11 at 10:06
APC
117k15116229
How can I ORDER BY p.nume ?
With UNION queries we can only order by named columns in the projection of the first subquery. Your query only has nume as a part of a concatenated column, hence the ORA-00904 error.
Sorting the UNION query by the columns in the result set means you can sort by your column alias...
order by "Info"
...which means it will effectively sort by prenume, nume, LOWER(REVERSE(c.titlu_curs)).
The alternative solution - useful when you're not aliasing the sorting column - is to use the positional notation
order by 1
answered Nov 11 at 10:06
APC
117k15116229
edited Nov 11 at 13:12
answered Nov 11 at 10:06
APC
117k15116229
answered Nov 11 at 10:06
APC
117k15116229
answered Nov 11 at 10:06
APC
117k15116229
117k15116229
1
Alternatively,order by 1would also work (meaning "sort by the first column" (which is "Info"). Though, that's probably not the best idea because people add columns and remove them, so "1" doesn't have to be the same column as it once was. I'd suggestorder by "Info".
– Littlefoot
Nov 11 at 13:03
add a comment |
1
Alternatively,order by 1would also work (meaning "sort by the first column" (which is "Info"). Though, that's probably not the best idea because people add columns and remove them, so "1" doesn't have to be the same column as it once was. I'd suggestorder by "Info".
– Littlefoot
Nov 11 at 13:03
1
1
Alternatively,
order by 1 would also work (meaning "sort by the first column" (which is "Info"). Though, that's probably not the best idea because people add columns and remove them, so "1" doesn't have to be the same column as it once was. I'd suggest order by "Info".– Littlefoot
Nov 11 at 13:03
Alternatively,
order by 1 would also work (meaning "sort by the first column" (which is "Info"). Though, that's probably not the best idea because people add columns and remove them, so "1" doesn't have to be the same column as it once was. I'd suggest order by "Info".– Littlefoot
Nov 11 at 13:03
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53244696%2foracle-order-by-in-union%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
try SELECT * FROM (your query) ORDER BY Info
– BigMike
Nov 11 at 0:20
I get the same error .
– Robi_d
Nov 11 at 0:25
select also p.nume as "Nume" in your query execute in sql*plus (or any other program) the query and check how Oracle is naming the column, then add the order by.
– BigMike
Nov 11 at 0:30
I can't do that , its for a task . I need to select only 'p.prenume||' '||p.nume ||' '||LOWER(REVERSE(c.titlu_curs))' . I can't select p.nume too .. this is the problem
– Robi_d
Nov 11 at 0:32
1
Thanks man , OUTER JOIN was the solution .
– Robi_d
Nov 11 at 0:49