gidgetsmychick
Programmer
Hi All,
I'm a little confused how I'm supposed to construct my SELECT statements when requesting from tables in MAS90 ODBC. I am using VB.NET to connect and get some data to compare. I can do normal select statements just fine, hoever in this case, I want to specify a result column of which data is greater, the last cost or average cost.
I have tried:
SELECT IM1_InventoryMasterfile.ItemNumber, IFF(IM1_InventoryMasterfile.LastCost > IM1_InventoryMasterfile.AveCost,IM1_InventoryMasterfile.LastCost,IM1_InventoryMasterfile.AveCost) as 'Cost', IM1_InventoryMasterfile.TotalQtyOnHand
FROM IM1_InventoryMasterfile IM1_InventoryMasterfile
Error message= "Expected lexical element not found: FROM"
and
SELECT IM1_InventoryMasterfile.ItemNumber, CASE WHEN IM1_InventoryMasterfile.LastCost > IM1_InventoryMasterfile.AveCost THEN IM1_InventoryMasterfile.LastCost ELSE IM1_InventoryMasterfile.AveCost END as 'Cost', IM1_InventoryMasterfile.TotalQtyOnHand
FROM IM1_InventoryMasterfile IM1_InventoryMasterfile
Error message= "Didn't expect 'LastCost' after the SELECT column list."
Where am I going wrong here?
Thanks for your help
I'm a little confused how I'm supposed to construct my SELECT statements when requesting from tables in MAS90 ODBC. I am using VB.NET to connect and get some data to compare. I can do normal select statements just fine, hoever in this case, I want to specify a result column of which data is greater, the last cost or average cost.
I have tried:
SELECT IM1_InventoryMasterfile.ItemNumber, IFF(IM1_InventoryMasterfile.LastCost > IM1_InventoryMasterfile.AveCost,IM1_InventoryMasterfile.LastCost,IM1_InventoryMasterfile.AveCost) as 'Cost', IM1_InventoryMasterfile.TotalQtyOnHand
FROM IM1_InventoryMasterfile IM1_InventoryMasterfile
Error message= "Expected lexical element not found: FROM"
and
SELECT IM1_InventoryMasterfile.ItemNumber, CASE WHEN IM1_InventoryMasterfile.LastCost > IM1_InventoryMasterfile.AveCost THEN IM1_InventoryMasterfile.LastCost ELSE IM1_InventoryMasterfile.AveCost END as 'Cost', IM1_InventoryMasterfile.TotalQtyOnHand
FROM IM1_InventoryMasterfile IM1_InventoryMasterfile
Error message= "Didn't expect 'LastCost' after the SELECT column list."
Where am I going wrong here?
Thanks for your help