Search for a Field with Translates

I was looking for an “Internal/External” field.  I wanted a field already built that had two translate values: “E” and “I”.  This SQL did the trick:

SELECT A.FIELDNAME,
A.XLATLONGNAME,
B.XLATLONGNAME,
A.XLATSHORTNAME,
B.XLATSHORTNAME
FROM   PSXLATITEM A,
PSXLATITEM B
WHERE  A.FIELDNAME = B.FIELDNAME
AND A.FIELDVALUE = 'E'
AND B.FIELDVALUE = 'I'
AND Upper(A.XLATLONGNAME) LIKE 'EXT%'
AND NOT EXISTS (SELECT 'X'
FROM   PSXLATITEM C
WHERE  A.FIELDNAME = C.FIELDNAME
AND C.FIELDVALUE NOT IN ( 'E', 'I' )) 

I found the INTERNAL_EXTERNAL field, which works just perfect for me.

Leave a Comment

Your email address will not be published. Required fields are marked *