Column Type displayed different in Backened and MSTR Warehouse catalog

[Rambo2292468]

Problem: MSTR warehouse did not pick up the correct datatype when the backend view update. In the backend view, production_start_date and production_end_date shows varchar 21; however, they are varchar 42 in the view in warehouse.



Here is the backend view:

V_Workcenter_itm

Production_Start_date VARCHAR[21]
Production_Start_date VARCHAR[21]
Queiried_Item_WorkCenter CHAR[10]
Queiried_Item_Identifier CHRA[21]
Queiried_Item_Version_Code CHAR[2]
Pln_Cd CHAR[10]







Here is the backend view definition:

REPLACE VIEW BOM.v_workcenter_itm AS

SELECT DISTINCT

(pln_strt_dt (format 'yyyy-mm-dd')(char(10))) || ' ' || (pln_strt_dt(format 'hh:mi:ss')(char(8))) production_start_date,

(pln_eff_end_dt (format 'yyyy-mm-dd')(char(10))) || ' ' || (pln_eff_end_dt(format 'hh:mi:ss')(char(8))) production_end_date,

p1.wrk_cntr_cd queried_item_workcenter,

p1.mtrl_id queried_item_identifier,

p1.mtrl_rvs_cd queried_item_revision_code,

p2.pln_cd

FROM

bom_drv.v_wrk_cntr_mtrl p1

JOIN

bom_drv.v_pln p2 ON p1.wrk_cntr_cd = p2.wrk_cntr_cd;





Here is the view in warehouse catalog:

Here is the backend view:

V_Workcenter_itm

Queiried_Item_WorkCenter CHAR[10]
Pln_Cd CHAR[10]
Queiried_Item_Identifier CHRA[21]
Queiried_Item_Version_Code CHAR[2]
Production_Start_date VARCHAR[42]
Production_Start_date VARCHAR[42]


What I have done: Removed completely all objects related to this view. Also removed this view from “tables being used in the project” and I still see incorrect datatype in the “tables available in the warehouse”.

Note: The warehouse updated for other columns fine except these columns. This means that we point to correct database environment.

I am also aware of one MSTR tech note about the samying that Varchar in warehouse double the size of data type.
My fear is does it have any impact ?

Any ideas?

Thanks in Advance !

 

[teccum]

Update the structure of the view and also update your schema and then check and see whether you have the new datatype values.

thanks
teccum.

 

[Rambo2292468]

hi teccum

I have done this many times it changes all but show 2 times more for VARCHAR datatype in the warehouse view.

Thanks !

 

[teccum]

Hi,

If possible try changing the definition of the view to someother column and especially for those two columns and see whether it is updating it correctly by following the same update process.

If it is still shows the same then it might be a bug. Try reporting to Tech support.

thanks
teccum.

 

[jonhoward]

Looking at your SQL, and guessing from the naming conventions, are you creating a view of a view? I have a similar issue in SQL Server 2000.

My example would be as follows:
the base table has a column of datatype Char;
the first view is a straightforward SELECT from the table, and this view also has the same column of datatype Char;
the second view, which is a SELECT from the first view joined to someother views (still with me?), might have the same column of datatype Char, or it might be seen by MSTR as a Varchar.

I get the same weirdness with Integers and TinyIntegers (which come through as Integer and Unsigned respectively) being interchanged.

I've got as far as believing it is SQL Server (in our case) 'guessing' what the datatype should be for the second view and sometimes getting it wrong. So far I haven't found it actually stops MSTR SQL from working, since the datatypes are essentially inter-changeable, but you are right to be concerned about any impacts. If I get more clarification I'm sure to post.

 

[Rambo2292468]

HI Johnoward,

you got me correct. You are right my database is sql 2000 and what i experienced in couple of poject that if we update multiple tables/view and have the same column name in different tables/views in the warehouse with different column definitions, MSTR will take the last definition that it reads and use that as the definition for the column.

Also I never faced any problem in running the report sql with that but my concern is only does it have any type of impact which might be overseen by us.

Keep me updated you get any other info. thanks for the response.