如何将With As迁移至GBase8t中

以下详细描述了如何将with as语法迁移至GBase8t中
with as短语，也叫做子查询部分(subquery factoring)
定义一个sql片断，该sql片断会被整个sql语句所用到。有的时候，也有可能是在union all的不同部分，作为提供数据的部分。
Oracle使用场景
情景1：关联子查询

样例描述如下：
1、表定义：

create table a 
  (  a1 integer, a2 char(100), a3 varchar(20));

create table b 
  ( b1 integer, b2 char(100), b3 varchar(20));
 

2、数据导入：

insert into a values(111,'111','11111');
insert into a values(222,'222','22222');
insert into b values(333,'333','33333');
insert into b values(444,'444','44444');
insert into b values(555,'555','55555');

3、ORACLE的with...as语句如下：

with sql1 as  (select a1,a2 from a),
sql2 as  (select b1,b2 from b)
select * from sql1 union all select * from sql2;

GBase 8t替代方案：
替代方案A：
可以拆分成临时表替代方式解决

select a1,a2 from a into temp tmp_a;
select b1,b2 from b into temp tmp_b;

select a1,a2  from tmp_a 
union all
select b1,b2  from tmp_b;

替代方案B：
可以通过子查询的替代方式解决

 
select a1,a2  from (select a1,a2 from a) 
union all
select b1,b2  from (select b1,b2 from b);

替代方案C：
可以通过子查询的替代方式解决

CREATE PROCEDURE get_result()
returning integer, char(100);
define p_a1 integer;
define p_a2 char(100);
select a1,a2 from a into temp tmp_a;
select b1,b2 from b into temp tmp_b;
foreach 
select ab.a1,ab.a2 into p_a1,p_a2
from 
(select a1,a2  from tmp_a
 union all
  select b1 as a1,b2 as a2 from tmp_b) ab
return p_a1,p_a2
with resume;
end foreach;
    
drop table tmp_a;
drop table tmp_b;
end procedure;

情景2：递归查询

样例描述如下：
1、表定义：

CREATE TABLE [dbo].[BUA_ORGANIZATION](
[ORG_ID] [varchar](38) NOT NULL,
[ORG_NAME] [varchar](64) NOT NULL,
[ORG_CODE] [varchar](36) NOT NULL,
[ORG_PARENT_ID] [varchar](38) NOT NULL,
[ORG_TYPE] [varchar](16) NULL,
[UNIT_TYPE] [varchar](16) NULL,
[ORG_PATH] [varchar](512) NULL,
[CREATE_USER_NAME] [varchar](36) NOT NULL,
[CREATE_TIME] [datetime] NOT NULL,
[LAST_UPDATE_USER_NAME] [varchar](36) NULL,
[LAST_UPDATE_TIME] [datetime] NULL,
[ORG_SEQUENCE] [decimal](15, 2) NULL,
 CONSTRAINT [PK_BUA_ORGANIZATION] PRIMARY KEY NONCLUSTERED 
(
[ORG_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,     ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]     

2.with...as语句如下：

with org_tree as (
select m.ORG_ID, m.ORG_NAME,m.UNIT_TYPE,m.ORG_PARENT_ID,dictionaryLevel=1
  from [BUA].[dbo].[BUA_ORGANIZATION] m where ORG_ID=@ORG_ID
union all
select m.ORG_ID,  m.ORG_NAME,m.UNIT_TYPE,m.ORG_PARENT_ID,
menuLevel=t.dictionaryLevel+1 
  from
 org_tree t,[BUA].[dbo].[BUA_ORGANIZATION] m where m.ORG_ID = t.ORG_PARENT_ID 
 )

GBase 8t替代方案：
替代方案：
使用connect by start with 替换with as 递归

select m.ORG_ID, m.ORG_NAME,m.UNIT_TYPE,m.ORG_PARENT_ID
 from BUA_ORGANIZATION m
start with m.org_id = 1
connect by  m.ORG_ID = prior m.ORG_PARENT_ID