Create View in SQL Server with data types

I need to create a view of a sql table, but change the data types. I know
the syntax below is not correct, and can't figure out if it is wrong or if
you just can't do this. I have only created views before with the same data
type.

CREATE VIEW F0005New(DRKY nchar(3), DRDL01 nchar(30))
INSERT (SELECT rtrim(F0005.DRKY), F0005.DRDL01
FROM F0005 AS F0005
WHERE DRSY = '41' AND DRRT = 'S1')

Thanks!!Aren't those JD Edwards column names a drag? I'm surprised you need
UNICODE (nchar and nvarchar instead of char and varchar), I did not
realize that JDE could handle anything beyond EBCDIC or ASCII.

A VIEW is just a SELECT statement inside. The example has an INSERT
statement. A VIEW can NOT have an INSERT statement.

CREATE VIEW does not allow for specifying the data types in the VIEW
column list. It is not part of the syntax.

When I need to control the data type this way I use CONVERT, though
CASE can do the same thing:

CREATE VIEW F0005New
AS
SELECT CONVERT(nhcar(3), rtrim(F0005.DRKY)) as DRKY,
CONVERT(nchar(30), F0005.DRDL01) as DRDL01
FROM F0005
WHERE DRSY = '41'
AND DRRT = 'S1'

Roy Harvey
Beacon Falls, CT

On Mon, 14 Aug 2006 15:58:01 GMT, "cognosqueen" <u25284@.uwewrote:

Quote:

Originally Posted by

>I need to create a view of a sql table, but change the data types. I know
>the syntax below is not correct, and can't figure out if it is wrong or if
>you just can't do this. I have only created views before with the same data
>type.
>
>CREATE VIEW F0005New(DRKY nchar(3), DRDL01 nchar(30))
>INSERT (SELECT rtrim(F0005.DRKY), F0005.DRDL01
>FROM F0005 AS F0005
>WHERE DRSY = '41' AND DRRT = 'S1')
>
>Thanks!!

|||Roy - thanks so much!!! You are right about the JE Edwards column names!! I
do not enjoy them at all. Regarding the data type - the client already had
it set up - I just have to live with it!! I'll try this and see if it works!

Roy Harvey wrote:

Quote:

Originally Posted by

>Aren't those JD Edwards column names a drag? I'm surprised you need
>UNICODE (nchar and nvarchar instead of char and varchar), I did not
>realize that JDE could handle anything beyond EBCDIC or ASCII.
>
>A VIEW is just a SELECT statement inside. The example has an INSERT
>statement. A VIEW can NOT have an INSERT statement.
>
>CREATE VIEW does not allow for specifying the data types in the VIEW
>column list. It is not part of the syntax.
>
>When I need to control the data type this way I use CONVERT, though
>CASE can do the same thing:
>
>CREATE VIEW F0005New
>AS
>SELECT CONVERT(nhcar(3), rtrim(F0005.DRKY)) as DRKY,
CONVERT(nchar(30), F0005.DRDL01) as DRDL01
FROM F0005
WHERE DRSY = '41'
AND DRRT = 'S1'
>
>Roy Harvey
>Beacon Falls, CT
>

Quote:

Originally Posted by

>>I need to create a view of a sql table, but change the data types. I know
>>the syntax below is not correct, and can't figure out if it is wrong or if

>[quoted text clipped - 7 lines]

Quote:

Originally Posted by

>>
>>Thanks!!

|||Roy - it worked! Thanks for your help.

cognosqueen wrote:

Quote:

Originally Posted by

>Roy - thanks so much!!! You are right about the JE Edwards column names!! I
>do not enjoy them at all. Regarding the data type - the client already had
>it set up - I just have to live with it!! I'll try this and see if it works!
>

Quote:

Originally Posted by

>>Aren't those JD Edwards column names a drag? I'm surprised you need
>>UNICODE (nchar and nvarchar instead of char and varchar), I did not

>[quoted text clipped - 25 lines]

Quote:

Originally Posted by

Quote:

Originally Posted by

>>>
>>>Thanks!!

|||You are welcome, and good luck! My experience with importing JD
Edwards data into a SQL Server data warehouse was quite painful, not
because of F0005 which in our case was quite clean, but the invoice
data that came in lacked any unique key. I hope your life is simpler
than mine was.

Roy

On Mon, 14 Aug 2006 18:37:37 GMT, "cognosqueen" <u25284@.uwewrote:

Quote:

Originally Posted by

>Roy - it worked! Thanks for your help.
>
>cognosqueen wrote:

Quote:

Originally Posted by

>>Roy - thanks so much!!! You are right about the JE Edwards column names!! I
>>do not enjoy them at all. Regarding the data type - the client already had
>>it set up - I just have to live with it!! I'll try this and see if it works!
>>

Quote:

Originally Posted by

>>>Aren't those JD Edwards column names a drag? I'm surprised you need
>>>UNICODE (nchar and nvarchar instead of char and varchar), I did not

>>[quoted text clipped - 25 lines]

Quote:

Originally Posted by

>>>>
>>>>Thanks!!