AnsweredAssumed Answered

Portal showing/not showing depending on WHERE clause in SQL?

Question asked by FrankvanderMost on Jul 15, 2015
Latest reply on Jul 15, 2015 by wimdecorte

I have a calculation field cal_simple_search_result that generates and executes an SQL statement. The result is a list of record keys from a table called 'innovations' separated by ¶. This calculation field is the key for a relationship to the same table.

 

When the calculation generates a query like this:

SELECT pk_record FROM innovations WHERE UPPER(firm_name) LIKE '%TOLERANS AB%'

 

The result is this 5875

 

and the portal showing the relationship shows this record as it should.

 

When the calculation generates a query like this

SELECT pk_record FROM innovations WHERE pk_record= NUMVAL('5875')

 

The result is also 5875 but the portal shows nothing. My question is: why does the portal show the result in the first case but not in the second?

 

I checked/tried the following without result:

- the result-type of the calculation field is 'number' as is the type of pk_record

- pk_record and the calculation field have 'unicode' language setting

- I read somewhere that the ExecuteSQL function is a bit picky when it comes to field types, I added NUMVAL since the field that contains the search value is a text field.

- I tried queries of the second type that resulted in more than one value 'pk_record>NUMVAL('5875')'

Outcomes