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

asked 18 Mar '14, 14:40

Rolle's gravatar image

Rolle
558495161
accept rate: 0%

closed 19 Mar '14, 08:35

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050

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.

(18 Mar '14, 14:47) Mark Culp
Replies hidden

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

(18 Mar '14, 15:07) Rolle

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.

(18 Mar '14, 15:34) Thomas Dueme...
Replies hidden

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

(18 Mar '14, 15:43) Rolle

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

(18 Mar '14, 21:09) Breck Carter

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...)

(19 Mar '14, 04:49) Volker Barth

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

(19 Mar '14, 08:35) Breck Carter
showing 4 of 7 show all flat view

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;
 
permanent link

answered 19 Mar '14, 08:33

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

edited 19 Mar '14, 08:37

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×31

question asked: 18 Mar '14, 14:40

question was seen: 2,097 times

last updated: 19 Mar '14, 08:37