pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: scottrarndt@aol.com
To: pgsql-sql@postgresql.org
Subject: Re: Stuck on SQL Query where a calculated value is used for a join.
Date: 27 Dec 2002 13:57:56 GMT
Message-ID: <20021227085756.15080.00000741@mb-fe.aol.com> (raw)
References: <20021226162618.18095.00000558@mb-fc.aol.com>

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

Message-ID: <20021227085756.15080.00000741@mb-fe.aol.com>
Permalink:  ../20021227085756.15080.00000741@mb-fe.aol.com/
Also on:    postgresql.org/message-id/20021227085756.15080.00000741@mb-fe.aol.com

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: scottrarndt@aol.com
  Subject: Re: Stuck on SQL Query where a calculated value is used for a join.
  In-Reply-To: <20021227085756.15080.00000741@mb-fe.aol.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox