Views - how do I define the column types?

[MattWoberts]

Hi,

I have created a view in my oracle database. I need the view to include some number that is "hard-coded" by me, so the SQL for the view could look like this:

Select Table.X, Table.Y, 10 as MyValue from Table

Now, this all works fine, but when I "DESC" my view, "MyValue" is defined as a "NUMBER" with no precision or scale - which is effectively a decimal. I need to explicitly force this number to be a integer (a NUMBER with precision of 8 and scale 0). Please can anyone tell me how to do this.... Can I use an oracle function in the view definiton or is it somehow possible to define the column precison and scale in the view?

Many thanks!

 

[sem]

Select Table.X, Table.Y, cast(10 as number(8,0)) as MyValue from Table

Regards, Dima

http://seminihin.narod.ru

 

[MattWoberts]

Thanks,

Thats exactly what I thought woudl do it, but when I try that, I still don't get what I need.

Heres the SQL:

CREATE OR REPLACE VIEW APPUSER_RELATEDITEM ( APPUSER_ID,
MODULE_CODE, SUB_CODE, RELSITEID, MODULE_ID,
ROLESITEID ) AS
SELECT UserRole_ID AS AppUser_ID, Module_Code, Sub_Code, Site_ID AS RelSiteID, Module_ID, cast(0 as number(8,0)) as RoleSiteID
FROM AppUserRole_RelatedItem
WHERE (UserRole_Code = 'P')

And when I DESC that view, see how RoleSiteID looks compared to Module_ID:

MODULE_ID N NUMBER 8 8 0
ROLESITEID Y NUMBER

Oracle still won't make the column for "RoleSiteID" into an integer....hmmm

 

[lewisp]

Question: Why do you need a view that always returns a 0 in a number(8,0) field? If you create a view with a hard coded value for a column, that value will always be returned in a query.

A view does exactly what is says on the tin: it is a view of another table(s). The column datatypes defined on the original tables are the datatypes you will see defined in your view.

 

[MattWoberts]

I knew I had this quesiton coming

Ok, the reason is that I am using ODP.NET to get the data out of the database into my .NET application. Now, I need the view to map the number to an integer, because thats what my .net code deals with, and for reasons I can't really go into, I can't change the .net code, so I need to make the view return an integer.....

Does that make sense?