Generated by column syntax
Jerry Alan Braga
fitzj...@cox.net
wrote:
> does anyone have any references to this and what are the capabilities
Do you have a better explanation of what you want?
David Fitzjarrell
Jerry Alan Braga
alter table test add col_3 generated always by (col_1 + col_2); -- virtual
col
alter table test add col_3 generated by (col_1 + col_2); -- stored virtual
col
what is the syntax of the generated statement, where are the docs on this
<fitzj...@cox.net> wrote in message
news:1188480358.7...@22g2000hsm.googlegroups.com...
Thomas Kellerer
> where are the docs on this
This is easy:
Brian Peasland
> I have seen on some posts that you can do the following
>
> alter table test add col_3 generated always by (col_1 + col_2); -- virtual
> col
>
> alter table test add col_3 generated by (col_1 + col_2); -- stored virtual
> col
>
> what is the syntax of the generated statement, where are the docs on this
This is a new 11g feature. Found at the following:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_3001.htm#CJAHHIBI
HTH,
Brian
--
===================================================================
Brian Peasland
d...@nospam.peasland.net
http://www.peasland.net
Remove the "nospam." from the email address to email me.
"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown
--
Posted via a free Usenet account from http://www.teranews.com
Shakespeare
"Jerry Alan Braga" <jerry...@hotmail.com> schreef in bericht
news:KBABi.16778$vP5.12746@edtnps90...
Isn't that DB2 code?
Shakespeare
Shakespeare
"Brian Peasland" <d...@nospam.peasland.net> schreef in bericht
news:46d6d205$0$4367$8826...@free.teranews.com...
Darn, missed that one. Time for an upgrade...
Shamespeare
DA Morgan
> I have seen on some posts that you can do the following
>
> alter table test add col_3 generated always by (col_1 + col_2); -- virtual
> col
>
> alter table test add col_3 generated by (col_1 + col_2); -- stored virtual
> col
>
> what is the syntax of the generated statement, where are the docs on this
>
There are demos of Virtual column creation and usage in Morgan's Library
at www.psoug.org under VIRTUAL COLUMNS.
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Serge Rielau
> Jerry Alan Braga wrote:
>> I have seen on some posts that you can do the following
>>
>> alter table test add col_3 generated always by (col_1 + col_2); --
>> virtual col
>>
>> alter table test add col_3 generated by (col_1 + col_2); -- stored
>> virtual col
>>
>> what is the syntax of the generated statement, where are the docs on this
>>
>
> There are demos of Virtual column creation and usage in Morgan's Library
> at www.psoug.org under VIRTUAL COLUMNS.
expression generated column on insert appears to be tolerated. Will it
return an error if the value isn't correct or is the value simply ignored?
(DB2 only allows the DEFAULT keyword if a generated column is specified)
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
DA Morgan
Too bad you don't have Oracle. Some of what is in my library, keep in
mind these are demos for my students, are intended to generate an error.
If you ran it here is what you'd see:
SQL> CREATE TABLE vcol (
2 salary NUMBER(8,2),
3 bonus NUMBER(3),
4 sal_plus_bonus NUMBER(10,2) GENERATED ALWAYS AS (salary+bonus)
VIRTUAL);
Table created.
SQL> INSERT into vcol
2 (salary, bonus, sal_plus_bonus)
3 VALUES
4 (100, 10, 110);
INSERT into vcol
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns
SQL>
Serge Rielau
> Too bad you don't have Oracle.
DA Morgan
> DA Morgan wrote:
>> Too bad you don't have Oracle.
> I hate lawyers....
Or a job with a different company. Can I recommend one? <g>
joel garry
> "Brian Peasland" <d...@nospam.peasland.net> schreef in berichtnews:46d6d205$0$4367$8826...@free.teranews.com...
>
>
>
>
>
> > Jerry Alan Braga wrote:
> >> I have seen on some posts that you can do the following
>
> >> alter table test add col_3 generated always by (col_1 + col_2); --
> >> virtual col
>
> >> alter table test add col_3 generated by (col_1 + col_2); -- stored
> >> virtual col
>
> >> what is the syntax of the generated statement, where are the docs on this
>
> > This is a new 11g feature. Found at the following:
>
>
>
> Darn, missed that one. Time for an upgrade...
>
> Shamespeare
I work with an environment that has long emulated this functionality.
Every so often I get burned as I write some SQL that requires these
columns. The app doesn't always display them as being any different,
so I have to compare SQL describes with the app, sometimes dozens of
fields not necessarily in the same order. Virtual columns _are_ a
very useful feature for enterprise apps.
It has slightly different limitations than the new Oracle
functionality, so this should be interesting upgrading this app to
11g. I'm guessing it won't use the Oracle functionality for backward
compatibility reasons, but I've been surprised before.
I wonder what changes Oracle will be making to FBI limitations?
jg
--
@home.com is bogus.
"Many of our customers are tourists from other countries. We seem
especially popular with those from the UK, there's a bunch at the
counter now." - heard on radio, interview with Las Vegas company where
customers shoot fully automatic weapons such as AK-47s.
Frank van Bortel
Hash: SHA1
joel garry wrote:
> jg
> --
> @home.com is bogus.
> "Many of our customers are tourists from other countries. We seem
> especially popular with those from the UK, there's a bunch at the
> counter now." - heard on radio, interview with Las Vegas company where
> customers shoot fully automatic weapons such as AK-47s.
Could I have a LAW against that?
No, sorry - I meant the M72
:s
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (MingW32)
iD8DBQFG3wPuLw8L4IAs830RAkLTAJoCaIOHMboSNAfSkw+ISIj2p5JfwACeLJN7
M5kdnh8oALIA1adXkc9g/uE=
=fTKr
-----END PGP SIGNATURE-----
William Robertson
> This is a new 11g feature. Found at the following:
>
> http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statem...
Well, it's similar to an 11g new feature, but I don't see any
GENERATED [ALWAYS] BY syntax:
SQL> CREATE TABLE testtab
2 ( dt DATE
3 , year INTEGER AS ( EXTRACT(YEAR FROM dt) ) );
Table created.
SQL> INSERT INTO testtab(dt) VALUES(sysdate);
1 row created.
SQL> SELECT * FROM testtab;
DT YEAR
----------- ----------
03-SEP-2007 2007
1 row selected.
Shakespeare
"William Robertson" <willia...@googlemail.com> schreef in bericht
news:1189032180....@k79g2000hse.googlegroups.com...
That is because search does not find it, it's in a picture. Just scroll and
you'll see it somewhere in one of the syntax pictures..
Search for
virtual_column_definition::=
(That's why I missed it at first too)
Shakespeare