Dmitry
1.
2.
select a.comp_id,b.adl_x,coalesce(a.comp_id,b.adl_x) from
automation a join adl b on (a.ato_id=b.adl_id)
Нормально работает. возвращает N записей.
Ex_Soft
Выше приведенный пример - утрирован. На самом деле все гораздо сложнее и побольше. Однако замечено: coalesce корректно работает до определенного предела.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
select
C.CONTRACT_ID,
coalesce(CA.COMMISSION_TYPE,IAP.AGENT_COMMISSION_TYPE) as COMMISSION_TYPE,
coalesce(CT1.CONTRACT_PARAM_NAME,CT2.CONTRACT_PARAM_NAME) as COMMISSION_TYPE_NAME,
case CA.COMMISSION_TYPE
when 0 then CA.COMMISSION_VALUE
when 1 then CA.COMMISSION_SUM
else IAP.AGENT_COMMISSION
end as AGENT_COMMISSION
from
CONTRACT C
join CONTRACT_AGENTS CA on (CA.CONTRACT_ID=C.CONTRACT_ID)
join COMIS_TYPE CT1 on (CT1.CONTRACT_PARAM_ID=CA.COMMISSION_TYPE)
join INSURANCE_AGENTS IA on (IA.AGENT_ID=CA.AGENT_ID)
join INSURANCE_AGENTS_PARAM IAP on (IAP.AGENT_MASTER_INTERNAL_ID=IA.AGENT_INTERNAL_ID)
join COMIS_TYPE CT2 on (CT2.CONTRACT_PARAM_ID=IAP.AGENT_COMMISSION_TYPE)
where
(C.RECORD_STATE= 100 )
and (CA.RECORD_STATE= 100 )
and (CT1.RECORD_STATE= 100 )
and (IA.RECORD_STATE= 100 )
and (IAP.RECORD_STATE= 100 )
and (CT2.RECORD_STATE= 100 )
работает
Но стоит добавить
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
select
C.CONTRACT_ID,
CA.AGENT_ID,
coalesce(CA.COMMISSION_TYPE,IAP.AGENT_COMMISSION_TYPE) as COMMISSION_TYPE,
coalesce(CT1.CONTRACT_PARAM_NAME,CT2.CONTRACT_PARAM_NAME) as COMMISSION_TYPE_NAME,
case CA.COMMISSION_TYPE
when 0 then CA.COMMISSION_VALUE
when 1 then CA.COMMISSION_SUM
else IAP.AGENT_COMMISSION
end as AGENT_COMMISSION
from
CONTRACT C
join CONTRACT_AGENTS CA on (CA.CONTRACT_ID=C.CONTRACT_ID)
join COMIS_TYPE CT1 on (CT1.CONTRACT_PARAM_ID=CA.COMMISSION_TYPE)
join INSURANCE_AGENTS IA on (IA.AGENT_ID=CA.AGENT_ID)
join INSURANCE_AGENTS_PARAM IAP on (IAP.AGENT_MASTER_INTERNAL_ID=IA.AGENT_INTERNAL_ID)
join COMIS_TYPE CT2 on (CT2.CONTRACT_PARAM_ID=IAP.AGENT_COMMISSION_TYPE)
where
(C.RECORD_STATE= 100 )
and (CA.RECORD_STATE= 100 )
and (CT1.RECORD_STATE= 100 )
and (IA.RECORD_STATE= 100 )
and (IAP.RECORD_STATE= 100 )
and (CT2.RECORD_STATE= 100 )
и усьо...
Но!!! Если его тупо выполнить ДВА раза
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
select
C.CONTRACT_ID,
CA.AGENT_ID,
coalesce(CA.COMMISSION_TYPE,IAP.AGENT_COMMISSION_TYPE) as COMMISSION_TYPE,
coalesce(CT1.CONTRACT_PARAM_NAME,CT2.CONTRACT_PARAM_NAME) as COMMISSION_TYPE_NAME,
case CA.COMMISSION_TYPE
when 0 then CA.COMMISSION_VALUE
when 1 then CA.COMMISSION_SUM
else IAP.AGENT_COMMISSION
end as AGENT_COMMISSION
from
CONTRACT C
join CONTRACT_AGENTS CA on (CA.CONTRACT_ID=C.CONTRACT_ID)
join COMIS_TYPE CT1 on (CT1.CONTRACT_PARAM_ID=CA.COMMISSION_TYPE)
join INSURANCE_AGENTS IA on (IA.AGENT_ID=CA.AGENT_ID)
join INSURANCE_AGENTS_PARAM IAP on (IAP.AGENT_MASTER_INTERNAL_ID=IA.AGENT_INTERNAL_ID)
join COMIS_TYPE CT2 on (CT2.CONTRACT_PARAM_ID=IAP.AGENT_COMMISSION_TYPE)
where
(C.RECORD_STATE= 100 )
and (CA.RECORD_STATE= 100 )
and (CT1.RECORD_STATE= 100 )
and (IA.RECORD_STATE= 100 )
and (IAP.RECORD_STATE= 100 )
and (CT2.RECORD_STATE= 100 )
select
C.CONTRACT_ID,
===>CA.AGENT_ID,
coalesce(CA.COMMISSION_TYPE,IAP.AGENT_COMMISSION_TYPE) as COMMISSION_TYPE,
coalesce(CT1.CONTRACT_PARAM_NAME,CT2.CONTRACT_PARAM_NAME) as COMMISSION_TYPE_NAME,
case CA.COMMISSION_TYPE
when 0 then CA.COMMISSION_VALUE
when 1 then CA.COMMISSION_SUM
else IAP.AGENT_COMMISSION
end as AGENT_COMMISSION
from
CONTRACT C
join CONTRACT_AGENTS CA on (CA.CONTRACT_ID=C.CONTRACT_ID)
join COMIS_TYPE CT1 on (CT1.CONTRACT_PARAM_ID=CA.COMMISSION_TYPE)
join INSURANCE_AGENTS IA on (IA.AGENT_ID=CA.AGENT_ID)
join INSURANCE_AGENTS_PARAM IAP on (IAP.AGENT_MASTER_INTERNAL_ID=IA.AGENT_INTERNAL_ID)
join COMIS_TYPE CT2 on (CT2.CONTRACT_PARAM_ID=IAP.AGENT_COMMISSION_TYPE)
where
(C.RECORD_STATE= 100 )
and (CA.RECORD_STATE= 100 )
and (CT1.RECORD_STATE= 100 )
and (IA.RECORD_STATE= 100 )
and (IAP.RECORD_STATE= 100 )
and (CT2.RECORD_STATE= 100 )
- первый резалтсет - пустой
, а второй резалтсет уже такой как надо...
_________________
"Helo, word!" - 17 errors 56 warnings
Posted via ActualForum NNTP Server 1.4
