SQL compilation error: Unsupported subquery type cannot be evaluated - Function Calling in SELECT Clause

Question

-Hi ,

I have created a scalar function which is having subquery in the FROM clause which is given below. when this function is getting called from another view's select query throws errors as

" SQL compilation error: Unsupported subquery type cannot be evaluated "

when i passed with hardcoded value its working and with single select also working fine.

create or replace function get_entry_value  (p_element_type_id number, p_effective_date  varchar,p_display_sequence number, p_element_entry_id number) 
RETURNS table (meaning varchar)   
as 'select meaning
   from   hr_lookups bb,
   (select eev.screen_entry_value, inv.lookup_type, inv.element_type_id, eev.element_entry_id, inv.effective_start_date, inv.effective_end_date, eev.effective_start_date, eev.effective_end_date
     from   pay_element_entry_values_f eev,
            pay_input_values_f inv
       where  P_ELEMENT_TYPE_ID = inv.element_type_id 
       and    inv.display_sequence  = P_DISPLAY_SEQUENCE
       and    eev.input_value_id    = inv.input_value_id
       and    eev.element_entry_id  = P_ELEMENT_ENTRY_ID
       and    P_EFFECTIVE_DATE between inv.effective_start_date and inv.effective_end_date
       and    P_EFFECTIVE_DATE between eev.effective_start_date and eev.effective_end_date ) aa
    where  bb.lookup_type = aa.screen_entry_value
   and    bb.lookup_code =aa.lookup_type';

Calling View's query

