There are three tables in the project (order, consists of and the article). I am trying to calculate the total amount of the order and return this value through a function for later use. The following code works and gives the total amount of the order:

select sum(Sub.LineItemSum) as gesamtsumme from ( select besteht_aus.ArtikelID, ArtikelAnzahl*Artikel.Preis as LineItemSum From besteht_aus inner join Artikel where besteht_aus.ArtikelID = Artikel.ArtikelID and BestellID = 2) Sub

But if I try to shove this piece of code into a function, I get an error.

 create or replace function gesamtsumme (@BestellID integer) returns money begin declare @summe money set @summe = (select sum(Sub.LineItemSum) as insgesamt from ( select besteht_aus.ArtikelID, ArtikelAnzahl*Artikel.Preis as LineItemSum From besteht_aus inner join Artikel where besteht_aus.ArtikelID = Artikel.ArtikelID and BestellID = @BestellID) Sub) return @summe end

I suspect that set simply does not cope with a subquery. Tell me, please, what is the error and how to fix it.

  • one
    you just get the word "error", or is there some kind of text? - teran
  • Try: return (select sum(Sub.LineItemSum) ...) - MaxU
  • I suspect that at the end of operators you need semicolons. You did not specify the DBMS and the dialects are different for everyone, but in the stored procedures, most of the semicolons are needed. And yes, set usually does not cope with requests. something like select ... into переменная usually used, or select переменная:=sum .... if it is suddenly MySQL - Mike
  • What is this SQL dialect, in which, when an internal join of two tables is not necessary to specify a join condition? - KadafiVSC
  • SQL Anywhere. Select = tried, select into no, in the end I managed with a “crutch” - I just created a procedure that shows this data. To the first comment: Sybase Central swore to a mistake in the set. Do not scold too much, just learning, asking a question during the preparation of a student project. - alex-sandr11

0