typedView generating wrong View Field Characteristics

Posts   
 
    
llblboy
User
Posts: 15
Joined: 18-Apr-2018
# Posted on: 25-Oct-2018 16:21:48   

Hello and sorry for asking a question that might have already been answered, but I've don a search on the forums and have not found any clues to what might be going wrong. Basically, I have a View on the SQL Server that is returning a column (AvgCost) that should have datatype DECIMAL(12,2), however the generated code is creating a Typed View Field that has characteristics that don't seem to match what should be expected. Attached is a screen shot of the conflict in crucible (version control).

Also, I've included the two functions (GetKitAvgCost and GetItemAverageCost) that are called (one depending on one condition and another on a different condition) that should (as I understand it) be returning DECIMAL(12,2).

edit: my supervisor said I could not post the details of our functions.

Any insight to what I am missing would be much appreciated.

Attachments
Filename File size Added on Approval
screenshot.png 15,749 25-Oct-2018 16:25.56 Approved
Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 25-Oct-2018 18:17:02   

Edit the TypedView in the Designer and go to FieldMappings, then select the Field. What's the precision and scale values in the Field Mapping?

llblboy
User
Posts: 15
Joined: 18-Apr-2018
# Posted on: 26-Oct-2018 16:00:52   

@Walaa Thank you for getting back with me. Currently I'm trying to fix this by altering the precision of the view in SQL Server Management Studio or via an ALTER query. Not having much success, but I will post the solution when I have one.

The original ''' View field characteristics (type, precision, scale, length): Decimal, 30, 15, 0</remarks> is now: ''' View field characteristics (type, precision, scale, length): Decimal, 28, 13, 0</remarks>

I realized that in SSMS the column (alias AvgCost) is set to the 28, 13. I am trying to find a way to do that and I am not having any success. If you know of a way for me to do that I would be very grateful.

Thank you, Jonathan

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 26-Oct-2018 17:40:56   

I think you ran into the phenomenon where sql server doesn't update the metadata of a view with updated metadata of the underlying tables until the view is dropped/recreated.

E.g. if you do in a view:

select * from customers

and then alter the customers table to change some length of a field in customers, the view metadata might not get updated. LLBLGen Pro pulls the metadata from the system views, but this thus can be outdated.

So if you go into SSMS, simply open the view in create script. Then drop it, then recreated it and then in llblgen pro sync with the catalog which should pull the right metadata into the project.

We use the following query:


SELECT INFORMATION_SCHEMA.COLUMNS.*, 
    (SELECT COLUMNPROPERTY(OBJECT_ID(@sFullTableName), 
    INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsComputed')) AS IsComputed, 
    (SELECT COLUMNPROPERTY(OBJECT_ID(@sFullTableName), 
    INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsIdentity')) AS IsIdentity, 
    (SELECT COLUMNPROPERTY(OBJECT_ID(@sFullTableName), 
    INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsRowGuidCol')) AS IsRowGuidColumn, 
    (SELECT COLUMNPROPERTY(OBJECT_ID('@sFullTableName'), 
    INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'GeneratedAlwaysType')) AS GeneratedAlwaysType 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME='<your table>'
    AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA='<your schema>' ORDER BY INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION ASC

(replace <your table> with the name of the view, <your schema> with the name of the schema (e.g. dbo) and @sFullTableName with the string [schema].[view] (or remove these fields from the query , as views don't need those)

This should give you the info the designer sees as well, which might give you an idea where the problem is...

Frans Bouma | Lead developer LLBLGen Pro
llblboy
User
Posts: 15
Joined: 18-Apr-2018
# Posted on: 26-Oct-2018 23:45:03   

Hello Otis and thank you and Walaa for your help with this. As it turns out, I was able to solve the problem by going into the view and adding CONVERT function so that it looked like this: SELECT AliasName = CONVERT(DECIMAL(30,15), [the COALESCE function, etc.]) Now SSMS is showing the correct precision and llbl is detecting / generating the correct precision as well. Sorry for the confusion and once again, thank you.