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, I have assigned a name to the value received, but when I execute the code, it always gives me "0", which is the value I have entered if there is nothing.

[embed:dc8ab71f-3b98-42d9-b0f6-e21e02a0f8e2:cf66ea03-c953-4381-876e-bad88390f9e3:type=csharp&text=Innovator%20innovator%20%3D%20this.getInnovator%28%29%3B%0A%0A%2F%2F%20var%20innovator%20%3D%20new%20Innovator%28%29%3B%0A%0Astring%20ID%20%3D%20this.getID%28%29%3B%0A%0A%2F%2F%20esto%20sumaria%20todas%20las%20etapas%0Avar%20SentenciaSql%20%3D%20%22%20select%20sum%28MT_R_RUT_ETA.mt_rp_rut_eta_ltime%29%20as%20%27Resultado%27%22%2B%0A%22%20from%20innovator.MT_RUTA%20%22%2B%0A%22%20join%20innovator.MT_R_RUT_ETA%20on%20MT_RUTA.id%20%3D%20MT_R_RUT_ETA.SOURCE_ID%20%22%2B%0A%22%20where%20MT_RUTA.id%20%3D%20%27%22%2BID%2B%22%27%22%3B%0A%0Avar%20ResultadoSQL%20%3D%20innovator.applySQL%28SentenciaSql%29%3B%0A%0A%2F%2F%20tomamos%20el%20valor%20de%20la%20suma%0Avar%20LT_Tot%20%3D%20ResultadoSQL.getProperty%28%22Resultado%22%2C%220%22%29%3B%0A%0A%2F%2F%20esto%20sumaria%20todas%20las%20etapas%20MENOS%20APROVISIONAMIENTO%0ASentenciaSql%20%3D%20%22%20select%20sum%28MT_R_RUT_ETA.mt_rp_rut_eta_ltime%29%20as%20%27Resultado%27%22%2B%0A%22%20from%20innovator.MT_RUTA%20%22%2B%0A%22%20join%20innovator.MT_R_RUT_ETA%20on%20MT_RUTA.id%20%3D%20MT_R_RUT_ETA.SOURCE_ID%20%22%2B%0A%22%20where%20MT_RUTA.id%20%3D%20%27%22%2BID%2B%22%27%20and%20MT_R_RUT_ETA.KEYED_NAME%20not%20like%20%27APROV%25%27%22%3B%0A%0AResultadoSQL%20%3D%20innovator.applySQL%28SentenciaSql%29%3B%0A%0A%2F%2F%20tomamos%20el%20valor%20de%20la%20suma%0Avar%20LT_Par%20%3D%20ResultadoSQL.getProperty%28%22Resultado%22%2C%220%22%29%3B%0A%0A%2F%2FString%20s%20%3D%20LT_Tot.ToString%28%29%20%2B%20%22%20-%20%22%20%2B%20LT_Par.ToString%28%29%3B%0AString%20s%20%3D%20ResultadoSQL.ToString%28%29%3B%0Areturn%20innovator.newError%28s%29%3B]

I do it using SQL because it is faster than creating an AML and going through it to add the values.

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.

In c# I usually use:
return innovator.newError(s);

With this the execution stops and it shows me "s".

Too many thanks!!!!!!

  • 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. 

3 Replies

  • 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!!!!