1

I have a firebird database where I call a procedure to get some data. This procedure needs an ID so I have two php queries, one of them gives me all the ID's and the another one calls the procedure. 

$queryP ="SELECT PROVEEDOR_ID FROM PROVEEDORES";
...some code...
while ($RowQ = ibase_fetch_object ($QueryObject)) 
{   
    $queryCompras =SELECT SUM(IMPORTE) FROM ORSP_CM_COMPRAS_PROV($RowQ->PROVEEDOR_ID, '2019-01-01', '2019-12-31', 'B', 'P', 'N');
...some code...

I would like to do something like 

 SELECT SUM(COMPRA_IMPORTE) FROM ORSP_CM_COMPRAS_PROV((SELECT PROVEEDOR_ID FROM PROVEEDORES), '2019-01-01', '2019-12-31', 'B', 'P', 'N');

To avoid doing too many database calls, but I'm getting

Statement failed, SQLSTATE = 21000 multiple rows in singleton select

Is there any way to do this?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Jorge Gallardo
  • 79
  • 1
  • 9

1 Answers1

1

You can join your subquery (or in this case: table) and the stored procedure to get the desired effect.

select sum(COMPRA_IMPORTE)
from PROVEEDORES p
left join ORSP_CM_COMPRAS_PROV(p.PROVEEDOR_ID, '2019-01-01', '2019-12-31', 'B', 'P', 'N') on 1 = 1

Note that I'm using left join, but its actual effect when joining a stored procedure is similar to what for example Microsoft SQL Server calls an OUTER APPLY.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • 1
    Well, I'd have a pretty hard time wrapping my head around that ... even if it worked beautifully ... so what comes to *my* mind is simply another stored procedure which, in a loop, executes the first. Now, the operation is being done start-to-finish on the database server. (Which, by the way, "can be a good thing or a bad thing," depending on how good your server's implementation of stored procedures actually is ...) – Mike Robinson Jan 08 '20 at 15:52
  • 1
    @MikeRobinson That is also an option, although in the end it would have the same effect as using a join with the stored procedure. Firebird will execute the stored procedure for each row (and pass the value of `p.PROVEEDOR_ID`) and join the rows from the stored procedure to that row. – Mark Rotteveel Jan 08 '20 at 16:58
  • Mb I forget to specify that I'm using a database backup so I can't create new procedures. The new query is working perfectly I just change cross join for left join due to a "no current record for fetch operation" error. Thank you both :) – Jorge Gallardo Jan 08 '20 at 20:36
  • @JorgeGallardo It should be [LEFT JOIN](http://www.firebirdfaq.org/faq143/). – BrakNicku Jan 09 '20 at 10:27
  • @BrakNicku That is what he said. However, I tested my solution and it worked with CROSS JOIN (and INNER JOIN), but apparently I missed to test the case where the stored procedure produces no rows. – Mark Rotteveel Jan 09 '20 at 10:43
  • You should always use left joins with stored procedures to force correct join order. Otherwise you let the optimizer choose the order and it may sometimes fail with the error OP quotes in his comment (it might be considered optimizer deficiency). Of course left/inner joins give different results for procedure returning no rows, so to get equivalent of inner join you have to use [workaround](http://www.firebirdfaq.org/faq144/) – BrakNicku Jan 09 '20 at 11:09
  • @BrakNicku Thanks, I changed my code to use left join. – Mark Rotteveel Jan 09 '20 at 11:14