1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Question Sql Join _file And _field Search For Fieldname

Discussion in 'SQL-92' started by AlexTheDroog, Jan 16, 2017.

  1. AlexTheDroog

    AlexTheDroog New Member

    I'm using Epicor 9 with OpenEdge 10.1B drivers and SQuirrel SQL. I was attempting to locate what table contains a field called FailedBin. I wrote the following query but its seems to have some issues.

    SELECT F."_Field-Name" AS "FieldName", T."_File-Name" AS "TableName"

    FROM PUB."_Field" AS F, PUB."_File" AS T

    WHERE F."_Field-Name" = 'FailedBin' AND T."_TBL-Type" = 'T' AND F."_File-recid" = RECID(T)

    ORDER BY T."_File-Name",F."_Field-Name";

    It doesn't like

    T."_TBL-Type" = 'T'


    T."_TBL-Type" = 'T' AND F."_File-recid" = RECID(T)

    I got the idea from Progress KB - 4GL/ABL: What is the relationship between the _File and the _Field tables? but it’s not taking into account the query being written from an ODBC SQL environment
  3. Rob Fitzpatrick

    Rob Fitzpatrick ProgressTalk.com Sponsor

  4. Stefan

    Stefan Active Member

    In 11.6.3 there are also views that already combine this stuff. I am not sure when exactly they were introduced, but I can just:

    Code (progress):
    1. select tbl from "sysprogress"."syscolumns" where col = 'acc_nr'
  5. Rob Fitzpatrick

    Rob Fitzpatrick ProgressTalk.com Sponsor

    Cool, even better! :)
  6. AlexTheDroog

    AlexTheDroog New Member

    Thanks guys! Saved me a lot of time looking for a field the didn't exist!

Share This Page