mysql create view and cast as syntax question

[archaniz]

I need to create a view of a table to view the primary key as int, but im not sure the correct syntax...

This is what I have:

CREATE
VIEW `database`.`Table`
AS
(SELECT * FROM Table);

But where does the CAST AS go and how?

[shadmego]

I am not familiar with either of these commands, so you might want to wait for a more knowledgeable expert, but from reading, CAST AS is not part of CREATE VIEW, though I imagine if CAST AS can be used in a SELECT statement, you should be able to use CAST AS with CREATE VIEW. Here is some more reading:

http://dev.mysql.com/doc/refman/5.0/en/create-view.html

http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html

Wish I could be more help.

[archaniz]

Thanks.

I think thats what the problem is... i know they can be used together, I'm just not sure how...

You're right about the SELECT CAST statement.

I'm trying to do this for the cast function:

SELECT CAST(field AS int) AS field FROM Table;

Except it won't let me use int or float as the datatype. The original datatype is bigint. The above query works with other datatypes, for ex. CHAR.

For example:

CREATE
VIEW `database`.`Table`
AS
(SELECT CAST(field AS CHAR) AS field FROM Table);

Works... but its only the one field, when I need all the fields from the table - similar to "SELECT *", but with the CAST function for that one field.

Except the whole point of the CAST function is to convert bigint to int or float, and it wont let me?

[archaniz]

Unfortunately, this isnt solving the original problem which was the reason for asking the question...

Im using an ODBC remote connection from access, except one of the tables is showing as #DELETED. Supposedly, using a CAST function with a CREATE VIEW is supposed to correct this issue - but it doesnt.

Any help would be appreciated.

[shadmego]

As I said before, I am not familiar with these mysql functions, so the following is completely taken from other people. The specific quote below was taken from the mysql link about casting I provided above. It can be found in the comment section on date: Jan 4, 2008 (10:04am).



"SELECT * FROM Sales WHERE TransactionDate BETWEEN CAST('$D1' as DATETIME) and CAST($D2 as DATETIME)"


I'm not sure why you can't go from bigint to int, unless it has something to do with your fields being "signed" or not(?).

[alienspaces]

Google search on the subject:

http://bugs.mysql.com/bug.php?id=10910

http://forums.mysql.com/read.php?65,256,256#msg-256

Do either of those sound like your problem?