• State Verified Answer
  • Replies 3 replies
  • Subscribers 12 subscribers
  • Views 2513 views
  • Users 0 members are here
Options
Related

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

Meupe ARAS
Offline

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.

Innovator innovator = this.getInnovator();

// var innovator = new Innovator();

string ID = this.getID();

// esto sumaria todas las etapas
var SentenciaSql = " select sum(MT_R_RUT_ETA.mt_rp_rut_eta_ltime) as 'Resultado'"+
" from innovator.MT_RUTA "+
" join innovator.MT_R_RUT_ETA on MT_RUTA.id = MT_R_RUT_ETA.SOURCE_ID "+
" where MT_RUTA.id = '"+ID+"'";

var ResultadoSQL = innovator.applySQL(SentenciaSql);

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

// esto sumaria todas las etapas MENOS APROVISIONAMIENTO
SentenciaSql = " select sum(MT_R_RUT_ETA.mt_rp_rut_eta_ltime) as 'Resultado'"+
" from innovator.MT_RUTA "+
" join innovator.MT_R_RUT_ETA on MT_RUTA.id = MT_R_RUT_ETA.SOURCE_ID "+
" where MT_RUTA.id = '"+ID+"' and MT_R_RUT_ETA.KEYED_NAME not like 'APROV%'";

ResultadoSQL = innovator.applySQL(SentenciaSql);

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

//String s = LT_Tot.ToString() + " - " + LT_Par.ToString();
String s = ResultadoSQL.ToString();
return innovator.newError(s);

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

  • 0 Offline

    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.

  • +1 in reply to Meupe ARAS

    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:  How to Implement "GroupBy" and "Max()" in AML? 

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

  • 0 Offline in reply to AngelaIp

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