Report Model Design - defining a logical primary key

• venus.pvr

  SSCarpal Tunnel

  Points: 4813

  More actions

  October 18, 2010 at 3:09 pm

  #225113

  Hi,

  I have a primary key table with Master data for which I have defined a primary Key.

  But my foreign key table is a transactional detail table which have duplicate records except for the identity field which is not included in the Named Query. The Named Query is a UNION ALL of records from two different transactional tables which have their own Identity primary Key column.

  How should I define primary key for the transactional Named query. Which is the best way to design the Model in this situation. I have several such transactional Named queries for which I am not able to define a logical primary key.

  Please suggest.

  Thanks in advance!

• etiennelopes

  SSC Enthusiast

  Points: 114

  More actions

  December 9, 2010 at 5:19 am

  #1260500

  Hi, here is a sugestion:

  Create a View like this:

  CREATE VIEW VWAuxTable

  AS

  SELECT ROW_NUMBER() OVER (ORDER BY c1) PK, c2 FROM

  (

  SELECT c1, c2 FROM TABLE1

  UNION ALL

  SELECT c1, c2 FROM TABLE2

  ) T

  Where TABLE1 and TABLE2 are your own tables and c1 is the identity column from each.

  Now go to your dsv and add this View to it and then right click the PK field and set your Logical Primary Key

  Hope this helps!

• venus.pvr

  SSCarpal Tunnel

  Points: 4813

  More actions

  December 9, 2010 at 7:38 am

  #1260561

  I did the same. I posted it here to see if I get something better long ago.

  Thank you for the response!