Error in Flexible search Query

[Former Member]

Hi All,

I am trying to fetch order details using below query. I tried below query SELECT createdTS FROM { Order AS o } Which is working fine

But when I try below query, I am getting below error . SELECT {o.code} AS orderNubmer, {op.processdefinitionname} AS orderProcessName, {ps.code} AS processStatus, {o.createdTS} AS Date FROM { Order AS o JOIN OrderProcess AS op ON {op.order} = {o.PK} JOIN ProcessState AS ps ON {ps.pk}={op.state} } WHERE {ps.code} IN ('WAITING','ERROR','FAILED')

Exception message: cannot search unknown field 'TableField(name='createdTS',langPK='null',type=Order)' within type Order unless you disable checking , infoMap=TypeInfoMap for type = 8796094169170 code = Order superType = 8796093382738 itemTable = orders UPTable = ordersup LTableName = orderslp PropsTable = orderprops core fields = owner = [owner,OwnerPkString,class

Is their anything wrong?

Thank you. Rasika

[Former Member · Accepted solution]

Hi Rasika,

We can't put createdTS inside { }, so better to use {date} attribute in place of {createdTS}.

 SELECT {o.code} AS orderNubmer, {op.processdefinitionname} AS orderProcessName, {ps.code} AS processStatus, {o.date} AS Date FROM { Order AS o JOIN OrderProcess AS op ON {op.order} = {o.PK} JOIN ProcessState AS ps ON {ps.pk}={op.state} } WHERE {ps.code} IN ('WAITING','ERROR','FAILED')

[former_member493648]

Hi Rasika

The error is simple - 'createdTS' is not recognized as a field. Please try the below query.

 SELECT {o.code} AS orderNubmer, {op.processdefinitionname} AS orderProcessName, {ps.code} AS processStatus, {o.creationtime} AS creationtime FROM 
  { 
      Order AS o JOIN OrderProcess AS op ON {op.order} = {o.PK} JOIN ProcessState AS ps ON {ps.pk}={op.state} 
  }
 
  WHERE {ps.code} IN ('WAITING','ERROR','FAILED')

General advice, please refrain to use the SQL reserved keywords as aliases, like you did here - {o.createdTS} AS Date

Hope this helps.