1 2 Previous Next 19 Replies Latest reply on Jul 15, 2015 2:02 PM by wimdecorte

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

    FrankvanderMost

      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')'

        1 2 Previous Next