I am running into a issue while trying to map a CDT to a Database view. Backgrou

georgej over 9 years ago

I am running into a issue while trying to map a CDT to a Database view. Background is ,we had a view which selects records by joining multiple tables. While defining the CDT we had defined one of the columns with @Id JPA Annotations. Everything worked fine ,but as part of an ongoing enhancement we changed the view to have an additional table. When joining with this table ,the column which had @Id is no more unique ,it has duplicate values. We are using a query entity to return data from this view ,but with this @Id having duplicate values ,"totalCount" returned takes only the distinct values of this ID. This in-turn mess up our Grid ; I tried making composite primary key by defining some additional columns with @Id. It is also not working ,possibly because those columns are VARCHAR

Discussion posts and replies are publicly visible

0 PhilB
A Score Level 1
over 9 years ago

Which database are you using? If it's MySQL, try using UUID() for the ID column value.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 georgej over 9 years ago

It is a view and we don't want to write ; we just want to read the data
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 PhilB
A Score Level 1
over 9 years ago

I understand that you don't want to write to the view, and I'm certainly not suggesting you do that; I'm suggesting you create your view something like the below:

CREATE VIEW view_name AS SELECT UUID() AS 'id', col1 AS 'column1'...

The UUID() function will generate a unique entry for each row, solving the problem you describe above.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 georgej over 9 years ago

Ok got it, so that means we have to change view to generate a unique column to map in CDT.Is there no other alternative?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 PhilB
A Score Level 1
over 9 years ago

It's also possible to do the same thing with row numbering or generating keys by concatenating values (as you've tried), but I've found UUID() reliable as it should guarantee a unique ID for each row regardless of data.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 nageswararaoa over 9 years ago

add one more column in your view by concatenating the values which are used in grouping.it will be act as a unique value in your view
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Chris over 9 years ago

Also to note, if you are using MS SQL you can use this in your select statement to create a unique ID column named 'Row':

SELECT TOP (100) PERCENT ROW_NUMBER() OVER (ORDER BY <any_column>) AS Row, ...
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 narasimhaadityac
A Score Level 2
over 9 years ago

Hi George, General practice is to either add a new column for sequence (uniqueness) or concatenate all the natural keys (which are business data which will make the column unique).
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 PhilB
A Score Level 1
over 9 years ago

Concatenating all keys does not guarantee uniqueness. As above, I'd suggest using either UUID() or row numbers as both guarantee uniqueness.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel