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 ?

edited Nov 11 at 6:26

Madhur Bhaiya

17.7k62236

asked Nov 11 at 0:16

Robi_d

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

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 ?

edited Nov 11 at 6:26

Madhur Bhaiya

17.7k62236

asked Nov 11 at 0:16

Robi_d

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

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 ?

edited Nov 11 at 6:26

Madhur Bhaiya

17.7k62236

asked Nov 11 at 0:16

Robi_d

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

17.7k62236

asked Nov 11 at 0:16

Robi_d

edited Nov 11 at 6:26

Madhur Bhaiya

17.7k62236

asked Nov 11 at 0:16

Robi_d

edited Nov 11 at 6:26

Madhur Bhaiya

17.7k62236

edited Nov 11 at 6:26

Madhur Bhaiya

17.7k62236

edited Nov 11 at 6:26

Madhur Bhaiya

17.7k62236

asked Nov 11 at 0:16

Robi_d

asked Nov 11 at 0:16

Robi_d

asked Nov 11 at 0:16

Robi_d

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

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

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

edited Nov 11 at 13:12

answered Nov 11 at 10:06

APC

116k15114227

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

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
);

);

draft saved

draft discarded

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

Name

Required, but never shown

1 Answer
1

active

oldest

votes

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

edited Nov 11 at 13:12

answered Nov 11 at 10:06

APC

116k15114227

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

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

edited Nov 11 at 13:12

answered Nov 11 at 10:06

APC

116k15114227

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

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

edited Nov 11 at 13:12

answered Nov 11 at 10:06

APC

116k15114227

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

edited Nov 11 at 13:12

answered Nov 11 at 10:06

APC

116k15114227

edited Nov 11 at 13:12

answered Nov 11 at 10:06

APC

116k15114227

answered Nov 11 at 10:06

APC

116k15114227

answered Nov 11 at 10:06

APC

116k15114227

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

add a comment |

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 |

draft saved

draft discarded

draft saved

draft discarded

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Odtnhj