cannot select into

Question

cannot select into

winston Smith

SSCoach

Points: 19500
More actions
July 25, 2006 at 5:51 am

#113896

im trying to select into a table ( has not been created yet) but i keep getting this error:
Msg 1038, Level 15, State 5, Procedure PaymentTransactionGet, Line 70
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.

Here is the insert code:
SELECT
@ProgramID
,
@CustomerID
,
P
.PaymentTransactionID,
P
.Period,
P
.PaymentTypeID,
P
.CurrencyCode,
P
.GLAccountID,
Amount
= convert(decimal (12,2),(P.AmountUSD / P.ExchangeRateToUSD)) ,
P
.PaymentDate,
P
.PaymentReference,
T
.PaymentTypeDescription,
G
.MSAccountCode,
PaymentConfirmedDate
=isnull(p.PaymentConfirmedDate,''),
P
.PTNotes,
P
.PTIncVAT
into Payments_report1288
FROM
PaymentTransaction P
,
PaymentType T
,
GLAccount G
,
CURR01Currency C
WHERE.........

In any derived fields, like PaymentConfirmedDate, i have tried putting quotes around the name (eg 'PaymentConfirmedDate' = isnull.....) and also tried using the as keyword ( eg isnull(p.PaymentConfirmedDate,'') as PaymentConfirmedDate,)

None of that works. what am i doing wrong here?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

winston Smith SSCoach Points: 19500 More actions · Answer 1

its cool. sorted it, was looking at the wrong columns completely. should have been looking at @ProgramID and @customerID. they both need an alias. thanks all

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 2

It is @ProgramID and @CustomerID that is missing an alias!

SELECT @ProgramID AS ProgramID,

@CustomerID AS CustomerID,

P.PaymentTransactionID,

P.Period,

P.PaymentTypeID,

P.CurrencyCode,

P.GLAccountID,

Amount = convert(decimal (12,2), (P.AmountUSD / P.ExchangeRateToUSD)),

P.PaymentDate,

P.PaymentReference,

T.PaymentTypeDescription,

G.MSAccountCode,

PaymentConfirmedDate=isnull(p.PaymentConfirmedDate,''),

P.PTNotes,

P.PTIncVAT

into Payments_report1288

FROM PaymentTransaction P,

PaymentType T,

GLAccount G,

CURR01Currency C

WHERE .........

N 56°04'39.16"
E 12°55'05.25"