Invalid column name in sql expression

Larisa Gasparian

Hello,

I created macro and get 'Invalid column name' with my syntax. What do I do wrong. Thank you: @Store17 gives me an error. I have similar expression above in the same script, but it works fine.   

@STORE16 = SQL('SELECT T0.[U_AT_PROD_CAT] FROM OITM T0 WHERE T0.[ItemCode] = $[$38.1.0]');

@STORE17 = SQL('SELECT CASE WHEN @STORE16 = 'W-CAN-100' OR @STORE16 = 'W-CAN-105' OR @STORE16 = 'W-CAN-120' OR @STORE16 = 'W-CAN-4.5' OR @STORE16 = 'W-CAN-81' THEN 1 ELSE 0 END');   

Comments

2 comments

Date Votes
  • Comment author
    Dave Gutman

    Hi Larisa,

    It looks like the code that assigned @STORE17  is having problems with the use of quotes.  

    I copied your code to my system and replaced the UDF in OITM with another field.  When I ran it from from the Sales Order screen errors happened assigning @STORE17. Like your case it had no problem with the assignment of @STORE16.

    Here's the changed code that I got to run: 

    @STORE16 = SQL('SELECT T0.[ItemName] FROM OITM T0 WHERE T0.[ItemCode] = $[$38.1.0]'); @STORE17 = SQL(SELECT CASE WHEN '@STORE16' = 'W-CAN-100' OR '@STORE16' = 'W-CAN-105' OR '@STORE16' = 'W-CAN-120' OR '@STORE16' = 'W-CAN-4.5' OR '@STORE16' = 'W-CAN-81' THEN 1 ELSE 0 END);

    Here's what I did to make it work:

    • Removed the quotes within the SQL() function - this can cause problems when quotes are used within the function's command itself.
    • Put quotes around the @STORExx references within the command.

    Hope this helps.

    0
  • Comment author
    Larisa Gasparian

    It worked! Thank you very much for quick respond.

    0

Please sign in to leave a comment.