Macro SQL - multiple @StoreX

Answered

Jay Cheah

• May 20, 2021 03:17

@STORE1 = SQL('SELECT CardType FROM OCRD WHERE CardCode = $[$5.0.0]');

Is it possible to do this:

@STORE1, @STORE2 = SQL('SELECT CardType, CardName FROM OCRD WHERE CardCode = $[$5.0.0]'); ?

this saves potentially multiple calls to the database.

 

Vote

0

• Official comment

  

  Thomas

  • May 21, 2021 09:18

  Hi Jay,

  I'm not sure if I misunderstood something here, but you can use:

  MultiSetFromSql(@STORE1|@STORE2|SELECT CardName, Balance FROM OCRD WHERE CardCode ='C20000')
  MessageBox(@STORE1 @STORE2);

  

  Best regards,

  Thomas
• 

  SSP Automation

  • May 20, 2021 03:20

  Thank you for your request. It has been scheduled for review by the development team that will get back to you should there be questions.
  (Please note that as we are user-driven we can't guarantee that your request will be met unless it gets many votes and/or fit the product vision)
  Read more about the Feature Request process here
  [Internal Id: 57290]
  
• 

  Dave Gutman

  • May 20, 2021 12:09

  This works on my test system:

  @STORE1 = SQL('SELECT CardType FROM OCRD WHERE CardCode = $[$5.0.0]');

  @STORE2 = @STORE1; 
• 

  Rasmus Jensen

  • May 20, 2021 12:56

  I don't think that is what Jay is after... The request is to set two different @Store values with two different values using only a single SQL Sentence.

  It is unfortunately not something we can do right now :-(
• 

  Jay Cheah

  • May 20, 2021 23:56

  Rasmus Jensen yes that is exactly what i want - too bad to hear that is a limitation. 

  It would a BIG performance improvement for sure - esp when we have multiple validations on screen. 
• 

  Rasmus Jensen

  • May 21, 2021 03:31

  If the macro is called from a B1 Validation you might be able to leverage the SQL condition keyword system that sort of does the same as you request: see more here - https://help.boyum-it.com/B1UP/theconditionsystem.html
• 

  Jay Cheah

  • May 23, 2021 21:48

  Thomas i tried :

  MultiSetFromSql(@store1|@store3|SELECT T0."U_UDF1",T0."U_UDF2" FROM OITM T0 WHERE T0."ItemCode" = $[$38.1.0])

  and this triggered some exception, i don't know exactly what is wrong if any from your statement or it's just a limitation of the multiset. I'm on HANA btw, hence the multiple double quotes..

  if i split the statements into 2 separate SQL calls, with the same syntax, then it works fine

Please sign in to leave a comment.