select ele.assignment_id
,      ele.element_entry_id
,      ele.element_link_id
,      etype.element_type_id
,      ele.effective_start_date
,      ele.effective_end_date
,      ele.comment_id
,      etype_tl.element_name
,      etype_tl.description element_description
,      pec_tl.classification_name
,      decode(etype.processing_type, 'R','Recurring','N', 'Non Recurring','Not Specified'||etype.processing_type) Processing
,      ele.creation_date Creation_date
,      ele.last_update_date Update_Date
,     get_pay_value_number(etype.element_type_id,ele.effective_start_date, ele.element_entry_id) Pay_Value
,     get_input_value_name(etype.element_type_id,ele.effective_start_date,1) input_name1
--,     get_entry_value(etype.element_type_id,ele.effective_start_date,1,ele.element_entry_id) input_value1
,     get_entry_value_number(etype.element_type_id,ele.effective_start_date,1,ele.element_entry_id) input_value_num1
,     get_input_value_name(etype.element_type_id,ele.effective_start_date,2) input_name2
--,     get_entry_value(etype.element_type_id,ele.effective_start_date,2,ele.element_entry_id) input_value2
,     get_entry_value_number(etype.element_type_id,ele.effective_start_date,2,ele.element_entry_id) input_value_num2
,     get_input_value_name(etype.element_type_id,ele.effective_start_date,3) input_name3
--,     get_entry_value(etype.element_type_id,ele.effective_start_date,3,ele.element_entry_id) input_value3
,     get_entry_value_number(etype.element_type_id,ele.effective_start_date,3,ele.element_entry_id) input_value_num3
,     get_input_value_name(etype.element_type_id,ele.effective_start_date,4) input_name4
--,     get_entry_value(etype.element_type_id,ele.effective_start_date,4,ele.element_entry_id) input_value4
,     get_entry_value_number(etype.element_type_id,ele.effective_start_date,4,ele.element_entry_id) input_value_num4
,     get_input_value_name(etype.element_type_id,ele.effective_start_date,5) input_name5
--,     get_entry_value(etype.element_type_id,ele.effective_start_date,5,ele.element_entry_id) input_value5
,     get_entry_value_number(etype.element_type_id,ele.effective_start_date,5,ele.element_entry_id) input_value_num5
,     get_input_value_name(etype.element_type_id,ele.effective_start_date,6) input_name6
--,     get_entry_value(etype.element_type_id,ele.effective_start_date,6,ele.element_entry_id) input_value6
,     get_entry_value_number(etype.element_type_id,ele.effective_start_date,6,ele.element_entry_id) input_value_num6
,     get_input_value_name(etype.element_type_id,ele.effective_start_date,7) input_name7
--,     get_entry_value(etype.element_type_id,ele.effective_start_date,7,ele.element_entry_id) input_value7
,     get_entry_value_number(etype.element_type_id,ele.effective_start_date,7,ele.element_entry_id) input_value_num7
,     get_input_value_name(etype.element_type_id,ele.effective_start_date,8) input_name8
--,     get_entry_value(etype.element_type_id,ele.effective_start_date,8,ele.element_entry_id) input_value8
,     get_entry_value_number(etype.element_type_id,ele.effective_start_date,8,ele.element_entry_id) input_value_num8
,     get_input_value_name(etype.element_type_id,ele.effective_start_date,9) input_name9
--,     get_entry_value(etype.element_type_id,ele.effective_start_date,9,ele.element_entry_id) input_value9
,     get_entry_value_number(etype.element_type_id,ele.effective_start_date,9,ele.element_entry_id) input_value_num9
,     get_input_value_name(etype.element_type_id,ele.effective_start_date,10) input_name10
--,     get_entry_value(etype.element_type_id,ele.effective_start_date,10,ele.element_entry_id) input_value10
,     get_entry_value_number(etype.element_type_id,ele.effective_start_date,10,ele.element_entry_id) input_value_num10
,     get_input_value_name(etype.element_type_id,ele.effective_start_date,11) input_name11
--,     get_entry_value(etype.element_type_id,ele.effective_start_date,11,ele.element_entry_id) input_value11
,     get_entry_value_number(etype.element_type_id,ele.effective_start_date,11,ele.element_entry_id) input_value_num11
,     get_input_value_name(etype.element_type_id,ele.effective_start_date,12) input_name12
--,     get_entry_value(etype.element_type_id,ele.effective_start_date,12,ele.element_entry_id) input_value12
,     get_entry_value_number(etype.element_type_id,ele.effective_start_date,12,ele.element_entry_id) input_value_num12
,     get_input_value_name(etype.element_type_id,ele.effective_start_date,13) input_name13
,     get_entry_value(etype.element_type_id,ele.effective_start_date,13,ele.element_entry_id) input_value13
,     get_entry_value_number(etype.element_type_id,ele.effective_start_date,13,ele.element_entry_id) input_value_num13
,     get_input_value_name(etype.element_type_id,ele.effective_start_date,14) input_name14
--,     get_entry_value(etype.element_type_id,ele.effective_start_date,14,ele.element_entry_id) input_value14
,     get_entry_value_number(etype.element_type_id,ele.effective_start_date,14,ele.element_entry_id) input_value_num14
,     get_input_value_name(etype.element_type_id,ele.effective_start_date,15) input_name15
--,     get_entry_value(etype.element_type_id,ele.effective_start_date,15,ele.element_entry_id) input_value15
,     get_entry_value_number(etype.element_type_id,ele.effective_start_date,15,ele.element_entry_id) input_value_num15
from   HR_ORA_PERSONAL_SCH.pay_element_classifications_tl pec_tl
,      HR_ORA_PERSONAL_SCH.pay_element_classifications    pec
,      HR_ORA_PERSONAL_SCH.pay_element_types_f_tl         etype_tl
,      HR_ORA_PERSONAL_SCH.pay_element_types_f            etype
,      HR_ORA_PERSONAL_SCH.pay_element_links_f            link
,      HR_ORA_PERSONAL_SCH.pay_element_entries_f          ele
where  ele.element_link_id = link.element_link_id
and    etype.element_type_id = link.element_type_id
and    etype.element_type_id = etype_tl.element_type_id
and    etype_tl.language = 'US'
and    etype.classification_id = pec.classification_id
and    pec.classification_id = pec_tl.classification_id
and    pec_tl.language = 'US';

Table definition

pay_element_entry_values_f

ELEMENT_ENTRY_VALUE_ID NUMBER(15,0) 
EFFECTIVE_START_DATE DATE 
EFFECTIVE_END_DATE DATE 
INPUT_VALUE_ID NUMBER(9,0) 
ELEMENT_ENTRY_ID NUMBER(15,0) 
SCREEN_ENTRY_VALUE VARCHAR(60)

pay_input_values_f

