Skip to Content
author's profile photo Former Member
Former Member

SQLScript: ARRAY_AGG with a string column

Hi all,

I'm trying to convert a table column of type NVARCHAR to an array inside a stored procedure (just as a test):

PROCEDURE "..."."...::test" (

out var_out "...::simple"

)

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

READS SQL DATA AS

BEGIN

DECLARE loc NVARCHAR(20) ARRAY;

DECLARE prod NVARCHAR(40) ARRAY;

tab = select "LOCATIONNUMBER" as LOCATION, "PRODUCTNUMBER" as PRODUCT from "..."."ZSCP_LOCPROD";

prod := ARRAY_AGG(:tab.PRODUCT);

loc := ARRAY_AGG(:tab.LOCATION);

var_out = unnest(:loc, :prod) as("LOCATION", "PRODUCT");

END;

When I try to activate the procedure I get the error message:

"Could not create catalog object: expression is of wrong type; Table column must be of same type than resulting array: line 16 col 19 (at pos 498)",

referring to the first ARRAY_AGG call. Does anyone have an idea what I am doing wrong?

I have already tried the same thing using other data types, like integers and dates, and it worked perfectly. Now I can't seem to be able to guess the correct syntax for string columns - unfortunately most of the examples in the SQLScript reference are with integer arrays, the only one using a VARCHAR data type is in the UNNEST section...

The table type of the output parameter is:

table.columns = [

{name = "LOCATION"; sqlType = NVARCHAR; nullable = false; length = 20;},

{name = "PRODUCT"; sqlType = NVARCHAR; nullable = false; length = 40;}

];

And the sql generating the queried table:

CREATE COLUMN TABLE "..."."ZSCP_LOCPROD" (

"MANDT" NVARCHAR(3) DEFAULT '000' NOT NULL ,

"LOCATIONNUMBER" NVARCHAR(20) DEFAULT '' NOT NULL ,

"PRODUCTNUMBER" NVARCHAR(40) DEFAULT '' NOT NULL ,

"PPLANNER" NVARCHAR(3) DEFAULT '' NOT NULL ,

"SPLANNER" NVARCHAR(3) DEFAULT '' NOT NULL ,

"USE" NVARCHAR(50),

PRIMARY KEY ("MANDT",

"LOCATIONNUMBER",

"PRODUCTNUMBER"))

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Dec 16, 2014 at 12:12 PM

    Hi Jordan,

    I got the same problem. Did u solve it?

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Its strange because I had the exactly the same message when I declared the array as VARCHAR. I changed it to NVARCHAR and it works without problem.

      In another procedure in another system a similar logic worked with VARCHAR.

      I am happy that it works but I am not sure why it was complaining in the first place.

      Did u try declaring the array as NVARCHAR too?

  • Posted on Dec 16, 2014 at 01:24 PM

    Ok, how is the table type for your var out defined?

    The following worked w/o issues on SP9:

    CREATE COLUMN TABLE "ZSCP_LOCPROD" (
      "MANDT" NVARCHAR(3) DEFAULT '000' NOT NULL ,
      "LOCATIONNUMBER" NVARCHAR(20) DEFAULT '' NOT NULL ,
      "PRODUCTNUMBER" NVARCHAR(40) DEFAULT '' NOT NULL ,
      "PPLANNER" NVARCHAR(3) DEFAULT '' NOT NULL ,
      "SPLANNER" NVARCHAR(3) DEFAULT '' NOT NULL ,
      "USE" NVARCHAR(50),
      PRIMARY KEY ("MANDT",
      "LOCATIONNUMBER",
      "PRODUCTNUMBER"))
    
    
    insert into zscp_locprod values ('001', 1234, 99999, 'te', 'te', 'te');
    insert into zscp_locprod values ('002', 14, 93999, 'te', 'te', 'te');
    insert into zscp_locprod values ('002', 234, 9499, 'te', 'te', 'te');
    
    create type unntest_tt as table (location nvarchar(30), product nvarchar(40));
    
    create PROCEDURE unnest_test (
      out var_out unntest_tt
      )
      LANGUAGE SQLSCRIPT
      SQL SECURITY INVOKER
      READS SQL DATA AS
    BEGIN
      DECLARE loc NVARCHAR(20) ARRAY;
      DECLARE prod NVARCHAR(40) ARRAY;
    
      tab = select "LOCATIONNUMBER" as LOCATION, "PRODUCTNUMBER" as PRODUCT from ZSCP_LOCPROD;
      prod := ARRAY_AGG(:tab.PRODUCT);
      loc := ARRAY_AGG(:tab.LOCATION);
      var_out = unnest(:loc, :prod) as("LOCATION", "PRODUCT");
    
    END;
    
    
    
    
    call unnest_test(?)
    

    - Lars

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.