Re: something like an If statement

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Fri, 6 Feb 2004 08:38:19 -0500
Message-ID: <fIadnTpRSPZqBb7dRVn-tA@comcast.com>

"Alex Schatten" <alex.schatten_at_gmx.de> wrote in message news:bvu9s9$udf$06$1_at_news.t-online.com...
| Hi,
|
| following is used in Sybase and in MsSqlserver for a jsp. Frontend to
build
| different menu structures.
| Is there any simple way to translate this to pl/sql
|
| IF (SELECT count(*) FROM Structure WHERE ID = 1000) = 0
| BEGIN
| select 'File', 1
| union
| select 'Menu 2', 2
| union
| select 'Menu 3', 3
| ...
| order by 2
| END
| ELSE
| BEGIN
| select 'Menu 11', 1
| union
| select 'Menu 12', 2
| union
| select 'Menu 13', 3
| ...
|
| order by 2
| END
|
| Regards, Alex
|
|

alex,

basic issue is SB and MS-SS support SELECT without a FROM clause, Oracle does not

Oracle typically used SELECT .... FROM DUAL as a work-around, but this should rarely be required in PL/SQL

if you can invoke a stored function or procedure from your jsp, then forget about using SELECT, just build the appropriate string or array (PL/SQL table) and return it in a parameter or as the function return value

if you need a SQL resultset, then consider making your menu structure table driven (easier to maintain and expand) or (depending on your oracle version) look into using the SELECT .. FROM TABLE(CAST ... ) syntax (search asktom.oracle.com for examples)

another possibility is to create a PIPELINED function:

---

• create generic varchar2 type for return value

create type vc4000tbl as table of varchar2(4000);

• function to return csv list as a table

create or replace function as_table (

    ip_arr in varchar2
    )
return vc4000tbl pipelined
is

  idx      number default 1;
  dlen     number default length(ip_arr);
  slen     number;
  sep      varchar2(1) default ',';

begin
  while idx < dlen
  loop

     slen := instr(ip_arr,sep,idx);
     if slen = 0
     then
        slen := dlen+1;
     end if;

  pipe row (substr(ip_arr,idx,slen-idx));

     idx := slen+1;
  end loop;

  return;

end as_table;

• use the function in a select statement

select column_value
from table (as_table('this,that,the other'))

---

Mark C. Stock
mcstock -> enquery(dot)com
www.enquery.com training & consulting Received on Fri Feb 06 2004 - 07:38:19 CST