Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 4 of 4 total |
SELECT expression with CLOB columns |
Wed, Aug 26 2009 8:34 PM | Permanent Link |
Richard Harding | 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 |
Thu, Aug 27 2009 12:00 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Thu, Aug 27 2009 5:00 PM | Permanent Link |
Richard Harding | 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 |
Mon, Aug 31 2009 2:55 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |