D365 X++ query to SQL Query

[Perumal]

Dear Team,

I am trying to convert D365 X++ query to SQL query and getting error Error converting data type varchar to bigint.

D365 X++ query :

SELECT FIRSTFAST FORUPDATE * FROM InventTransOrigin(InventTransOrigin) USING INDEX InventTransIdIdx JOIN FORUPDATE * FROM InventTrans(InventTrans) ON InventTransOrigin.RecId = InventTrans.InventTransOrigin JOIN FORUPDATE * FROM InventDim(InventDim) ON InventTrans.inventDimId = InventDim.inventDimId JOIN FORUPDATE * FROM InventTableModule(InventTableModule) ON InventTrans.ItemId = InventTableModule.ItemId AND ((ModuleType = 0)) OUTER JOIN FORUPDATE InventTransId FROM InventTransOrigin(Ref_InventTransOrigin_MarkingRefInventTransOrigin) ON InventTrans.MarkingRefInventTransOrigin = InventTransOrigin.RecId OUTER JOIN FORUPDATE InventTransId FROM InventTransOrigin(Ref_InventTransOrigin_returnInventTransOrigin) ON InventTrans.ReturnInventTransOrigin = InventTransOrigin.RecId OUTER JOIN FORUPDATE InventTransId, InventTransId FROM InventTransOrigin(Ref_InventTrans_InventTransOrigin_InventTransOrigin) ON InventTrans.InventTransOrigin = InventTransOrigin.RecId

SQL  Query:

select inventtransorigin.itemid,inventtransorigin.referenceid,inventtransorigin.referencecategory,inventbatchid,qty,unitid,costamountphysical,costamountposted
from inventtransorigin
join inventtrans on inventtransorigin.recid = inventtrans.inventtransorigin
join inventdim on inventtrans.inventdimid = inventdim.inventdimid
join inventtablemodule on inventtrans.itemid = inventtablemodule.itemid and ((moduletype = 0))
Left join inventtransorigin io1 on io1.inventtransid = inventtransorigin.recid
Left join inventtransorigin io2 on io2.inventtransid = inventtransorigin.recid
Left join inventtransorigin io3 on io3.inventtransid = inventtransorigin.recid
where inventtransorigin.itemid like '100465' and inventbatchid='hdh'

Please help me if anything I am doing wrong.

Regards

[Perumal]

Thanks for your support

[AmiraBedh · Accepted solution]

You're trying to compare or join a varchar (string) field with a bigint (numeric) field :

LEFT JOIN inventtransorigin io1 ON io1.inventtransid = inventtransorigin.recid

Here is the full query :

SELECT 
    inventtransorigin.itemid,
    inventtransorigin.referenceid,
    inventtransorigin.referencecategory,
    inventdim.inventbatchid,
    inventtrans.qty,
    inventtrans.unitid,
    inventtrans.costamountphysical,
    inventtrans.costamountposted
FROM inventtransorigin
JOIN inventtrans 
    ON inventtransorigin.recid = inventtrans.inventtransorigin
JOIN inventdim 
    ON inventtrans.inventdimid = inventdim.inventdimid
JOIN inventtablemodule 
    ON inventtrans.itemid = inventtablemodule.itemid 
    AND inventtablemodule.moduletype = 0
LEFT JOIN inventtransorigin io1 
    ON inventtrans.markingrefinventtransorigin = io1.recid
LEFT JOIN inventtransorigin io2 
    ON inventtrans.returninventtransorigin = io2.recid
LEFT JOIN inventtransorigin io3 
    ON inventtrans.inventtransorigin = io3.recid
WHERE 
    inventtransorigin.itemid = '100465' 
    AND inventdim.inventbatchid = 'hdh'