What version of Agile is this for???
 For 9.0.x and beyond, the list values are actually stored in LISTENTRY. The LISTNAME table is where the list parent information is stored. PROPERTYTABLE is no longer used for lists (that was for Agile 8.5 and earlier), it now is used mostly just for storing properties of NODETABLE objects.
 For the most part, you can simply get the list text value by linking the attribute directly to LISTENTRY using ENTRYID (see the query below). It is a bit more complicated for Multi-List attributes, as you must parse out the individual IDs and then get the text value for each one. In addition, note that if the multi-list attribute is overflowed (too many ID values to fit in the length), you need to go to the MSATT table to get the ID values. It can also get VERY complicated when using out-of-the-box lists as they sometimes have non-unique ID values, so you *must* use the list parent ID.

 A query to show the values for LIST01 would be as follows :
select i.item_number, l.entryvalue from item i, listentry l, page_two p
 where i.id = p.id and i.class=p.class and p.list01 = l.entryid(+) and l.parentid=(select selection from propertytable where parentid=2020 and propertyid=10);

 Given that the list assigned to the attribute changes *very* little, you are better off looking up the list parent ID rather then trying to embed that lookup into the query. Also note that it is not always assigned directly to the attribute, but maybe to the base attribute it inherits from (although that usually only applies to attributes that have a list assigned out-of-the-box, like PRODUCT LINES). Given that the list attribute may not have a value, use an outer join between the attribute and ENTRYID so that you can see that some values are null.

 Also note I have not touched on cascade lists. That is an entirely separate issue to try and get values, as you must start with the ID in the attribute, and then climb the list hierarchy to get the entire value. Dynamic lists (which use the IDs of objects in the database) are not very hard, so long as you can determine what the object type is that you need to select from.

I was able to solve it by joining nodetable and propertytable only. It seems that the list id is stored in SELECTION column of the propertytable having propertyid as 15.

Here is the final query. I hope it helps.

