Re: SQL ACS Run SQL Scripts Declare Variable of User Defined Type -- MIDRANGE-L

Hi Jack,

AFAIK you can't create a "global" variable with an array type. As the message states, an array type is only valid

-- As an argument of as SQL scalar function or an SQL or JAVA procedure

-- For an SQL variable declared in an SQL procedure or SQL scalar function

-- In a CAST specification in an SQL procedure or SQL scalar function

It's not valid as the type of a global SQL variable.

Maybe because global SQL variables are implemented as *SRVPGM and AFAIK array types can also not the used as parameters for functions/procedures that are externally implemented in C or RPGLE. Strangely they seem to be allowed for SQL implementations, which are "transpiled" from SQL/PL to C.

HTH and kind regards,
Daniel

Am 07.03.2025 um 01:19 schrieb Jack Woehr via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx>:

We're trying in Run SQL Statements something like:

CREATE TYPE INTARRAY AS INTEGER ARRAY[100];
CREATE VARIABLE MYLIST INTARRAY;

And variations thereof.
This fails:

SQL State: 428H2 Vendor Code: -20441 Message: [SQ20441] Array type not valid where specified. Cause . . . . . : An array type was used but is not allowed in the specified context. Array types can only be used: -- As an argument of as SQL scalar function or an SQL or JAVA procedure. -- For an SQL variable declared in an SQL procedure or SQL scalar function. -- In a CAST specification in an SQL procedure or SQL scalar function.

What should we be doing instead, please?

Jack Woehr
Independent Consulting Programmer
IBM Champion 2021-2025
[cid:db563e9f-2636-4abb-8389-035a79dec5f2]
303‑847-8442
[cid:4c668542-4e0b-461d-9ce3-113843cb76c7]<mailto:Jack.Woehr@xxxxxxxxxxx>
Jack.Woehr@xxxxxxxxxxx<mailto:Jack.Woehr@xxxxxxxxxxx>

[cid:3449d177-75e8-42d8-967c-422533a965cf]<https://www.procern.com/>
www.procern.com<https://us.content.exclaimer.net/?url=http%3A%2F%2Fwww.procern.com%2F&tenantid=v6Geib_MEe62YGBFvdVfmg&templateid=83f6ff9d7fc0ee11b6606045bdd55f9a&signature=OPsICYt_LnuO-3w8Yl7Oyt-sEI6fWkmTwgwlaWPdM5MFwI5OfNjEXLegCKuPYUrzprLzazjLXf1ddEA6nYz6TuMgyHpzPFCWLJ1drx1WWuCdm9I9dtE4etDBR2eXyrqqpY13M1A6sITS86afx58qiU0CNA8rM37codaQWuX4q-i5aL10UWsW1XL4dJKZrfpnk7o1-71hkl8hG0HDCZAvE5Fvw05ed0tAz-zIDpwdfWbXypTF3MFlRKunifANiYz2SPR8mOGnWusTCv8oCiF9JqxwLaebQ893YmsJIgWwuU86EOWTie1kN9fO_XpHzmevhbPI9bGthB-RyQ38ufGtJA&v=1&imprintMessageId=6de8375c-28af-408a-b165-f460ac526396>
[cid:c55f2499-19b6-4e4a-bf2a-bf0986e6da89]<https://www.linkedin.com/in/jackwoehr/>
Stay Connected!<https://www.linkedin.com/in/jackwoehr/>
NON-DISCLOSURE NOTICE: This communication including any and all attachments is for the intended recipient(s) only and may contain confidential and privileged information. If you are not the intended recipient of this communication, any disclosure, copying further distribution or use of this communication is prohibited. If you received this communication in error, please contact the sender and delete/destroy all copies of this communication immediately.

Jack Woehr
Independent Consulting Programmer

303-847-8442

jack.woehr@xxxxxxxxxxx

www.procern.com

Stay Connected!

Not Just MSP, We Also MSSP.

NON-DISCLOSURE NOTICE: This communication including any and all attachments is for the intended recipient(s) only and may contain confidential and privileged information. If you are not the intended recipient of this communication, any disclosure, copying further distribution or use of this communication is prohibited. If you received this communication in error, please contact the sender and delete/destroy all copies of this communication immediately.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.