Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-01785: Order by item must be the number of a select-list expression on running classic report

VS121Feb 28 2024 — edited Feb 28 2024

Hi,

I have code that’s generating a dynamic query to select parameters from multiple Shuttles and display a report. However on running the query, it throws an error . This is APEX 21.

ORA-01785: Order by item must be the number of a select-list expression

Generated query:

select FREQUENCY, AVERAGE_DEPTH, SPECIES_NAME from fsh_general_sampling_vw where 1 = 1 and (fsh_survey_id in (select column_value from table(apex_string.split('', ':'))) or :P251_FSH_SURVEY_ID is null ) and (gis_id in (select column_value from table(apex_string.split('', ':'))) or :P251_FACILITY is null ) and (species_id in (select column_value from table(apex_string.split('553:590', ':'))) or :P251_SPECIES_ID is null )

However APEX does its own thing and adds order by 7 resulting in the error since there are only 3 fields. Not sure where the 7 is coming from! Also, this does not happen to everyone.. only to a few.

If its not very visible in picture above.. this is the sql generated by APEX

select i.*, count(*) over () as APEX$TOTAL_ROW_COUNT from (select * from(select /*+ qb_name(apex$inner) */* from(select FREQUENCY, AVERAGE_DEPTH, SPECIES_NAME from fsh_general_sampling_vw where 1 = 1 and (fsh_survey_id in (select column_value from table(apex_string.split('', ':'))) or :P251_FSH_SURVEY_ID is null ) and (gis_id in (select column_value from table(apex_string.split('', ':'))) or :P251_FACILITY is null ) and (species_id in (select column_value from table(apex_string.split('553:590', ':'))) or :P251_SPECIES_ID is null ) )d )i )i where 1=1 order by 7 asc nulls last,1 asc nulls last

Thanks

Vidya

This post has been answered by fac586 on Feb 28 2024
Jump to Answer
Comments
Post Details
Added on Feb 28 2024
5 comments
372 views