ORA-00936 error with SQL NVL statement

1149570 pts.
Tags:
Oracle
SQL
I'm using Oracle and I have this SQL NVL statement:
select a.*, 
    case when NVL (SELECT max(b.field1)
        FROM b
        where b.field2 = a.tbl_a_PK , 'TRUE') = 'TRUE' 
            then 'has no data in b'
            else 'has data in b' end as b_status
from a
My SELECT is returning one value (as it should) but I'm also getting ORA-00936: Missing expression. What should I do?
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

You need to enclose the subquery in parentheses, as is usually the case with subqueries.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • TheRealRaven
    Your NVL() function only has a single expression. You haven't specified the replace-with expression part of the function.
    36,025 pointsBadges:
    report
  • carlosdl
    Raven, the second parameter is there, but it is not easily recognizable because of the lack of parentheses for the first one (the subquery):

    NVL (SELECT max(b.field1)
            FROM b
            where b.field2 = a.tbl_a_PK , 'TRUE')

    85,025 pointsBadges:
    report
  • TheRealRaven
    Carlosdl, that's true. The provided syntax makes it difficult to recognize by eye, but it also makes it difficult for Oracle's parser to link tokens with certainty.

    For the parser, the second right-paren technically closes the NVL(); but the sub-SELECT is yet to be closed. It's ambiguous whether "'TRUE'" is the last token of the sub-SELECT or a separate expression. Somewhat fortunately the parser makes an 'educated guess' about what the developer intended and gives a (possibly) correct result.

    If it gave no error message at all, there'd be no clue that anything was wrong. The message is just the closest that the parser can get to figuring out what's wrong. From it's viewpoint, the NVL() is the problem because it only has a single expression in its parameter list where two are needed.

    In other ambiguous circumstances, if the parser allows flawed statements to run, very undesirable results could happen.
    36,025 pointsBadges:
    report
  • carlosdl
    Totally agree, Raven.
    85,025 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: