CODEWITHSUNDEEP
sqloracle-database
laughsloudly
laughsloudly

Reputation: 614

ORACLE SQL: Use a SELECT statement to pass a value to a function parameter

I have a function in Oracle that will convert a comma-delimited string into a table of values. It is usually called like this:

SELECT DISTINCT COLUMN_VALUE FROM TABLE(Comma_String_To_Table('abc,123,def,456'));

This call would return a table with four rows:

COLUMN_VALUE
abc
123
def
456

I use this many times to accept multi-value parameters from a front end web page, convert the parameter to a table, then join the table to filter my results, something like this:

SELECT *
FROM MYTABLE m
INNER JOIN (
    SELECT DISTINCT COLUMN_VALUE FROM TABLE(Comma_String_To_Table('abc,123'))
) f ON M.STATUS = F.COLUMN_VALUE

Now, to the question... I am writing a piece of standalone SQL. I want to use this Comma_String_To_Table function to filter my results just like in the example above, except that I want the value passed to the function to be the result of a SELECT statement. i.e:

SELECT *
FROM MYTABEL m
INNER JOIN (
    SELECT DISTINCT COLUMN_VALUE FROM TABLE(Comma_String_To_Table(SELECT VALUE FROM APPSETTING WHERE APPSETTINGID = 76))
) f ON M.STATUS = F.COLUMN_VALUE

The SQL SELECT VALUE FROM APPSETTING WHERE APPSETTINGID = 76 results in a single comma-delimited string value.

The error I am getting is: 

ORA-00936: missing expression

Any help to get this to work would be much appreciated.

Upvotes: 2

Views: 5450

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132570

Try this:

SELECT *
FROM MYTABEL m
INNER JOIN (
    SELECT DISTINCT COLUMN_VALUE 
    FROM  TABLE (SELECT Comma_String_To_Table(VALUE)
                 FROM APPSETTING WHERE APPSETTINGID = 76)
) f ON M.STATUS = F.COLUMN_VALUE

Upvotes: 4

Related Questions