[SOLVED] How to append a query in where clause of existing query

How to append a query in where clause of existing query

In Oracle form I have a query just like

if po_no is not null then
    str := str ||'and po_no = '||:block.po_no;
end if;

now I want to append the str in below query.

select po_no
   from po,
        po_det
   where po_id = p_det_id
      &str

I want to append the str in end of query but it gives ORA-01722 error ..how i can do in right way..

Solution

Concatenate str to the rest of the query.

Pay attention to leading space (in front of the AND):

if po_no is not null then
   str := str ||' and po_no = ' || :block.po_no;
end if;

select po_no
from po, po_det
where po_id = p_det_id || str;

You didn't explain where exactly you'll be using that dynamic WHERE clause; if it is to filter rows fetched in a data block, consider using SET_BLOCK_PROPERTY built-in and its ONETIME_WHERE (or DEFAULT_WHERE) property (read more about it in Oracle Online Help system).

[EDIT]

I'm still not sure what is what in this query, but - to me - it looks as if you don't need IF at all, but use query which looks like this:

select po_no
  into v_po_no
  from po, po_det
  where po_id = p_det_id
    and (po_no = :block.po_no or po_no is null);     --> this substitutes your IF

Once again: to me, it is unclear what is exactly po_no (column? block field? variable?), but that's more or less what you might need to do.

[EDIT #2]

Dynamic SQL:

declare
  str    varchar2(500);
  result number;
begin
  str := 'select po_no from po, po_det where po_id = p_det_id ' ||
         case when po_no is not null then ' and po_no = :a'
         end;
         
  execute immediate str into result using :block.po_no;
end;