Inline view?

[former_SQLA_member1694869]

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

[MarkCulp]

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.

[former_SQLA_member1694869]

The error message is that it does not find artnr_head, artid_head in sp_get_info (artnr_head, artid_head, 1).

[thomas_duemesnil]

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.

[former_SQLA_member1694869]

Yepp, the subselect works on it's own. Can you explain a bit more how a function could look like?

[Breck_Carter]

Please show us the exact error message produced by the above code.

[VolkerBarth]

If your stored procedure requires columns from some tables as arguments, it's usually necessary to use a lateral call, say something like ...

...
FROM tblArt, tblBen,
   LATERAL(sp_get_info(tblArt.artnr_head, tblBen.artid_head, 1) as MyProcResult
WHERE ...

(I can't comment whether your subselect approach should work here, too...)

[Breck_Carter]

Please post a new question, this time with code that actually demonstrates the problem you are having.

[Breck_Carter]

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;