If I run a query through the Pervasive Control Center that has multiple in-line views (virtual tables) and that uses the DISTINCT keyword within those views, Pervasive 10 will crash. If I use GROUP BY rather than DISTINCT, then the database does not crash.
Here's an example:
SELECT v_ee.*, v_sp.Spouse_Coverage, v_ch.Child_Coverage
FROM
(SELECT DISTINCT ' 11-' + CONVERT(pm.loc_no,SQL_CHAR) As cl_id, pm.first_name, pm.last_name,
pm.ss_alpha as ss_no, pm.hire_date, pd.cover_beg, pm.term_date, pd.cover_end, pd.emp_amt as Emp_Coverage
FROM APEX112010.pr_mast pm, APEX112010.pr_cded pc, APEX112010.pr_ded pd
WHERE pm.loc_no = pc.loc_no and pm.emp_no = pc.emp_no
and pd.emp_no = pc.emp_no and pd.loc_no = pc.loc_no and pd.code = pc.code and pd.plan = pc.plan
and (pm.active_stat = 'T' or pd.cover_end is not null) and pc.code = 363
and (pd.plan = '1HRT EE NS' or pd.plan = '2HRT EE S')
and pd.plan NOT IN ('BENEFICIAL', '6HRT BC EE', '7HRT BC SP', '8HRT BC CH')
and (pd.cover_end BETWEEN '2010-01-01' AND '2010-03-31'
or pm.term_date BETWEEN '2010-01-01' AND '2010-03-31')) AS v_ee
LEFT JOIN
(SELECT DISTINCT ' 11-' + CONVERT(pm.loc_no,SQL_CHAR) As cl_id, pm.first_name, pm.last_name,
pm.ss_alpha as ss_no, pm.hire_date, pd.cover_beg, pm.term_date, pd.cover_end, pd.emp_amt as Spouse_Coverage
FROM APEX112010.pr_mast pm, APEX112010.pr_cded pc, APEX112010.pr_ded pd
WHERE pm.loc_no = pc.loc_no and pm.emp_no = pc.emp_no
and pd.emp_no = pc.emp_no and pd.loc_no = pc.loc_no and pd.code = pc.code and pd.plan = pc.plan
and (pm.active_stat = 'T' or pd.cover_end is not null) and pc.code = 363
and (pd.plan = '3HRT SP NS' or pd.plan = '4HRT SP S')
and pd.plan NOT IN ('BENEFICIAL', '6HRT BC EE', '7HRT BC SP', '8HRT BC CH')
and (pd.cover_end BETWEEN '2010-01-01' AND '2010-03-31'
or pm.term_date BETWEEN '2010-01-01' AND '2010-03-31')) as v_sp ON v_ee.cl_id = v_sp.cl_id and v_ee.ss_no = v_sp.ss_no
LEFT JOIN
(SELECT DISTINCT ' 11-' + CONVERT(pm.loc_no,SQL_CHAR) As cl_id, pm.first_name, pm.last_name,
pm.ss_alpha as ss_no, pm.hire_date, pd.cover_beg, pm.term_date, pd.cover_end, pd.emp_amt as Child_Coverage
FROM APEX112010.pr_mast pm, APEX112010.pr_cded pc, APEX112010.pr_ded pd
WHERE pm.loc_no = pc.loc_no and pm.emp_no = pc.emp_no
and pd.emp_no = pc.emp_no and pd.loc_no = pc.loc_no and pd.code = pc.code and pd.plan = pc.plan
and (pm.active_stat = 'T' or pd.cover_end is not null) and pc.code = 363
and pd.plan = '5HRT CHILD'
and pd.plan NOT IN ('BENEFICIAL', '6HRT BC EE', '7HRT BC SP', '8HRT BC CH')
and (pd.cover_end BETWEEN '2010-01-01' AND '2010-03-31'
or pm.term_date BETWEEN '2010-01-01' AND '2010-03-31')) as v_ch ON v_ee.cl_id = v_ch.cl_id and v_ee.ss_no = v_ch.ss_no;