Archives of the TeradataForum
Message Posted: Tue, 27 Oct 2015 @ 19:22:19 GMT
I've got this here piece of sql ...
SELECT 'Account', 'V3',t1.token_ndx,TRIM(t1.FieldName) from table(regexp_split_to_table(1,DEVALLVIEWS_V.SFDC_Account_V3.SFDC_FIELD_LIST_TX ,DEVALLVIEWS_V.SFDC_Account_V3.EDW_PATTERN_TX ,DEVALLVIEWS_V.SFDC_Account_V3.EDW_MATCH_CD) returns (OutKey VARCHAR(128),token_ndx integer, FieldName varchar(8192) character set latin)) as t1 ;
it splits a string of comma separated fields (held in SFDC_FIELD_LIST_TX) into separate Columns .... and very nice it is too.
Thing is, the SFDC_Account_V3 is a specific View (with a Where Clause on Version code = 'V3') on top of the base Table, itself which contains relational sets of values, each set given a relevant Version.
So, what I'd like to be able to do is something like this :
SELECT 'Account', DEVALLVIEWS_V.SFDC_Account.EDW_DIAGRAM_VERSION_NR,t1.token_ndx,TRIM(t1.FieldName) from table(regexp_split_to_table(1,DEVALLVIEWS_V.SFDC_Account.SFDC_FIELD_LIST_TX ,DEVALLVIEWS_V.SFDC_Account.EDW_PATTERN_TX ,DEVALLVIEWS_V.SFDC_Account.EDW_MATCH_CD) returns (OutKey VARCHAR(128),token_ndx integer, FieldName varchar(8192) character set latin)) as t1 WHERE DEVALLVIEWS_V.SFDC_Account.EDW_DIAGRAM_VERSION_NR = 'V3' ;
That way, I don't then have to have several Views, each one specific to a particular Version .... as it's just a bit crap doing it that way.
Is this possible ? Is it just a case of incorporating the correct syntax somewhere in that sql ?
I just don't see where it's possible to restrict the entries in the Table or View.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 24 Jul 2020|