Searching with USEEDIT

Because many of the record properties are hidden in the Bit Map field USEEDIT, it is hard to search for properties.  If you are on Oracle, here’s some SQL that might help…

This SQL lists all of the fields with their properties broken out of that field:

[sourcecode language=”SQL”]SELECT FIELDNAME,
       USEEDIT,
       Bitand(USEEDIT, 1)         KEY,
       Bitand(USEEDIT, 2)         DUP_KEY,
       Bitand(USEEDIT, 4)         SYS_MAINT,
       Bitand(USEEDIT, 8)         AUDIT_ADD,
       Bitand(USEEDIT, 16)        ALT_SEARCH_KEY,
       Bitand(USEEDIT, 32)        LIST_BOX_ITEM,
       Bitand(USEEDIT, 64)        ASCENDING_KEY,
       Bitand(USEEDIT, 128)       AUDIT_CHANGE,
       Bitand(USEEDIT, 256)       REQUIRED,
       Bitand(USEEDIT, 512)       XLAT,
       Bitand(USEEDIT, 1024)      AUDIT_DEL,
       Bitand(USEEDIT, 2048)      SEARCH_KEY,
       Bitand(USEEDIT, 4096)      EDIT_REASONABLE_DATE,
       Bitand(USEEDIT, 8192)      EDIT_YES_NO,
       Bitand(USEEDIT, 16384)     EDIT_PROMPT_TABLE,
       Bitand(USEEDIT, 32768)     AUTO_UPDATE,
       Bitand(USEEDIT, 65536)     BIT65536,
       Bitand(USEEDIT, 131072)    BIT131072,
       Bitand(USEEDIT, 262144)    FROM_SEARCH,
       Bitand(USEEDIT, 524288)    TO_SEARCH,
       Bitand(USEEDIT, 1048576)   EDIT_BINARY,
       Bitand(USEEDIT, 2097152)   DISABLE_ADV_SEARCH,
       Bitand(USEEDIT, 4194304)   REGULAR_FIELD,
       Bitand(USEEDIT, 8388608)   DEFAULT_SEARCH_FIELD,
       Bitand(USEEDIT, 16777216)  BIT16777216,
       Bitand(USEEDIT, 33554432)  SEARCH_EDIT_KEYS,
       Bitand(USEEDIT, 67108864)  BIT67108864,
       Bitand(USEEDIT, 134217728) BIT134217728,
       Bitand(USEEDIT, 268435456) BIT268435456
FROM   PSRECFIELDALL
WHERE  RECNAME = ‘JOB’; [/sourcecode]

Next, I had not seen the 1/0 Table Edit option before.  So, I wanted to find an example in the system.

[sourcecode language=”SQL”]SELECT RECNAME,
       FIELDNAME,
       USEEDIT,
       Bitand(USEEDIT, 1048576) EDIT_BINARY
FROM   PSRECFIELDALL
WHERE  Bitand(USEEDIT, 1048576) > 0  [/sourcecode]

I found one: AE_SYNCGEN_AET record, ONLYSETINVALIDID field

Example of 1/0 Edit Table

So, I was searching for a field that has been used as a Yes/No field that is something like “Screening”.  I tried this SQL:

[sourcecode language=”SQL”]SELECT RECNAME,
       FIELDNAME,
       USEEDIT,
       Bitand(USEEDIT, 8192) EDIT_YES_NO
FROM   PSRECFIELDALL
WHERE  Bitand(USEEDIT, 8192) > 0
       AND FIELDNAME LIKE ‘%SCR%’ [/sourcecode]

That gave me a lot of results, so I thought I would try to compare against the label:

[sourcecode language=”SQL”]SELECT A.RECNAME,
       A.FIELDNAME,
       A.USEEDIT,
       Bitand(A.USEEDIT, 8192) EDIT_YES_NO,
       B.*
FROM   PSRECFIELDALL A,
       PSDBFLDLABL B
WHERE  Bitand(A.USEEDIT, 8192) > 0
       AND A.FIELDNAME = B.FIELDNAME
       AND Upper(B.LONGNAME) LIKE ‘%SCREEN%’ [/sourcecode]

So, I decided to use: HRS_JO_SCR_USE

Resources

Leave a Comment

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