8 Replies - 369 Views - Last Post: 27 November 2013 - 12:08 PM Rate Topic: -----

#1 Random01  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 42
  • Joined: 06-October 13

Trigger question / help needed

Posted 27 November 2013 - 09:17 AM

So i have to calculate the revenue of orders in a certain year.
But when i try to extract the date the column sizes don't add up so it gives an error.
But I don't know how to do this in another way, so if anyone can help me on my way I'd greatly appreciate it.

Create or alter trigger tCalcRevenue_aiud
Active after insert or update or delete on Order
As
Declare variable number integer;
Begin
If (updating and (new.amount= old.amount)) then 
Exit;
If (inserting or updating) then
number = new.customer;
Else 
number= old.customer;
Update Customer C
Set C.revenue = (select sum(amount), extract(year from order_date)
From Order O
Where 
O.customer= C.nr 
)

where
C.nr = :number; 

End



Is This A Good Question/Topic? 0
  • +

Replies To: Trigger question / help needed

#2 DarenR  Icon User is online

  • D.I.C Lover

Reputation: 386
  • View blog
  • Posts: 2,655
  • Joined: 12-January 10

Re: Trigger question / help needed

Posted 27 November 2013 - 09:18 AM

what do you mean they dont add up?

also post your error
Was This Post Helpful? 0
  • +
  • -

#3 Random01  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 42
  • Joined: 06-October 13

Re: Trigger question / help needed

Posted 27 November 2013 - 09:21 AM

The error reads when i try to execute the sql code:

Error -104
Invalid command
count of column list and variable list do not match
Was This Post Helpful? 0
  • +
  • -

#4 mojo666  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 337
  • View blog
  • Posts: 729
  • Joined: 27-June 09

Re: Trigger question / help needed

Posted 27 November 2013 - 10:12 AM

Update Customer C  
Set C.revenue = (select sum(amount), extract(year from order_date)  



I'm not sure what language this is, but typically you cannot store 2 fields into 1 field. Perhaps you wanted something like SELECT C.revenue = sum(amount), C.SomeYear=extract(year from order_date)?
Was This Post Helpful? 0
  • +
  • -

#5 Random01  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 42
  • Joined: 06-October 13

Re: Trigger question / help needed

Posted 27 November 2013 - 10:30 AM

View Postmojo666, on 27 November 2013 - 10:12 AM, said:

Update Customer C  
Set C.revenue = (select sum(amount), extract(year from order_date)  



I'm not sure what language this is, but typically you cannot store 2 fields into 1 field. Perhaps you wanted something like SELECT C.revenue = sum(amount), C.SomeYear=extract(year from order_date)?


Oh i see, so maybe i could create another temporary variable like
Declare variable year integer;

because i don't have a field called SomeYear...

and then add
Set C.revenue = (select sum(amount)
From Order O
Where
O.customer= C.nr and
:year = extract(year from order_date)-1


(I added the -1, because revenue should be calculated for the previous year)
Was This Post Helpful? 0
  • +
  • -

#6 mojo666  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 337
  • View blog
  • Posts: 729
  • Joined: 27-June 09

Re: Trigger question / help needed

Posted 27 November 2013 - 11:21 AM

If I understand what you are trying to do correctly the logic would be more like

Declare variable myYear integer;
.
.
.
myYear = extract(year from new.order_date)-1;
.
.
.
Set C.revenue = (select sum(amount)  
From Order O  
Where 
O.customer= C.nr and 
extract(year from O.order_date)=myYear)



Again, I don't know the language so I'm not sure exactly what syntax is valid or invalid, but I would assume something like this is possible.
Was This Post Helpful? 0
  • +
  • -

#7 Random01  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 42
  • Joined: 06-October 13

Re: Trigger question / help needed

Posted 27 November 2013 - 11:40 AM

the language is just SQL i believe

I insert it into the SQL editor from Cathedron so..
Was This Post Helpful? 0
  • +
  • -

#8 DarenR  Icon User is online

  • D.I.C Lover

Reputation: 386
  • View blog
  • Posts: 2,655
  • Joined: 12-January 10

Re: Trigger question / help needed

Posted 27 November 2013 - 11:52 AM

View PostRandom01, on 27 November 2013 - 02:40 PM, said:

the language is just SQL i believe

I insert it into the SQL editor from Cathedron so..



wait how are you supposed to know how to use said query of you dont know the language
Was This Post Helpful? 0
  • +
  • -

#9 Random01  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 42
  • Joined: 06-October 13

Re: Trigger question / help needed

Posted 27 November 2013 - 12:08 PM

View PostDarenR, on 27 November 2013 - 11:52 AM, said:

View PostRandom01, on 27 November 2013 - 02:40 PM, said:

the language is just SQL i believe

I insert it into the SQL editor from Cathedron so..



wait how are you supposed to know how to use said query of you dont know the language


it's SQL, i don't know what more you want me to say here

instead of inserting it via Firebird (PSQL) it's inserted via the SQL editor of Cathedron
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1