-
вопрос знатокам:
как правильно задать параметры для такого запроса ?
в ADODataSet определен такой запрос:
------------------------------------------------------
SELECT
o.overall_id
,overall_num
,designation
,overall_name
,duration
,ss.safety_standards_id
,safety_assurance_id
,overall_state_id
,size1
,size2
,sa_start_date
,sa_end_date
,sa_start_date+duration*30.5 sa_plan_date
,CAST(sa_start_date+duration*30.5+1-GETDATE() AS NUMERIC) rate
,relevant
FROM v_overall o
JOIN v_safety_standards ss
ON o.overall_id = ss.overall_id
LEFT OUTER JOIN
(
SELECT
safety_assurance_id
,safety_standards_id
,overall_state_id
,size1
,size2
,sa_start_date
,sa_end_date
,relevant
FROM v_safety_assurance
WHERE staff_id=:STAFF_ID
UNION
SELECT
null safety_assurance_id
,safety_standards_id
,null overall_state_id
,MAX(size1)
,MAX(size2)
,null sa_start_date
,null sa_end_date
,1 relevant
FROM v_safety_assurance
WHERE staff_id=:STAFF_ID
AND relevant = 0
GROUP BY safety_standards_id
) sa
ON ss.safety_standards_id = sa.safety_standards_id
WHERE ss.profession_id=:PROFESSION_ID
------------------------------------------------------
соответственно имеем параметры в запросе:
:STAFF_ID
:PROFESSION_ID
компонент просит определить 3 параметра в свойствах:
:STAFF_ID
:STAFF_ID
:PROFESSION_ID
параметры передаются из родительского запроса, через связь мастер-деталь, откуда можно забрать 2 параметра (:STAFF_ID, :PROFESSION_ID).
вопрос знатокам:
как правильно задать параметры для такого запроса ?
-
DECLARE @PROFESSION_ID INT, @STAFF_ID INT
SET @STAFF_ID=:STAFF_ID
SET @PROFESSION_ID = :PROFESSION_ID
SELECT o.overall_id,
overall_num,
designation,
overall_name,
duration,
ss.safety_standards_id,
safety_assurance_id,
overall_state_id,
size1,
size2,
sa_start_date,
sa_end_date,
sa_start_date + duration * 30.5 sa_plan_date,
CAST(sa_start_date + duration * 30.5 + 1 -GETDATE() AS NUMERIC) rate,
relevant
FROM v_overall o
JOIN v_safety_standards ss
ON o.overall_id = ss.overall_id
LEFT OUTER JOIN (
SELECT safety_assurance_id,
safety_standards_id,
overall_state_id,
size1,
size2,
sa_start_date,
sa_end_date,
relevant
FROM v_safety_assurance
WHERE staff_id = @STAFF_ID
UNION
SELECT NULL safety_assurance_id,
safety_standards_id,
NULL overall_state_id,
MAX(size1),
MAX(size2),
NULL sa_start_date,
NULL sa_end_date,
1 relevant
FROM v_safety_assurance
WHERE staff_id = @STAFF_ID
AND relevant = 0
GROUP BY
safety_standards_id
) sa
ON ss.safety_standards_id = sa.safety_standards_id
WHERE ss.profession_id = @PROFESSION_ID
-
спасибо !!!
|
Есть новые 
Нет новых [134430 +1][b:0][p:0.002]