Creating a computed column VARCHAR(4)


ElevateDB error #700, an error was found in the statement at line 2 and column 100 (Expected BETWEEN, LIKE, IN but instead found NULL)

The computation was

IF(_Title IS NULL,'',_Title)

Roy Lambert

ps can  we have a button on the error messages to copy to clipboard - it would certainly save me typing and might give you better error reporting.

This does work so its the app (EDBManager) not the engine.


ALTER TABLE "Contacts"
ADD Column "_sTitle"  VARCHAR(4) COMPUTED ALWAYS AS IF(_Title IS NULL,'',_Title);

Another one (I think its been asked before) can we have someway of seeing the sql generated, especially when it raises two fingers?


Roy Lambert

Roy,

<< Creating a computed column VARCHAR(4)

ElevateDB error #700, an error was found in the statement at line 2 and
column 100 (Expected BETWEEN, LIKE, IN but instead found NULL)

The computation was

IF(_Title IS NULL,'',_Title) >>

That works fine here.

As for the error messages - the idea is that you shouldn't get compilation
errors when using the dialogs unless you're actually inputting SQL.  The
only exception to this is the column-based expressions, which I don't have
good solution for yet because it's hard to trace the expression error back
to the generated SQL.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

Just tried again with a different table with the same result. V2.00b1

You should have the catlog - the one I sent for the delete/insert speed problem - if you want to try it with my catalog. There might be a difference since I reran the conversion routines setting string fields to '' and not nullable, but I did try setting the fields I was experimenting back to null and nullable with no difference.

Roy Lambert

Tim

Just tried with b2 and the problem is still there.


Roy Lambert

Roy,

<< You should have the catlog - the one I sent for the delete/insert speed
problem - if you want to try it with my catalog. There might be a difference
since I reran the conversion routines setting string fields to '' and not
nullable, but I did try setting the fields I was experimenting back to null
and nullable with no difference. >>

I just did it again with your Contacts table, no problem.  This is the SQL
that is generated by the EDB Manager (via the SQL History):

ALTER TABLE "Contacts"
ADD COLUMN "_sTitle" VARCHAR(4) COLLATE "ANSI" COMPUTED ALWAYS AS IF(_Title
IS NULL,'',_Title)

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy,

<<
ps can we have a button on the error messages to copy to clipboard - it
would certainly save me typing and might give you better error reporting.
>>

Did you ever try to press CTRL+C while the messagebox has the focus? It
works!

Uli

Uli

>Did you ever try to press CTRL+C while the messagebox has the focus? It
>works!

How did you discover that? I've been trying to swipe the message to copy it, never thought of just pressing ctrl-C .

Thanks muchly.

Roy Lambert

Tim

>I just did it again with your Contacts table, no problem. This is the SQL
>that is generated by the EDB Manager (via the SQL History):
>
>ALTER TABLE "Contacts"
>ADD COLUMN "_sTitle" VARCHAR(4) COLLATE "ANSI" COMPUTED ALWAYS AS IF(_Title
>IS NULL,'',_Title)

I just tried again with two different tables and I get the same problem. But I have now found out what the problem is.

If you uncheck Nullable you get the problem, check nullable and it works.

It would have been left in whatever state it defaulted to when I opened EDBManager and because my personal view is that nullable is immaterial for a computed column (and probably for a generated one) I never bothered checking or even thought about it.

Roy Lambert

Roy,

> How did you discover that? I've been trying to swipe the message to copy
> it, never thought of just pressing ctrl-C .

That seems to be a quite unknown feature of all Windows messageboxes. Long
time ago I discovered it in a ng.

http://tinyurl.com/2s7bs6

Regards Uli