declare @account table(
  [BankId] decimal(5)
,  [AccountNumber] varchar(10)
,  [ClientId] varchar(10)
)

declare @customer table(
  [BankId] decimal(5)
,  [ClientId] varchar(10)
,  [CustomerNumber] varchar(10)
)

insert @account
select
  [BankId]
,  [AccountNumber]
,  [ClientId]
from
  [Src].[Account]

insert @customer
select
  [BankId]
,  [ClientId]
,  [OfficialId]
from
  [Src].[Client]

-->EntryPoint<--
select
  A.[BankId]
,  A.[AccountNumber]
,  B.[CustomerNumber]
from
  @account as A
  inner join @customer as B
  on  
    A.[BankId] = B.[BankId]
  and A.[ClientId] = B.[ClientId]

Query contains a common table expression (CTE)

The query below contains a CTE:

with account as (
  select
    [BankId]
  ,  [AccountNumber]
  ,  [ClientId]
  from
    [Src].[Account]
)

-->EntryPoint<--
select
  A.[BankId]
,  A.[AccountNumber]
,  B.[OfficialId]
from
  account as A
  inner join [Src].[Client] as B
  on  
    A.[BankId] = B.[BankId]
  and A.[ClientId] = B.[ClientId]

select
  A.[BankId]
,  A.[AccountNumber]
,  A.[AccountType]
,  B.[TargetProductCode]
,  case B.[SourceProductType]
      when '' then null
      else B.[SourceProductType]
   end as [SourceProductType]
from
  Src.[Account] as A
  inner join Vs.[TranslateProductTypes] as B
  on
       A.[BankId] = B.[BankId]
  and  A.[AccountType] = B.[SourceProductType]

A click on the Synchronize button in the dialog will create the Exposed Fields on the view.

Cast

Both the list of Exposed Fields and the Synch dialog has a 'Cast' column. This is order for you to impose your knowledge of the actual data in the staging database.

Let's take for instance the column SourceProductType returned by the view:

case B.[SourceProductType]

   when '' then null

   else B.[SourceProductType]

end as [SourceProductType]

This case statement clearly means that the view may return a null value for the SourceProductType. That is also why the SourceProductType is marked as nullable in the Exposed Fields.

But say for instance that you know for sure there are no matching rows in the Valueset Vs.TranslateProductTypes that has an empty SourceProductType. So you know that the column SourceProductType returned by the view will never be null.

But if you remove the check mark for Nulls for the exposed Field SourceProductType, the Studio validation will complain:

By placing a check mark in the Cast column for SourceProductType, you tell Studio that you know what you are doing and that the query will never return Null for this column. 

So now the Studio validation will not complain anymore. On the other hand, if it turns out that you are wrong and the query actually does return null then the Hopp Runtime will fail when trying to populate the view.

Viewing the Referenced Objects

Clicking the Objects... button below the query will discover the objects actually referenced by the SQL query and open a dialog displaying a list of the Referenced Objects:

select
  A.[BankId]
,  A.[AccountType]                                           -- Moved
,  cast(A.[AccountNumber] as decimal(16)) as [AccountNumber] -- Datatype changed
--,  B.[TargetProductCode]                                   -- No longer returned
--,  case B.[SourceProductType]                              -- No longer returned
--      when '' then null
--      else B.[SourceProductType]
--   end as [SourceProductType]
,  B.[CustomerNumber]                                        -- Added
from
  Src.[Account] as A
  --inner join Vs.[TranslateProductTypes] as B	             -- No longer referenced
  --on
  --     A.[BankId] = B.[BankId]
  --and  A.[AccountType] = B.[SourceProductType]
  inner join Vw.[Customer] as B                              -- Added reference
  on
       A.[BankId] = B.[BankId]
  and  A.[ClientId] = B.[ClientId]

Synchronizing Exposed Fields

Again, synchronizing the Exposed Fields is easy and transparent, again making it clear what changes the synchronization will make to the Exposed Fields:

A click on the Synchronize button will automatically synchronize the list of Exposed Fields with the columns returned by the query.

Cast

The new query returns the AccountNumber column with a new data type, Num(16) instead of Num(10) and it proposes to update the data type of the Exposed Field accordingly.

The Cast column in the Synch dialog lets you lock in the existing Num(10) data type of the Exposed Field. This means that synchronize will not change the data type of the Exposed Field - but it will put a check mark in the Cast column for the field.

Referenced Objects

The Objects... button will now show the objects referenced by the modified query:

Testing the view

The query can be copied to the clipboard with the Copy button:

In addition to copying the query itself, Studio will also add a table variable containing the Exposed Fields defined by the view - including the data types defined on the exposed fields.

You can paste the query into Sql Server management Studio and test it there. The result of the query will be inserted into the table variable, and any data type discrepancies in columns you have Cast will be uncovered.