Re: Stuck on SQL Query where a calculated value is used for a join.

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"
and reg_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