使用 SQL 查询 报没有当前游标，使用到了 sys_connect_by_path

@9527：我改成了这样了，但还是报语法错误 这是完整SQL


SELECT
r.*
FROM
(
SELECT
pid,
SYS_CONNECT_BY_PATH( sln, ';' ) all_sample_no
FROM
(
SELECT
pid,
sln,
(
ROW_NUMBER() OVER(
ORDER BY
pid,
sln DESC
)+ DENSE_RANK() OVER(
ORDER BY
pid
)
) rn,
MAX( sln ) OVER(
PARTITION BY pid
) sln1
FROM
(
SELECT
spm.person_object_id AS pid,
COALESCE(
si.sample_lab_no,
' '
) AS sln
FROM
sample_person_map spm
LEFT JOIN sample_info si ON
si.id = spm.sample_id
WHERE
si.delete_flag = 0
)
) START WITH sln = sln1 CONNECT BY rn - 1 = PRIOR rn INTO
temp tmp1;

SELECT
pid,
MAX( all_sample_no ) all_sample_no
FROM
tmp1
GROUP BY
pid INTO
temp tmp2;

SELECT
pm.all_sample_no AS sampleLabNo,
pi.id,
pi.init_server_no,
pi.lab_id,
pi.consignment_id,
pi.consign_org_code,
pi.input_category,
pi.db_category,
pi.person_no,
pi.person_name,
pi.generate_mode,
pi.alias,
pi.gender,
pi.birth_datetime,
pi.age,
pi.id_card_no,
pi.certificate_type,
pi.certificate_no,
pi.race,
pi.nationality,
pi.mobile_phone,
pi.home_phone,
pi.email,
pi.education_level,
pi.identity,
pi.occupation,
pi.native_place_regionalism,
pi.native_place_addr,
pi.residence_regionalism,
pi.residence_addr,
pi.fingerprint_no,
pi.blood_type,
pi.height,
pi.bodily_form,
pi.birth_date_from,
pi.birth_date_to,
pi.rough_age,
pi.roughly_birthdate,
pi.missing_type,
pi.missing_time,
pi.missing_place,
pi.extrinsic_sign,
pi.special_sign,
pi.involved_case_name,
pi.involved_case_no,
pi.case_property,
pi.prison_type,
pi.prison_no,
pi.death_flag,
pi.delete_flag,
pi.index_flag,
pi.transfer_flag,
pi.transfer_user,
pi.transfer_datetime,
pi.data_source,
pi.remark,
pi.create_user,
pi.create_datetime,
pi.update_user,
pi.update_datetime,
pi.person_label,
pi.abduct_type,
pi.if_sampling,
pi.sampling_datetime,
pi.sampling_regionalism,
pi.if_testdna,
pi.test_datetime,
pi.test_regionalism,
pi.family_no,
pi.family_name,
pi.hiredate,
pi.dg_case_name,
pi.if_finger,
pi.dg_data_type,
pi.post,
pi.communication_address,
pi.unit,
pi.guardian_date,
pi.communication_regionalism,
pi.memory,
pi.other_info,
pi.missing_place_regionalism,
(
SELECT
sd.dict_value1
FROM
sys_dict sd
WHERE
PI.DB_CATEGORY = sd.dict_key
AND sd.dict_category = 'PERSON_CATEGORY'
) AS dbCategoryName,
(
SELECT
sd.dict_value1
FROM
sys_dict sd
WHERE
pi.INPUT_CATEGORY = sd.dict_key
AND sd.dict_category = 'PERSON_CATEGORY'
) AS inputCategoryName,
(
SELECT
sd.dict_value1
FROM
sys_dict sd
WHERE
PI.GENDER = sd.dict_key
AND sd.dict_category = 'GENDER'
) AS genderName,
(
SELECT
sd.dict_value1
FROM
sys_dict sd
WHERE
PI.EDUCATION_LEVEL = sd.dict_key
AND sd.dict_category = 'EDUCATION_LEVEL'
) AS educationLevelName,
(
SELECT
sd.dict_value1
FROM
sys_dict sd
WHERE
PI.NATIONALITY = sd.dict_key
AND sd.dict_category = 'COUNTRY'
) AS nationalityName,
(
SELECT
sd.dict_value1
FROM
sys_dict sd
WHERE
PI.RACE = sd.dict_key
AND sd.dict_category = 'NATIONALITY'
) AS raceName,
(
SELECT
sd.dict_value1
FROM
sys_dict sd
WHERE
PI.CERTIFICATE_TYPE = sd.dict_key
AND sd.dict_category = 'CERTIFICATE_TYPE'
) AS certificateTypeName,
(
SELECT
sd.dict_value1
FROM
sys_dict sd
WHERE
PI.CASE_PROPERTY = sd.dict_key
AND sd.dict_category = 'CASE_PROPERTY'
) AS casePropertyName,
(
SELECT
sd.dict_value1
FROM
sys_dict sd
WHERE
PI.PRISON_TYPE = sd.dict_key
AND sd.dict_category = 'PRISON_TYPE'
) AS prisonTypeName,
CI.CASE_NAME AS caseName,
CI.CASE_BRIEF AS caseBrief,
C.ACCEPT_ORG_NAME AS acceptOrgName,
C.CONSIGN_ORG_REGIONALISM AS consignOrgRegionalism,
C.CONSIGN_ORG_NAME AS consignOrgName,
C.CONSIGNER_NAME AS consignerName,
C.CONSIGNER_NAME2 AS consignerName2,
C.ACCEPTOR_NAME AS acceptorName,
C.CONSIGN_DATETIME AS consignDate,
li.LAB_REGIONALISM AS labRegionalism,
sm.sample_count AS collectNum
FROM
person_info pi
LEFT JOIN CONSIGNMENT c ON
c.id = pi.CONSIGNMENT_ID
LEFT JOIN CASE_INFO CI ON
CI.ID = C.EVENT_ID
LEFT JOIN LAB_INFO li ON
pi.LAB_ID = li.id
LEFT JOIN(
SELECT
spm.person_object_id,
COUNT( si.id ) AS sample_count
FROM
sample_info si,
sample_person_map spm
WHERE
si.id = spm.sample_id
AND si.delete_flag = 0
GROUP BY
spm.person_object_id
) sm ON
pi.id = sm.person_object_id
LEFT JOIN tmp2 pm ON
pi.id = pm.pid
WHERE
1 = 1
AND pi.CONSIGNMENT_ID ='996252404FE2314BA4BD50CBD245B671'
AND(
pi.GENERATE_MODE ='1'
)
ORDER BY
pi.CREATE_DATETIME
) r
WHERE
1 = 1