INPUT_VALUE_ID NUMBER(9,0) 
EFFECTIVE_START_DATE TIMESTAMP_NTZ(9) 
EFFECTIVE_END_DATE TIMESTAMP_NTZ(9) 
ELEMENT_TYPE_ID NUMBER(9,0) 
LOOKUP_TYPE VARCHAR(30) 
BUSINESS_GROUP_ID NUMBER(15,0) 
LEGISLATION_CODE VARCHAR(30) 
FORMULA_ID NUMBER(9,0) 
DISPLAY_SEQUENCE NUMBER(5,0) 
GENERATE_DB_ITEMS_FLAG VARCHAR(30) 
HOT_DEFAULT_FLAG VARCHAR(30) 
MANDATORY_FLAG VARCHAR(1) 
NAME VARCHAR(80) 
UOM VARCHAR(30) 
DEFAULT_VALUE VARCHAR(60) 
LEGISLATION_SUBGROUP VARCHAR(30) 
MAX_VALUE VARCHAR(60) 
MIN_VALUE VARCHAR(60) 
WARNING_OR_ERROR VARCHAR(30) 
LAST_UPDATE_DATE TIMESTAMP_NTZ(9) 
LAST_UPDATED_BY NUMBER(15,0) 
LAST_UPDATE_LOGIN NUMBER(15,0) 
CREATED_BY NUMBER(15,0) 
CREATION_DATE TIMESTAMP_NTZ(9) 
OBJECT_VERSION_NUMBER NUMBER(9,0) 
VALUE_SET_ID NUMBER(10,0)

Answer 1

Yes i have changed from tabular Function to Scalar Function as below, which is throwing the same error.

create or replace function get_entry_value  (p_element_type_id number, p_effective_date  varchar,p_display_sequence number, p_element_entry_id number) 
RETURNS varchar   
as 'select meaning
   from   hr_lookups bb,
   (select eev.screen_entry_value, inv.lookup_type, inv.element_type_id, eev.element_entry_id, inv.effective_start_date, inv.effective_end_date, 
            eev.effective_start_date, eev.effective_end_date
     from   pay_element_entry_values_f eev,
            pay_input_values_f inv
       where  P_ELEMENT_TYPE_ID = inv.element_type_id 
       and    inv.display_sequence  = P_DISPLAY_SEQUENCE
       and    eev.input_value_id    = inv.input_value_id
       and    eev.element_entry_id  = P_ELEMENT_ENTRY_ID
       and    P_EFFECTIVE_DATE between inv.effective_start_date and inv.effective_end_date
       and    P_EFFECTIVE_DATE between eev.effective_start_date and eev.effective_end_date ) aa
    where  bb.lookup_type = aa.screen_entry_value
   and    bb.lookup_code =aa.lookup_type';

Answer 2

sergiu, Below is my table definition.

pay_element_entry_values_f

ELEMENT_ENTRY_VALUE_ID NUMBER(15,0) EFFECTIVE_START_DATE DATE EFFECTIVE_END_DATE DATE INPUT_VALUE_ID NUMBER(9,0) ELEMENT_ENTRY_ID NUMBER(15,0) SCREEN_ENTRY_VALUE VARCHAR(60)

pay_input_values_f

INPUT_VALUE_ID NUMBER(9,0) EFFECTIVE_START_DATE TIMESTAMP_NTZ(9) EFFECTIVE_END_DATE TIMESTAMP_NTZ(9) ELEMENT_TYPE_ID NUMBER(9,0) LOOKUP_TYPE VARCHAR(30) BUSINESS_GROUP_ID NUMBER(15,0) LEGISLATION_CODE VARCHAR(30) FORMULA_ID NUMBER(9,0) DISPLAY_SEQUENCE NUMBER(5,0) GENERATE_DB_ITEMS_FLAG VARCHAR(30) HOT_DEFAULT_FLAG VARCHAR(30) MANDATORY_FLAG VARCHAR(1) NAME VARCHAR(80) UOM VARCHAR(30) DEFAULT_VALUE VARCHAR(60) LEGISLATION_SUBGROUP VARCHAR(30) MAX_VALUE VARCHAR(60) MIN_VALUE VARCHAR(60) WARNING_OR_ERROR VARCHAR(30) LAST_UPDATE_DATE TIMESTAMP_NTZ(9) LAST_UPDATED_BY NUMBER(15,0) LAST_UPDATE_LOGIN NUMBER(15,0) CREATED_BY NUMBER(15,0) CREATION_DATE TIMESTAMP_NTZ(9) OBJECT_VERSION_NUMBER NUMBER(9,0) VALUE_SET_ID NUMBER(10,0)

Answer 3

You have provided very little information and your code is messy. However, I can see that your function get_entry_value is defined as a tabular function (UDTF). Was this intentional? If so, then you are using it incorrectly in the calling view. A tabular function is used like a table

select * from table(get_entry_value (p1, p2, p3, p4))

But in your calling view you are using it as a scalar function (UDF). If this was intentional, then you need to fix your function, the return line should be a scalar

RETURNS varchar