VFP9 - Create SQL View not working!

[SSSMan]

I have just installed VFP9 and have been testing my applications that is working in VFP8. I generated my views from a program and I am having problems with 3 of them.

Here is the code for the main one.

CREATE SQL VIEW lv_OrderItem AS ;
SELECT ;
Products.product_no, ;
Products.masteritem, ;
Products.subitem, ;
OrderItem.product_id, ;
Products.product_name, ;
Products.keyword, ;
Products.size, ;
Products.unit, ;
Products.casequantity, ;
OrderItem.quantityordered, ;
OrderItem.quantityreceived, ;
OrderItem.price, ;
OrderItem.order_id, ;
OrderItem.orderitem_id, ;
ProductLocation.location, ;
Products.producttype, ;
Products.productstatus, ;
Products.quantityonhand, ;
Products.quantityinprocess, ;
OrderItem.saleitem, ;
OrderItem.quantitydiscount, ;
OrderItem.promotiongroupsold, ;
OrderItem.specialpricing, ;
OrderItem.specialpricenotes, ;
OrderItem.linenumber, ;
OrderItem.updated, ;
OrderItem.updated_by, ;
OrderItem.discarded, ;
Products.priceretail, ;
Products.priceregular, ;
Products.pricesale, ;
Products.pricebrokencase, ;
Products.pricecashandcarry, ;
Products.pricecashandcarrysale, ;
Products.pricegroup, ;
Products.priceinstitution, ;
Products.priceinstitutionsale, ;
Products.promotiongroup, ;
Products.promotiongroupquantity, ;
Products.quantitydiscount1, ;
Products.quantitydiscount2, ;
Products.quantitydiscountcashandcarry1, ;
Products.quantitydiscountcashandcarry2, ;
Products.quantityprice1, ;
Products.quantityprice2, ;
Products.quantitypricecashandcarry1, ;
Products.quantitypricecashandcarry2, ;
Products.pricewholesale, ;
Products.standardcost, ;
Products.costaverage, ;
Products.costlast, ;
Products.upc_code, ;
Products.newitem, ;
Products.parentitem, ;
Products.notes AS subitemdescription, ;
0000 AS subitemdescriptionline, ;
Products.productmessage, ;
NVL(v_OrderProductHistory.historycount,0) AS historycount, ;
IIF(Products.productstatus > PRODUCT_STATUS_ACTIVE, DISCONTINUED_ITEM, ACTIVE_ITEM) AS activeproductnotation, ;
PADR(IIF(OrderItem.saleitem, SALE_ITEM, ;
IIF(OrderItem.specialpricing, SPECIAL_PRICE, ;
IIF(OrderItem.quantitydiscount, QUANTITY_DISCOUNT, ;
IIF(NOT EMPTY(OrderItem.promotiongroupsold), SUBSTR(OrderItem.promotiongroupsold,1,6), ;
IIF(Products.casequantity = 0, NON_DISCOUNTED_ITEM, ;
IIF(((OrderItem.quantityordered / Products.casequantity) = CEILING(OrderItem.quantityordered / Products.casequantity) ) OR Products.pricebrokencase = 0 , ;
NON_DISCOUNTED_ITEM, BROKEN_CASE)))))),6,' ') ;
AS linenotation, ;
IIF(Products.casequantity > 0, ;
IIF(MOD(OrderItem.quantityordered,IIF(Products.casequantity>0,Products.casequantity,1)) > 0, BROKEN_CASE,' '), ' ') ;
AS BrokenCaseNotation, ;
.F. AS lSetQuantityReceived ;
FROM ;
jms!OrderItem ;
LEFT JOIN jms!Products ;
ON Products.product_id = Orderitem.product_id ;
LEFT JOIN jms!ProductLocation ;
ON ProductLocation.productlocation_id = Products.keylocation_id ;
LEFT JOIN v_OrderProductHistory ;
ON v_OrderProductHistory.parentitem == Products.parentitem ;
WHERE ;
OrderItem.order_id = ?v_Order.order_id ;
ORDER BY OrderItem.linenumber, Products.product_no, OrderItem.orderitem_id


This has been working fine in VFP8. When I run this in VFP9, I get error 1845.

The Select statement will run fine without the Create command.

Has anyone else encountered any other problems with VFP9 and views.

Thanks, David

 

[Olaf Doschke]

Hi David,

I wonder if your parameter naming is correct:
?v_Order.order_id should perhaps be ?vOrder_id

But the error message "too complex" isn't very helpful, is it? How about shortening it with alias names for the tables: ...FROM jms!OrderItem As OI...

Use ICAST instead of the nested IIF...

Bye, Olaf.

 

[Olaf Doschke]

.. I meant ICASE()

Bye, Olaf.

 

[SSSMan]

Olaf,

The Parameter is OK. that is another view.
I tried the Alias names but there was not difference.

I have already tried removing the nested IIF with no help.

I can remove columns from the view and eventual get it to work, but it is not consistent. Sometimes I can remove only 10 and other combinations take as many as 18.

Thanks, David

 

[Mike Lewis]

David,

Perhaps you should try experimenting with SYS(3055).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[TamarGranor]

There's been some discussion over on the Universal Thread about some views, involving large tables, not working in VFP 9. Look for a thread in the data section with the words "too complex" in the thread title.

Tamar

 

[KoenPiller]

David,

Could you check the size of your tables in VFP9 and in VFP8? Maybe you are dealing with >2G tables in VFP9.

Koen