Hi

Having a little trouble with this SQL with DBISAM v3.30:-

START TRANSACTION
;
SELECT TransNo, TransType
INTO MEMORY Temp1
FROM TenantTrans
WHERE TenantCode = '0072'
;
ALTER TABLE MEMORY Temp1
ADD PRIMARY KEY (TransNo)
;
UPDATE TransReg R
SET PreDeletion = T1.TransType,
   TransType = 'Deleted',
   Changed = True
FROM TransReg R
JOIN MEMORY Temp1 T1 ON (T1.TransNo = R.TransNo)
;
DROP TABLE MEMORY Temp1
;
COMMIT FLUSH
================
I'm getting error 11949 "Invalid column name TransNo or table correlation name 'R' specified in WHERE or JOIN clause."

Can't work out the correct syntax - the column name TransNo is definitely correct.

TIA

Jeff
--
Jeff Cook
Aspect Systems Ltd
Phone: +64-9-424 5388
Skype: jeffcooknz
www.aspect.co.nz

Jeff

You can not use ALTER TABLE inside transactions, even in v4.

Eduardo

"Jose Eduardo Helminsky" <contato@hpro.com.br> wrote on Tue, 13 Jun 2006 07:01:13 -0300

>Jeff
>
>You can not use ALTER TABLE inside transactions, even in v4.
>
>Eduardo
>
>
Jose


Oh!  That's odd - it seems to be executing that bit OK and only fails on the next statement  Actually, the script I started with had more statements that ran successfully between the ALTER and the UPDATE that is failing.  :-\

And I just removed the START TRANSACTION and the COMMIT and the problem remains.

Cheers


Jeff



--
Jeff Cook
Aspect Systems Ltd
Phone: +64-9-424 5388
Skype: jeffcooknz
www.aspect.co.nz

Jeff,

<< Having a little trouble with this SQL with DBISAM v3.30:- >>

Eduardo is correct, don't use ALTER TABLE or other statements that alter
metadata inside of a transaction.  Version 3.x let you get away with it, but
it is problematic at best.

<< I'm getting error 11949 "Invalid column name TransNo or table correlation
name 'R' specified in WHERE or JOIN clause." >>

Are you sure that you've got the correct table and that the table contains a
TransNo column ?

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote on Tue, 13 Jun 2006 17:10:31 -0400

>Jeff,
>
>Eduardo is correct, don't use ALTER TABLE or other statements that alter
>metadata inside of a transaction. Version 3.x let you get away with it, but
>it is problematic at best.
>

OK - that's easy fixed.  And I'll be all set up for v5 now


><< I'm getting error 11949 "Invalid column name TransNo or table correlation
>name 'R' specified in WHERE or JOIN clause." >>
>
>Are you sure that you've got the correct table and that the table contains a
>TransNo column ?

Much head scratching going on here!  I changed the MEMORY table to a real one so I can run the steps one at a time and each step seems OK ...


AAAAAAAAAH! Enlightenment!  It was the next step that was generating the error.  Dummy!  I must have got it fixed in my brain that it was the UPDATE statement that was causing the problem - but it was the next statement and it was indeed an invalid correlation name.

Thanks for your help (and patience) Tim and Eduardo.

Will ElevateDB have error messages pointing to the statement in a script that is generating the error?  Hope so!

Cheers

Jeff
--
Jeff Cook
Aspect Systems Ltd
Phone: +64-9-424 5388
Skype: jeffcooknz
www.aspect.co.nz

Jeff

> Will ElevateDB have error messages pointing to the statement in a script
> that is generating the error?

DBISAM v4 has already have it. It informs you the line/column number of the
SQL statement.

Eduardo