Get product number if

[Charity]

Here is what I need.  I have global field called gColorized.  How would I write this, please?

Select ProductNumber from Products where ProductDescription contains value in Globals::gColorized or where Refactor contains value in gColorized

Everything I've tried, I only get ?

Thank you for helping.  SQL does not like me yet.

[eos]

Well, I am posting to have fun, which puzzle solving is …

So relax a little, and try

ExecuteSQL ( "
  SELECT ProductNumber
  FROM Products
  WHERE ProductDescription LIKE '%" & Globals::gColorized & "%' OR Refactor LIKE '%" & Globals::gColorized & "%'
  " ; "" ; ""
)

which thanks to LIKE and the % placeholder works like PatternCount(), i.e. matches on substrings; depending on your need, you may want to work with Lower() or Upper() (same function names in both FM and SQL) to find case-insensitive matches. 

Note that you cannot use FM syntax in/as SQL references; i.e. a fully qualified field name in FM is TableName::fieldName, but in SQL its TableName.fieldname.
But as long as the table reference is unambiguous (which in simple queries it usually is), you don't have to use that form anyway.

Since you have Advanced, you could also put the Data Viewer to good use and try

Let (
  sql =
  "SELECT ProductNumber 
  FROM Products
  WHERE ProductDescription LIKE '%" & Globals::gColorized & "%' OR Refactor = '%" & Globals::gColorized & "%'
  " ;
  Execute ( sql ; "" ; "" ) // sql ; switch commented expressions, i.e. use sql // Execute ( sql ; "" ; "" ), to quickly see and examine the literal sql string (and spot errors)
)

to aid in debugging.

 

Edited May 6, 2015 by eos

[Charity]

Okay I have mellowed.

Thank you for helping me Eos.  This is exactly what I needed.  and the tip on data viewer with SQL helps also.

 

Edited May 6, 2015 by Charity

[eos]

This is exactly what I needed.​

Good to hear…