Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I've been staring at this statement for the better part of an hour now, I've begun building a front end in Access to enable user interaction with the database and have suddenly found that one of my views is now throwing an error that I'm 90% sure it wasn't earlier. I have another similar view that works just fine:

 -- SALES_REPORT view
CREATE VIEW SALES_REPORT_VIEW AS SELECT INVOICE_NUMBER, INVOICE_DATE, CLIENTS.CLIENT_ID, CLIENT_NAME, WORK_ORDER_COST + 
(WORK_ORDER_HOURLY_COST * (SELECT COUNT(*) FROM WORK_LOG WHERE WORK_LOG.WORK_ORDER_NUMBER_FK = INVOICES.WORK_ORDER_NUMBER_FK)) AS SUBTOTAL, 0 + 20 AS VAT, CAST((WORK_ORDER_COST + (WORK_ORDER_HOURLY_COST * (SELECT COUNT
(*) FROM WORK_LOG WHERE WORK_LOG.WORK_ORDER_NUMBER_FK = INVOICES.WORK_ORDER_NUMBER_FK))) * (1 + (20/100)) AS DECIMAL(10,2)) AS GRAND_TOTAL FROM INVOICES, CLIENTS, WORK_ORDERS WHERE CLIENTS.CLIENT_ID = INVOICES.CLIENT_ID_FK AND WORK_ORDERS.WORK_ORDER_NUMBER = INVOICES.WORK_ORDER_NUMBER_FK;

I've even used a text comparison tool to compare it to the other view which is essentially the same, I couldn't see a difference and that view works just fine.

share|improve this question
Is the Backend DB Microsoft Access or MySQL ??? – RolandoMySQLDBA Dec 7 '12 at 16:20
Please post the exact error message – RolandoMySQLDBA Dec 7 '12 at 16:22
If the view exists in MySQL, please run SHOW CREATE VIEW SALES_REPORT_VIEW\G and post it in the question. – RolandoMySQLDBA Dec 7 '12 at 16:23
I'm not really sure I can class this as solved but I essentially just re-typed part of the statement exactly as it was before and now there is no error and the view works fine, backend is MySQL and the error message was incorrect syntax usage for: *) FROM WORK_LOG WHERE WORK_LOG.WORK_ORDER_NUMB which wasn't even a section I re-typed. – Sam Fulcher Dec 7 '12 at 16:33

closed as too localized by RolandoMySQLDBA, Phil, Derek Downey, Paul White, Mark Storey-Smith Dec 8 '12 at 3:02

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, see the FAQ.

Browse other questions tagged or ask your own question.