Never mind ... a good night's sleep with my subconscious working on the
problem yielded:
SELECT
reg_type.reg_type_desc "Registration Type",
CASE result.result_defn WHEN NULL THEN CONVERT(VARCHAR,reg_stats.result_code) ELSE result.result_defn
END 'Result',
reg_stats.create_date "Create Date",
forms.logical_form_number,
-- FORM 1
CASE
WHEN
(CHARINDEX('',SUBSTRING(reg_trace.reg_trace1,CHARINDEX('992',reg_trace.
reg_trace1) + 5, 255)) - 1) < 0 THEN SUBSTRING(reg_trace.reg_trace1,CHARINDEX('992',reg_trace.reg_trace1)
+ 5,255) ELSE SUBSTRING(reg_trace.reg_trace1,CHARINDEX('992',reg_trace.reg_trace1)
+
5,CHARINDEX('',SUBSTRING(reg_trace.reg_trace1,CHARINDEX('992',reg_trace
.reg_trace1) + 5, 255)) - 1)
END 'Form 1',
-- END FORM 1
CASE CHARINDEX('992',reg_trace.reg_trace1)
WHEN 0 THEN 'No Forms Trace' ELSE SUBSTRING(reg_trace.reg_trace1,CHARINDEX('992',reg_trace.reg_trace1) +
5, 255)
END 'Forms Trace',
reg_trace.reg_trace1
from reg_stats, reg_trace, reg_type, result, forms
where
reg_stats.reg_type_code = reg_type.reg_type_code
and reg_stats.reg_stats_logical_key = reg_trace.reg_stats_logical_key
and reg_stats.result_code *= result.result_code
and reg_trace.reg_stats_logical_key in (select reg_trace.reg_stats_logical_key from reg_stats, reg_trace WHERE
reg_stats.reg_stats_logical_key= reg_trace.reg_stats_logical_key and reg_stats.create_date >= "12/18/02 09:57:00"
andreg_stats.create_date <= "12/18/02 10:15:00" and reg_trace.reg_trace1 like '%992%')
and forms.form_number = CASE WHEN
(CHARINDEX('',SUBSTRING(reg_trace.reg_trace1,CHARINDEX('992',reg_trace.
reg_trace1) + 5, 255)) - 1) < 0 THEN
convert(smallint,SUBSTRING(reg_trace.reg_trace1,CHARINDEX('992',reg_trac
e.reg_trace1) + 5,255)) ELSE convert(smallint,SUBSTRING(reg_trace.reg_trace1,CHARINDEX('
992',reg_trace.reg_trace1) +
5,CHARINDEX('',SUBSTRING(reg_trace.reg_trace1,CHARINDEX('992',reg_trace
.reg_trace1) + 5, 255)) - 1)) END
