Solved

not a selected expression error ?

Posted on 2006-07-18
18
676 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 ?
 
0
Comment
Question by:tech_question
  • 5
  • 4
  • 3
  • +5
18 Comments
 
LVL 19

Expert Comment

by:actonwang
ID: 17130419
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.
0
 

Author Comment

by:tech_question
ID: 17130428
but I need the above query to be ordered by date though - if I remove it how can I order it
0
 
LVL 19

Expert Comment

by:actonwang
ID: 17130435
>>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')
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17130447
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')
0
 
LVL 19

Expert Comment

by:actonwang
ID: 17130457
or simply:

order by test_name, open_date
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17130477
order by 3,7
0
 

Author Comment

by:tech_question
ID: 17130498
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.
0
 

Author Comment

by:tech_question
ID: 17130520
I actually got it - to_date(test_open_date,'MM/DD/YYYY') - had to be - to_date(open_date, 'MM/DD/YYYY')
0
 
LVL 34

Expert Comment

by:johnsone
ID: 17130531
Why not:

order by test_name, test_open_date
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17130532
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')
0
 

Author Comment

by:tech_question
ID: 17130560
Angel - we have to put the alias in the order by clause - isn't it ?
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 17130582
>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')
0
 

Author Comment

by:tech_question
ID: 17130611
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')


 

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17130715

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')
0
 
LVL 34

Expert Comment

by:johnsone
ID: 17130749
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
0
 
LVL 32

Expert Comment

by:awking00
ID: 17130860
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'));
0
 
LVL 3

Expert Comment

by:Keyurkumar
ID: 17132890
you can just say trunc(test_open_date) that will just keep 07/01/2004 part of whole date
0
 
LVL 6

Expert Comment

by:JJSmith
ID: 17134420

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

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now