Re: Upgraded MySQL5.6 to MySQL5.7
Hi Peter.
Here is the query.
SELECT
job.Job_No,
job.Invoice_Tax_Date,
job.Contract,
job.CustomerRef,
job.Order_Date,
job.File_No,
job.VAT,
job.CustomerName,
job.CustomerBuildingNumber,
job.Line1,
job.Line2,
job.Line3,
job.Line4,
job.Line5,
job.PostCode,
job.OrderSiteAddress,
job.SitesBuildingNumber,
job.SLine1,
job.SLine2,
job.SLine3,
job.SLine4,
job.SLine5,
job.SPostCode,
IFNULL((select ROUND(sum(InvoiceSubtotal),2) from labour where labour.Job_No = job.Job_No), 0) AS Labsubtotal,
IFNULL((select ROUND(sum(InvoiceSubtotal),2) from material where material.Job_No = job.Job_No), 0) AS Matsubtotal,
IFNULL((select Labsubtotal+Matsubtotal), 0) AS Subtotal,
IFNULL((select (Labsubtotal+Matsubtotal)*job.VAT/100), 0) AS VATAmount,
IFNULL((select (Labsubtotal+Matsubtotal)*job.VAT/100 +(Labsubtotal+Matsubtotal)), 0) AS Total,
IFNULL((SELECT SUM(Working_Hrs) FROM `labour`where labour.Job_No = job.Job_No),0) as JSH,
labour.Job_No AS Job_No1,
material.Job_No AS Job_No2
FROM job
LEFT OUTER JOIN labour ON job.Job_No = labour.Job_No
LEFT OUTER JOIN material ON job.Job_No = material.Job_No
Then when I run MySQL5.7 I get this on the webpage.
php error happened
Error type 256
Error description Unknown column 'Labsubtotal' in 'field list'
URL 172.16.16.5/job_list.php?orderby=dJob_No
Error file C:\inetpub\wwwroot\JobDesk\connections\Connection.php
Error line 626
SQL query select count(*) from (select `Job_No`, `Job_No` as `BasicInvoice`, `Job_No` as `VRCI`, `Job_No` as `FullInvoice`, `Job_No` as `Invoice`, IFNULL((select ROUND(sum(InvoiceSubtotal),2) from labour where labour.Job_No = job.Job_No),0) as `Labsubtotal`, IFNULL((select ROUND(sum(InvoiceSubtotal),2) from material where material.Job_No = job.Job_No),0) as `Matsubtotal`, IFNULL((select Labsubtotal+Matsubtotal),0) as `Subtotal`, IFNULL((select (Labsubtotal+Matsubtotal)*job.VAT/100),0) as `VATAmount`, IFNULL((select (Labsubtotal+Matsubtotal)*job.VAT/100 +(Labsubtotal+Matsubtotal)),0) as `Total`, IFNULL((SELECT SUM(Working_Hrs) FROM `labour`where labour.Job_No = job.Job_No),0) as `JSH` FROM `job` WHERE ( ( ( Job_No=1063036 ) ) ) ) a
Tried to post the full error but this forum tells me
A word you have used in your post has been banned from use. Please use a different word or contact the forum administrators.