I have a view where this error occurs occsionally. I will post the view definition below, but I think the error is comming out of one field expresion which is:
IIF((DCMIINVENTORY.iQtyOnHand - DCMIINVENTORY.iQtyCommitted)>= 0,(DCMIINVENTORY.iQtyOnHand - DCMIINVENTORY.iQtyCommitted),0000000000) AS "iQtyAvailable" ,;
the error was generared this morning, while testing the APP at the clients site, where the inventory table did not contain a record for this item, which I suspect caused the error.
The entire view SQL definiton is:
SELECT ;
DCMIRELEASES.CID , ;
DCMIRELEASES.cArtist_fk , ;
DCMIRELEASES.cInventoryCode_fk , ;
DCMIRELEASES.cItemCode_fk ,;
DCMIRELEASES.cLabel_fk , ;
DCMIRELEASES.cVendorCode_fk , ;
DCMIARTIST.cArtist , ;
DCMIRELEASES.cCatNo ,;
DCMIINVTYCODE.cCodeNo , ;
DCMIRELEASES.cInputBy , ;
DCMILABELS.cLabelName , ;
DCMIINVTYCODE.cDescription AS "cInvtyCodeDescription" ,;
DCMIITEMCODE.cItemCode , ;
DCMIITEMCODE.cDescription AS "cItemDescription" , ;
DCMIVENDORS.cName ,;
DCMIRELEASES.cND , ;
DCMIRELEASES.cTitle , ;
DCMIRELEASES.cUPC , ;
DCMIVENDORS.cVendorCode , ;
DCMIRELEASES.dDue ,;
DCMIRELEASES.dInput , ;
DCMIRELEASES.dStreet , ;
DCMIRELEASES.iExtQty , ;
IIF((DCMIINVENTORY.iQtyOnHand - DCMIINVENTORY.iQtyCommitted)>= 0,(DCMIINVENTORY.iQtyOnHand - DCMIINVENTORY.iQtyCommitted),0000000000) AS "iQtyAvailable" ,;
DCMIRELEASES.lCutOut , ;
DCMIRELEASES.lPending , ;
DCMIRELEASES.nListPrice , ;
DCMIRELEASES.nCMIPrice ,;
DCMIRELEASES.nCustPrice , ;
DCMIRELEASES.mRemarks , ;
DCMIARTIST.lArtistDummy , ;
DCMIINVTYCODE.lInvtDummy ,;
DCMIITEMCODE.lItemsDummy , ;
DCMILABELS.lLabelsDummy , ;
DCMIRELEASES.lReleasesDummy , ;
DCMIVENDORS.lVendorsDummy ,;
DCMIINVENTORY.iQtyCommitted ;
FROM ( ( ( ( ( CMI!DCMIRELEASES ;
INNER JOIN CMI!DCMIARTIST ON DCMIRELEASES.cArtist_fk = DCMIARTIST.CID ) ;
INNER JOIN CMI!DCMIINVTYCODE ON DCMIRELEASES.cInventoryCode_fk = DCMIINVTYCODE.CID ) ;
INNER JOIN CMI!DCMILABELS ON DCMIRELEASES.cLabel_fk = DCMILABELS.CID ) ;
INNER JOIN CMI!DCMIITEMCODE ON DCMIRELEASES.cItemCode_fk = DCMIITEMCODE.CID ) ;
INNER JOIN CMI!DCMIVENDORS ON DCMIRELEASES.cVendorCode_fk = DCMIVENDORS.CID ) ;
LEFT OUTER JOIN CMI!DCMIINVENTORY ON DCMIRELEASES.CID = DCMIINVENTORY.cReleases_fk ;
GROUP BY DCMIRELEASES.CID
Can someone suggest a way to accomplish what I am trying o do here safely?
Thanks,
Steve Buttress, MCP
ProMatrix MVP - Life
ProSysPlus Developer