Tim,

I have 2 tables which in part look like:

CREATE TABLE "Configuration"
(
"ID" INTEGER,
"ReportTitle" VARCHAR(16) COLLATE "ANSI",
"PageHeader" VARCHAR(16) COLLATE "ANSI",
"ReportSummary" VARCHAR(16) COLLATE "ANSI",
)

CREATE TABLE "Template"
(
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 11, INCREMENT BY 1) NOT NULL,
"Name" VARCHAR(16) COLLATE "ANSI_CI" NOT NULL,
"Notes" CLOB COLLATE "ANSI"
)

Configuration only has 1 row.

I want to the result query to contain (ReportTitle Notes, PageHeader Notes, ReportSummary
Notes).

I can obtain the IDs (ReportTitle ID, PageHeader ID, ReportSummary ID) with the following
query.

SELECT
   (SELECT ID FROM Template WHERE Template.Name = C.PageHeader) AS PageHeader,
   (SELECT ID FROM Template WHERE Template.Name = C.ReportTitle) AS ReportTitle,
   (SELECT ID FROM Template WHERE Template.Name = C.ReportSummary) AS ReportSummary
 FROM
   Configuration AS C

However, when I try to get the Notes, I obtain a row of nulls.

SELECT
   (SELECT Notes FROM Template WHERE Template.Name = C.PageHeader) AS PageHeader,
   (SELECT Notes FROM Template WHERE Template.Name = C.ReportTitle) AS ReportTitle,
   (SELECT Notes FROM Template WHERE Template.Name = C.ReportSummary) AS ReportSummary
 FROM
   Configuration AS C

I can obtain the required result by:

SELECT T1.Notes, T2.Notes,  T3.Notes
 FROM Template T1, Template T2, Template T3, Configuration C
 WHERE
   T1.Name = C.PageHeader AND
   T2.Name = C.ReportTitle AND
   T3.Name = C.ReportSummary

Richard Harding

Richard,

<< However, when I try to get the Notes, I obtain a row of nulls. >>

Are you requesting a sensitive or insensitive result set ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

If Request Sensitive Result Set is false then I get my row with the values - otherwise I
get a row of nulls.

SELECT T1.Notes, T2.Notes,  T3.Notes
FROM Template T1, Template T2, Template T3, Configuration C
WHERE
  T1.Name = C.PageHeader AND
  T2.Name = C.ReportTitle AND
  T3.Name = C.ReportSummary

With the query above, I get a result irrespective of the setting of the Request Sensitive
Result Set.

This probably makes sense to you.

Richard Harding

Richard,

<< If Request Sensitive Result Set is false then I get my row with the
values - otherwise I get a row of nulls. >>

Okay, that's what I suspected.  I'll check it out.

--
Tim Young
Elevate Software
www.elevatesoft.com