Adding Additinal Field in View

Adding Additinal Field in View

Post by Arijit Chatterj » Sun, 22 Jun 2003 11:23:01



Can I add any field in my Create view satement.
Example my view is showing data like
      Col1            Col2
      -----           -----
       a               100
       b               200
       c               300
but I want to show an identity colum like

     Col1           Col2            Col3
     ----          -----           -----
      1             a               100
      2             b               200
      3             c               300
without selecting any table.
Thanks
Arijit Chatterjee

 
 
 

Adding Additinal Field in View

Post by Knut Stolz » Sun, 22 Jun 2003 19:15:44


Arijit Chatterjee wrote on Saturday 21 June 2003 04:23:

Quote:> Can I add any field in my Create view satement.
> Example my view is showing data like
>       Col1            Col2
>       -----           -----
>        a               100
>        b               200
>        c               300
> but I want to show an identity colum like

>      Col1           Col2            Col3
>      ----          -----           -----
>       1             a               100
>       2             b               200
>       3             c               300
> without selecting any table.

Drop the view and create a new one with the additional table.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

 
 
 

Adding Additinal Field in View

Post by Arijit Chatterj » Tue, 24 Jun 2003 11:31:42



> Can I add any field in my Create view satement.
> Example my view is showing data like
>       Col1            Col2
>       -----           -----
>        a               100
>        b               200
>        c               300
> but I want to show an identity colum like

>      Col1           Col2            Col3
>      ----          -----           -----
>       1             a               100
>       2             b               200
>       3             c               300
> without selecting any table.
> Thanks
> Arijit Chatterjee

Can't I do any thing with my existing table!!!
Please help me out.
Arijit Chatterjee
 
 
 

Adding Additinal Field in View

Post by Mark » Tue, 24 Jun 2003 11:40:35




Quote:> > Can I add any field in my Create view satement.
> > Example my view is showing data like
> >       Col1            Col2
> >       -----           -----
> >        a               100
> >        b               200
> >        c               300
> > but I want to show an identity colum like

> >      Col1           Col2            Col3
> >      ----          -----           -----
> >       1             a               100
> >       2             b               200
> >       3             c               300
> > without selecting any table.
> > Thanks
> > Arijit Chatterjee

> Can't I do any thing with my existing table!!!
> Please help me out.
> Arijit Chatterjee

I have no idea what you are talking about. What do you mean by "without
selecting any table".
 
 
 

Adding Additinal Field in View

Post by Arijit Chatterj » Tue, 24 Jun 2003 19:34:02




> > Can I add any field in my Create view satement.
> > Example my view is showing data like
> >       Col1            Col2
> >       -----           -----
> >        a               100
> >        b               200
> >        c               300
> > but I want to show an identity colum like

> >      Col1           Col2            Col3
> >      ----          -----           -----
> >       1             a               100
> >       2             b               200
> >       3             c               300
> > without selecting any table.
> > Thanks
> > Arijit Chatterjee

> Can't I do any thing with my existing table!!!
> Please help me out.
> Arijit Chatterjee

I want to select my Col2 and Col3 from a table.But the Col1 should be
generated automatically at the time of creating view..
Thanks..
Arijit Chatterjee
 
 
 

Adding Additinal Field in View

Post by Mark » Tue, 24 Jun 2003 20:31:38





Quote:> > > Can I add any field in my Create view satement.
> > > Example my view is showing data like
> > >       Col1            Col2
> > >       -----           -----
> > >        a               100
> > >        b               200
> > >        c               300
> > > but I want to show an identity colum like

> > >      Col1           Col2            Col3
> > >      ----          -----           -----
> > >       1             a               100
> > >       2             b               200
> > >       3             c               300
> > > without selecting any table.
> > > Thanks
> > > Arijit Chatterjee

> > Can't I do any thing with my existing table!!!
> > Please help me out.
> > Arijit Chatterjee
> I want to select my Col2 and Col3 from a table.But the Col1 should be
> generated automatically at the time of creating view..
> Thanks..
> Arijit Chatterjee

If you have an identity column that you define as automatically generated,
the value is generated when the row is inserted into the table. This has
nothing to do with a view or when the view is created. To add a column to a
view (which already exists in the table), you need to drop the existing view
and recreate it with the new column (unless you specified select *).

If the identity column is not already defined in the table, you need to
alter the table to add the new column, but it will not populate the column
for existing rows (only when new rows are inserted). To get around this,
unload all the data in the table, drop the table, recreate the table with
new identity column, and then reload the data.

I am not sure if this answers you question, but if not, please clarify.

 
 
 

Adding Additinal Field in View

Post by Paul Verno » Tue, 24 Jun 2003 22:01:40




