Your Cart

Shopping Cart Items: 0   Sub-Total : $0.00    View Cart    Checkout
Welcome, Guest ( Customer Panel | Login )


Home | Profile | Register | Active Topics | Members | Search | FAQ


Forgot your Password?
 All Forums
 VPCart Forum
 VP-ASP 7.0 Questions
 Query and Export		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Renners
Starting Member

United Kingdom
46 Posts
Posted - May 10 2011 :  04:24:01  Show Profile  Visit Renners's Homepage  Reply with Quote
Hi,

I'm trying to run a query to report sales of products by a specific supplier over a specfic period using the following sql code

SELECT products.ccode, products.cname, products.cstock, COUNT(oitems.numitems) AS [Sold in Period], SUM(oitems.unitprice) AS [Total SV] FROM orders INNER JOIN oitems ON orders.orderid = oitems.orderid INNER JOIN suppliers ON oitems.supplierid = suppliers.supplierid INNER JOIN products ON oitems.catalogid = products.catalogid WHERE (orders.odate > CONVERT(DATETIME, '2010-02-01', 102)) AND (orders.odate < CONVERT(DATETIME, '2011-05-01', 102)) AND (orders.customercancel = 0 OR orders.customercancel IS NULL) AND (orders.canceled = 0) AND (orders.ocardtype <> N'Fail') AND (suppliers.supplierid = 22) GROUP BY products.cstock, products.ccode, products.cname ORDER BY [Total SV] DESC

This runs fine from within MS SQL server studio but will dump me out of the Admin section entirely with an 'An error occured please restart shopping' if I run it via the Query and Export Manager.

Does anyone know if the query and export is a single table query tool?

Regards,
Renners

diegomilito
VP-CART Expert

Argentina
779 Posts
Posted - May 14 2011 :  05:22:47  Show Profile  Reply with Quote
seems like you cannot use the builtin sql function such as SUM(oitems.unitprice) , COUNT(oitems.numitems), CONVERT(DATETIME, '2011-05-01', 102)) etc...

Diego
Go to Top of Page

support
Administrator

4679 Posts
Posted - July 12 2011 :  08:17:41  Show Profile  Visit support's Homepage  Reply with Quote
Hi Renners,

Not sure if this is what you are looking for but have a look at the following helpnote:

http://helpnotes.vpasp.com/kb/574-Troubleshooting/1025-SQL-functions-not-able-to-be-used-for-Query-Manager-SQL-Server/

Thank you.

Cam Flanigan
VP-ASP Cart Support

Follow us on Twitter:
http://www.twitter.com/vpasp
Go to Top of Page

Renners
Starting Member

United Kingdom
46 Posts
Posted - August 03 2011 :  10:23:17  Show Profile  Visit Renners's Homepage  Reply with Quote
Hi Cam,

Thanks for the Link, that cured it.

Regards,
Renners.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
Snitz Forums 2000
0 Item(s)
$0.00
  • Your Cart Is Empty