AnsweredAssumed Answered

Complex ExecuteSQL Statement

Question asked by russbrad on Jun 1, 2014
Latest reply on Jun 2, 2014 by wimdecorte

Hi

 

I have asked a friend with SQL skills to help me construct a complex SQL expression. In essence we are trying to construct a report that totals line item costs (accross various order components) by account codes.

Currently the script does work - except it takes 5 minutes at least!!! The file is being shared via FMS 12. How can a script take 5 minutes to run??? There are only about 100 orders in the database at the moment. Are we expecting too much from ExectuteSQL or misusing it??? Any comments welcome :-)

 

 

Let([

oid = Orders::id;

vid = ExecuteSQL("select "t%CurrentVariationID" from Orders where id = ?";"";"";oid);

val = 0 + ExecuteSQL("select "t%CurrentVariation" from Orders where id = ?";"";"";oid) ;

sum = ExecuteSQL("select Sum("t%Cost" * C.nQuantity) from

LineItemCost L

inner join ComponentProduct P on P.id_LineItem = L.id_Lineitem

inner join Components C on P.id_Component = C.id

inner join VariationComponent V on C.id = V.id_Component

Where V.id_Variation = ? and "t%Cost" > 0";"";"";vid);

com = 0 +ExecuteSQL("select nCommission from Variations where id = ?";"";"";vid);

cam = (com/100) * val;

val0 = val;

val = val - cam;

lid = ExecuteSQL("select tAccountCode, Sum(Round("t%Cost" * C.nQuantity/ ? * ?,2)) from

LineItemCost L

inner join ComponentProduct P on P.id_LineItem = L.id_Lineitem

inner join Components C on P.id_Component = C.id

inner join VariationComponent V on C.id = V.id_Component

Where V.id_Variation = ? and "t%Cost" > 0 Group by tAccountCode";" 0";"";sum;val ;vid);

lid0 = lid;

lid = If(cam>0; "4-0600 0" & #.##(cam) & ¶;"") & lid;

gst = ExecuteSQL("select nGST from Admin";"";"");

cnt = ValueCount(lid);

lvs = LeftValues(lid;cnt-1);

slvs = val0-SumRightWords(lvs);

rv = RightValues(lid;1);

rvrw = LeftWords(rv;1) & " 0" & slvs;

rtn = lvs & rvrw & ¶;

wgst = InsertExGSTRightWords(rtn;gst)

];

wgst

)

Outcomes