topic Re: Problem in Custom Code using SQL which uses label on token in WHERE clause in FlexSim Forum

Problem in Custom Code using SQL which uses label on token in WHERE clause

Tom24 — Fri, 15 Dec 2023 13:12:49 GMT

[ FlexSim 23.0.8 ]

I'm trying to get slot wise item/SKU count using Custom Code in Process Flow.

I've created and tried 3 patterns, "Get SlotWiseItemCount" 1 to 3 but all 3 has issues.

There are diffrence in location of "WHERE" clause but all 3 Custom Code has practically the same SQL but all results are different and incorrect.

Since SQL is having WHERE condition, LocationCD = token.LocationCD, only single row should come but as you could find in attached model, it wont.

Please take a look into Custom Code "Get SlotWiseItemCount" 1 to 3.

Checking will be easier if you use Global Table "QueryDump".

Kindly let me know whether I have done something wrong.

Thanks for the support.

Re: Problem in Custom Code using SQL which uses label on token in WHERE clause

joerg_vogel_HsH — Fri, 15 Dec 2023 14:07:50 GMT

If you want to get the content of a slot address, why do you try to do it so complicated.

Variant slotitems = Storage.system.queryItems("WHERE item.slot.address = $1",0,token.LocationCD);
token.labels.assert("inSlot",slotitems.length);

Re: Problem in Custom Code using SQL which uses label on token in WHERE clause

jason_lightfoot_adsk — Fri, 15 Dec 2023 14:40:41 GMT

You can get your table at any time directly from the storage system:

Storage.system.querySlots("SELECT  slot.slotItems.length, slot.address").as(Array).as(Table).cloneTo("QueryDump")

Re: Problem in Custom Code using SQL which uses label on token in WHERE clause

Tom24 — Sat, 16 Dec 2023 00:46:26 GMT

@Joerg Vogel @Jason Lightfoot

What I'm trying to achieve is count base on specific label not the content.

For example, slot has 16 flowitems which is equal to the content/lenght.

All 16 flowitems are randomly labeled 1 to 8. In my model named as "ItemCD" (similar to "Type") on creation event.

When stored, slot would be like

i.e.

ItemCD 1 : 8 boxes

ItemCD 3 : 5

ItemCD 5 : 2

ItemCD 7 : 1

total 16 boxes/contents are stored in slot.

Length/conten is 16 but count of "ItemCD" should be 4.

That is why I'm using group by/count in sql.

Yes, I understand that there is no need to use sql if there is an easier way to get the count.

If I'm still missing something, kindly let me know.

Re: Problem in Custom Code using SQL which uses label on token in WHERE clause

joerg_vogel_HsH — Sat, 16 Dec 2023 07:44:07 GMT

in relation to your comment: Use more lines of code. First collect values of itemCD label name. Then push them onto a list with only single value option. List entries are your desired result.

E.g, you could first get all slotItems as an array. Iterate over this array all label values of itemCD in a table query. Push an aggregated result onto a list.

Re: Problem in Custom Code using SQL which uses label on token in WHERE clause

jason_lightfoot_adsk — Sat, 16 Dec 2023 13:41:18 GMT

Storage.system.queryItems("SELECT item.ItemCD, count(*) as freq, slot.address GROUP BY item.ItemCD,slot.address ORDER BY address").as(Array).as(Table).cloneTo("QueryDump");
Table.query("SELECT address, count(*) as numDiffItemCDs FROM QueryDump GROUP BY address ORDER BY address ").cloneTo(Table("SlotMixSummary"));

Model with script attached.

test-countnumbersofiteminslot_jl.fsm

Re: Problem in Custom Code using SQL which uses label on token in WHERE clause

Tom24 — Mon, 18 Dec 2023 02:27:14 GMT

@Jason Lightfoot

Thanks for the support.

Essential problem I'm having in SQL is in WHERE condition.

Since picking/target address will be different by each tokens, I want to use label value on each token. But the problem is "WHERE address = [" + token.LocationCD + "]" not working. I have also tried in your SQL using Custom Code but didn't work either.

Please provide me a support how to use token in WHERE condition.

I believe what I'm missing is how to code/write WHERE condition properly.

Re: Problem in Custom Code using SQL which uses label on token in WHERE clause

jason_lightfoot_adsk — Mon, 18 Dec 2023 02:50:12 GMT

Not sure why you're using brackets - I'd remove those - but you should use the apostrophe around your string when concatenating the value.

"WHERE address = '" + token.LocationCD + "'"

Re: Problem in Custom Code using SQL which uses label on token in WHERE clause

Tom24 — Mon, 18 Dec 2023 23:56:00 GMT

@Jason Lightfoot
Thanks, now its working.
So what I was missing was appropriate symbol.

Thanks for the support