Comm.Table Exp. In S.Proc

[Henry Olive 5]

I wish everyone a Healthy day
 

Interbase 2020

I try to use a comman table expression in below stored procedure 

I created the s.proc w/o any problem but when i try to call the s.proc

I get  Multiple Rows in a Singletone Select err.msg

What is wrong ?

Thank You

 

CREATE PROCEDURE EXPMONTHLY 
RETURNS
(
  TMONTH SMALLINT,
  MONTHNAME VARCHAR(8),
  TOTAL NUMERIC(18, 2)
)
AS
BEGIN

WITH CTE (TMONTH,TOTAL)
AS(

SELECT TMONTH,SUM(SUBTOTAL) TOTAL FROM INVEXP
WHERE TNAME='ExpInv'
GROUP BY TMONTH
UNION ALL

SELECT TMONTH,SUM(TOTAL) TOTAL  FROM SALARY
WHERE TNAME = 'Sal.Pay'
GROUP BY TMONTH )

 

SELECT CTE.TMONTH,M.MONTHNAME,SUM(CTE.TOTAL) TOTAL FROM CTE
JOIN MONTHS M ON CTE.TMONTH=M.SMONTH
GROUP BY CTE.TMONTH,M.MONTHNAME

INTO :TMONTH,:MONTHNAME,:TOTAL;

SUSPEND;
END;

 

[Guest]

try my sample:

WITH CTE_RESULTED( MYMONTH, MYVALUE)
AS
(
SELECT TMONTH,SUM(SUBTOTAL) TOTAL FROM INVEXP AS A
WHERE TNAME='JOHN'
GROUP BY TMONTH

UNION ALL

SELECT TMONTH,SUM(TOTAL) TOTAL  FROM SALARY AS B
WHERE TNAME = 'JOHN'
GROUP BY TMONTH
)
/*  === JUST 1 LINE-RETURN
 SELECT SUM(MYVALUE) AS MYTOTAL FROM CTE_RESULTED AS C
*/

/* MANY LINES-RETURN */
SELECT MYVALUE AS MYTOTAL FROM CTE_RESULTED AS C

 

hug

[Henry Olive 5]

Thank you so much EMailX45

I think i couldnt express my problem well i'm very sorry.

You made JUST common table exprression SQL,  NOT Stored Procedure

JUST common table expression works w/o any problem in my side also

I need to use this CTE in a Stored Proc to get expenses according to months

 

Expect result should be like below ( for 12 months) with a stored procedure

Month......Total (It is Expenses Total)

1................10,000

2................16,000

...

12.............14,000

 

 

[Guest]

the regular use would be put the "SELECT  = CTE" into your "Stored Procedure" and in the end use "SUSPEND" command to send each line to your result.

Now, you can create a "VIEW" with your CTE statement for easy use in "FOR SELECT ...." command in your SP.

 

CREATE PROCEDURE GET_EMP_PROJ (EMP_NO SMALLINT)
RETURNS (PROJ_ID CHAR(5))
AS
BEGIN
  FOR SELECT <<fields desided or all>> 
  FROM << your CTE statement or VIEW>>
  WHERE << etc.. etc.. etc.. >>
  INTO :VAR_TEMP << etc.. etc.. etc..>>>
  DO
    SUSPEND;
END
///////
on app:
select * from your_SP /// for example!

 

http://docwiki.embarcadero.com/InterBase/2020/en/Using_Stored_Procedures_that_Return_Result_Sets

 

[Guest]

SELECT MYMONTH, MYVALUE AS MYTOTAL FROM CTE_RESULTED AS C

 

[Guest]

If you want to emit more than one "row" from a stored procedure, you will need to use:

 

FOR <SELECT CLAUSE>

INTO <PAR_LIST>

DO

BEGIN

  SUSPEND;

END

 

However, the error message indicates you have a problem in the join clause (not 100% sure).

 

So i have made a procedure that works in Firebird 3.0 (i do not have Interbase, so this might not work for you).

 

create or alter procedure TEST_OUTPUT
returns (
    TMONTH integer,
    TOTAL numeric(18,2),
    MONTH_NAME varchar(26))
as
begin

  for
    with cte (tmonth, total)
    as
    (
      select extract(month from pt.prj_actual_date), sum(pt.prj_actual_amount)
      from prj_trans pt
      where pt.prj_account_no = 13
      group by 1
  
      union
  
      select extract(month from pt.prj_actual_date), sum(pt.prj_actual_amount)
      from prj_trans pt
      where pt.prj_account_no = 10
      group by 1
    )
    select cte.tmonth, tm.monthname, sum(total)
    from cte
      join test_months tm on (tm.monthnum = cte.tmonth)
    group by 1, 2
    into :tmonth, :month_name, :total
    do
      suspend;
end

My "prj_trans" table does not have a "month number" so i did an extract for the cte, it is just what i had "at hand".

I added a test_month table to be sure only one row is generated for each month (could be a cartesian product in your SQL, not sure) but the error message indicates that you coded for one row but got more, so it will not hurt to check your join.

The output is as expected now (12 rows, monthname and amount).

 

HTH,

 

/Dany

[Henry Olive 5]

Thank you SO SO SO MUCH  Dany

I just added FOR , DO , SUSPEND

and now it works perfectly
I wish you a healthy, happy new year with your loved ones

 

[Guest]

It is even better to know one can be of help than being helped 🙂 do click "thanks".