Quote:> > Can I add any field in my Create view satement.
> > Example my view is showing data like
> >       Col1            Col2
> >       -----           -----
> >        a               100
> >        b               200
> >        c               300
> > but I want to show an identity colum like

> >      Col1           Col2            Col3
> >      ----          -----           -----
> >       1             a               100
> >       2             b               200
> >       3             c               300
> > without selecting any table.
> > Thanks
> > Arijit Chatterjee

CREATE VIEW xx AS (
SELECT    ROW_NUMBER() OVER() as Col1
,    col2, col3
FROM table
)

Regards
Paul Vernon
Business Intelligence, IBM Global Services

 
 
 

Adding Additinal Field in View

Post by Lennart Jonss » Tue, 24 Jun 2003 23:12:26





> > > Can I add any field in my Create view satement.
> > > Example my view is showing data like
> > >       Col1            Col2
> > >       -----           -----
> > >        a               100
> > >        b               200
> > >        c               300
> > > but I want to show an identity colum like

> > >      Col1           Col2            Col3
> > >      ----          -----           -----
> > >       1             a               100
> > >       2             b               200
> > >       3             c               300
> > > without selecting any table.
> > > Thanks
> > > Arijit Chatterjee

> > Can't I do any thing with my existing table!!!
> > Please help me out.
> > Arijit Chatterjee
> I want to select my Col2 and Col3 from a table.But the Col1 should be
> generated automatically at the time of creating view..
> Thanks..
> Arijit Chatterjee

something like

  select x, y, rownumber() over (partition by x order by y) from T

since you dont provide any info on how you want to number your rows,
it is difficult beeing more specific.

HTH
/Lennart

 
 
 

Adding Additinal Field in View

Post by Arijit Chatterj » Wed, 25 Jun 2003 12:12:59







> > > Can I add any field in my Create view satement.
> > > Example my view is showing data like
> > >       Col1            Col2
> > >       -----           -----
> > >        a               100
> > >        b               200
> > >        c               300
> > > but I want to show an identity colum like

> > >      Col1           Col2            Col3
> > >      ----          -----           -----
> > >       1             a               100
> > >       2             b               200
> > >       3             c               300
> > > without selecting any table.
> > > Thanks
> > > Arijit Chatterjee

> CREATE VIEW xx AS (
> SELECT    ROW_NUMBER() OVER() as Col1
> ,    col2, col3
> FROM table
> )

> Regards
> Paul Vernon
> Business Intelligence, IBM Global Services

Thanks Paul You gave me the answer.
Regards
Arijit Chatterjee
 
 
 

Adding Additinal Field in View

Post by pm3iinc-nospa » Fri, 27 Jun 2003 01:26:02


Your question isn't very clear to me.

Do you mean something like
create view ... select col1, col2, f() from t ...

PM



Quote:> Can I add any field in my Create view satement.
> Example my view is showing data like
>       Col1            Col2
>       -----           -----
>        a               100
>        b               200
>        c               300
> but I want to show an identity colum like

>      Col1           Col2            Col3
>      ----          -----           -----
>       1             a               100
>       2             b               200
>       3             c               300
> without selecting any table.
> Thanks
> Arijit Chatterjee

 
 
 

Adding Additinal Field in View

Post by Gert van der Kooi » Fri, 27 Jun 2003 03:14:46




Quote:> Your question isn't very clear to me.

> Do you mean something like
> create view ... select col1, col2, f() from t ...

He did get his answer :)


Quote:> CREATE VIEW xx AS (
> SELECT    ROW_NUMBER() OVER() as Col1
> ,    col2, col3
> FROM table
> )

> Regards
> Paul Vernon
> Business Intelligence, IBM Global Services

Thanks Paul You gave me the answer.
Regards
Arijit Chatterjee

**** end copy

Probably your newsreader was fooled by the changing header. My
newsreader (Gravity) displayed the posts with the headers "Adding
Additinal Field in View" and "Can't I do any thing with my existing
table!!!" all in one thread.

 
 
 

Adding Additinal Field in View

Post by pm3iinc-nospa » Fri, 27 Jun 2003 05:50:16


When i did open my reader this morning, there was no replies...
This afternoon, i see replies from the 2003-06-23 ... ???

Looks like there's some lag at the news server or something...
;-)

PM

 
 
 

Adding Additinal Field in View

Post by Mark » Fri, 27 Jun 2003 04:42:33



Quote:> When i did open my reader this morning, there was no replies...
> This afternoon, i see replies from the 2003-06-23 ... ???

> Looks like there's some lag at the news server or something...
> ;-)

> PM

It's only your ISP's server. Mine works fine