Re: Need Help

[sujatha shanmugasundaram]

** Thanks Ankur..its working Fine

On 10/16/06, Gulati, Ankur <[EMAIL PROTECTED]> wrote:

**

Hi Sujatha

 

I went in to archives and found out this query to get the field lables from SQL Query. Just replace the schemaid in all the three places in there. It works perfect.

 

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',34,'Column',35,'Page',
36,'Page Holder',37,'AttachPool',42,'View',43,'Flashboards')
from field a, field_dispprop b, vui c
where a.schemaId = 2875 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 = 2875 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 = 2875 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 = 2875 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

 

 

 

 

Ankur

 

 

-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto: arslist@ARSLIST.ORG]On Behalf Of sujatha shanmugasundaram
Sent: Monday, October 16, 2006 4:37 PM
To: arslist@ARSLIST.ORG
Subject: Re: Need Help

**

Hi Sam,

 

The field name specifies the database name,but i need the label of the field,not the database name/

 

On 10/16/06, Sam Rx <[EMAIL PROTECTED] > wrote:

**

select Schemaid from arschema where name = '<Your Form Name>'

select fieldname, fieldid from field where schemaid = <schemaid retrieved from the first query>

HTH

On 10/16/06, Sujatha Shanmugasundaram < [EMAIL PROTECTED]> wrote:

Hi All,


    Do anyone know how to retrieve the Field Label for a particular Form
through SQL Query??

_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org

__20060125_______________________This posting was submitted with HTML in it___

__20060125_______________________This posting was submitted with HTML in it___

__20060125_______________________This posting was submitted with HTML in it___

__20060125_______________________This posting was submitted with HTML in it___