This discussion is archived
3 Replies Latest reply: May 10, 2013 5:23 AM by Christian Erlinger RSS

Coalesce with Cursor in a form?

Neimad Newbie
Currently Being Moderated
Hi everyone,

I have a problem when i try to use a coalesce method inside a cursor with a 11g form.

my select liook like this

SELECT c.sendingdate,
c.no_evenment,
coalesce((select no_action from action_type where no_action = c.no_action), c.no_action) no_action,
     

i don't put the rest of the select, but when i compile the PL/SQL, i got error with the "," inside the coalesce coalesce((select no_action from action_type where no_action = c.no_action) _,_ c.no_action) no_action

but this sql works in pl/sql developer

and works on other form without cursor.

thanks for your help.
  • 1. Re: Coalesce with Cursor in a form?
    GregorM Explorer
    Currently Being Moderated
    Hi.

    It is possible the pl/sql engine in forms is not recognizing coalesce function. To be honest I haven't tried it. If that is the reason the simplest solution is to create database view and in forms you simply query that view.

    Best regards.
  • 2. Re: Coalesce with Cursor in a form?
    Neimad Newbie
    Currently Being Moderated
    Hi, thanks for your answer,

    i think pl/sql in a form recognize Coalesce because i used it in a form inside DBMS text query execution. but if i try to use it with a CURSOR, i get the error. Same thing with VLM.. that strange a bit!

    my query works if i don't use CURSOR, if i use it, it doesnt works.

    thanks for your help.

    have a nice day
  • 3. Re: Coalesce with Cursor in a form?
    Christian Erlinger Guru
    Currently Being Moderated
    Neimad wrote:
    DBMS text query execution.
    I don't know what that means or is, probably Dynamic SQL?!?
    Anyway; coalesce doesn't seem to be the problem, your correlated subquery however is:
    select coalesce(1,2)
    into dummy
    from dual;
    works whereas
    select coalesce(1,2), coalesce((select 1 from dual), 2)
    into dummy
    from dual;
    does not compile.

    reason and solution is the same as already stated: the forms PL/SQL engine simply doesn't know correlated subquerys, and if you use a view you will be able to use a correlated subquery. Or you simply could rewrite your query to not use correlated subqueries.

    cheers

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points