I use an inline view according below that will use a stored procedure. Input data to the stored procedure arrive from main sql. But it does not work. The code below is an outline to show what I mean. I want to use artid_head and artnr_head from main sql as input to my stored procedure in the inline view. SQL is much larger in practice... Select tblArt.artid_head, tblBen.artnr_head, (SELECT FIRST STRING(ct_disp.customer) + ' / ' + (select customer.k_namn from customer where customer.k_kod =ct_disp.customer) FROM (SELECT period as period, lev_customer as customer, qty + SUM(best - reserv) OVER (PARTITION BY artnr ORDER BY datum ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as qty FROM sp_get_info(artnr_head, artid_head, 1)) AS ct_disp WHERE ct_disp.qty < 0 ORDER BY ct_disp.period) as cc_plan_customer FROM tblArt, tblBen WHERE tblArt.ID = tblBen.ID |
The question has been closed for the following reason "Code posted does not demonstrate problem reported." by Breck Carter 19 Mar '14, 08:35
Please post a new question, with code that actually demonstrates the problem described, and we will be happy to look at it. The code you posted here works on every version of SQL Anywhere from 9 to 16... @@VERSION,artid_head,artnr_head,cc_plan_customer '9.0.2.3951',2,5,'333 / X' @@VERSION,artid_head,artnr_head,cc_plan_customer '10.0.1.4310',2,5,'333 / X' @@VERSION,artid_head,artnr_head,cc_plan_customer '11.0.1.2960',2,5,'333 / X' @@VERSION,artid_head,artnr_head,cc_plan_customer '12.0.1.3994',2,5,'333 / X' @@VERSION,artid_head,artnr_head,cc_plan_customer '16.0.0.1691',2,5,'333 / X' Here is the "reproducible"... CREATE TABLE tblArt ( ID INTEGER, artid_head INTEGER ); INSERT tblArt VALUES ( 1, 2 ); CREATE TABLE tblBen ( ID INTEGER, artnr_head INTEGER ); INSERT tblBen VALUES ( 1, 5 ); CREATE TABLE customer ( k_namn VARCHAR ( 10 ), k_kod INTEGER ); INSERT customer VALUES ( 'X', 333 ); CREATE PROCEDURE sp_get_info ( p1 INTEGER, p2 INTEGER, p3 INTEGER ) BEGIN SELECT 111 AS customer, 222 AS period, 333 AS lev_customer, -444 AS qty, 555 AS best, 666 AS reserv, 777 AS artnr, 888 AS datum; END; Select @@VERSION, tblArt.artid_head, tblBen.artnr_head, (SELECT FIRST STRING(ct_disp.customer) + ' / ' + (select customer.k_namn from customer where customer.k_kod =ct_disp.customer) FROM (SELECT period as period, lev_customer as customer, qty + SUM(best - reserv) OVER (PARTITION BY artnr ORDER BY datum ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as qty FROM sp_get_info(artnr_head, artid_head, 1)) AS ct_disp WHERE ct_disp.qty < 0 ORDER BY ct_disp.period) as cc_plan_customer FROM tblArt, tblBen WHERE tblArt.ID = tblBen.ID; |
What exactly is not working? What error are you getting? I.e. more information is needed.
Also please tell us the version and build number that you are using.
The error message is that it does not find artnr_head, artid_head in sp_get_info (artnr_head, artid_head, 1).
Can you try if your subselect works on its own ? Then try to create a function which takes your two parameters and returns the prepared string.
I suspect that the window function in the subselect can produce the problem.
Only my 2 cents.
Yepp, the subselect works on it's own. Can you explain a bit more how a function could look like?
Please show us the exact error message produced by the above code.
If your stored procedure requires columns from some tables as arguments, it's usually necessary to use a lateral call, say something like ...
(I can't comment whether your subselect approach should work here, too...)
Please post a new question, this time with code that actually demonstrates the problem you are having.