Topic: A little conditions join for the ambiguous data
Prompt, how it is better such to implement?
It is necessary to connect among themselves two data sets for which conditions on binding can depend on several factors.
There is a Client. The Client has child Services of different Types. For the new data restriction is observed that at the Client can be no more than one Service of each Type. But for old Clients of such restriction was not and some Clients can have some Services of one type. Services have a dial-up of the various Attributes which composition depends on service Type.
There are two Types of services which are logically coordinated with each other at level of business processes. At level of a database of service it is possible to connect among themselves (for this purpose in a DB there is a special table of communications), however in an information system for operators it is not provided GUI for this operation, communication is installed by means of administrative tools of an information system.
It is necessary to synchronize by means of an indirect script them among themselves. At service synchronization are compared among themselves on following algorithm (rules are applied within the limits of one Client, as their listing):
0. If services are present at the table of communications existing communication is always used.
1. If there is exactly one Service with type Tip1 and is equal one service with type Tip2 they communicate (and this communication is added in the table of communications).
2. Service with type Tip1 has Atribut1. Service with type Tip2 has Atribut2. If at services of different types values of Atribut1 and Atribut2 they communicate coincide.
3. Service with type Tip1 has an attribute Login. Service with type Tip2 has an attribute of DataDobavlenija. Untied services are arranged on the above-stated attributes and communicate on ordinal value.
4. The remained Services are fixed in log, in them the manager of an information system manually is engaged.
There is a sampling for Services of type Tip1 about following a type:
select rank () over (partition by S.CUSTOMER_ID order by S.LOGIN) as "IDX" - ordinal value of Service for the Client , count (*) over (partition by S.CUSTOMER_ID) as "CNT" - an amount of Services at the Client , S.CUSTOMER_ID - The identifier of the Client , S.SERVICE_ID - The Service identifier , S.LOGIN - Attribute Login , RR.VALUE - Attribute of Atribut1 ... from SERVICES S join RADREPLY RR on (RR.SERVICE_ID = S.SERVICE_ID and RR.ATTRIBUTE = ' Framed-IP-Address' and RR.DATE_BEG <= sysdate and (RR.DATE_END is null or RR.DATE_END> sysdate)) where...
There is a sampling for Services of type Tip2 about following a type:
select rank () over (partition by S.CUSTOMER_ID order by S.CREATE_DATE) as "IDX" - ordinal value of Service for the Client , count (*) over (partition by S.CUSTOMER_ID) as "CNT" - an amount of Services at the Client , S.CUSTOMER_ID - The identifier of the Client , S.SERVICE_ID - The Service identifier , S.LOGIN - Attribute of Atribut2 , S.CREATE_DATE - Attribute of DataDobavlenija , L.SERVICE_ID as "PARENT_ID" - existing communication with services of Tip1 ... from BM_TARIFF T join SERVICES S on (S.TARIFF_ID = T.TARIFF_ID and S.STATUS! =-20) left join SERVICES_LINK L on (L.SERVICE_ID_1 = S.SERVICE_ID and L.SERVICE_ID_2 is null and L.MNEMONIC = ' parent ') where...
Separately these requests return the correct results.
How to me them to connect among themselves?
with SET1 as (...) - services of Tip1 , SET2 as (...) - services of Tip2 select * from SET1 left join SET2 on ( SET2.PARENT_ID = SET1.SERVICE_ID or (SET1.CNT = 1 and SET2.CNT = 1 and SET1.CUSTOMER_ID = SET2.CUSTOMER_ID) or (SET1.CUSTOMER_ID = SET2.CUSTOMER_ID and SET1.VALUE = SET2.LOGIN and SET1.CUSTOMER_ID! = SET2.CUSTOMER_ID) or (SET1.CUSTOMER_ID = SET2.CUSTOMER_ID and SET1.IDX = SET2.IDX and SET1.VALUE! = SET2.LOGIN and SET1.CUSTOMER_ID! = SET2.CUSTOMER_ID) )
Or there can be a condition connection to describe through CASE (then it will not be necessary to repeat the inverted previous conditions)?