Someone on the list posted the following query a while back and I found it really useful. Can't remember the lister's name (kudo). You might have to tweak it a little bit for your own use... Make sure to replace the schemaid in the query
select vuiname, fieldName, SUBSTR(SUBSTR(SUBSTR(propshort,INSTR(propshort, '\20\4\')+6), INSTR(SUBSTR(propshort,INSTR(propshort, '\20\4\')+6),'\')+1),0, SUBSTR(SUBSTR(propshort,INSTR(propshort, '\20\4\')+6),0, INSTR(SUBSTR(propshort,INSTR(propshort, '\20\4\')+6),'\')-1)) "FIELDLABEL", a.fieldid, decode(datatype,2,'Integer',3,'RealNum',4,'Char', 5,'Diary',6,'RadioBtn',7,'Date/Time',10,'Decimal', 11,'Attachment',12,'Currency',13,'Date',14,'Time',31,'Trim',33,'Table',3 4,'Column',35,'Page', 36,'Page Holder',37,'AttachPool',42,'View',43,'Flashboards') from field a, field_dispprop b, vui c where a.schemaId = 255 and a.schemaid = b.schemaid and b.schemaid = c.schemaid and a.fieldid=b.fieldid and c.vuiid = b.vuiid and datatype != 32 and proplong is null UNION select vuiname, fieldName, SUBSTR(SUBSTR(SUBSTR(dbms_lob.substr(proplong,4000,1), INSTR(dbms_lob.substr(proplong,4000,1), '\20\4\')+6),INSTR(SUBSTR(dbms_lob.substr(proplong,4000,1), INSTR(dbms_lob.substr(proplong,4000,1), '\20\4\')+6),'\')+1),0, SUBSTR(SUBSTR(dbms_lob.substr(proplong,4000,1), INSTR(dbms_lob.substr(proplong,4000,1), '\20\4\')+6),0, INSTR(SUBSTR(dbms_lob.substr(proplong,4000,1), INSTR(dbms_lob.substr(proplong,4000,1), '\20\4\')+6),'\')-1)) "FIELDLABEL", a.fieldid, decode(datatype,2,'Integer',3,'RealNum',4,'Char', 5,'Diary', 6,'RadioBtn',7,'Date/Time',10,'Decimal',11,'Attachment',12,'Currency',13 ,'Date',14,'Time', 31,'Trim',33,'Table',34,'Column',35,'Page',36,'Page Holder',37,'AttachPool',42,'View', 43,'Flashboards') from field a, field_dispprop b, vui c where a.schemaId = 255 and a.schemaid = b.schemaid and b.schemaid = c.schemaid and a.fieldid=b.fieldid and c.vuiid = b.vuiid and datatype != 32 and propshort is null UNION select vuiname, fieldName,SUBSTR(SUBSTR(SUBSTR(propshort,INSTR(propshort, '\110\4\')+7), INSTR(SUBSTR(propshort,INSTR(propshort, '\110\4\')+7),'\')+1),0, SUBSTR(SUBSTR(propshort,INSTR(propshort, '\110\4\')+7),0, INSTR(SUBSTR(propshort,INSTR(propshort, '\110\4\')+7),'\')-1)) "Field Label", a.fieldid, 'Button' from field a, field_dispprop b, vui c where a.schemaId = 255 and a.schemaid = b.schemaid and b.schemaid = c.schemaid and a.fieldid=b.fieldid and c.vuiid = b.vuiid and datatype=32 and proplong is null UNION select vuiname, fieldName, SUBSTR(SUBSTR(SUBSTR(dbms_lob.substr(proplong,4000,1), INSTR(dbms_lob.substr(proplong,4000,1), '\110\4\')+7), INSTR(SUBSTR(dbms_lob.substr(proplong,4000,1), INSTR(dbms_lob.substr(proplong,4000,1), '\110\4\')+7),'\')+1),0, SUBSTR(SUBSTR(dbms_lob.substr(proplong,4000,1), INSTR(dbms_lob.substr(proplong,4000,1), '\110\4\')+7),0, INSTR(SUBSTR(dbms_lob.substr(proplong,4000,1), INSTR(dbms_lob.substr(proplong,4000,1), '\110\4\')+7),'\')-1)) "Field Label", a.fieldid, 'Button' from field a, field_dispprop b, vui c where a.schemaId = 255 and a.schemaid = b.schemaid and b.schemaid = c.schemaid and a.fieldid=b.fieldid and c.vuiid = b.vuiid and datatype=32 and propshort is null order by 1 Hope it help. Jason Lin Programmer Analyst UCSF - OAAIS - Remedy 1855 Folsom Street San Francisco, CA 94143 -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Kirk Sent: Wednesday, December 20, 2006 5:35 AM To: arslist@ARSLIST.ORG Subject: Oracle SQL SELECT statement to find field LABEL Hello ARLIST, I'm customizing the AR Message Catalog schema to allow select users to update the Help details for schema fields. I'm at the stage where I've added a SQL statement to present the user with the english db field name and a char field to update the help text. However since sometimes the db field name is not the same as the field LABEL that the user would normally see I need to find that in the db tables. I've looked through the AR DatabaseRefGuide doc but can't determine the proper table/column where this info is stored. Does anyone know what the SELECT statement would be or what db table this would reside in? Thx in advance, Kirk ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are"
