not a selected expression error ?

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 ?
 
tech_questionAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>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
 
actonwangCommented:
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
 
tech_questionAuthor Commented:
but I need the above query to be ordered by date though - if I remove it how can I order it
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Access It Now
 
actonwangCommented:
>>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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
actonwangCommented:
or simply:

order by test_name, open_date
0
 
sathyagiriCommented:
order by 3,7
0
 
tech_questionAuthor Commented:
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
 
tech_questionAuthor Commented:
I actually got it - to_date(test_open_date,'MM/DD/YYYY') - had to be - to_date(open_date, 'MM/DD/YYYY')
0
 
johnsoneSenior Oracle DBACommented:
Why not:

order by test_name, test_open_date
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
tech_questionAuthor Commented:
Angel - we have to put the alias in the order by clause - isn't it ?
0
 
tech_questionAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

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
 
johnsoneSenior Oracle DBACommented:
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
 
awking00Commented:
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
 
KeyurkumarCommented:
you can just say trunc(test_open_date) that will just keep 07/01/2004 part of whole date
0
 
JJSmithCommented:

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.