How To Get 1 Data from List in SQL

Question

Question

Steven Henry Su... · Sep 27, 2023

#SQL #TrakCare

Hello My Friend,

I have a problem when I Query to get the data from SQL

Here's my code

SELECT OBS_ParRef,

OBSAT_ParRef,

OBS_ParRef->MRADM_ADM_DR->PAADM_RowID,

OBS_ParRef->MRADM_ADM_DR->PAADM_PAPMI_DR->PAPMI_RowId,

List(a) as No_Urut,

List(b) as No_KK,

List(c) as Hub_RT,

List(d) as Tempat_Mati

FROM

(SELECT OBS_ParRef,

OBSAT_ParRef,

OBS_ParRef->MRADM_ADM_DR->PAADM_RowID,

OBS_ParRef->MRADM_ADM_DR->PAADM_PAPMI_DR->PAPMI_RowId,

CASE WHEN OBS_Item_DR = '157' THEN OBS_Value ELSE NULL END as a,

CASE WHEN OBSAT_ItemAttribute_DR = '157||2' THEN OBSAT_ValueDisplay ELSE NULL END as b,

CASE WHEN OBSAT_ItemAttribute_DR = '157||4' THEN OBSAT_ValueDisplay ELSE NULL END as c,

CASE WHEN OBSAT_ItemAttribute_DR = '157||6' THEN OBSAT_ValueDisplay ELSE NULL END as d

FROM SQLUser.MR_ObservationsAttribute

Join SQLUser.MR_Observations on OBS_RowId = OBSAT_ParRef

Join SQLUser.MRC_ObservationItemAttribute on ATTR_RowId = OBSAT_ItemAttribute_DR

) temp

where OBS_ParRef ='75'

group by OBS_ParRef--, OBSAT_ParRef

and I Want the column No Urut should become 1 value = 67 not 67,67,67

I know my query is using List(a) as No_Urut -> I've no idea how to get this data without List,

I've ever using Distinct but It doen't works, so I've to try get another way

maybe someone have any suggestion for me ? Thank You :-)

Product version: IRIS 2023.2

Discussion (9)4

Log in or sign up to continue

Pietro Di Leo · Oct 3, 2023

I don't know if this can help, but in objectscript the command to extract some elements from a list is the following:

SELECT * FROM <tableName> WHERE FOR SOME %ELEMENT (<listName>) (%VALUE = '<value>')

For example, if you have a persistent class like this:

Class User.SQLtable Extends %Persistent
{
    Property propertyList As list Of %String(MAXLEN = 100) [ Required ];
    Index ListIdx On List(ELEMENTS);
}

You can extract information using:
SELECT * FROM User.SQLtable WHERE FOR SOME %ELEMENT (propertyList) (%VALUE = '67')

In this way you can retrieve the row that contains the specific data value of interest with dynamic or static SQL and subsequently extract the list as a property of the SQL result object

1 0

score 0 · Answer 1 · 2023-09-27T06:13:10-04:00

Use string functions to get the values in your query. If your OBS_Value value is already a list then you can take from query $listget(a) as No_Urut instead List(a) as No_Urut. Or if it's a string $PIECE((a,",",1) as No_Urut

score 0 · Answer 2 · 2023-09-28T21:56:33-04:00

Steven Henry Su... · Sep 28, 2023

Hy @AshokKum

Thank you for your help, but it doesn't work, I just try like this and it give an error

0 0

score 0 · Answer 3 · 2023-09-28T21:58:35-04:00

Steven Henry Su... · Sep 28, 2023

0 0

score 1 · Answer 4 · 2023-09-27T22:50:47-04:00

$LISTGET is easiest. $LISTGET(a) will get the first value, $LISTGET(a,number) gets the value at that number.

Select $LISTGET(a,1) as ColumnOne, $LISTGET(a,2) as ColumnTwo from SQLTable

score 0 · Answer 5 · 2023-09-28T21:57:25-04:00

Hy @Arshiya Syeda
Thank you for your help, but it doesn't work, I just try like this and it give an error

score 2 · Answer 6 · 2023-09-29T08:16:15-04:00

David Hockenbroch · Sep 29, 2023

The argument for $LISTGET has to be a list, so it would have to be $LISTGET(LIST(a)).

2 0

score 0 · Answer 7 · 2023-10-02T22:29:39-04:00

Steven Henry Su... · Oct 2, 2023

thanks for your help, it works :)

0 0

score 0 · Answer 8 · 2023-10-03T05:29:02-04:00

Sean Connelly · Oct 3, 2023

I wonder if MIN or MAX would also work, assuming the values are all the same...

MAX(a) as No_Urut

0 0