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

You can embed the text you wish to display in the message inside your SQL statement. For example:

SQL: SELECT 'This customer has ' + CAST(count(*) AS NVARCHAR(254)) + ' outstanding invoice/s.' FROM OINV T0 WHERE T0.[CardCode] =$[$4.0.0] and  T0.[DocDueDate] < getdate()

By using a UF Macro with MessageBox, you can get multi-line SQL results by using the <NEWLINE> command.

​

Was this article helpful?

2 out of 2 found this helpful

Comments

2 comments

Date Votes
  • Comment author
    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?

     

     

    0
  • Comment author
    Michelle Campbell

    Nevermind.  The following worked:

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

    0

Please sign in to leave a comment.