MAS90 SQL syntax to ODBC

[gidgetsmychick]

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

 

[dfeller]

I don't believe IFF or CASE are supported.

Dawn

Visit Sage's Online Community

http://community.sagemas.com/sagemas/?category.id=SageMAS90and200Disc

 

[gbaughma]

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

http://parallel.tzo.com

 

[gidgetsmychick]

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?

 

[MASGuy]

No, the SOTAMAS90 ODBC driver has limited options. It does not support complex statements.

 

[gidgetsmychick]

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

 

[MASGuy]

A common solution is to create a SQL Specific Pass Through Query in Access and then base your query on that. Should work. I think BigLouie posted how to do the pass through before.

Visit Sage's Online Community

http://community.sagemas.com/sagemas/?category.id=SageMAS90and200Disc