[Crystal Reposts] Параметры с множественным выбором в запросах
Код:
-- Некий код до
INSERT @pat_tracking(person_id, last_name, first_name, dob,
mrn, enc_id, enc_nbr, enc_timestamp, enc_date,
provider, attending_ind, location, time1)
SELECT DISTINCT p.person_id, p.last_name, p.first_name, CONVERT(DATE, p.date_of_birth, 112),
CONVERT(INT, pa.med_rec_nbr), pe.enc_id, pe.enc_nbr, pe.create_timestamp, CONVERT(DATE, pe.create_timestamp),
pm.description, ppm.attending_ind, lm.location_name, CONVERT(datetime, CONVERT(TIME, pe.create_timestamp))
FROM person p
JOIN patient_encounter pe ON p.person_id=pe.person_id
JOIN patient pa ON p.person_id=pa.person_id AND pe.practice_id=pa.practice_id
JOIN provider_mstr pm ON pe.rendering_provider_id=pm.provider_id
JOIN provider_practice_mstr ppm ON pe.practice_id=ppm.practice_id AND pm.provider_id=ppm.provider_id
JOIN location_mstr lm ON pe.location_id=lm.location_id
JOIN pat_apt_status_hx_ pash ON pe.enc_id=pash.enc_id
WHERE
lm.location_name IN {?Location Name}
AND CONVERT(VARCHAR(36), pe.rendering_provider_id) IN {?Provider}
-- Некий код после
INSERT @pat_tracking(person_id, last_name, first_name, dob,
mrn, enc_id, enc_nbr, enc_timestamp, enc_date,
provider, attending_ind, location, time1)
SELECT DISTINCT p.person_id, p.last_name, p.first_name, CONVERT(DATE, p.date_of_birth, 112),
CONVERT(INT, pa.med_rec_nbr), pe.enc_id, pe.enc_nbr, pe.create_timestamp, CONVERT(DATE, pe.create_timestamp),
pm.description, ppm.attending_ind, lm.location_name, CONVERT(datetime, CONVERT(TIME, pe.create_timestamp))
FROM person p
JOIN patient_encounter pe ON p.person_id=pe.person_id
JOIN patient pa ON p.person_id=pa.person_id AND pe.practice_id=pa.practice_id
JOIN provider_mstr pm ON pe.rendering_provider_id=pm.provider_id
JOIN provider_practice_mstr ppm ON pe.practice_id=ppm.practice_id AND pm.provider_id=ppm.provider_id
JOIN location_mstr lm ON pe.location_id=lm.location_id
JOIN pat_apt_status_hx_ pash ON pe.enc_id=pash.enc_id
WHERE
lm.location_name IN {?Location Name}
AND CONVERT(VARCHAR(36), pe.rendering_provider_id) IN {?Provider}
-- Некий код после
Вобщем то понятно. Проблема в выражении in. Если же запрос изменить на:
Код:
-- код до
WHERE
lm.location_name IN ('{?Location Name}')
AND CONVERT(VARCHAR(36), pe.rendering_provider_id) IN ('{?Provider}')
--код после
WHERE
lm.location_name IN ('{?Location Name}')
AND CONVERT(VARCHAR(36), pe.rendering_provider_id) IN ('{?Provider}')
--код после
Как побороть?
Да. Спасибо всем за участие - проблема в багах самого CR.