Written by

Steven Henry Suhendra

Question Steven Henry Suhendra · Sep 27, 2023

How To Get 1 Data from List in SQL

#TrakCare #SQL

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

Add reply

Comments

Ashok Kumar T · Sep 27, 2023

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

0 0

Steven Henry Suhendra Sep 28, 2023 to Ashok Kumar T

Hy @AshokKum

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

0 0

Steven Henry Suhendra Sep 28, 2023 to Steven Henry Suhendra

0 0

Arshiya Syeda · Sep 27, 2023

$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

0 0

Steven Henry Suhendra Sep 28, 2023 to Arshiya Syeda

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

0 0

David Hockenbroch Sep 29, 2023 to Steven Henry Suhendra

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

0 0

Steven Henry Suhendra Oct 2, 2023 to David Hockenbroch

thanks for your help, it works :)

0 0

Sean Connelly Oct 3, 2023 to Steven Henry Suhendra

I wonder if MIN or MAX would also work, assuming the values are all the same...
MAX(a) as No_Urut

0 0

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

0 0