Archives of the TeradataForum

Message Posted: Tue, 27 Oct 2015 @ 19:22:19 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi guys,

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.

Regards

David Clough
Senior BI Database Designer
BI Competency Centre

	<Prev	Next>		<<First	<Prev	Next>	Last>>