Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 9 of 9 total |
Problem with a view. EDB won't recognize a subselect column name when used in an expression |
Wed, Dec 19 2012 8:46 PM | Permanent Link |
Barry | This works:
select total_expenses from v_testbug --This works as expected This doesn't work: select total_expenses+1 from v_testbug --Does not like total_expenses in any expression Total_Expenses is a Float returned from a subselect into Expenses that sums up Expense_Amount. I've had it fail on other tables, with the subselect summing an integer column. Either I'm doing something wrong, or this is a bug. I am using EDB 2.11 Build 3 Unicode. I doubt Unicode has anything to do with it because the sub-select returns a float. If someone wants to give it a try, here is the code to build the tables (I don't think you need to put data into the tables to get it to fail): Barry EXECUTE IMMEDIATE 'CREATE TABLE "Company" ( "PL_Id" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1), "Num_People" INTEGER DEFAULT 0 , "Start_Date" DATE ) VERSION 1.00 READWRITE UNENCRYPTED INDEX PAGE SIZE 8192 BLOB BLOCK SIZE 1024 PUBLISH BLOCK SIZE 1024 PUBLISH COMPRESSION 0 MAX ROW BUFFER SIZE 32768 MAX INDEX BUFFER SIZE 65536 MAX BLOB BUFFER SIZE 32768 MAX PUBLISH BUFFER SIZE 32768'; EXECUTE IMMEDIATE 'CREATE TABLE "Expenses" ( "PL_Id" INTEGER NOT NULL, "Expense_Code" VARCHAR(5) COLLATE "UNI_CI_AI_KI_WI" NOT NULL, "Expense_Amount" FLOAT DEFAULT 0 NOT NULL ) VERSION 1.00 READWRITE UNENCRYPTED INDEX PAGE SIZE 8192 BLOB BLOCK SIZE 1024 PUBLISH BLOCK SIZE 1024 PUBLISH COMPRESSION 0 MAX ROW BUFFER SIZE 32768 MAX INDEX BUFFER SIZE 65536 MAX BLOB BUFFER SIZE 32768 MAX PUBLISH BUFFER SIZE 32768'; EXECUTE IMMEDIATE 'CREATE VIEW "v_TestBug" AS select tc.pl_id, tc.num_people, tc.start_date, (select sum(expense_amount) from expenses texp where texp.pl_id=tc.pl_id) as Total_Expenses from company tc VERSION 1.00'; |
Thu, Dec 20 2012 3:48 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Barry
It works here in a non unicode version. I suggest you contact Tim direct. Roy Lambert [Team Elevate] |
Thu, Dec 20 2012 5:54 AM | Permanent Link |
Malcolm Taylor | Barry
It runs without errors here using 2.11 b3 unicode ElevateDB Manager on empty tables. Malcolm |
Thu, Dec 20 2012 9:48 AM | Permanent Link |
Adam Brett Orixa Systems | As I quick fix I would try
SELECT CAST(TotalExpenses as FLOAT) + 1, FROM ... just to see whether an enforced cast tricks it into working. I would also try SELECT CAST(TotalExpenses as FLOAT) + 1.00, FROM ... as sometimes if you just write "1" some part of the engine assumes you are requesting an INTEGER rather than FLOAT & this may cause a problem. Adam |
Thu, Dec 20 2012 11:12 AM | Permanent Link |
Barry | I should have posted data for the tables. You can find it attached.
Also I should have posted the darn error message. I guess it was late last night and I was dozing off. That's my excuse and I'm sticking to it. VBG "ElevateDB Error #700 An error was found in the __Column-2 computed column expression at line 1 and column 1 (ElevateDB Error #401 The column total_expenses does not exist)" BTW, I tried Cast() last night but that didn't work either. Whenever Total_Expenses is used in an expression, I get the same error message. I have it failing on two different sets of tables. Weird. Barry Attachments: Company_Expenses.zip |
Fri, Dec 21 2012 5:26 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Barry
Works fine here using your data. Roy Lambert |
Sat, Dec 22 2012 12:59 PM | Permanent Link |
Barry | >Works fine here using your data.<
Roy, Thanks for testing it. I have passed it on to Tim and he's looking into it. It is strange that it rejects : select total_expenses+1 from v_testbug; but accepts: select total_expenses from v_testbug; I looked at Information.ViewColumns and total_expenses is a Float. This morning I discovered the problem only occurs if I am using the server. It works fine if run locally. I am using Unicode Server v2.11 B3. Barry |
Sat, Dec 22 2012 9:06 PM | Permanent Link |
Terry Swiers | Hi Barry,
> This morning I discovered the problem only occurs if I am using the > server. It works fine if run locally. I've been able to reproduce it here, and only with a C/S connection. Additionally, it only throws up if you request a sensitive result set. --------------------------------------- Terry Swiers Millennium Software, Inc. http://www.1000years.com http://www.atrex.com --------------------------------------- |
Mon, Dec 24 2012 2:30 AM | Permanent Link |
Barry | Thanks Terry,
I handed it over to Tim and he's working on it. So it will be fixed in the next release. Barry |
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 |