1. Using this synon Y file, we can find the help text of a field.

SELECT * FROM MDLLIB/YTXTDTA00L WHERE @@OBJ in (SELECT @@FLD FROM MDLLIB/YFLDDTA1JL WHERE upper(fld)='Reject Code' )       

YTXTDTA00L contains Help text of all fields.

YFLDDTA1JL contains all synon Fields.   

2. Find all fields with word reject code in Synon in Synon Y Files

SELECT * FROM MDLLIB/YFLDDTA1JL WHERE upper(fld) like '%COMPLEX%'      

 SELECT FLD, INTLEN FROM MDLLIB/YFLDDTA1JL WHERE  (upper(fld) like '%REJ%CD%' or upper(fld) like '%REJ%CDE%'   or upper(fld) like '%REJ%COD%' ) 

3. Find all fields of length 3,4,12 in Synon

SELECT * FROM MDLLIB/YFLDDTA1JL WHERE INTLEN in (2,3, 12) and 
(upper(fld) like '%REJ%CD%' or upper(fld) like '%REJ%CDE%' or 
upper(fld) like '%REJ%COD%' ) 

4. Find reference fields of particular fields in Synon in Synon Y files

SELECT * FROM MDLLIB/YFLDDTA1JL WHERE @@DOM in (SELECT @@FLD 
FROM MDLLIB/YFLDDTA1JL WHERE @@FLD in (SELECT DISTINCT @@DOM 
FROM MDLLIB/YFLDDTA1JL WHERE upper(fld) like '%REJ%CD%' or upper(fld) like '%REJ%CDE%' or 
upper(fld) like '%REJ%COD%' )) 

5. Get Field Mode used in a Screen Program in Synon

SELECT a.IMPNME, b.IOOVR, c.fld FROM MDLLIB/YMDLOBJ00L a,        
MDLLIB/YSCRENT02L b, MDLLIB/YFLDDTA1JL c WHERE @@OBJ = @@MSG  
and b.@@FLD =c.@@FLD and upper(fld)='REJECT CDE'          

SQL Output:

Implement. Name  NPT               Field       
                 usage (override)  name        
  XXXCRL010              O          Reject Cde  
  CXXCRL011              O          Reject Cde  
  XXCRL005               O          Reject Cde  
  XXEIZ002               I          Reject Cde  
  XXCRL012               O          Reject Cde  
********  End of data  ********      

6. SQL to get FIeld Mode, Screen name, Field name in Synon

SELECT DISTINCT                                                     
T02.IOOVR, T01.IMPNME, T01.OBJNME, T01.OBJATR, T01.FUNTYP,T02.@@FLD, T03.FLD                                        FROM                                                                
MDLLIB/YMDLOBJ00L T01,                                           
MDLLIB/YSCRENT02L T02,                                           
MDLLIB/YFLDDTA1JL T03,                                           
ACMSCTL.OBJSRELOBJ  T04,                                                                                                                           
(SELECT * FROM MDLLIB/YFLDDTA1JL WHERE                                                               
 (UPPER(FLD) LIKE '%DIA%' AND UPPER(FLD) LIKE '%CD%' and            
  UPPER(FLD) LIKE '%QL%'   )       OR                               
 (UPPER(FLD) LIKE '%DIAGNOSIS CDE QL%'  )                                                                                                
OR UPPER(FLD) LIKE '%DIAGNOSIS CODE Q%') as T05   
WHERE                                                           
T01.@@OBJ  = T02.@@MSG                 AND                    
T02.@@FLD  = T03.@@FLD                 AND                    
T01.IMPNME = T04.OBJNAME               AND                    
T03.@@FLD  = t05.@@FLD                 AND                    
T01.IMPNME        <> ' '               AND                    
T01.IMPNME        NOT LIKE '%BK'       AND                    
T02.IOOVR         IN       ('I','O')   AND                    
T01.FUNTYP        <>       'PRTFIL'                 
ORDER BY T02.IOOVR, T01.IMPNME   

7. Get Field Conditions:
SELECT * FROM MDLLIB/YCNDDTA1JL WHERE @@OWN in                   
(SELECT @@DOM FROM MDLLIB/YFLDDTA1JL WHERE FLD ='Flag IFSInp')   
 
8. Retrieving Condition Value of a Field:
SELECT * FROM MDLLIB/YCNDDTA1JL inner  
join MDLLIB/YFLDDTA1JL on @@OWN = @@DOM where                  
FLD ='TCD Claim Status'                                           
and CND='Accepted'   

Select * from mdlib/y2vllsp

9. Get Aldon Objects using SQL:

SELECT ENH, substr(OBJNAME,1,12), OBJFMLY FROM ACMSCTL/ENHOBJENH 
WHERE ENH ='BC2152112' ORDER BY OBJNAME                         

select  ENH, substr(OBJNAME,1,12), OBJFMLY, (select FUNTYP from 
MDLLIB/YMDLOBJ00L where IMPNME = OBJNAME)
FROM ACMSCTL/ENHOBJENH 
WHERE IMPNME in (select OBJNAME from ACMSCTL/ENHOBJENH           
WHERE ENH ='BC2152112')       

with temp as ( select ENH, substr(OBJNAME,1,12) obj, OBJFMLY ,     
FUNTYP from ACMSCTL/ENHOBJENH left outer join MDLLIB/YMDLOBJ00L 
on trim(IMPNME)=trim(OBJNAME) where ENH ='BC2152112' ORDER BY      
OBJNAME) select * from temp where OBJFMLY <> '*BNDDIR' and (FUNTYP  
='EXCEXTPGM' or FUNTYP='EXCUSRSRC'  or FUNTYP is NULL)             
order by OBJ