Select query works but create/replace view doesn't work

Database
N/A

Select query works but create/replace view doesn't work

Hello All,

I am new to Teradata. I encountered a weird error and I am not sure about the cause of this error. I am trying to build a view using some select statement. Pasted below is the select statement -

Select A.* 
from (
Select Distinct bk2_sf_metadata_file.sf_metadata_file_key, bk2_sf_metadata_file.full_nm,
bk2_sf_metadata_file.metadata_typ
, bk2_sf_metadata_node_obj.val as ObjectName
, substr(substr(bk2_sf_metadata_node_fld.xml_hierarchy, index(bk2_sf_metadata_node_fld.xml_hierarchy,
',') + 1, length(bk2_sf_metadata_node_fld.xml_hierarchy)), -1,
length(substr(bk2_sf_metadata_node_fld.xml_hierarchy, index(bk2_sf_metadata_node_fld.xml_hierarchy,
',') + 1, length(bk2_sf_metadata_node_fld.xml_hierarchy)))) as ObjectHierarchy
, bk2_sf_metadata_node_fld.val as FieldName
, substr(substr(bk2_sf_metadata_node_fldapi.xml_hierarchy, index(bk2_sf_metadata_node_fldapi.xml_hierarchy,
',') + 1, length(bk2_sf_metadata_node_fldapi.xml_hierarchy)),
-1, length(substr(bk2_sf_metadata_node_fldapi.xml_hierarchy,
index(bk2_sf_metadata_node_fldapi.xml_hierarchy, ',') + 1, length(bk2_sf_metadata_node_fldapi.xml_hierarchy)))) as FieldHierarchy
,
Case when substr(substr(bk2_sf_metadata_node_fld.xml_hierarchy,
index(bk2_sf_metadata_node_fld.xml_hierarchy, ',') + 1, length(bk2_sf_metadata_node_fld.xml_hierarchy)),
-1, length(substr(bk2_sf_metadata_node_fld.xml_hierarchy, index(bk2_sf_metadata_node_fld.xml_hierarchy,
',') + 1, length(bk2_sf_metadata_node_fld.xml_hierarchy)))) = substr(substr(bk2_sf_metadata_node_fldapi.xml_hierarchy,
index(bk2_sf_metadata_node_fldapi.xml_hierarchy, ',') + 1, length(bk2_sf_metadata_node_fldapi.xml_hierarchy)),
-1, length(substr(bk2_sf_metadata_node_fldapi.xml_hierarchy,
index(bk2_sf_metadata_node_fldapi.xml_hierarchy, ',') + 1, length(bk2_sf_metadata_node_fldapi.xml_hierarchy)))) then bk2_sf_metadata_node_fldapi.val
else null
end as FieldAPIName
, substr(substr(bk2_sf_metadata_node_fldtyp.xml_hierarchy,
index(bk2_sf_metadata_node_fldtyp.xml_hierarchy, ',') + 1, length(bk2_sf_metadata_node_fldtyp.xml_hierarchy)),
-1, length(substr(bk2_sf_metadata_node_fldtyp.xml_hierarchy,
index(bk2_sf_metadata_node_fldtyp.xml_hierarchy, ',') + 1, length(bk2_sf_metadata_node_fldtyp.xml_hierarchy)))) as TypeHirarchy
,
Case when length(substr(substr(bk2_sf_metadata_node_fldtyp.xml_hierarchy,
index(bk2_sf_metadata_node_fldtyp.xml_hierarchy, ',') + 1, length(bk2_sf_metadata_node_fldtyp.xml_hierarchy)),
-1, length(substr(bk2_sf_metadata_node_fldtyp.xml_hierarchy,
index(bk2_sf_metadata_node_fldtyp.xml_hierarchy, ',') + 1, length(bk2_sf_metadata_node_fldtyp.xml_hierarchy))))) > 2
and Index(substr(substr(bk2_sf_metadata_node_fldtyp.xml_hierarchy,
index(bk2_sf_metadata_node_fldtyp.xml_hierarchy, ',') + 1, length(bk2_sf_metadata_node_fldtyp.xml_hierarchy)),
-1, length(substr(bk2_sf_metadata_node_fldtyp.xml_hierarchy,
index(bk2_sf_metadata_node_fldtyp.xml_hierarchy, ',') + 1, length(bk2_sf_metadata_node_fldtyp.xml_hierarchy)))),
substr(substr(bk2_sf_metadata_node_fld.xml_hierarchy,
index(bk2_sf_metadata_node_fld.xml_hierarchy, ',') + 1, length(bk2_sf_metadata_node_fld.xml_hierarchy)),
-1, length(substr(bk2_sf_metadata_node_fld.xml_hierarchy, index(bk2_sf_metadata_node_fld.xml_hierarchy,
',') + 1, length(bk2_sf_metadata_node_fld.xml_hierarchy))))) > 0 then bk2_sf_metadata_node_fldtyp.val
when length(substr(substr(bk2_sf_metadata_node_fldtyp.xml_hierarchy,
index(bk2_sf_metadata_node_fldtyp.xml_hierarchy, ',') + 1, length(bk2_sf_metadata_node_fldtyp.xml_hierarchy)),
-1, length(substr(bk2_sf_metadata_node_fldtyp.xml_hierarchy,
index(bk2_sf_metadata_node_fldtyp.xml_hierarchy, ',') + 1, length(bk2_sf_metadata_node_fldtyp.xml_hierarchy))))) = 2
and substr(substr(bk2_sf_metadata_node_fld.xml_hierarchy,
index(bk2_sf_metadata_node_fld.xml_hierarchy, ',') + 1, length(bk2_sf_metadata_node_fld.xml_hierarchy)),
-1, length(substr(bk2_sf_metadata_node_fld.xml_hierarchy, index(bk2_sf_metadata_node_fld.xml_hierarchy,
',') + 1, length(bk2_sf_metadata_node_fld.xml_hierarchy)))) = substr(substr(bk2_sf_metadata_node_fldtyp.xml_hierarchy,
index(bk2_sf_metadata_node_fldtyp.xml_hierarchy, ',') + 1, length(bk2_sf_metadata_node_fldtyp.xml_hierarchy)),
-1, length(substr(bk2_sf_metadata_node_fldtyp.xml_hierarchy,
index(bk2_sf_metadata_node_fldtyp.xml_hierarchy, ',') + 1, length(bk2_sf_metadata_node_fldtyp.xml_hierarchy)))) then bk2_sf_metadata_node_fldtyp.val
else null
end as FieldType
, Case when length(substr(substr(bk2_sf_metadata_node_fldtrck.xml_hierarchy,
index(bk2_sf_metadata_node_fldtrck.xml_hierarchy, ',') + 1, length(bk2_sf_metadata_node_fldtrck.xml_hierarchy)),
-1, length(substr(bk2_sf_metadata_node_fldtrck.xml_hierarchy,
index(bk2_sf_metadata_node_fldtrck.xml_hierarchy, ',') + 1, length(bk2_sf_metadata_node_fldtrck.xml_hierarchy))))) > 2
and Index(substr(substr(bk2_sf_metadata_node_fldtrck.xml_hierarchy,
index(bk2_sf_metadata_node_fldtrck.xml_hierarchy, ',') + 1, length(bk2_sf_metadata_node_fldtrck.xml_hierarchy)),
-1, length(substr(bk2_sf_metadata_node_fldtrck.xml_hierarchy,
index(bk2_sf_metadata_node_fldtrck.xml_hierarchy, ',') + 1, length(bk2_sf_metadata_node_fldtrck.xml_hierarchy)))),
substr(substr(bk2_sf_metadata_node_fld.xml_hierarchy,
index(bk2_sf_metadata_node_fld.xml_hierarchy, ',') + 1, length(bk2_sf_metadata_node_fld.xml_hierarchy)),
-1, length(substr(bk2_sf_metadata_node_fld.xml_hierarchy, index(bk2_sf_metadata_node_fld.xml_hierarchy,
',') + 1, length(bk2_sf_metadata_node_fld.xml_hierarchy))))) > 0 then bk2_sf_metadata_node_fldtrck.val
when length(substr(substr(bk2_sf_metadata_node_fldtrck.xml_hierarchy,
index(bk2_sf_metadata_node_fldtrck.xml_hierarchy, ',') + 1, length(bk2_sf_metadata_node_fldtrck.xml_hierarchy)),
-1, length(substr(bk2_sf_metadata_node_fldtrck.xml_hierarchy,
index(bk2_sf_metadata_node_fldtrck.xml_hierarchy, ',') + 1, length(bk2_sf_metadata_node_fldtrck.xml_hierarchy))))) = 2
and substr(substr(bk2_sf_metadata_node_fld.xml_hierarchy,
index(bk2_sf_metadata_node_fld.xml_hierarchy, ',') + 1, length(bk2_sf_metadata_node_fld.xml_hierarchy)),
-1, length(substr(bk2_sf_metadata_node_fld.xml_hierarchy, index(bk2_sf_metadata_node_fld.xml_hierarchy,
',') + 1, length(bk2_sf_metadata_node_fld.xml_hierarchy)))) = substr(substr(bk2_sf_metadata_node_fldtrck.xml_hierarchy,
index(bk2_sf_metadata_node_fldtrck.xml_hierarchy, ',') + 1, length(bk2_sf_metadata_node_fldtrck.xml_hierarchy)),
-1, length(substr(bk2_sf_metadata_node_fldtrck.xml_hierarchy,
index(bk2_sf_metadata_node_fldtrck.xml_hierarchy, ',') + 1, length(bk2_sf_metadata_node_fldtrck.xml_hierarchy)))) then bk2_sf_metadata_node_fldtrck.val
else 'false'
end as FieldTrackingFlag
from ddcoe_tbls.bk2_sf_metadata_file bk2_sf_metadata_file
Inner join ddcoe_tbls.bk2_sf_metadata_node bk2_sf_metadata_node_obj
on bk2_sf_metadata_file.sf_metadata_file_key = bk2_sf_metadata_node_obj.sf_metadata_file_key
and bk2_sf_metadata_node_obj.metadata_hierarchy = 'CustomObject -> label'
Inner join ddcoe_tbls.bk2_sf_metadata_node bk2_sf_metadata_node_fld
on bk2_sf_metadata_file.sf_metadata_file_key = bk2_sf_metadata_node_fld.sf_metadata_file_key
and bk2_sf_metadata_node_fld.metadata_hierarchy = 'CustomObject -> fields -> label'
Inner Join ddcoe_tbls.bk2_sf_metadata_node bk2_sf_metadata_node_fldapi
on bk2_sf_metadata_file.sf_metadata_file_key = bk2_sf_metadata_node_fldapi.sf_metadata_file_key
and bk2_sf_metadata_node_fldapi.metadata_hierarchy = 'CustomObject -> fields -> fullName'
Inner Join ddcoe_tbls.bk2_sf_metadata_node bk2_sf_metadata_node_fldtyp
on bk2_sf_metadata_file.sf_metadata_file_key = bk2_sf_metadata_node_fldtyp.sf_metadata_file_key
and bk2_sf_metadata_node_fldtyp.metadata_hierarchy = 'CustomObject -> fields -> type'
Left Outer Join ddcoe_tbls.bk2_sf_metadata_node bk2_sf_metadata_node_fldtrck
on bk2_sf_metadata_file.sf_metadata_file_key = bk2_sf_metadata_node_fldtrck.sf_metadata_file_key
and bk2_sf_metadata_node_fldtrck.metadata_hierarchy = 'CustomObject -> fields -> trackHistory'

where bk2_sf_metadata_file.metadata_typ = 'CustomObject'

) A
where A.FieldAPIName is not null
and A.FieldType is not null and A.FieldTrackingFlag is not null

When I tried to use replace or create view for the same select statement, which worked perfectly fine by the way, it showed error 3706 : DataType 'xml_hierarchy' does not match a defined data type name.

I am not sure how to eradicate this issue. Any help would be appreciated.

thanks,

Subhashish

1 REPLY

Re: Select query works but create/replace view doesn't work

Hi Subhashish,

Today while coding I also faced the same issue....try to change the LENGTH function to CHAR function. Hope it works for you too. Thanks.