Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

Solved

not a selected expression error ?

Posted on 2006-07-18

Oracle Database

1 solution

Medium Priority

685 Views

Last Modified: 2008-02-01

select distinct test_code,test_id,test_name,test_no, testentity, curr_status,
to_char(test_open_date,'mm/dd/yyyy') as open_date,
to_char(test_close_date,'mm/dd/yyyy') as close_date, recommended from vw_test
where test_id is not null AND curr_status ='Active'
AND test_open_date >= TO_DATE('07/01/2004','MM/DD/YYYY')
AND test_close_date <= TO_DATE('07/18/2006','MM/DD/YYYY')
order by test_name, to_date(test_open_date,'MM/DD/YYYY')

I am trying to run the above query - I get this error - "not a selected expression error " - any ideas why ?

Comment

Question by:tech_question

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

18 Comments

LVL 19

Expert Comment

by:actonwang

ID: 171304192006-07-18

ORA-01791:      not a SELECTed expression
Cause:      There is an incorrect ORDER BY item. The query is a SELECT DISTINCT query with an ORDER BY clause. In this context, all ORDER BY items must be constants, SELECT list expressions, or expressions whose operands are constants or SELECT list expressions.
Action:      Remove the inappropriate ORDER BY item from the SELECT list and retry the statement.

Author Comment

by:tech_question

ID: 171304282006-07-18

but I need the above query to be ordered by date though - if I remove it how can I order it

LVL 19

Expert Comment

by:actonwang

ID: 171304352006-07-18

>>order by test_name, to_date(test_open_date,'MM/DD/YYYY')

might try this:

order by test_name,to_char(test_open_date,'mm/dd/yyyy')

LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]

ID: 171304472006-07-18

select test_code,test_id,test_name,test_no, testentity, curr_status,
to_char(test_open_date,'mm/dd/yyyy') as open_date,
to_char(test_close_date,'mm/dd/yyyy') as close_date, recommended
from vw_test
where test_id is not null AND curr_status ='Active'
AND test_open_date >= TO_DATE('07/01/2004','MM/DD/YYYY')
AND test_close_date <= TO_DATE('07/18/2006','MM/DD/YYYY')
group by test_code,test_id,test_name,test_no, testentity, curr_status,
to_char(test_open_date,'mm/dd/yyyy') as open_date,
to_char(test_close_date,'mm/dd/yyyy') as close_date,
recommended
order by test_name, to_date(test_open_date,'MM/DD/YYYY')

LVL 19

Expert Comment

by:actonwang

ID: 171304572006-07-18

or simply:

order by test_name, open_date

LVL 14

Expert Comment

by:sathyagiri

ID: 171304772006-07-18

order by 3,7

Author Comment

by:tech_question

ID: 171304982006-07-18

Angel - I tried this query that you posted before I posted here the problem is I get not a selected expression error
at "AND test_open_date >= TO_DATE('07/01/2004','MM/DD/YYYY') "

Acton - if I just put open_date wouldn't it order by string rather than actual date because I am converting it to char.

Author Comment

by:tech_question

ID: 171305202006-07-18

I actually got it - to_date(test_open_date,'MM/DD/YYYY') - had to be - to_date(open_date, 'MM/DD/YYYY')

LVL 35

Expert Comment

by:johnsone

ID: 171305312006-07-18

Why not:

order by test_name, test_open_date

LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]

ID: 171305322006-07-18

I see:

select test_code,test_id,test_name,test_no, testentity, curr_status,
to_char(test_open_date,'mm/dd/yyyy') as open_date,
to_char(test_close_date,'mm/dd/yyyy') as close_date, recommended
from vw_test
where test_id is not null AND curr_status ='Active'
AND test_open_date >= TO_DATE('07/01/2004','MM/DD/YYYY')
AND test_close_date <= TO_DATE('07/18/2006','MM/DD/YYYY')
group by test_code,test_id,test_name,test_no, testentity, curr_status,
to_char(test_open_date,'yyyy/mm/dd') as open_date,
to_char(test_close_date,'yyyy/mm/dd') as close_date,
to_char(test_open_date,'mm/dd/yyyy') as open_date,
to_char(test_close_date,'mm/dd/yyyy') as close_date,
recommended
order by test_name, to_date(test_open_date,'YYYY/MM/DD')

Author Comment

by:tech_question

ID: 171305602006-07-18

Angel - we have to put the alias in the order by clause - isn't it ?

LVL 143

Accepted Solution

by:Guy Hengel [angelIII / a3]

Guy Hengel [angelIII / a3] earned 2000 total points

ID: 171305822006-07-18

>Angel - we have to put the alias in the order by clause - isn't it ?
no,...

