Forum Discussion

team_aras_meupe_com's avatar
team_aras_meupe_com
Creator III
2 years ago
Solved

C# - applySQL. "Select sum()......"

Hello colleagues, I have a problem. I'm trying to do a SQL, select sum(.....). The code doesn't give me an error, but I don't know how to access the value of the variable later. If you see my code,...
  • angela's avatar
    angela
    2 years ago

    Hi,

    glad to see you haven´t given up yet! :)

    Normally SQL is executed form server Method. Does your version really work from a client Method?

    One issue that I notice are these two lines:

    ResultadoSQL = innovator.applySQL(SentenciaSql);

    // tomamos el valor de la suma
    var LT_Par = ResultadoSQL.getProperty("Resultado","0");

    You execute the SQL, but than immediately want to read a column of the result. But you haven´t got the result item itself yet. applySQL can return multiple items!

    Try something like this

    Item sqlItem = ResultadoSQL.getItemByIndex(0).getProperty("Resultado","0");

    To my knowledge there is no inbuilt option to make "SUM". You can replicate "Count", but for other stuff you would need a custom Method, like described here:  link 

    But for your use case (updating multiple very specific relationships), using SQL is ok. 

team_aras_meupe_com's avatar
team_aras_meupe_com
Creator III
2 years ago

Ok, I have passed the code to Javascript and used the debugger.

The problem is that I called it "Resultado", but when I execute the SQL it returns as "resultado".

If someone can answer questions 1 and 2 for me, I would be grateful!!

1.- There is something similar in AML to "select sum"

2.- How can I pass the value of "SQLResult" to a variable to be able to display it on the screen.

  • angela's avatar
    angela
    Catalyst II
    2 years ago

    Hi,

    glad to see you haven´t given up yet! :)

    Normally SQL is executed form server Method. Does your version really work from a client Method?

    One issue that I notice are these two lines:

    ResultadoSQL = innovator.applySQL(SentenciaSql);

    // tomamos el valor de la suma
    var LT_Par = ResultadoSQL.getProperty("Resultado","0");

    You execute the SQL, but than immediately want to read a column of the result. But you haven´t got the result item itself yet. applySQL can return multiple items!

    Try something like this

    Item sqlItem = ResultadoSQL.getItemByIndex(0).getProperty("Resultado","0");

    To my knowledge there is no inbuilt option to make "SUM". You can replicate "Count", but for other stuff you would need a custom Method, like described here:  link 

    But for your use case (updating multiple very specific relationships), using SQL is ok. 

    • team_aras_meupe_com's avatar
      team_aras_meupe_com
      Creator III
      2 years ago

      ha ha ha. Never surrender!!!. Only I already suffer it in silence!! hehehe. What I've been doing lately, I more or less control it!! Although I have some "polstergeists" that I can't figure out why.

      But yes, sometimes it makes me want to run away!! :).

      The problem was that although I had written in the SQL to return the value as "Result", in the getProperty it had to say "result", all in lowercase, I don't know why. In SQL Server, it returns the field name as I define it: "Result".

      Thanks for the link!!, I searched with a magnifying glass, but I didn't find anything. or broken links.

      I have some questions, I'll post them in the forum...... :)

      I have in hand to modify the "PE_GetMultilBom" method. I made a first attempt, but it doesn't show me the value on the screen. I'll put a post in the forum asking for help, hehe.

      Thank you so much!!!!