Code Snippet
CREATE Table events (
id INT not null,
PxMiss Real Not Null,
PyMiss Real Not Null,
filenames Varchar(50));
ALTER TABLE events
ADD CONSTRAINT pk_particle PRIMARY KEY (id,filenames);
GO
CREATE Table Muon (
idap INT Not Null,
id INT Not Null,
eventid INT Not Null,
Px Real,
Py Real,
Pz Real,
Kf Real,
Ee Real);
GO
CREATE Table Electron(
idap INT Not Null,
id INT NOT NULL,
eventid INT Not Null,
Px Real,
Py Real,
Pz Real,
Kf Real,
Ee Real);
GO
CREATE Table Jet (
idap INT Not Null,
id INT NOT NULL,
eventid INT Not Null,
Px Real,
Py Real,
Pz Real,
Kf Real,
Ee Real);
GO
Create View lepton AS
select * from Muon
Union all
select * from Electron;
GO
Create View particle AS
select * from lepton
Union all
select * from Jet;
GO
I need that every particle had a different idap, but all the date is filled in muon, electron and jet. and then is joined in a view called particle.
The way that you are going about this is likely to cause some problems.
For example, while it is possible to create a VIEW that would provide a unique idap for for each Particle, since it is a view and will be re-constituted at every execution, there is no certainity that the idap will be the same at each execution. (In my opinion -that is a big issue. -but maybe not for your situation...)
It seems more stable if you were to create a Particle idap that was the combination of each constituent Identifier + idap. Something like this for example:
CREATE VIEW Particle
AS
SELECT
'M' + cast( idap AS varchar(12)),
id,
eventid,
Px,
Py
Pz
Kf,
Ee
FROM Muon
UNION ALL
SELECT
'E' + cast( idap AS varchar(12),
id,
eventid,
Px,
Py,
Pz,
Kf,
Ee
FROM Electron
UNION ALL
SELECT
'J' + cast( idap AS varchar(12),
id,
eventid,
Px,
Py,
Pz,
Kf,
Ee
FROM Jet
GO
With this example, each idap will be unique, and also give you a clue about the constitutent component.
