Re: Dynamic SQL in PRO-C (type 3), problem with NULL

This is kludgy but I believe this should work. Try adding an outer-join of DUAL.

A simple query such as:

   SELECT myTable.aField

      FROM myTable
      WHERE ...
      ;

Change to:

   SELECT NVL(myTable.aField, valueIfNull)

      FROM myTable, Dual
      WHERE ...
      AND   myTable.aField(+) IS NOT NULL;

If your query involves a join, wrap it as follows:

   SELECT whatever

      FROM ( SELECT NVL(aTable.aField, valueIfNull) anAlias
                FROM aTable, bTable
                WHERE ...
           ) myQuery,
           Dual
      WHERE myQuery.anAlias(+) IS NOT NULL
      ;
 

Another technique would be to wrap your query into a PL/SQL block with an exception handler to catch the NO_DATA_FOUND condition.

Good luck.

John Gasch

"Riccardo D.S." wrote:
>
> I use e dynamic SQL statment (pro c - type 3 ) to estract
> the value of single column and put it in the host-variable.
> When the return of query is Null the SQL statment go in error .
> Is it possible when this occur continue the process?
> Now use the NVL function to return a particulat string
> or number when the value is NULL. I prefer ignore the select
> when tha value is Null and continue to other select.
>
> Thanks,
> Sorry for my english...
>
> I'm italian
>
> Dott. Riccardo Di Stefano
> Alenia Marconi System s.p.a.
> Rome - Italy
Received on Sat Apr 01 2000 - 04:14:45 CEST