Create Report - SQL Query not working in ZC screen (in DB ok)

[iris - karen]

Hi all,

I've created what I need in a report, basic stuff from 4 tables. I've gotten the report to run in the DB of the store, and export to CSV.

We tried to run the same report in the ZC Admin section however it did'nt run. We got no real advice on what to do with it, it just bugged out.

Our SQL is 5.0 (I believe) and I want to be able to run the report starting from the last download (ie option to start at order # to current). Hell, I'd just be happy to get the SQL Query to work in ZC [SQL Query Executor] OR have I lost the plot and am not doing it in the right place?

Now... to convince my husband to learn about CRON and see if he can't create me an auto... LOL

Cheers.
~Karen

[DrByte]

I'm kinda lost about what actual "problem" you're talking about.

Things I do know:
a) the SQL Patch tool is NOT designed to extract data. It is only a means of running SQL statements *without* obtaining any results back for display/output of any sort.
b) you say you've got your report running great outside of ZC, but then you say it's not working right inside ZC and needs to start at a certain number ... but starting at a certain number is something you would do the same in your SQL that you run outside of ZC ... so that's not really a ZC issue either.

So ... you have this "report" that does "something", and that "something" might be related to "orders"?
And you want to run this from inside your admin for some reason?
And why would cron be relevant?
Also, the business "problem"/need that you're trying to solve here isn't stated.

[iris - karen]

I'm kinda lost about what actual "problem" you're talking about.

Thanks DrByte for taking the time to answer my questions. Google & I have been having words over this.

Things I do know:
a) the SQL Patch tool is NOT designed to extract data. It is only a means of running SQL statements *without* obtaining any results back for display/output of any sort.
b) you say you've got your report running great outside of ZC, but then you say it's not working right inside ZC and needs to start at a certain number ... but starting at a certain number is something you would do the same in your SQL that you run outside of ZC ... so that's not really a ZC issue either.

No, you're right, it's really not a ZC issue - now that I know that the SQL box inside is not what I need it for
As for the certain number, if my last report had order # 191, I only want to download from #192 the next day/week. (I haven't figured out how to do that, or set a condition in my SQL query).

So ... you have this "report" that does "something", and that "something" might be related to "orders"?
And you want to run this from inside your admin for some reason?

I had hoped that I could set an admin up to run that report inside ZC rather than having to run it in CPanel files & handing over the server password when I can't get the reports. The reports must be run weekly on all sales by 2 x manufacturers and monthly on the rest of the store (I have got one SQL working perfectly, needing the 2nd to be adapted just for manufacturers).

And why would cron be relevant?

If I can't run it from Admin in ZC then I think the only option I have is to set up a CRON to automate my reports so I can get my Admin/Bookkeeper get the files without needing the database password.

Also, the business "problem"/need that you're trying to solve here isn't stated.

The problem is - the sales report modules I've installed don't work as I need them too. I don't get the information that I need to run my admin/accounting with. We pay out on royalties on some of our products & we need to take into account all costs involved (sales inc) to pay out accurately. Without paying $175+ for a 3rd party plugin I designed my own SQL query to give me a report:

select 
orders.orders_id,
orders.date_purchased,
orders_products.products_id, 
orders_products.products_name, 
orders_products.products_price, 
orders_products.final_price, 
orders_total.title, 
orders_total.text,
orders_total.class,
orders.orders_status, 
orders.coupon_code, 
orders.payment_module_code,
orders.customers_id,
orders.customers_name,
customers.customers_email_address,
products.manufacturers_id
from (
orders_total, 
orders_products, 
orders, 
customers, 
products
)
where (
orders_products.products_id=products.products_id 
and orders_total.orders_id=orders.orders_id 
and orders_products.orders_id=orders.orders_id 
and orders.customers_email_address=customers.customers_email_address
) 
order by orders.orders_id DESC;

What's frustrating is that the PayPal mod doesn't have the same column names as the orders table and I can't get the 2 linked together so I can run the 1 store report AND get the PayPal data at the same time. (I run 3 downloads now - (store) Database to CSV; (paypal.com) PayPal to CSV; & (paymate.com) Paymate to CSV - as there is no table I can see for the Paymate mod).

So. That's why I was hoping that the lovely shiny white box would run the above command and spit me out a report on the inside of ZC so I could have an admin do the report if i'm unavailable.

I hope that clears things up.

Thanks again :)

[DrByte]

To automagically 'start' from a certain 'last record number', you'll need to add more business logic to your process to 'remember' (store) that last number someplace, and then add additional logic to retrieve that number and use it as a selection criteria in your reporting.