How To Get 1 Data from List in 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 :-)
.png)
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
Hy @AshokKum
Thank you for your help, but it doesn't work, I just try like this and it give an error
$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
Thank you for your help, but it doesn't work, I just try like this and it give an error
The argument for $LISTGET has to be a list, so it would have to be $LISTGET(LIST(a)).
thanks for your help, it works :)
I wonder if MIN or MAX would also work, assuming the values are all the same...
MAX(a) as No_Urut
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