1

-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)
Improve this question
1
  • Can you post the table definition used in the subquery? Commented Jul 30, 2021 at 15:02

3 Answers 3

Reset to default
1

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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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)
Improve this answer

Comments

0

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';
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.