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';

Barry


It works here in a non unicode version. I suggest you contact Tim direct.

Roy Lambert [Team Elevate]

Barry

It runs without errors here using 2.11 b3 unicode ElevateDB Manager on
empty tables.

Malcolm

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

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

Barry


Works fine here using your data.

Roy Lambert

>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

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
---------------------------------------

Thanks Terry,

I handed it over to Tim and he's working on it. So it will be fixed in the next release.

Barry