Samples of T-SQL queries for applications (for example – Web Application (WebApp)).
(For queries stated below It is necessary to pay attention to that the result query implementation located in the right column are fitted with CUEAV and SourceDB_For_EAV have Cyrillic_General_CI_AS collation)
The order of user’s operations consists of the following steps:
WebApp executes query | and gives user management tool to select necessary class. Let’s suppose it will be class Type1_BloodTest where ID = 4. |
SELECT ClassID, Code FROM Class |
|
WebApp executes query | to determine classes for which Type1_BloodTest is descendant because it is necessary to know identifiers of patient and his hospitalizations. |
WITH Tree(ClassID, ParentClassID) AS (SELECT ClassID, ParentClassID FROM ClassLinks WHERE ClassID = 4 UNION ALL SELECT p.ClassID, p.ParentClassID FROM ClassLinks P INNER JOIN Tree A ON A.ParentClassID = P.ClassID ) SELECT Code, Class.ClassID FROM Tree INNER JOIN Class ON Tree.ClassID = Class.ClassID |
|
WebApp executes query. | User selects patient Cambell where ObjectID = 8. |
SELECT Attribute.Code AS AttributeCode, Value.Value, Object.ObjectID FROM Attribute INNER JOIN Class ON Attribute.ClassID = Class.ClassID INNER JOIN Object ON Class.ClassID = Object.ClassID INNER JOIN Value ON Attribute.AttributeID = Value.AttributeID AND Object.ObjectID = Value.ObjectID WHERE (Class.ClassID = 3) AND (Attribute.Code='Surname') |
|
WebApp executes query. | User selects hospitalization dated 15/05/2012 where OblectID = 2. |
SELECT Attribute.Code AS AttributeCode, Value.Value, Object.ObjectID FROM Attribute INNER JOIN Class ON Attribute.ClassID = Class.ClassID INNER JOIN Object ON Class.ClassID = Object.ClassID INNER JOIN Value ON Attribute.AttributeID = Value.AttributeID AND Object.ObjectID = Value.ObjectID WHERE (Value.ObjectID IN ( SELECT Object.ObjectID FROM Attribute INNER JOIN Class ON Attribute.ClassID = Class.ClassID INNER JOIN Object ON Class.ClassID = Object.ClassID INNER JOIN Value ON Attribute.AttributeID = Value.AttributeID AND Object.ObjectID = Value.ObjectID WHERE (Class.ClassID = 1) AND (Value.Value = (SELECT Value.Value FROM Attribute INNER JOIN Class ON Attribute.ClassID = Class.ClassID INNER JOIN Object ON Class.ClassID = Object.ClassID INNER JOIN Value ON Attribute.AttributeID = Value.AttributeID AND Object.ObjectID = Value.ObjectID WHERE (Value.ObjectID = 8) AND (Attribute.Code = 'Type1_PatientID')) ) ) AND Attribute.Code = 'Date' ) |
|
WebApp executes query. | User selects analysis dated 17/05/2012 where ObjectID = 13. |
SELECT Attribute.Code AS AttributeCode, Value.Value, Object.ObjectID FROM Attribute INNER JOIN Class ON Attribute.ClassID = Class.ClassID INNER JOIN Object ON Class.ClassID = Object.ClassID INNER JOIN Value ON Attribute.AttributeID = Value.AttributeID AND Object.ObjectID = Value.ObjectID WHERE (Value.ObjectID IN ( SELECT Object.ObjectID FROM Attribute INNER JOIN Class ON Attribute.ClassID = Class.ClassID INNER JOIN Object ON Class.ClassID = Object.ClassID INNER JOIN Value ON Attribute.AttributeID = Value.AttributeID AND Object.ObjectID = Value.ObjectID WHERE (Class.ClassID = 4) AND (Value.Value = (SELECT Value.Value FROM Attribute INNER JOIN Class ON Attribute.ClassID = Class.ClassID INNER JOIN Object ON Class.ClassID = Object.ClassID INNER JOIN Value ON Attribute.AttributeID = Value.AttributeID AND Object.ObjectID = Value.ObjectID WHERE (Value.ObjectID = 2) AND (Attribute.Code = 'Type1_HospitalisationID')) ) ) AND Attribute.Code = 'Date' ) |
|
WebApp executes query to prepare editing date form. | User changes values of Sodium from 140 to 142 and Potassium from 3.5 to 3.3 |
SELECT ValueID, Attribute.Code, Value FROM Attribute INNER JOIN Class ON Attribute.ClassID = Class.ClassID INNER JOIN Object ON Class.ClassID = Object.ClassID INNER JOIN Value ON Attribute.AttributeID = Value.AttributeID AND Object.ObjectID = Value.ObjectID WHERE (Value.ObjectID = 13) AND (Attribute.Code = 'Date' OR Attribute.Code = 'Sodium' OR Attribute.Code = 'Potassium' OR Attribute.Code = 'Urea') |
|
WebApp executes query to change CUEAV data. | |
UPDATE Value SET Value='142' WHERE ValueID = 55
UPDATE Value SET Value='3.4' WHERE ValueID = 56 |
Principle scheme of building the query
2) SELECT Value.Value
……………………………………………
……………………………………………
WHERE (Attribute.Code = ‘Type1_PatientID’)
AND Value.OblectID IN
1) SELECT Value.ObjectID
…………………………………………..
……………………………………………
WHERE (Class.code = ’Type1_Patient’) AND (Attribute.Code = ‘Surname’)
AND (Value.Value = ‘Cambel’)
Subqueries 1) and 2) are iterated for all classes of hierarchy with necessary filters.