FlexibleSearch, how to do LEFT JOIN on Enumeration Values Table

[Former Member]

I'm trying to get the enumeration values related products but with a left join to fetch data even when a particular enum value does not have a value, without success, my query look like:

 SELECT  enumValue.p_name
 
 FROM
 {   
     MyType! as myType
  } 
  LEFT join
         Enumerationvalueslp enumLineValue ON {myType.enumField} = enumValue.itempk
 WHERE
          {myType.enumField} = enumValue.itempk

But I get "no composed type with code myType.enumField Any suggest would be appreciated.

[Former Member]

I've already solved doing the following:

 SELECT evl.p_name
 FROM {
      MyType! as myType
  }
 LEFT JOIN enumerationvalueslp evl
 ON P_MYENUMFIELD = evl.itemPK 

[geffchang]

Enumerationvalueslp is a Database Table, not a Type. When you create a FlexibleSearch query, you should use Type names, not DB Table names (or column names). You also cannot mix Type names and DB Table names.

This is an example with a Product type and an ArticleApprovalStatus enumeration, to get the list of Products, joined with Approval Status:

 SELECT * FROM {Product AS p LEFT JOIN ArticleApprovalStatus AS s ON {p.approvalStatus}={s.pk}}

If you wanted to get the list Products that are approved, you could add a WHERE clause:

 SELECT * FROM {Product AS p LEFT JOIN ArticleApprovalStatus AS s ON {p.approvalStatus}={s.pk}} WHERE {s.code} = 'approved'

[Former Member]

Hi Geff,

I want to get the description of a enum filed defined in my custom type 'MyType' thats why I consult that table, Is there a type associated with that table? or another way to get the desciption of a enum via flexible search? Thanks in advance!