Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 426CA47602A
	for <pgsql-sql@postgresql.org>; Fri, 27 Dec 2002 09:35:14 -0500 (EST)
Received: from news.hub.org (news.hub.org [64.49.215.80])
	by postgresql.org (Postfix) with ESMTP id CE7CE475AEC
	for <pgsql-sql@postgresql.org>; Fri, 27 Dec 2002 09:35:13 -0500 (EST)
Received: by news.hub.org (Postfix, from userid 8)
	id B98D943AF60; Fri, 27 Dec 2002 09:35:13 -0500 (EST)
Lines: 59
X-Admin: news@aol.com
From: scottrarndt@aol.com (ScottRArndt)
X-Newsgroups: comp.databases.postgresql.sql
Date: 27 Dec 2002 13:57:56 GMT
References: <20021226162618.18095.00000558@mb-fc.aol.com>
Organization: AOL http://www.aol.com
Subject: Re: Stuck on SQL Query where a calculated value is used for a join.
Message-ID: <20021227085756.15080.00000741@mb-fe.aol.com>
To: pgsql-sql@postgresql.org
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200301/5
X-Sequence-Number: 10870

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