Issue with linking views

1

I have a field that I am extracting from a table. In my first view I am extracting Customer Code from Comment field. I have the following code to do that; LEFT (STUFF(Comment, 1, CHARINDEX('Cus:', Comment + 'Cus:') + 3, ''), 7)

All works fine however in my next view when I am trying to link my newly created field Customer with Customer Master table to display the customer name, my customer name shows as Null.

Customer Code in Master Customer Table is a char(7) and a primary key.

2

Post the view defintion, the output of the view for some Customer (1 row), the second view definition and the row from the Customer Master table you are expecting to get the customer name from.

Also, does the first view output the new field as a char(7)? Is it padded with spaces? What about the customer master table? Is the Customer Code column also padded with spaces?

3

It could be because there are leading or trailing spaces. With CHAR data type, if the data is less than the declared length, it would pad with spaces. In your join condition, try something like this:

LTRIM(RTRIM(ColFromA)) = LTRIM(RTRIM(ColFromB))
4

SELECT LEFT(STUFF(Comment, 1, CHARINDEX('Cus:', Comment + 'Cus:') + 3, ''), 7) AS Customer, Comment, TimeStamp, GlYear, GlPeriod
FROM dbo.GenTransaction
WHERE (Comment LIKE '%Cus:%')

One row is like this:
ID00001 Jnl: 1 Cus:ID00001 Br:ID Area:BC Payment 2014 12

sample cust table
CD00004 Family Trust Famil Y Y

How I worked around this problem is; Created a view first from Customer Master and then linked view to view.

5

Creating a view in and of itself should not really change anything in regards to a column - in your case CustomerId. So unless you cast it to some other data type, or did some other kind of data transformation in your view created out of the Customer Master, it is puzzling why creating a view helped. My concern is that if we don't understand why it worked, it may fail in the future when you least expect it to, or at the most inopportune moment.

6

isn't this simpler?

    select replace(comment, 'Cus:', '') as Customer,
    Comment, TimeStamp, GlYear, GlPeriod
    FROM dbo.GenTransaction
    WHERE (Comment LIKE '%Cus:%')

Also, please post the row from your cust table with id = 'ID00001'