web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Introducing our New Video Series: Community Spotlight
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Returning wrong lines ...
Finance | Project Operations, Human Resources, ...
Answered

Returning wrong lines from sql statement.

(0) ShareShare
ReportReport
Posted on by KeithM 535

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'

pastedimage1680793794669v1.png

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;

pastedimage1680793901324v2.png

pastedimage1680793958330v3.png

I have the same question (0)
  • Martin Dráb Profile Picture
    Martin Dráb 238,642 Most Valuable Professional on at

    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.

  • KeithM Profile Picture
    KeithM 535 on at

    In what way doesn't this match?  Can I see this query in the output window?

  • Martin Dráb Profile Picture
    Martin Dráb 238,642 Most Valuable Professional on at

    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.

  • KeithM Profile Picture
    KeithM 535 on at

    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

            }
        }

  • KeithM Profile Picture
    KeithM 535 on at

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

    pastedimage1681404801821v1.png

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 238,642 Most Valuable Professional on at

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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Introducing our New Video Series: Community Spotlight

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Congratulations to our 2025 Community Spotlights

Thanks to all of our 2025 Community Spotlight stars!

Congratulations to the January Top 10 Community Leaders

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 673 Super User 2026 Season 1

#2
Abhilash Warrier Profile Picture

Abhilash Warrier 280 Super User 2026 Season 1

#3
Martin Dráb Profile Picture

Martin Dráb 213 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans