Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

MAS90 SQL syntax to ODBC

Status
Not open for further replies.

gidgetsmychick

Programmer
Sep 8, 2010
3
US
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 agree with dawn... IFF is, I believe, specific to Microsoft Access.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg
 
is there a syntax reference that you know of for what I can use for the mas90 ODBC driver?

Can I use conditional if then statements?
 
No, the SOTAMAS90 ODBC driver has limited options. It does not support complex statements.
 
Thank you all. Yes my best bet to do this in vb.net would be to pull all of the fields from mas90 into a data table, then do any complex select statements on the temporary data table i just created in vb.net. that is definitely the long way, I'll let you all know if that works out
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top