select test_code,test_id,test_name,test_no, testentity, curr_status,
to_char(test_open_date,'mm/dd/yyyy') as open_date,
to_char(test_close_date,'mm/dd/yyyy') as close_date, recommended
from vw_test
where test_id is not null AND curr_status ='Active'
AND test_open_date >= TO_DATE('07/01/2004','MM/DD/YYYY')
AND test_close_date <= TO_DATE('07/18/2006','MM/DD/YYYY')
group by test_code,test_id,test_name,test_no, testentity, curr_status,
to_char(test_open_date,'yyyy/mm/dd') ,
to_char(test_close_date,'yyyy/mm/dd') ,
to_char(test_open_date,'mm/dd/yyyy') ,
to_char(test_close_date,'mm/dd/yyyy') ,
recommended
order by test_name, to_date(test_open_date,'YYYY/MM/DD')

Author Comment

by:tech_question

ID: 171306112006-07-18

I am getting an error "invalid column name" if I do not put the alias name i.e. I have to change this
order by test_name, to_date(test_open_date,'YYYY/MM/DD') to this order by test_name, to_date(open_date,'YYYY/MM/DD')

LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]

ID: 171307152006-07-18

select test_code,test_id,test_name,test_no, testentity, curr_status,
to_char(test_open_date,'mm/dd/yyyy') as open_date,
to_char(test_close_date,'mm/dd/yyyy') as close_date, recommended
from vw_test
where test_id is not null AND curr_status ='Active'
AND test_open_date >= TO_DATE('07/01/2004','MM/DD/YYYY')
AND test_close_date <= TO_DATE('07/18/2006','MM/DD/YYYY')
group by test_code,test_id,test_name,test_no, testentity, curr_status,
to_char(test_open_date,'yyyy/mm/dd') ,
to_char(test_close_date,'yyyy/mm/dd') ,
to_char(test_open_date,'mm/dd/yyyy') ,
to_char(test_close_date,'mm/dd/yyyy') ,
recommended
order by test_name, to_char(test_open_date,'YYYY/MM/DD')

LVL 35

Expert Comment

by:johnsone

ID: 171307492006-07-18

According to an earlier post, it has to be ordered by date. Why are we taking the date, converting it to a string, then back to a date to do the sorting?

Can't we just use the date field itself?

The other question, is what is the group by getting you? I see no aggregate function. Therefore, you are accomplishing a distinct, aren't you?

select distinct test_code,test_id,test_name,test_no, testentity, curr_status,
to_char(test_open_date,'mm/dd/yyyy') as open_date,
to_char(test_close_date,'mm/dd/yyyy') as close_date, recommended
from vw_test
where test_id is not null AND curr_status ='Active'
AND test_open_date >= TO_DATE('07/01/2004','MM/DD/YYYY')
AND test_close_date <= TO_DATE('07/18/2006','MM/DD/YYYY')
order by test_name, test_open_date

LVL 32

Expert Comment

by:awking00

ID: 171308602006-07-18

The reason for the error is not due to the use of the alias, it's because test_open_date is apparently of date data type and can't be used with to_date(test_open_date, ...) while open_date is the alias for to_char(test_open_date,'mm/dd/yyyy') which is of character data type and can be converted using to_date(...).
I still don't understand why you don't just use the suggestion already presented of -
order by test_name, test_open_date;
What you are doing using the alias is tantamount to doing this -
order by test_name, to_date(to_char(test_open_date,'mm/dd/yyyy'));

LVL 3

Expert Comment

by:Keyurkumar

ID: 171328902006-07-18

you can just say trunc(test_open_date) that will just keep 07/01/2004 part of whole date

LVL 6

Expert Comment

by:JJSmith

ID: 171344202006-07-18

if you are selecting:

to_char(test_open_date,'mm/dd/yyyy')

then you can't order by;

to_date(test_open_date,'MM/DD/YYYY')

because THAT is not in your result set or base table!!

If you insist on re-formatting a retrieved column 'to_char(test_open_date,'mm/dd/yyyy') as open_date' - but you want to sort on that column, then simply use the base column name 'test_open_date'

so leaving your query as it stands:- change your order clause to:

order by test_name, test_open_date

Cheers
JJ

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to use Access Control Lists in Oracle

Article by: sdstuber

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…

Oracle Database

Downloading Oracle Log Files in AWS RDS

Article by: Doug

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface. I made a script to download the Alert Log, look for errors, and email me the trace files. In this article I'll describe what I did and share my script.

Copy Users and their Permissions between Databases

Video by: Steve

This video shows how to copy a database user from one database to another user DBMS_METADATA. It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

Oracle Database

Executing a SQL Script from a Unix Shell Script and Passing Parameters

Video by: Steve

This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.