So I have written the SQL statement in management studio to return what I need based on the proper billofladingid.
select * from WHSLOADLINE whsl inner JOIN WHSSHIPMENTTABLE wshsp ON wshsp.LOADID = whsl.LOADID inner JOIN WHSBILLOFLADING whsb ON whsb.SHIPMENTID = wshsp.SHIPMENTID where whsb.BILLOFLADINGID = 'SCSK-000000101'
Then I try to recreate it in the code and its returning some other line that to me doesn't correspond at all. The billofladingid is correct for the end. I'm confused. I have tried moving the where's around to no avail.
select whsloadline JOIN whsshipmenttable WHERE whsloadline.LOADID == whsshipmenttable.LOADID JOIN WHSbillOfLading2 WHERE WHSbillOfLading2.SHIPMENTID == whsshipmenttable.SHIPMENTID && WHSbillOfLading2.BILLOFLADINGID == ExtbillOfLading.BillOfLadingId; WHSBillOfLadingTmp.GRO_OrderNum = whsloadline.OrderNum;
When you use count() in SQL, it'll create a new column on the fly. In X++, the schema is fixed; fields are defined by the table. When you use an aggregated function, the result is put into the field you're aggregating.
For example, you'll use count(RecId) and then access the result by myTable.RecId.
The return value of count() is a number, therefore you need to use it with a numeric field (which isn't the case of ContainerTypeCode).
So I am counting the containertypecodes and it's griping about it being a string.
The aggregate function 'Count(ContainerTypeCode)' returns a numeric result, but field 'ContainerTypeCode' is of type 'str'.
That worked pretty well, thank you. I am trying to do a while and inside the while select loop trying to sum and get a count on the containertypes. I've looked at examples and can't find one with multiple fields to select from. Is there something I am missing for this? It's giving me red squiggly lines for 'Illegal table specification'.
QueryRun queryRun; queryRun = new QueryRun(this.parmQuery()); ExtbillOfLading = queryRun.get(tableNum(WHSBillOfLading)); while (queryRun.next()) { while SELECT forceLiterals whsLoadtable JOIN whsshipmenttable WHERE whsshipmenttable.LOADID == whsLoadtable.LOADID && whsshipmenttable.BILLOFLADINGID == extBillOfLading.BillOfLadingId { SELECT sum(weight), count(CONTAINERTYPECODE) AS Qty whscontainertable WHERE whscontainertable.ShipmentId == whsShipmentTable.ShipmentId GROUP BY CONTAINERTYPECODE } }
As I explained, F&O automatically adds filters for DataAreaId and Partition. You don't have them in your SQL code.
I don't know what you mean by your last question.
In what way doesn't this match? Can I see this query in the output window?
I don't see the problem, but maybe I have a hint that might help you. You can look at the actual SQL query generated by your X code. Reviewing it, running it and adjusting it might give you a concrete idea about the problem.
select forceLiterals generateOnly whsLoadLine join whsShipmentTable where whsShipmentTable.LoadId == whsLoadLine.LoadId join whsBillOfLading2 where whsBillOfLading2.ShipmentId == whsShipmentTable.ShipmentId && whsBillOfLading2.BillOfLadingId == extBillOfLading.BillOfLadingId; info(whsLoadLine.getSqlStatement());
It doesn't seem to be a problem in this case, but notice that your SQL doesn't match your X code, because X code filters by DataAreaId and Partition automatically. If you want to have the same SQL, you need to apply these filters by yourself.
André Arnaud de Cal...
291,979
Super User 2025 Season 1
Martin Dráb
230,848
Most Valuable Professional
nmaenpaa
101,156