2 Replies Latest reply on Apr 20, 2013 8:15 AM by PeterGort

    SQL Query to get structure subset

    PeterGort

      I'm trying to build an SQL Query to give me the stored data fields only from a table.

       

      If I execute

       

      SELECT * FROM Filemaker_Fields

       

      I get a nice long list of every field in every table occurence. The columns are

       

      TableName, FieldName, FieldType FieldId, FieldClass, FieldReps, ModCount

       

      Subsetting to FieldClass = 'Normal' leaves out all the calcs & summaries, so far so good.

       

      Then I want to leave out the globals, and whether or not a field is global is in the "FieldType" column. Types are varchar, decimal, date, global varchar, global decimal, global date etc.

       

      (yes I know the first two variables in this let statement are redundant)

       

      Let([

      tabchar = Char ( 9 );

      returnchar = Char ( 13 );

      ltn = Get ( LayoutTableName );

      sqlQuery = "SELECT FieldName FROM Filemaker_fields ff

      WHERE ( ff.Tablename = ? ) AND ( ff.FieldClass = 'Normal' ) AND ( ff.FieldType NOT LIKE 'global*' )

      ORDER BY ff.FieldName"

      ];

      ExecuteSQL ( sqlQuery ; tabchar ; returnchar ; ltn )

      )

       

      But the NOT LIKE clause is ignored and it returns the global field names in the results. Now if I change it to explicit LEFT(FieldType,6 ) <> 'global' it works

       

      Let([

      tabchar = Char ( 9 );

      returnchar = Char ( 13 );

      ltn = Get ( LayoutTableName );

      sqlQuery = "SELECT FieldName

      FROM Filemaker_fields ff

      WHERE ( ff.Tablename = ? ) AND ( ff.FieldClass = 'Normal' ) AND ( LEFT ( ff.FieldType , 6 ) <> 'global' ) )

      ORDER BY ff.FieldName ASC"

      ];

      ExecuteSQL ( sqlQuery ; tabchar ; returnchar ; ltn )

      )

       

      But I don't understand why the NOT LIKE syntax didn't work. Can anybody tell me what I did wrong?