SELECT DISTINCT T.*,p.value as attribute_name,substr(p.value, 1, instr(p.value, ‘.’) -1) as attribute_tab_name FROM
(
WITH CLASSID AS (
SELECT n1.ID, n1.description classname,–NSCLS.ID AS SCLSID, NSCLS.INHERIT,
n3.id as subclassid, n3.description as subclassname,n5.id as baseclassid,n5.description as baseclassname
from nodetable n5

join nodetable n6 on n5.id=n6.inherit
join
NODETABLE n1 on n6.parentid=n1.id
join nodetable n2 on n2.parentid = n1.id
left outer join nodetable n3 on n3.parentid = n2.id

WHERE 1 = 1
AND n1.parentid = 5002
AND UPPER (n5.DESCRIPTION) = UPPER (‘Items’)
and n2.objtype=14 and n5.parentid = 900
and n6.inherit=901
— AND UPPER (DESCRIPTION) = UPPER (‘Items’)
)
, METADATA3 as (
SELECT –distinct na2.*
— nt.id,
plistid.selection,
classid.id ClassId,
baseclassname,ClassName,subclassname,subclassid,
na.id AttrId,
nt.description TableName,
LOWER(NVL(NA3.NAME,NA.NAME)) Name,
NVL(NA3.DESCRIPTION,na.description) DisplayName,
P.VALUE DTNum,
P.PROPERTYID DTPID,
TI.ORDERING,nt.inherit,apv.value vs,PV.VALUE vis
FROM
tableinfo ti join CLASSID on ti.classid=subclassid

INNER JOIN nodetable nt
ON ti.tabid = nt.id
INNER JOIN nodetable na
ON ti.att = na.id
INNER JOIN NODETABLE NA2
ON na2.PARENTid = ti.classid
LEFT OUTER JOIN NODETABLE NA3
ON NA3.PARENTID = NA2.ID
AND NA3.INHERIT = TI.ATT
LEFT OUTER JOIN propertytable p
ON P.PARENTID = na.id
AND p.propertyid = 2
LEFT OUTER JOIN propertytable pV
ON Pv.PARENTID = na3.id
AND pv.propertyid = 9
left outer join propertytable apv
on na.id = apv.parentid and apv.propertyid=9
left outer join propertytable plistid
on na3.id = plistid.PARENTID
and plistid.propertyid = 15
WHERE 1 = 1– and classid = 28949
AND nt.objtype = 9 –and nt.inherit = 1 –UPPER (nt.description) = UPPER (‘Page Two’)
AND na2.objtype = 4 –UPPER (na2.description) = UPPER (‘Attributes’)
), HEADINGS3 AS (
SELECT DISTINCT M1.CLASSID, M1.CLASSNAME, M1.TABLENAME, 0 AS ORDERING, -1 AS PREV_ORDER, 0 AS SEQ
FROM METADATA3 M1

UNION ALL

SELECT M1.CLASSID, M1.CLASSNAME, M1.DISPLAYNAME, ORDERING, -1 AS PREV_ORDER, ROW_NUMBER() OVER (PARTITION BY CLASSID ORDER BY ORDERING ASC) AS SEQ
FROM METADATA3 M1
WHERE M1.DTNum IS NULL
AND M1.ORDERING < 999
), GROUPHEADINGS3 AS (
SELECT M1.CLASSID, M1.CLASSNAME, M1.TABLENAME GROUPHEADING3, M1.ORDERING STARTORDER, NVL(M2.ORDERING,999) ENDORDER
FROM HEADINGS3 M1
LEFT OUTER JOIN HEADINGS3 M2
ON M1.CLASSID = M2.CLASSID
AND (M1.SEQ +1) = M2.SEQ
),pagethree as (
SELECT
M.SELECTION,
M.ClassId,baseclassname,
M.ClassName,subclassname,subclassid,
AttrId,
TableName,
NVL(GP.GROUPHEADING3,TableName) AS GroupName,
Name,
DisplayName,
CASE
WHEN DTNUM = 0 AND DTPID = 2
THEN
‘nvarchar’
WHEN DTNUM = 1 AND DTPID = 2
THEN
‘lookup’
WHEN DTNUM = 2 AND DTPID = 2
THEN
‘lookup’
WHEN DTNUM = 3 AND DTPID = 2
THEN
‘number’
WHEN DTNUM = 4 AND DTPID = 2
THEN
‘datetime’
WHEN DTNUM = 5 AND DTPID = 2
THEN
‘money’
ELSE
”
END AS Type,
NULL AS DeafultValue,
ORDERING AS DisplayOrder,
0 AS IsLink,
CASE WHEN ((vs=1 and vis is null) or vis=1) THEN 1 ELSE 0 END AS Visible,vs,vis,
CASE
WHEN DTNUM = 0 AND DTPID = 2
THEN
‘Text/Multitext’
WHEN DTNUM = 1 AND DTPID = 2
THEN
‘List’
WHEN DTNUM = 2 AND DTPID = 2
THEN
‘MultiList’
WHEN DTNUM = 3 AND DTPID = 2
THEN
‘Numeric’
WHEN DTNUM = 4 AND DTPID = 2
THEN
‘Date’
WHEN DTNUM = 5 AND DTPID = 2
THEN
‘Money/Cost’
WHEN DTNUM IS NULL– AND P.PROPERTYID = 2
THEN
‘Heading’
ELSE
”
END AS SourceType, 2 as inherit

FROM METADATA3 M
LEFT OUTER JOIN GROUPHEADINGS3 GP
ON M.CLASSID = GP.CLASSID
AND M.ORDERING > GP.STARTORDER
AND M.ORDERING < GP.ENDORDER
WHERE M.DTNUM IS NOT NULL
ORDER BY CLASSID,ORDERING)

, METADATA1 AS (select distinct * from (
SELECT –distinct na2.*
plistid.selection,
classid.id ClassId,baseclassname,
ClassName,subclassname,subclassid,
na.id AttrId,
nt.description TableName,
LOWER(NVL(NA3.NAME,NA.NAME)) Name,
NVL(NA3.DESCRIPTION,na.description) DisplayName,
P.VALUE DTNum,
P.PROPERTYID DTPID,
TI.ORDERING,nt.inherit,apv.value vs,PV.VALUE vis
FROM
classid
INNER JOIN tableinfo ti
ON ti.classid = classid.id
INNER JOIN nodetable nt
ON ti.tabid = nt.id
INNER JOIN nodetable na
ON ti.att = na.id
INNER JOIN NODETABLE NA2
ON na2.PARENTid = ti.classid
LEFT OUTER JOIN NODETABLE NA3
ON NA3.PARENTID = NA2.ID
AND NA3.INHERIT = TI.ATT
LEFT OUTER JOIN propertytable p
ON P.PARENTID = na.id
AND p.propertyid = 2
LEFT OUTER JOIN propertytable pV
ON Pv.PARENTID = na3.id
AND pv.propertyid = 9
left outer join propertytable apv
on na.id = apv.parentid and apv.propertyid=9
left outer join propertytable plistid
on na3.id = plistid.PARENTID
and plistid.propertyid = 15
WHERE 1 = 1
AND nt.objtype = 9 — –UPPER (nt.description) = UPPER (‘Page Two’)
AND na2.objtype = 4 and (nt.inherit = 1) and langid =0 –UPPER (na2.description) = UPPER (‘Attributes’)
)), metaData0 as (
select distinct * from (
SELECT –distinct na2.*
plistid.selection,
classid.id ClassId,baseclassname,
ClassName,subclassname,subclassid,
na.id AttrId,
nt.description TableName,
LOWER(NVL(NA3.NAME,NA.NAME)) Name,
NVL(NA3.DESCRIPTION,na.description) DisplayName,
P.VALUE DTNum,
P.PROPERTYID DTPID,
TI.ORDERING,nt.inherit,apv.value vs,PV.VALUE vis
FROM
classid
INNER JOIN tableinfo ti
ON ti.classid = classid.id
INNER JOIN nodetable nt
ON ti.tabid = nt.id
INNER JOIN nodetable na
ON ti.att = na.id
INNER JOIN NODETABLE NA2
ON na2.PARENTid = ti.classid
LEFT OUTER JOIN NODETABLE NA3
ON NA3.PARENTID = NA2.ID
AND NA3.INHERIT = TI.ATT
LEFT OUTER JOIN propertytable p
ON P.PARENTID = na.id
AND p.propertyid = 2
LEFT OUTER JOIN propertytable pV
ON Pv.PARENTID = na3.id
AND pv.propertyid = 9
left outer join propertytable apv
on na.id = apv.parentid and apv.propertyid=9
left outer join propertytable plistid
on na3.id = plistid.PARENTID
and plistid.propertyid = 15
WHERE 1 = 1
AND nt.objtype = 9 — –UPPER (nt.description) = UPPER (‘Page Two’)
AND na2.objtype = 4 and (nt.inherit = 0) and langid =0 –UPPER (na2.description) = UPPER (‘Attributes’)
–and nt.description = ‘Title Block’
)
),HEADINGS1 AS (
SELECT DISTINCT M1.CLASSID, M1.CLASSNAME, M1.TABLENAME, 0 AS ORDERING, -1 AS PREV_ORDER, 0 AS SEQ
FROM METADATA1 M1
where inherit = 1

UNION ALL

SELECT M1.CLASSID, M1.CLASSNAME, M1.DISPLAYNAME, ORDERING, -1 AS PREV_ORDER, ROW_NUMBER() OVER (PARTITION BY CLASSID ORDER BY ORDERING ASC) AS SEQ
FROM METADATA1 M1
WHERE inherit = 1 and M1.DTNum IS NULL
AND M1.ORDERING < 999
), GROUPHEADINGS1 AS (
SELECT M1.CLASSID, M1.CLASSNAME, M1.TABLENAME GROUPHEADING, M1.ORDERING STARTORDER, NVL(M2.ORDERING,999) ENDORDER
FROM HEADINGS1 M1
LEFT OUTER JOIN HEADINGS1 M2
ON M1.CLASSID = M2.CLASSID
AND (M1.SEQ +1) = M2.SEQ
), HEADINGS0 AS (
SELECT DISTINCT M1.CLASSID, M1.CLASSNAME, M1.TABLENAME, 0 AS ORDERING, -1 AS PREV_ORDER, 0 AS SEQ
FROM METADATA0 M1
where inherit = 0

UNION ALL

SELECT M1.CLASSID, M1.CLASSNAME, M1.DISPLAYNAME, ORDERING, -1 AS PREV_ORDER, ROW_NUMBER() OVER (PARTITION BY CLASSID ORDER BY ORDERING ASC) AS SEQ
FROM METADATA0 M1
WHERE inherit = 0 and M1.DTNum IS NULL
AND M1.ORDERING < 999
), GROUPHEADINGS0 AS (
SELECT M1.CLASSID, M1.CLASSNAME, M1.TABLENAME GROUPHEADING, M1.ORDERING STARTORDER, NVL(M2.ORDERING,999) ENDORDER
FROM HEADINGS0 M1
LEFT OUTER JOIN HEADINGS0 M2
ON M1.CLASSID = M2.CLASSID
AND (M1.SEQ +1) = M2.SEQ
), pagetwo as (select * from (
SELECT
M.selection,
M.ClassId,baseclassname,
M.ClassName,subclassname,subclassid,
AttrId,
TableName,
NVL(GP.GROUPHEADING,TableName) AS GroupName,
Name,
DisplayName,
CASE
WHEN DTNUM = 0 AND DTPID = 2
THEN
‘nvarchar’
WHEN DTNUM = 1 AND DTPID = 2
THEN
‘lookup’
WHEN DTNUM = 2 AND DTPID = 2
THEN
‘lookup’
WHEN DTNUM = 3 AND DTPID = 2
THEN
‘number’
WHEN DTNUM = 4 AND DTPID = 2
THEN
‘datetime’
WHEN DTNUM = 5 AND DTPID = 2
THEN
‘money’
ELSE
”
END AS Type,
NULL AS DeafultValue,
ORDERING AS DisplayOrder,
0 AS IsLink,
CASE WHEN ((vs=1 and vis is null) or vis=1) THEN 1 ELSE 0 END AS Visible,vs,vis,
CASE
WHEN DTNUM = 0 AND DTPID = 2
THEN
‘Text/Multitext’
WHEN DTNUM = 1 AND DTPID = 2
THEN
‘List’
WHEN DTNUM = 2 AND DTPID = 2
THEN
‘MultiList’
WHEN DTNUM = 3 AND DTPID = 2
THEN
‘Numeric’
WHEN DTNUM = 4 AND DTPID = 2
THEN
‘Date’
WHEN DTNUM = 5 AND DTPID = 2
THEN
‘Money/Cost’
WHEN DTNUM IS NULL– AND P.PROPERTYID = 2
THEN
‘Heading’
ELSE
”
END AS SourceType,inherit

FROM METADATA1 M
LEFT OUTER JOIN GROUPHEADINGS1 GP
ON M.CLASSID = GP.CLASSID
AND M.ORDERING > GP.STARTORDER
AND M.ORDERING < GP.ENDORDER
WHERE M.DTNUM IS NOT NULL
ORDER BY CLASSID,ORDERING)
)
, titleblock as (select * from (
SELECT
M.selection,
M.ClassId,baseclassname,
M.ClassName,subclassname,subclassid,
AttrId,
TableName,
NVL(GP.GROUPHEADING,TableName) AS GroupName,
Name,
DisplayName,
CASE
WHEN DTNUM = 0 AND DTPID = 2
THEN
‘nvarchar’
WHEN DTNUM = 1 AND DTPID = 2
THEN
‘lookup’
WHEN DTNUM = 2 AND DTPID = 2
THEN
‘lookup’
WHEN DTNUM = 3 AND DTPID = 2
THEN
‘number’
WHEN DTNUM = 4 AND DTPID = 2
THEN
‘datetime’
WHEN DTNUM = 5 AND DTPID = 2
THEN
‘money’
ELSE
”
END AS Type,
NULL AS DeafultValue,
ORDERING AS DisplayOrder,
0 AS IsLink,
CASE WHEN ((vs=1 and vis is null)or vis=1) THEN 1 ELSE 0 END AS Visible,vs,vis,
CASE
WHEN DTNUM = 0 AND DTPID = 2
THEN
‘Text/Multitext’
WHEN DTNUM = 1 AND DTPID = 2
THEN
‘List’
WHEN DTNUM = 2 AND DTPID = 2
THEN
‘MultiList’
WHEN DTNUM = 3 AND DTPID = 2
THEN
‘Numeric’
WHEN DTNUM = 4 AND DTPID = 2
THEN
‘Date’
WHEN DTNUM = 5 AND DTPID = 2
THEN
‘Money/Cost’
WHEN DTNUM IS NULL– AND P.PROPERTYID = 2
THEN
‘Heading’
ELSE
”
END AS SourceType,inherit

FROM METADATA0 M
LEFT OUTER JOIN GROUPHEADINGS0 GP
ON M.CLASSID = GP.CLASSID
AND M.ORDERING > GP.STARTORDER
AND M.ORDERING < GP.ENDORDER
WHERE M.DTNUM IS NOT NULL
ORDER BY CLASSID,ORDERING
))

(
select selection,
ClassId,baseclassname,
ClassName,subclassid,subclassname,
AttrId,
TableName,
GroupName,
Name,
DisplayName,

Type,
‘ ‘DeafultValue,
ROW_NUMBER() OVER (PARTITION BY classid,subclassid ORDER BY inherit,Displayorder,attrid) as DisplayOrder,
IsLink,SourceType,visible,vs,vis from (
select *
from pagetwo
where visible=1
union
select *
from titleblock
where visible=1
union
select *
from pagethree
where visible=1
))
)T
inner join propertytable p
on t.ATTRID = P.PARENTID
and P.PROPERTYID = 10
WHERE BASECLASSNAME = ‘Items’
AND CLASSNAME = ‘Documents’
AND SUBCLASSNAME = ‘Bill of Materials’
AND NAME = ‘list08’
order by subclassname

