List,
Based mostly on the info below I created a SQL View that returns field
labels which is formatted for SQL Server 2000/2005.
The script inside the attached txt file creates one view
(uvw_ARSFieldLabels) and two supporting functions
(udf_DecodeFieldDataType, udf_DecodeFieldLabel)
To query,
SELECT * FROM uvw_ARSFieldLabels WHERE SchemaName = 'HPD:HelpDesk'
HTH
Stephen
-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Lin, Jason
Sent: Wednesday, December 20, 2006 6:49 PM
To: arslist@ARSLIST.ORG
Subject: Re: Oracle SQL SELECT statement to find field LABEL
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"
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[udf_DecodeFieldDataType]
(
@DataType INT
)
RETURNS VARCHAR(8000)
AS
BEGIN
RETURN CASE @DataType
WHEN 2 THEN 'Integer'
WHEN 3 THEN 'RealNum'
WHEN 4 THEN 'Char'
WHEN 5 THEN 'Diary'
WHEN 6 THEN 'RadioBtn'
WHEN 7 THEN 'Date/Time'
WHEN 10 THEN 'Decimal'
WHEN 11 THEN 'Attachment'
WHEN 12 THEN 'Currency'
WHEN 13 THEN 'Date'
WHEN 14 THEN 'Time'
WHEN 31 THEN 'Trim'
WHEN 32 THEN 'Button'
WHEN 33 THEN 'Table'
WHEN 34 THEN 'Column'
WHEN 35 THEN 'Page'
WHEN 36 THEN 'Page Holder'
WHEN 37 THEN 'AttachPool'
WHEN 42 THEN 'View'
WHEN 43 THEN 'Flashboards'
ELSE CONVERT(VARCHAR, @DataType)
END
END
GO
CREATE FUNCTION [dbo].[udf_DecodeFieldLabel]
(
@PropShort VARCHAR(8000),
@PropLong TEXT,
@DataType INT
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Pos INT,
@Len INT,
@Pattern VARCHAR(9)
IF @DataType = 32
SET @Pattern = '\110\4\'
ELSE
SET @Pattern = '\20\4\'
IF @PropShort IS NULL
SET @PropShort = SUBSTRING(@PropLong, PATINDEX('%' + @Pattern +
'%', @PropLong) + LEN(@Pattern), 8000)
ELSE
SET @PropShort = SUBSTRING(@PropShort, CHARINDEX(@Pattern,
@PropShort) + LEN(@Pattern), 8000)
SET @Pos = CHARINDEX('\', @PropShort)
SET @Len = CONVERT(INT, LEFT(@PropShort, @Pos - 1))
RETURN SUBSTRING(@PropShort, @Pos + 1, @Len)
END
GO
CREATE VIEW [dbo].[uvw_ARSFieldLabels] AS
SELECT [Name] schemaName, d.schemaId, vuiName, b.vuiid, fieldName, a.fieldId,
dbo.udf_DecodeFieldLabel(PropShort, PropLong, DataType)
fieldLabel,
dbo.udf_DecodeFieldDataType(DataType) dataType --, propLong,
propShort
FROM field a, field_dispprop b, vui c, arschema d
WHERE a.schemaid = b.schemaid
AND b.schemaid = c.schemaid
AND a.fieldid=b.fieldid
AND c.vuiid = b.vuiid
AND a.schemaid = d.schemaid
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
