-- @@@ START COPYRIGHT @@@
--
-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements.  See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership.  The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License.  You may obtain a copy of the License at
--
--   http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing,
-- software distributed under the License is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-- KIND, either express or implied.  See the License for the
-- specific language governing permissions and limitations
-- under the License.
--
-- @@@ END COPYRIGHT @@@

--set pattern $$VERS_CURR_SCHEMA_VERSION$$ 2400;
set param ?mvDescSubId  -2;
set param ?mvName 'SUMBY_DAY';
set param ?mvSchemaName 'SUMBY';

-- query the TEXT SMD table for the descriptor of an MV
prepare getMVDescFromTEXT  from
  select text0 || 
         coalesce(text1, '') || 
         coalesce(text2, '') || 
         coalesce(text3, '') || 
         coalesce(text4, '') ||
         coalesce(text5, '') 
           as mv_descriptor_text
  from
		 (SELECT o.object_uid as uid0, text as text0
		         from  HP_DEFINITION_SCHEMA.TEXT t,
		               HP_DEFINITION_SCHEMA.objects o,
		               HP_SYSTEM_CATALOG.SYSTEM_SCHEMA.schemata s
		         where o.object_name = ?mvName
		           and o.OBJECT_TYPE = 'MV'
		           and o.object_uid = t.object_uid
		           and o.schema_uid = s.schema_uid
		           and s.schema_name = ?mvSchemaName
		           and t.object_sub_id = ?mvDescSubId
		           and sequence_num = 0 ) as row0
	left outer join
		 (SELECT o.object_uid as uid1, text as text1
		         from  HP_DEFINITION_SCHEMA.TEXT t,
		               HP_DEFINITION_SCHEMA.objects o,
		               HP_SYSTEM_CATALOG.SYSTEM_SCHEMA.schemata s
		         where o.object_name = ?mvName
		           and o.OBJECT_TYPE = 'MV'
		           and o.object_uid = t.object_uid
		           and o.schema_uid = s.schema_uid
		           and s.schema_name = ?mvSchemaName
		           and t.object_sub_id = ?mvDescSubId
		           and sequence_num = 1 ) as row1
		 on uid0 = uid1
	left outer join
		 (SELECT o.object_uid as uid2, text as text2
		         from  HP_DEFINITION_SCHEMA.TEXT t,
		               HP_DEFINITION_SCHEMA.objects o,
		               HP_SYSTEM_CATALOG.SYSTEM_SCHEMA.schemata s
		         where o.object_name = ?mvName
		           and o.OBJECT_TYPE = 'MV'
		           and o.object_uid = t.object_uid
		           and o.schema_uid = s.schema_uid
		           and s.schema_name = ?mvSchemaName
		           and t.object_sub_id = ?mvDescSubId
		           and sequence_num = 2 ) as row2
		 on uid0 = uid2
	left outer join
		 (SELECT o.object_uid as uid3, text as text3
		         from  HP_DEFINITION_SCHEMA.TEXT t,
		               HP_DEFINITION_SCHEMA.objects o,
		               HP_SYSTEM_CATALOG.SYSTEM_SCHEMA.schemata s
		         where o.object_name = ?mvName
		           and o.OBJECT_TYPE = 'MV'
		           and o.object_uid = t.object_uid
		           and o.schema_uid = s.schema_uid
		           and s.schema_name = ?mvSchemaName
		           and t.object_sub_id = ?mvDescSubId
		           and sequence_num = 3 ) as row3
		 on uid0 = uid3
	left outer join
		 (SELECT o.object_uid as uid4, text as text4
		         from  HP_DEFINITION_SCHEMA.TEXT t,
		               HP_DEFINITION_SCHEMA.objects o,
		               HP_SYSTEM_CATALOG.SYSTEM_SCHEMA.schemata s
		         where o.object_name = ?mvName
		           and o.OBJECT_TYPE = 'MV'
		           and o.object_uid = t.object_uid
		           and o.schema_uid = s.schema_uid
		           and s.schema_name = ?mvSchemaName
		           and t.object_sub_id = ?mvDescSubId
		           and sequence_num = 4 ) as row4
		 on uid0 = uid4
	left outer join
		 (SELECT o.object_uid as uid5, text as text5
		         from  HP_DEFINITION_SCHEMA.TEXT t,
		               HP_DEFINITION_SCHEMA.objects o,
		               HP_SYSTEM_CATALOG.SYSTEM_SCHEMA.schemata s
		         where o.object_name = ?mvName
		           and o.OBJECT_TYPE = 'MV'
		           and o.object_uid = t.object_uid
		           and o.schema_uid = s.schema_uid
		           and s.schema_name = ?mvSchemaName
		           and t.object_sub_id = ?mvDescSubId
		           and sequence_num = 5 ) as row5
		 on uid0 = uid5;