My apologies, I mis-read your question.
Yes, the list parent ID is stored in PROPERTYTABLE.SELECTION where PROPERTYID=15. In general, the list ID is associated directly to the attribute. But for some cover/title page attributes (like PRODUCT_LINES), it is associated to the underlying base attribute because it has a list assigned out-of-the-box. I normally run a script to put all list parent IDs into a table with other information (like is it a cascade  or dynamic list) so it is easily found. but if you need something to find the list parent ID for a single attribute, you can use the following function :

create or replace function ListParent(attrID IN number, inherID in number) RETURN number IS

csrID number;
result varchar2(100);
listID number;

cursor csr_UTyp is select value from propertytable where parentid = csrID and propertyid = 2;
cursor csr_ALst is select selection from propertytable where parentid = csrID and propertyid = 15;

BEGIN
— Find out what the data type of the attribute is
csrID := attrID;
OPEN csr_UTyp;
FETCH csr_UTyp INTO result;
IF csr_UTyp%notfound THEN
CLOSE csr_UTyp;
csrID := inherID;
OPEN csr_UTyp;
FETCH csr_UTyp INTO result;
END IF;
CLOSE csr_UTyp;
result := trim(result);

— Make sure that we only process when LIST or MULTILIST or WEIGHT or MONEY
IF result = ‘1’ OR result = ‘2’ or result = ‘5’ or result = ‘6’ THEN

— Now get the list parent ID from PROPERTYTABLE
listID := 0;
csrID := attrID;
OPEN csr_ALst;
FETCH csr_ALst INTO listID;
IF csr_ALst%notfound THEN
CLOSE csr_ALst;
csrID := inherID;
OPEN csr_ALst;
FETCH csr_ALst INTO listID;
END IF;
CLOSE csr_ALst;

ELSE
listID := 0;
END IF;

RETURN listID;

END;
/
show err

 If it is not a list-type of attribute, the function returns a zero. If the base attribute doesn’t have a list assigned, it looks to see if the INHERIT attribute has one assigned.