Skip to main content

Can we add SQL combined with text into a Message type universal Function?



  • Michelle Campbell

    I'm having problems getting this to work.  What I need is a combination of text and query results to be returned in the message.

    When I try the following (the sql here is just a simple example - I know it doesn't make sense to write it this way if I actually just wanted the Item Code returned):

    SQL:SELECT 'Test ' + "OITM"."ItemCode" from "OITM" WHERE "OITM"."ItemCode"=$[$5.0.0]

    I get this error:

    General error;339 invalid number: not a valid number string 'Test '

    So I try using COALESCE:

    SQL:SELECT COALESCE('Test ',"OITM"."ItemCode") from "OITM" WHERE "OITM"."ItemCode"=$[$5.0.0]

    But that just returns "Test" in the message box. 

    Just to exhaust all options, just displaying the Item Code does work, using this: 

    SQL:SELECT "OITM"."ItemCode" from "OITM" WHERE "OITM"."ItemCode"=$[$5.0.0]

    And this:

    SQL:SELECT COALESCE('Test ','Test') from "OITM" WHERE "OITM"."ItemCode"=$[$5.0.0]

    Only returns "Test".  

    So using + or using coalesce both are having issues.  Any thoughts?



  • Michelle Campbell

    Nevermind.  The following worked:

    SQL:SELECT 'Test '||"OITM"."ItemCode" from "OITM" WHERE "OITM"."ItemCode"=$[$5.0.0]


Please sign in to leave a comment.