MeshTable with string concatenation

[#1]

Hi to all,

I'm a newbie.

Please, I need help for this scenario:

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=DataStore1;Data Source=192.168.10.1;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=GIANLUCA-PC;Use Encryption for Data=False;Tag with column collation when possible=False];

CustomersDB1:

Load

CustPrefix as CustCode1,

text(CustCode) as CustCode2,

RegionalCode,

CustName, CustFiscalCode, Address, City, ZipCode, Country;

SQL SELECT

CustPrefix,

CustCode,

RegionalCode,

CustName, CustFiscalCode, Address, City, ZipCode, Country

FROM "DataStore1".dbo."CustDAT"

where "DataStore1".dbo."CustDAT".CustStatus = 'active'

;

Directory;

LOAD CODE,

     ACTIVITYTYPE,

     TRANSACTION,

     CHARGE,

     DATE

FROM

Data.xls

(biff, embedded labels, table is Customer$);

CODE in table Customer$ is the upper result of literal join of CustCode1, CustCode2 and the first 5 char of RegionalCode

example:

CustPrefix = 'cc', CustCode='00011', RegionalCode='New England'

CODE= 'CC00011NEWEN'

CustPrefix = 'cc', CustCode='00730', RegionalCode='Italy'

CODE= 'CC00730ITALY'

CustPrefix = 'rc', CustCode='00974', RegionalCode='Sweden'

CODE= 'RC00974SWEDE'

I need to connect the two tables.

I tried:

MeshTable:

Load

CustFiscalCode,

(upper(CustCode1) & text(CustCode2) & upper(left(trim(RegionalCode)),5)) as CODE

resident CustomersDB1

;

But my code has no desire to work.

Thanks in Advance.

Gianluca.

[swuehl]

Do you get an error? Or is the result not like expected (and if so, how does it look, then)?

I think there is a misplaced parenthesis in your code:

( upper(CustCode1) & text(CustCode2) & upper(left(trim(RegionalCode),5)) ) as CODE

[#3]

yep, was a typo error in the post.

the problem is that CODE for customer 00011:

CustPrefix = 'cc', CustCode='00011', RegionalCode='New England'

is 'CC00011NEW E' instead of 'CC00011NEWEN'

[swuehl · Accepted solution]

So you want to remove the space in regional code?

There are multiple approaches, please check the string functions. You can try something like

Replace(RegionalCode,' ','')

or

keepchar(upper(RegionalCode),'ABCDEFGHIJKLMNOPQRSTUVWXYZ')

edit: In total, it may look like

upper(CustCode1) & text(CustCode2) & upper(left(replace(RegionalCode,' ',''),5)) ) as CODE

[#5]

thanks exactly what i needed