Select element from Progress array column

[tale103108]

Can anyone provide an example on how to filter on a array column in SQL?

for example:

select "employee"
from "employee"
where "age[2]" > 40;

"age" would be an array column in the table "employee" and I wish to look at the third element.
Of course this SQL does not work but you get the idea. How does one doe this in Progress? I see that the array elements are separated by a ";" but I can not parse them.

Please advise....with an example SQL.


Cheers!

 

[Rob Fitzpatrick]

Try moving your closing quote in your where clause, i.e. where "age"[2] > 40;.

The following works for me in the sports database:

SQLExplorer>select "rep-name","month-quota"[1] from PUB."salesrep" where "month-quota"[1] > 1600;
Rep-Name                                                     Month-Quota[1]
------------------------------------------------------------ --------------
Pitt , Dirk K.                                                         1800
Donna Swindall                                                         3800
Harry Munvig                                                           3800
Jan Loopsnel                                                           2200
Kari Iso-Kauppinen                                                     1800
Robert Roller                                                          4200
Smith , Spike Louise                                                   3000

 

[MMARCHI]

Hi Rob , sorry if i disturb you, but i need a similar help..about How to refer to a single element of a extent.

I post my query ...
I have to run a SQL Query through a Linked server in a SQL SERVER engine...
I have a table called magsin that contains an extent called magsin with 50 references.
I need to retrieve only the second...
WHAT CAN I DO ???
I tried to test your example and also i studie PRO_ELEMENT function..but NOTHING.

cAN YOU HELP ME ???

Regards in advance...



SELECT
1 AS CodAzienda,
codpro
AS CodProdotto,
codcema
AS CodMagazzino,
quantita_pro_element2
AS Quantita

FROM
openquery
(
COMETA_BASE02P ,

'select magsin.codpro,
magsin.codcema, PRO_ELEMENT("magsin.quantita",1,1) AS Quantita from pub.magsin
where magsin.codpro <> '''' '


==========================================================


The error i receive is this...
==============================================================================
OLE DB provider "MSDASQL" for linked server "COMETA_BASE02P" returned message "[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Column "magsin.quantita" cannot be found or is not specified for query. (13865)".

Msg 7321, Level 16, State 2, Line 1

An error occurred while preparing the query "select magsin.codpro,

magsin.codcema, PRO_ELEMENT("magsin.quantita",1,1) AS Quantita from pub.magsin

where magsin.codpro <> '' " for execution against OLE DB provider "MSDASQL" for linked server "COMETA_BASE02P".
)