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

Question

question

Tom asked Dec 15, '23 Tom answered Dec 18, '23

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

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.

Software Version:

FlexSim 23.0.8

sql customcode queries

test-countnumbersofiteminslot.fsm (65.6 KiB)

· 1

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Tom commented · Dec 16, 2023 at 12:46 AM

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

0 ·

Answer 1 · 2023-12-15T14:40:41Z

Jason Lightfoot answered Dec 15, '23 Jason Lightfoot edited Dec 18, '23

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")

· 3

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Jason Lightfoot ♦♦ commented · Dec 16, 2023 at 01:41 PM

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

0 ·

test-countnumbersofiteminslot-jl.fsm (65.3 KiB)

Tom commented · Dec 18, 2023 at 02:27 AM

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

0 ·

Jason Lightfoot ♦♦ Tom commented · Dec 18, 2023 at 02:50 AM

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 + "'"

0 ·

Answer 2 · 2023-12-18T23:56:00Z

Tom answered Dec 18, '23

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

Thanks for the support

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Answer 3 · 2023-12-15T14:07:50Z

Joerg Vogel answered Dec 15, '23 Joerg Vogel commented Dec 16, '23

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);

· 1

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Joerg Vogel commented · Dec 16, 2023 at 07:44 AM

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.

0 ·

question