Union and view operators

Chapter 21, “Abstract Plans Language Reference” describes the use of the operators union and view. In versions earlier than Adaptive Server 12.5, a union defined the union of two or more derived tables. The view operator identifies a view that contains the base table to be scanned.

Abstract Plans now fully support unions in views. This example shows how this can be accomplished:

1> sp_helptext vua
2> go

------------------------------------------------------
# Lines of Text
---------------
1
(1 row affected)
text
------------------------------------------------------

create view vua(v1, v2)
as
select r1, sum(s1) from r, s where r2 = s2 group by
	r1
union all
select t.t1, t.t2 from t

------------------------------------------------------
(1 row affected)
(return status = 0)
------------------------------------------------------

1> sp_helptext vud
2> go

------------------------------------------------------

# Lines of Text
---------------
1
(1 row affected)
text
------------------------------------------------------

create view vud(v1, v2)
as
select r1, sum(s1) from r, s where r2 = s2 group by
	r1
union
select t.t1, t.t2 from t

------------------------------------------------------
(1 row affected)
(return status = 0)
------------------------------------------------------

1> select t1, max(t2) from vua, vud, t group by t1
2> go

AP Input Association :

select t1, max(t2) from vua, vud, t group by t1
Input Abstract Plan (id 544001938) :
( plan
		( store Worktab3
				( union
						( plan
							( store Worktab5
									( nl_g_join
											( t_scan s )
											( t_scan r )
									)
							)
							( t_scan ( work_t Worktab5 ) )
						)
						( t_scan t )
				)
		)
		( store Worktab2
				( union
						( plan
							( store Worktab6
									( nl_g_join
											( t_scan s )
											( t_scan r )
									)
							 )
							( t_scan ( work_t Worktab6 ) )
						)
						( t_scan t )
				)
		)
		( plan
				( store Worktab1
						( nl_g_join
							( t_scan ( work_t Worktab3 ) 
							( t_scan ( work_t Worktab2 ) )
								( t_scan t )
						)
				)
				( t_scan ( work_t Worktab1 ) )
		)
)
Parsing the input Abstract Plan
Applying the Abstract Plan
Capturing the Abstract Plan.
AP Output Association :
select t1, max(t2) from vua, vud, t group by t1
Generating the output Abstract Plan.
Output Abstract Plan :
( plan
		( store Worktab3
				( union
						( plan
							( store Worktab5
									( nl_g_join
											( t_scan s )
											( t_scan r )
									)
							)
							( t_scan ( work_t Worktab5 ) )
						)
						( t_scan t )
				)
		)
		( store Worktab2
			( union
						(( plan
							( store Worktab6
									( nl_g_join
											( t_scan s )
											( t_scan r )
									)
							)
							( t_scan ( work_t Worktab6 ) )
						()
						(( t_scan t )
				)
			)
			( plan
					( store Worktab1
						( nl_g_join
							( t_scan ( work_t Worktab3 ) )
							( t_scan ( work_t Worktab2 ) )
											( t_scan t )
						)
					)
					( t_scan ( work_t Worktab1 ) )
			)
)