zOs/SQL/EXPLAIEL
set current sqlid = 'A540769';
delete from plan_table;
set current path = oa1t;
explain plan set queryno = 1 for
SELECT '1' FROM oa1t.VEL005A1V B
WHERE (B.CPID = ? AND B.OWNER_CIF
= '') OR B.USER_CIF = ? OR B.USER_ID = ? OR B.COLLECTION_ID = ?
;
select * from plan_view2
order by applname, progname, queryNo, qblockno, planno
;
rollback
;;;;
explain plan set queryno = 1 for
SELECT DISTINCT
CAST(P.PARTNER_ID AS CHAR(12)) AS CIF
,CAST(P.BU AS CHAR(04)) AS BU
,CAST(LE.LE AS CHAR(04)) AS LE
,CAST(
CASE
WHEN COALESCE(P.CUSTOMER_TYPE,' ') = ' ' THEN '000'
ELSE P.CUSTOMER_TYPE
END AS CHAR(03)) AS CLIENT_TYPE
,CAST(
CASE
WHEN COALESCE(P.CUSTOMER_SEGMENT,' ') = ' ' THEN '000'
ELSE P.CUSTOMER_SEGMENT
END AS CHAR(03)) AS CLIENT_SEGMENT
,CAST(COALESCE(TCD.COUNTRY_CODE,'001')
AS CHAR(03)) AS NAT_CODE
,CAST(COALESCE(TCD.COUNTRY_CODE_ISO2,'EU')
AS CHAR(02)) AS NAT_CODE_ISOA2
,CAST(COALESCE(TCD.COUNTRY_CODE_ISO3,'EU ')
AS CHAR(03)) AS NAT_CODE_ISOA3
,CAST(P.DOMIZIL AS CHAR(03)) AS DOMICILE_CODE
,CAST(DOM.COUNTRY_CODE_ISO2 AS CHAR(02)) AS DOMICILE_CODE_ISOA2
,CAST(DOM.COUNTRY_CODE_ISO3 AS CHAR(03)) AS DOMICILE_CODE_ISOA3
,CAST('A ' AS CHAR(02)) AS STATUS
,CAST(
CASE
WHEN COALESCE(TCD.CD100452,' ') = ' '
THEN ' '
ELSE TCD.CD100452
END AS CHAR(16)) AS CLIENT_OFFICE
,CAST(P.COMMENT AS CHAR(255)) AS COMMENTS
,CAST(CASE
WHEN COALESCE(TCD.CD100322,' ') = ' ' THEN 0
WHEN SUBSTR(CD100322,1,4) = '0000' THEN 0
ELSE
(SMALLINT(YEAR(CURRENT DATE)) -
SMALLINT(SUBSTR(CD100322,1,4)))
END AS SMALLINT) AS AGE
,TIMESTAMP(P.LOCK_FROM_DATE,'00.00.00') AS LOCK_FROM
,TIMESTAMP(P.LOCK_TO_DATE,'00.00.00') AS LOCK_TO
,P.LAST_UPDATE_TIME AS LAST_UPDATE_TIME
,CAST(P.LAST_UPDATE_ID AS CHAR(10)) AS LAST_UPDATE_ID
,P.CONTROL_TIME AS CONTROL_TIME
FROM
oa1t.VEL105A1V LE ,
oa1t.VEL140A1V DOM,
oa1t.VELKS831A1VC4 P
LEFT OUTER JOIN
(SELECT CD100001
,CD100004
,CD100452
,CD100322
,COUNTRY_CODE
,COUNTRY_CODE_ISO2
,COUNTRY_CODE_ISO3
FROM oa1t.TCD100A1
,oa1t.VEL140A1V
WHERE COUNTRY_CODE = CD100325 ) AS TCD
ON P.PARTNER_ID = TCD.CD100001
AND P.BU = TCD.CD100004
WHERE LE.BU = P.BU
AND DOM.COUNTRY_CODE = P.DOMIZIL
AND CURRENT DATE BETWEEN P.VALID_FROM_DATE AND P.VALID_TO_DATE
UNION
SELECT DISTINCT
CAST(P.PARTNER_ID AS CHAR(12)) AS CIF
,CAST(P.BU AS CHAR(04)) AS BU
,CAST(LE.LE AS CHAR(04)) AS LE
,CAST(
CASE
WHEN COALESCE(P.CUSTOMER_TYPE,' ') = ' ' THEN '000'
ELSE P.CUSTOMER_TYPE
END AS CHAR(03)) AS CLIENT_TYPE
,CAST(
CASE
WHEN COALESCE(P.CUSTOMER_SEGMENT,' ') = ' ' THEN '000'
ELSE P.CUSTOMER_SEGMENT
END AS CHAR(03)) AS CLIENT_SEGMENT
,CAST(COALESCE(TCD.COUNTRY_CODE,'001')
AS CHAR(03)) AS NAT_CODE
,CAST(COALESCE(TCD.COUNTRY_CODE_ISO2,'EU')
AS CHAR(02)) AS NAT_CODE_ISOA2
,CAST(COALESCE(TCD.COUNTRY_CODE_ISO3,'EU ')
AS CHAR(03)) AS NAT_CODE_ISOA3
,CAST(P.DOMIZIL AS CHAR(03)) AS DOMICILE_CODE
,CAST(DOM.COUNTRY_CODE_ISO2 AS CHAR(02)) AS DOMICILE_CODE_ISOA2
,CAST(DOM.COUNTRY_CODE_ISO3 AS CHAR(03)) AS DOMICILE_CODE_ISOA3
,CAST('I ' AS CHAR(02)) AS STATUS
,CAST(
CASE
WHEN COALESCE(TCD.CD100452,' ') = ' '
THEN ' '
ELSE TCD.CD100452
END AS CHAR(16)) AS CLIENT_OFFICE
,CAST(P.COMMENT AS CHAR(255)) AS COMMENTS
,CAST(CASE
WHEN COALESCE(TCD.CD100322,' ') = ' ' THEN 0
WHEN SUBSTR(CD100322,1,4) = '0000' THEN 0
ELSE
(SMALLINT(YEAR(CURRENT DATE)) -
SMALLINT(SUBSTR(CD100322,1,4)))
END AS SMALLINT) AS AGE
,TIMESTAMP(P.LOCK_FROM_DATE,'00.00.00') AS LOCK_FROM
,TIMESTAMP(P.LOCK_TO_DATE,'00.00.00') AS LOCK_TO
,P.LAST_UPDATE_TIME AS LAST_UPDATE_TIME
,CAST(P.LAST_UPDATE_ID AS CHAR(10)) AS LAST_UPDATE_ID
,P.CONTROL_TIME AS CONTROL_TIME
FROM
oa1t.VEL105A1V LE ,
oa1t.VEL140A1V DOM,
oa1t.VELKS831A1VC4 P
LEFT OUTER JOIN
(SELECT CD100001
,CD100004
,CD100452
,CD100322
,COUNTRY_CODE
,COUNTRY_CODE_ISO2
,COUNTRY_CODE_ISO3
FROM oa1t.TCD100A1
,oa1t.VEL140A1V
WHERE COUNTRY_CODE = CD100325 ) AS TCD
ON P.PARTNER_ID = TCD.CD100001
AND P.BU = TCD.CD100004
WHERE LE.BU = P.BU
AND DOM.COUNTRY_CODE = P.DOMIZIL
AND CURRENT DATE NOT BETWEEN P.VALID_FROM_DATE AND P.VALID_TO_DATE
WITH UR;
select * from plan_view2
order by applname, progname, queryNo, qblockno, planno
;
rollback
;;;;
explain plan set queryno=2 for
SELECT DISTINCT
CAST(V45.C_PID AS CHAR(8)) AS CPID
,CAST(' ' AS CHAR(12)) AS PARTNER_ID
,CAST(CD600013 AS CHAR (20)) AS AGREEMENT_ID
,CAST(V31O.PARTNER_ID AS CHAR(12)) AS OWNER_PARTNER_ID
,CAST(ACT.ACCOUNT_GROUP AS CHAR(05)) AS ACCOUNT_GROUP
,CAST(ACT.ACCOUNT_GROUP AS CHAR(05)) AS AGREEMENT_TYPE
,CAST(MP.MPRODUCT AS CHAR(10)) AS PRODUCT
,CAST(V43.TAB_TEXT3 AS CHAR(10)) AS SERVICE
,CAST(COALESCE(V38.VISUM_TIME,'9999-12-31-23.59.59.999999')
AS CHAR(26)) AS VISUM_2_TIMESTAMP
,CAST(COALESCE(V38.VISUM,' ') AS CHAR(8)) AS VISUM_2_PID
,CAST(CASE
WHEN V35.STATE = 'F2' THEN '1'
ELSE '0'
END AS CHAR(1)) AS FREE_FORM_SIGN_BOOL
,CAST(CASE
WHEN V35.STATE = 'F2' THEN V35.COMMENT
ELSE ' '
END AS CHAR(255)) AS FREE_FORM_SIGN_COMMENT
,CAST('0' AS CHAR(1)) AS RESTRICTED_BOOL
,CAST(' ' AS CHAR(255)) AS RESTRICTED_RULE
,V35.VALID_FROM_DATE AS VALID_FROM
,V35.VALID_TO_DATE AS VALID_TO
,TIMESTAMP(V35.LOCK_FROM_DATE,'00.00.00') AS LOCKED_FROM
,TIMESTAMP(V35.LOCK_TO_DATE,'00.00.00') AS LOCKED_TO
,CAST(' ' AS CHAR(255)) AS COMMENTS
,CAST('1' AS CHAR(1)) AS VIRTUAL_BOOL
,V35.LAST_UPDATE_TIME AS LAST_UPDATE_TIME
,V35.LAST_UPDATE_ID AS LAST_UPDATE_ID
,V35.CONTROL_TIME AS CONTROL_TIME
FROM
oa1t.VELKS833A1VC4 V33,
oa1t.VELKS858A1VC4 V58,
oa1t.VELKS831A1VC4 V31U,
oa1t.VELKS831A1VC4 V31O,
oa1t.VELKS845A1VC4 V45,
oa1t.VEL220A1V ACT,
oa1t.VEL105A1V BU ,
oa1t.VEL080A1V MP ,
oa1t.TCD600A1 ,
oa1t.VELKS843A1VC4 V43,
oa1t.VELKS835A1VC4 V35
LEFT OUTER JOIN
oa1t.VELKS838A1VC4 V38
ON V35.CONTRACT_KEY = V38.CONTRACT_KEY
AND V35.PARTNER_KEY = V38.PARTNER_KEY
AND V35.PARTNER_TYPE = V38.PARTNER_TYPE
AND V35.ARRANGEMENT_ID = V38.ARRANGEMENT_ID
AND V35.ARRANGEMENT_TYPE = V38.ARRANGEMENT_TYPE
AND V35.SERVICE_KEY = V38.SERVICE_KEY
AND V38.VISUM_NUMBER = 2
WHERE V35.ARRANG_PARTNER_KEY = V31O.PARTNER_KEY
AND V35.PARTNER_KEY = V31U.PARTNER_KEY
AND MP.PRODUCT = V35.PRODUCT_ID
AND V43.TAB_ART = 'SERVICE_ID'
AND V43.TAB_LANGUAGE_CODE = '004'
AND V43.TAB_CODE = V35.SERVICE_KEY
AND V35.ARRANGEMENT_TYPE = 'CIF'
AND ACT.ACCOUNT_ID = CD600013
AND ( ACT.OWNER_ID = V31O.PARTNER_ID||' '
OR ACT.ACCOUNT_HOLDER= V31O.PARTNER_ID||' ')
AND V35.CONTRACT_KEY = V33.CONTRACT_KEY
AND V35.PARTNER_KEY = V33.PARTNER_KEY
AND V35.PARTNER_TYPE = V33.PARTNER_TYPE
AND V58.PARTNER_KEY = V35.PARTNER_KEY
AND V45.PARTNER_KEY = V33.PARTNER_KEY
AND V45.CONTRACT_COLL = V33.CONTRACT_COLL
AND V45.IDENT_ID = V58.IDENT_ID
AND CD600016 = V31O.PARTNER_ID
AND CD600020 IN ('0001','1001')
AND CD600018 = BU.BU
AND NOT EXISTS ( SELECT 'F'
FROM oa1t.VELKS843A1VC4 CP
WHERE CP.TAB_ART = 'CIFE_PRODUCT'
AND CP.TAB_CODE = V35.PRODUCT_ID )
AND NOT EXISTS ( SELECT 'F'
FROM oa1t.VELKS843A1VC4 CS
WHERE CS.TAB_ART = 'CIFE_SERVICE'
AND CS.TAB_CODE = V35.SERVICE_KEY
AND CS.TAB_TEXT3 = V35.ARRANGEMENT_TYPE )
WITH UR;
select * from plan_view2
order by applname, progname, queryNo, qblockno, planno
;
rollback
set current sqlid = 'A540769';
delete from plan_table;
set current path = oa1t;
explain plan set queryno=5 for
SELECT DISTINCT
CAST(V31O.PARTNER_ID AS CHAR(12)) AS PARTNER_ID
,CAST('CIF* ' AS CHAR(05)) AS AGREEMENT_TYPE
,CAST(MP.MPRODUCT AS CHAR(10)) AS PRODUCT
,CAST(V43.TAB_TEXT3 AS CHAR(10)) AS SERVICE
,CAST(COALESCE(V38.VISUM_TIME,'9999-12-31-23.59.59.999999')
AS CHAR(26)) AS VISUM_2_TIMESTAMP
,CAST(COALESCE(V38.VISUM,' ') AS CHAR(8)) AS VISUM_2_PID
,CAST(CASE
WHEN V35.STATE = 'F2' THEN '1'
ELSE '0'
END AS CHAR(1)) AS FREE_FORM_SIGN_BOOL
,CAST(CASE
WHEN V35.STATE = 'F2' THEN V35.COMMENT
ELSE ' '
END AS CHAR(255)) AS FREE_FORM_SIGN_COMMENT
,V35.VALID_FROM_DATE AS VALID_FROM
,V35.VALID_TO_DATE AS VALID_TO
,TIMESTAMP(V35.LOCK_FROM_DATE,'00.00.00') AS LOCKED_FROM
,TIMESTAMP(V35.LOCK_TO_DATE,'00.00.00') AS LOCKED_TO
,V35.LAST_UPDATE_TIME AS LAST_UPDATE_TIME
,CAST(V35.LAST_UPDATE_ID AS CHAR(10)) AS LAST_UPDATE_ID
,V35.CONTROL_TIME AS CONTROL_TIME
FROM
oa1t.VELKS831A1VC4 V31O,
oa1t.VEL080A1V MP ,
oa1t.VELKS843A1VC4 V43,
oa1t.VELKS835A1VC4 V35
LEFT OUTER JOIN
oa1t.VELKS838A1VC4 V38
ON V35.CONTRACT_KEY = V38.CONTRACT_KEY
AND V35.PARTNER_KEY = V38.PARTNER_KEY
AND V35.PARTNER_TYPE = V38.PARTNER_TYPE
AND V35.ARRANGEMENT_ID = V38.ARRANGEMENT_ID
AND V35.ARRANGEMENT_TYPE = V38.ARRANGEMENT_TYPE
AND V35.SERVICE_KEY = V38.SERVICE_KEY
AND V38.VISUM_NUMBER = 2
WHERE V35.ARRANG_PARTNER_KEY = V31O.PARTNER_KEY
AND MP.PRODUCT = V35.PRODUCT_ID
AND V43.TAB_ART = 'SERVICE_ID'
AND V43.TAB_LANGUAGE_CODE = '004'
AND V43.TAB_CODE = V35.SERVICE_KEY
AND V35.ARRANGEMENT_TYPE = 'CIF'
;
explain plan set queryno=404 for
SELECT DISTINCT
CAST(V45.C_PID AS CHAR(8)) AS CPID
,CAST(' ' AS CHAR(12)) AS PARTNER_ID
,CAST(CD600013 AS CHAR (20)) AS AGREEMENT_ID
,CAST(V31O.PARTNER_ID AS CHAR(12)) AS OWNER_PARTNER_ID
,CAST(ACT.ACCOUNT_GROUP AS CHAR(05)) AS ACCOUNT_GROUP
,CAST(ACT.ACCOUNT_GROUP AS CHAR(05)) AS AGREEMENT_TYPE
,CAST(MP.MPRODUCT AS CHAR(10)) AS PRODUCT
,CAST(V43.TAB_TEXT3 AS CHAR(10)) AS SERVICE
,CAST(COALESCE(V38.VISUM_TIME,'9999-12-31-23.59.59.999999')
AS CHAR(26)) AS VISUM_2_TIMESTAMP
,CAST(COALESCE(V38.VISUM,' ') AS CHAR(8)) AS VISUM_2_PID
,CAST(CASE
WHEN V35.STATE = 'F2' THEN '1'
ELSE '0'
END AS CHAR(1)) AS FREE_FORM_SIGN_BOOL
,CAST(CASE
WHEN V35.STATE = 'F2' THEN V35.COMMENT
ELSE ' '
END AS CHAR(255)) AS FREE_FORM_SIGN_COMMENT
,CAST('0' AS CHAR(1)) AS RESTRICTED_BOOL
,CAST(' ' AS CHAR(255)) AS RESTRICTED_RULE
,V35.VALID_FROM_DATE AS VALID_FROM
,V35.VALID_TO_DATE AS VALID_TO
,TIMESTAMP(V35.LOCK_FROM_DATE,'00.00.00') AS LOCKED_FROM
,TIMESTAMP(V35.LOCK_TO_DATE,'00.00.00') AS LOCKED_TO
,CAST(' ' AS CHAR(255)) AS COMMENTS
,CAST('1' AS CHAR(1)) AS VIRTUAL_BOOL
,V35.LAST_UPDATE_TIME AS LAST_UPDATE_TIME
,V35.LAST_UPDATE_ID AS LAST_UPDATE_ID
,V35.CONTROL_TIME AS CONTROL_TIME
FROM
oa1t.VELKS833A1VC4 V33,
oa1t.VELKS858A1VC4 V58,
oa1t.VELKS831A1VC4 V31U,
oa1t.VELKS831A1VC4 V31O,
oa1t.VELKS845A1VC4 V45,
oa1t.VEL220A1V ACT,
oa1t.VEL105A1V BU ,
oa1t.VEL080A1V MP ,
oa1t.TCD600A1 ,
oa1t.VELKS843A1VC4 V43,
oa1t.VELKS835A1VC4 V35
LEFT OUTER JOIN
oa1t.VELKS838A1VC4 V38
ON V35.CONTRACT_KEY = V38.CONTRACT_KEY
AND V35.PARTNER_KEY = V38.PARTNER_KEY
AND V35.PARTNER_TYPE = V38.PARTNER_TYPE
AND V35.ARRANGEMENT_ID = V38.ARRANGEMENT_ID
AND V35.ARRANGEMENT_TYPE = V38.ARRANGEMENT_TYPE
AND V35.SERVICE_KEY = V38.SERVICE_KEY
AND V38.VISUM_NUMBER = 2
WHERE V35.ARRANG_PARTNER_KEY = V31O.PARTNER_KEY
AND V35.PARTNER_KEY = V31U.PARTNER_KEY
AND MP.PRODUCT = V35.PRODUCT_ID
AND V43.TAB_ART = 'SERVICE_ID'
AND V43.TAB_LANGUAGE_CODE = '004'
AND V43.TAB_CODE = V35.SERVICE_KEY
AND V35.ARRANGEMENT_TYPE = 'CIF'
AND ACT.ACCOUNT_ID = CD600013
AND ( ACT.OWNER_ID = V31O.PARTNER_ID||' '
OR ACT.ACCOUNT_HOLDER= V31O.PARTNER_ID||' ')
AND V35.CONTRACT_KEY = V33.CONTRACT_KEY
AND V35.PARTNER_KEY = V33.PARTNER_KEY
AND V35.PARTNER_TYPE = V33.PARTNER_TYPE
AND V58.PARTNER_KEY = V35.PARTNER_KEY
AND V45.PARTNER_KEY = V33.PARTNER_KEY
AND V45.CONTRACT_COLL = V33.CONTRACT_COLL
AND V45.IDENT_ID = V58.IDENT_ID
AND CD600016 = V31O.PARTNER_ID
AND CD600020 IN ('0001','1001')
AND CD600018 = BU.BU
AND NOT EXISTS ( SELECT 'F'
FROM oa1t.VELKS843A1VC4 CP
WHERE CP.TAB_ART = 'CIFE_PRODUCT'
AND CP.TAB_CODE = V35.PRODUCT_ID )
AND NOT EXISTS ( SELECT 'F'
FROM oa1t.VELKS843A1VC4 CS
WHERE CS.TAB_ART = 'CIFE_SERVICE'
AND CS.TAB_CODE = V35.SERVICE_KEY
AND CS.TAB_TEXT3 = V35.ARRANGEMENT_TYPE )
WITH UR;
explain plan set queryno=405 for
SELECT DISTINCT
CAST(V45.C_PID AS CHAR(8)) AS CPID
,CAST(V31O.PARTNER_ID AS CHAR(12)) AS PARTNER_ID
,CAST(' ' AS CHAR (20)) AS AGREEMENT_ID
,CAST(' ' AS CHAR(12)) AS OWNER_PARTNER_ID
,CAST(' ' AS CHAR(05)) AS ACCOUNT_GROUP
,CAST('CIF* ' AS CHAR(05)) AS AGREEMENT_TYPE
,CAST(MP.MPRODUCT AS CHAR(10)) AS PRODUCT
,CAST(V43.TAB_TEXT3 AS CHAR(10)) AS SERVICE
,CAST(COALESCE(V38.VISUM_TIME,'9999-12-31-23.59.59.999999')
AS CHAR(26)) AS VISUM_2_TIMESTAMP
,CAST(COALESCE(V38.VISUM,' ') AS CHAR(8)) AS VISUM_2_PID
,CAST(CASE
WHEN V35.STATE = 'F2' THEN '1'
ELSE '0'
END AS CHAR(1)) AS FREE_FORM_SIGN_BOOL
,CAST(CASE
WHEN V35.STATE = 'F2' THEN V35.COMMENT
ELSE ' '
END AS CHAR(255)) AS FREE_FORM_SIGN_COMMENT
,CAST('0' AS CHAR(1)) AS RESTRICTED_BOOL
,CAST(' ' AS CHAR(255)) AS RESTRICTED_RULE
,V35.VALID_FROM_DATE AS VALID_FROM
,V35.VALID_TO_DATE AS VALID_TO
,TIMESTAMP(V35.LOCK_FROM_DATE,'00.00.00') AS LOCKED_FROM
,TIMESTAMP(V35.LOCK_TO_DATE,'00.00.00') AS LOCKED_TO
,CAST(' ' AS CHAR(255)) AS COMMENTS
,CAST('0' AS CHAR(01)) AS VIRTUAL_BOOL
,V35.LAST_UPDATE_TIME AS LAST_UPDATE_TIME
,CAST(V35.LAST_UPDATE_ID AS CHAR(10)) AS LAST_UPDATE_ID
,V35.CONTROL_TIME AS CONTROL_TIME
FROM
oa1t.VELKS833A1VC4 V33,
oa1t.VELKS858A1VC4 V58,
oa1t.VELKS831A1VC4 V31O,
oa1t.VELKS831A1VC4 V31U,
oa1t.VELKS845A1VC4 V45,
oa1t.VEL105A1V BU ,
oa1t.VEL080A1V MP ,
oa1t.VELKS843A1VC4 V43,
oa1t.VELKS835A1VC4 V35
LEFT OUTER JOIN
oa1t.VELKS838A1VC4 V38
ON V35.CONTRACT_KEY = V38.CONTRACT_KEY
AND V35.PARTNER_KEY = V38.PARTNER_KEY
AND V35.PARTNER_TYPE = V38.PARTNER_TYPE
AND V35.ARRANGEMENT_ID = V38.ARRANGEMENT_ID
AND V35.ARRANGEMENT_TYPE = V38.ARRANGEMENT_TYPE
AND V35.SERVICE_KEY = V38.SERVICE_KEY
AND V38.VISUM_NUMBER = 2
WHERE V35.ARRANG_PARTNER_KEY = V31O.PARTNER_KEY
AND V35.PARTNER_KEY = V31U.PARTNER_KEY
AND MP.PRODUCT = V35.PRODUCT_ID
AND V43.TAB_ART = 'SERVICE_ID'
AND V43.TAB_LANGUAGE_CODE = '004'
AND V43.TAB_CODE = V35.SERVICE_KEY
AND BU.BU = V31O.BU
AND V35.ARRANGEMENT_TYPE = 'CIF'
AND V35.CONTRACT_KEY = V33.CONTRACT_KEY
AND V35.PARTNER_KEY = V33.PARTNER_KEY
AND V35.PARTNER_TYPE = V33.PARTNER_TYPE
AND V58.PARTNER_KEY = V35.PARTNER_KEY
AND V45.PARTNER_KEY = V33.PARTNER_KEY
AND V45.CONTRACT_COLL = V33.CONTRACT_COLL
AND V45.IDENT_ID = V58.IDENT_ID
AND NOT EXISTS ( SELECT 'F'
FROM oa1t.VELKS843A1VC4 CP
WHERE CP.TAB_ART = 'CIFE_PRODUCT'
AND CP.TAB_CODE = V35.PRODUCT_ID
)
AND NOT EXISTS ( SELECT 'F'
FROM oa1t.VELKS843A1VC4 CS
WHERE CS.TAB_ART = 'CIFE_SERVICE'
AND CS.TAB_CODE = V35.SERVICE_KEY
AND CS.TAB_TEXT3 = V35.ARRANGEMENT_TYPE
)
WITH UR;
select * from plan_view2
order by applname, progname, queryNo, qblockno, planno
;
rollback