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