LOV query is invalid in Oracle ApEx 4.1 when using subquery

So you create a List of Values in Oracle Application Express 4.1 using a simple in-line query similar to the one below:


SELECT d, r FROM
(
SELECT 1 d, 1 r
from dual
UNION ALL
select 2 d, 2 r
from dual
UNION ALL
select 3 d, 3 r
from dual
)

You get the following error:

1 error has occurred
LOV query is invalid, a display and a return value are needed, the column names need to be different. If your query contains an in-line query, the first FROM clause in the SQL statement must not belong to the in-line query.

LOV query invalid

LOV query invalid

You copy the query, paste it in SQL Commands in SQL Workshop, and it ran successfully. You try to create the LOV one more time, but you get the same error again. By this time your brain is working overtime thinking of a thousand different workarounds. There are a hundred strands of hair on your desk resulting from your frustration.

Lucky for you, you stumbled upon this site because the solution is simple.

Put a space after the first “FROM” keyword in your query.

LOV query invalid workaround

LOV query invalid workaround

ShareShare on FacebookTweet about this on TwitterShare on Google+Share on LinkedInEmail this to someone

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *