Multidimensional arrays in SQLScript

[Former Member]

Hi dear experts,

I have two questions that are related to the project I'm currently working on:

1) is there any possibility to declare an multidimensional array in the SQLScript-based procedure ?

2) I'm actually trying to use table types (instead of arrays), but how could I access the specific table filed of the input table in my procedure:

CREATE PROCEDURE simpl (in intab tableau, in i integer, out outtab tableau)

AS

BEGIN

outtab= select top 10 from :intab where :intab.X = :i;

END;

tableau has three columns: A, B, C (type integer)

Thanks in advance!

[Former Member · Accepted solution]

Solution to question two (I have found yesterday):

assumption: X is the name of one of the intab columns - therefore should be written between quotation marks

CREATE PROCEDURE simpl (in intab tableau, in i integer, out outtab tableau)

AS

BEGIN

outtab= select top 10 from :intab where :intab."X" = :i;

END;

[sagarjoshi]

Could you please give an example with some sample data what you want to achieve here? Are you trying to read a specific row & column of the table?

[Former Member]

Hi,

this problem can be simply described as a problem of searching of the pivot value as a part of simplex method.

I need to identify the column (index) with a max value in the last row, and a row (index) with the min value in the last column. Then I need to identify the value at the intersection of the found row (index) and column (index), which should be used for next calculation steps.

Eample:

Tableau: {  2, 1, 1, 1, 0, 0, 0, 90

                    1, 3, 2, 0, 1, 0, 0, 300

                    2, 1, 2, 0, 0, 1, 0, 120

                    6, 5, 4, 0, 0, 0, 1, 0     }

Here the max value of last row has index=1 and min value of last column has index=1, so the value with indexes 1, 1 in Tableau is 2.

Currently I try to solve this problem using only SQLScript (& I still belive it is possible ).

[sagarjoshi]

Yes I think it is possible since you have fixed set of columns (some hints below on possible logic)

First you have to select the last row which you can do easily by just doing max(row_no) . If you don't have row_no in your input then you can generate row_num column additionally in SQL Script

Then you use CASE STATEMENT to find the index of column which has max value. If you have fixed no, of columns in input table then you can write something like

CASE

        WHEN A >= B AND A >= C THEN 1

        WHEN B >= A AND B >= C THEN 2

        WHEN C >= A AND C >= B THEN 3

        END  AS max_colid

It is easier to get the rowid with min value by just doing ORDER BY on last column and selecting top row.

Then you need to write 1 more SELECT statement with CASE

select CASE WHEN :max_colid = 1 THEN A

  WHEN :max_colid = 2 THEN B

  WHEN :max_colid = 3 THEN C

  END AS INTERSECT_VAL

from table where row_num = :min_rowid;

I am avoiding providing complete script logic so that you have satisfaction of solving on your own.