Can't Modify the view

[OraWiz]

I have a local view, which I am using in Report.
Now I want to add more column into this view so that I can show them in my Report.
But when I am trying to modify this view it is giving an error message 'Alias name is already in use '
Why it is happening, How can I modify this view.
Please give me some clue..
Thanks and Rgds
Sebastian

 

[Mike Lewis]

Sebastian,

How are you trying to modify the view? Are you issuing MODIFY VIEW, or what?

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[ramani]

HI

SELECT *, field1+field2 AS newColumn1, ;
field3*10 AS newColumn2 ;
FROM myLocalView INTO CURSOR myCursor READWRITE

Use this myCursor in your report rather than the view.




ramani
(Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[OraWiz]

Hello Mike
Thanks for your reply.
Let me answer your question first...
I have tried both, from the PROJECT MANAGER and COMMAND WINDOW, result is same..
In the command window following were my commands

OPEN DATABASE MyDataBase
MODIFY VIEW MyView
Then the error was poping up 'alias already in use...'

Hi Ramani,
I agree with the round about way....
But you mean to say that once I created a view, I can't modify this view, But I modified view in the past, but that time there was no problem pops up....

Thanks Ramani
Sebastian.

 

[ramani]

Hi Sebastian,

USE myDBC!myView
ALTER TABLE myView ADD COLUMN myNewField C(25)

This should help you to add a new field in your view.


ramani
(Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[Mike Lewis]

Ramani,

ALTER TABLE myView ADD COLUMN myNewField C(25)

That works with tables, but not with views.

In general, the way to programmatically add a column to a view is something like this:

lcSQL = DBGETPROP("MyView","VIEW","SQL&quot
DROP VIEW MyView
CREATE SQL VIEW MyView AS &lcSQL

(Do a backup of the DBC first.)

Sebastian,

I can't see any reason for the error message, and I cannot reproduce it. Maybe somthing is corrupted in the DBC? Perhaps you could try the above code, or simply delete the view and manually recreate it (that's not a solution -- just a suggestion for getting rid of any possible corruption).

Let us know if this helps.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[ramani]

Hi Mike

You are right. I blundered. They apply to tables only. Thanks for correcting the suggestion.

The way I create views is..

CREATE SQL VIEW myView ;
SELECT myTable1.myField1 , ;
myTable2.Field1, ;
.. etc.. , ;
FROM DBC!myTable1 ;
INNER JOIN DBC!myTable2 ; && or whatever joins
ON .. join conditions ;
WHERE filter conditions ;
ORDER BY ordering of fields

DBSetProp("myView","View","Comment","myComments&quot
DBSetProp("myView","View","SendUpdates",.F.)
DBSetProp("myView","View","BatchUpdateCount",1)
DBSetProp("myView","View","CompareMemo",.T.)
DBSetProp("myView","View","FetchAsNeeded",.F.)
DBSetProp("myView","View","FetchMemo",.T.)
DBSetProp("myView","View","FetchSize",100)
DBSetProp("myView","View","MaxRecords",-1)
DBSetProp("myView","View","Prepared",.F.)
DBSetProp("myView","View","UpdateType",1)
DBSetProp("myView","View","UseMemoSize",255)
DBSetProp("myView","View","Tables", ;
"DBC!myTable1,DBC!myTable2&quot
DBSetProp("myView","View","WhereType",3)

DBSetProp("myView"+".field1","Field","Caption","Date&quot
DBSetProp("myView"+".field1","Field","DataType","D&quot
DBSetProp("myView"+".field1","Field","KeyField",.F.)
DBSetProp("myView"+".field1","Field","Updatable",.F.)
etc etc... to set the properties.

Even if you dont drop a view, the view will be substituted and overwritten with the latest such creation.



ramani
(Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[Mike Lewis]

Ramani,

Yes, you need all those DBSETPROPs as well. I guess the ideal way to do it would be to write a series of DBGETPROPs to save all the properties, then use DBSETPROPs to restore them.

Even if you dont drop a view, the view will be substituted and overwritten with the latest such creation.

That's worth knowing.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[OraWiz]

Hello Mike and Ramani,

As Mike said As soon as I create the view I can also modify that view, but if I refer this view in some report or something then only this error generally pops up any way ...

I have a dozen of these kinds of view, Hence forth I will keep the source code of this view and when ever I want to modify the same I will regenerate the view using the code

Thanks a lot for your contribution..
Sebastian