Newbie Help (again)

[Schnappa]

Hi All

Can someone help me with the following code - I keep getting errors, but can't work out why

CREATE TABLE [AccountInvestment] (
[AccountID] char (12) NOT NULL PRIMARY KEY,
[DepositNo] char (12) NULL,
[StartDate] smalldatetime NOT NULL,
[MatureDate] smalldatetime NULL,
[Term] smallint NULL
)

SELECT

dp_display.acct_no AS AccountID,
substring(dp_display.acct_no from 4 for 8) as DepositNo,
dp_display.last_dep_dt AS StartDate,
dp_display.mat_dt AS MatureDate,
dp_display.trm AS Term

FROM EOMUCU.dbo.dp_display dp_display INNER JOIN EOMUCU.dbo.dp_acct_int_opt dp_acct_int_opt ON dp_display.acct_no=dp_acct_int_opt.acct_no

WHERE dp_display.acct_type='INV' AND dp_acct_int_opt.debit_credit='CR'

ORDER BY dp_display.acct_no
____________________________________________________

Thanks for any help

Gezza

 

[7280]

What is the error?

 

[JD1866DSC]

I think you need to look at your SUBSTRING for starters, as you seem to have a FROM cluase in there. You just need to seperate the start position and number of characters with a comma. so SUBSTRING(field,4,8).

 

[Schnappa]

Thanks to both of you. JDs tip has worked a treat. Just goes to show you that you can't always believe what you read in books.

Cheers

Gez

 

[SQLBill]

Which book did you read that in?

-SQLBill

Posting advice: FAQ481-4875