Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Rotating columns

3 posts in Trigger Last posting was on 2004-08-02 11:26:47.0Z
francois Posted on 2004-08-01 14:18:28.0Z
Reply-To: "francois" <fransh_@westnet.com.au>
From: "francois" <fransh_@westnet.com.au>
Newsgroups: Advantage.Trigger
Subject: Rotating columns
Date: Sun, 1 Aug 2004 22:18:28 +0800
Lines: 152
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
NNTP-Posting-Host: 202.72.164.126
Message-ID: <410cfd28@solutions.advantagedatabase.com>
X-Trace: 1 Aug 2004 08:24:40 -0700, 202.72.164.126
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!202.72.164.126
Xref: solutions.advantagedatabase.com Advantage.Trigger:99
Article PK: 1136166

Hi

I need a trigger to copy the EndDate, EndAmount from last cycle
to StartDate,StartAmount of the new appended record
Date format dd/MM/ccyy
========================================
If the last record is: (recID = autoInc field)

RecID, StartDate, StartAmount, EndDate, EndAmount, Done
1, 1/1/2000, 100, 31/1/2000, 200, true

I need the appended record to look like this
2, 31/1/2000, 200, null, null, false
========================================
CREATE TRIGGER ON SmtBal INSTEAD OF INSERT
BEGIN
Insert Into __new (StartDate, StartAmount)
Select EndDate, EndAmount from TestTable
where StartDate=(Select max(StartDate) from TestTable);
END NO MEMOS PRIORITY 1;
=========================================
Error as in attached gif
=========================================
Select EndDate, EndAmount from TestTable
where StartDate=(Select max(StartDate) from TestTable);

works fine on its own but

Insert Into TestTable (StartDate, StartAmount)
Select EndDate, EndAmount from TestTable
where StartDate=(Select max(StartDate) from TestTable);
generates the error

TIA
Francois


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.726 / Virus Database: 481 - Release Date: 22/07/2004

trig.gif

Francois Posted on 2004-08-02 07:31:13.0Z
Reply-To: "francois" <francois@geedee.com.au>
From: "francois" <francois@geedee.com.au>
Newsgroups: Advantage.Trigger
References: <410cfd28@solutions.advantagedatabase.com>
Subject: Re: Rotating columns
Date: Mon, 2 Aug 2004 15:31:13 +0800
Lines: 27
Organization: geedee
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
NNTP-Posting-Host: 202.72.180.220
Message-ID: <410deef5@solutions.advantagedatabase.com>
X-Trace: 2 Aug 2004 01:36:21 -0700, 202.72.180.220
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!202.72.180.220
Xref: solutions.advantagedatabase.com Advantage.Trigger:100
Article PK: 1136169

Hi

Sorry, This did not work because I stll had the erroneous trigger in place:
The following SQL works OK in ARC32

> Insert Into TestTable (StartDate, StartAmount)
> Select EndDate, EndAmount from TestTable
> where StartDate=(Select max(StartDate) from TestTable);

How do I make this (sql above) into a trigger to pre-populate a new record?
Should it be a before insert or instead of insert trigger?
I also have a Boolean field named DONE and would like new records to be
blocked if DONE<>True.

The following failed
> CREATE TRIGGER ON SmtBal INSTEAD OF INSERT
> BEGIN
> Insert Into __new (StartDate, StartAmount)
> Select EndDate, EndAmount from TestTable
> where StartDate=(Select max(StartDate) from TestTable);
> END NO MEMOS PRIORITY 1;
and gave the error as in attached gif (to the previous message) implying
that I have multiple records in __new

TIA
Francois


francois Posted on 2004-08-02 11:26:47.0Z
Reply-To: "francois" <fransh_@westnet.com.au>
From: "francois" <fransh_@westnet.com.au>
Newsgroups: Advantage.Trigger
References: <410cfd28@solutions.advantagedatabase.com> <410deef5@solutions.advantagedatabase.com>
Subject: Re: Rotating columns
Date: Mon, 2 Aug 2004 19:26:47 +0800
Lines: 36
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
NNTP-Posting-Host: 202.72.164.126
Message-ID: <410e2687@solutions.advantagedatabase.com>
X-Trace: 2 Aug 2004 05:33:27 -0700, 202.72.164.126
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!202.72.164.126
Xref: solutions.advantagedatabase.com Advantage.Trigger:101
Article PK: 1136167

Ok I got it to work but still need a way to block insertion when the last
record
( where Tme=max(Tme) or RecID=max(RecID)) has Done=false
or maybe when count(Done)>0 where Done=False?

Any takers?

I'm also not yet happy with
where EndDte = ( Select max(EndDte) from smtBal where Done=True)
as (theoretically at least) I may have more than one per day.
Under Paradox the Autoinc of this particular table regularly lost its way
and we had
to abandon looking for the max(RecID). I wish to replace that whole
logic with a trigger.

TIA
Francois

CREATE TABLE SmtBal ( RecID AutoInc, EndDte Date, EndAmt Money, Done
Logical,
FromDte Date, FromAmt Money, Tme TimeStamp);

CREATE TRIGGER SmtBalNewRec ON SmtBal INSTEAD OF INSERT
BEGIN
Update __New set
EndDte = ( Select max(EndDte) from smtBal where Done=True),
Tme = Now(),
Done = False,
FromDte = ( Select EndDte from smtBal where EndDte =
( Select max(EndDte) from smtBal where Done=True)),
FromBal = ( Select EndAmt from smtBal where EndDte=
( Select max(EndDte) from smtBal where Done=True));
Insert into SmtBal Select * from __New;
END NO MEMOS PRIORITY 1;