2. PHPRunner General questions
  3. [SOLVED]  Extra FROM in Select Statements
This topic is locked
[SOLVED]

 Extra FROM in Select Statements

 		10/13/2015 01:29:16
PHPRunner General questions
S
 swanside author

I have this statement in one of my projects

SELECT

Job_No,

IFNULL((SELECT ROUND(sum(Invoice),2) FROM labour WHERE Job_No = job.Job_No), 0) AS Labsubtotal,

IFNULL((SELECT ROUND(sum(Quantity*Unit_Price+(Quantity*Unit_Price*+Additional_Cost/100)),2)FROM material WHERE Job_No = job.Job_No), 0) AS Matsubtotal,

(SELECT Labsubtotal+Matsubtotal) AS Subtotal,

(SELECT Subtotal*VAT/100) AS VATAmount,

(SELECT Subtotal+VATAmount) AS Total

FROM job


I then build my Project and run it. Save and Close it.
I open my project again a go to the same Query and for some reason there are added FROM's in the subselect statements?
Any reason why this would happen? 8.1(24969)

SELECT

Job_No,

IFNULL((SELECT ROUND(sum(Invoice),2) FROM labour WHERE Job_No = job.Job_No), 0) AS Labsubtotal,

IFNULL((SELECT ROUND(sum(Quantity*Unit_Price+(Quantity*Unit_Price*+Additional_Cost/100)),2)FROM material WHERE Job_No = job.Job_No), 0) AS Matsubtotal,

(SELECT

Labsubtotal+Matsubtotal

FROM ) AS Subtotal,

(SELECT

Subtotal*VAT/100

FROM ) AS VATAmount,

(SELECT

Subtotal+VATAmount

FROM ) AS Total

FROM job


Cheers
Sergey Kornilov admin 10/13/2015

There are some complicated SQL queries that PHPRunner may not understand. In cases like this you can create a view in MySQL on the top of this query and use this view as a data source in PHPRunner.
Sergey Kornilov admin 10/13/2015

One more thing - there are excessive selects in your query. This one should work better:

SELECT

Job_No,

IFNULL((SELECT ROUND(sum(Invoice),2) FROM labour WHERE Job_No = job.Job_No), 0) AS Labsubtotal,

IFNULL((SELECT ROUND(sum(Quantity*Unit_Price+(Quantity*Unit_Price*+Additional_Cost/100)),2) FROM material WHERE Job_No = job.Job_No), 0) AS Matsubtotal,

(Labsubtotal+Matsubtotal) AS Subtotal,

(Subtotal*VAT/100) AS VATAmount,

(Subtotal+VATAmount) AS Total

FROM job
S
 		swanside author 10/14/2015

Thanks Sergey.
I tried the code you said below, then I get the error Unknown Colunm 'Labsubtotal' in field list. So I then add ' either side of the fields, but the maths wont work on it until I add the SELECT statement at the star of the other three fields.

Thanks anyway. <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=78009&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />

SELECT

Job_No,

IFNULL((SELECT ROUND(sum(Invoice),2) FROM labour WHERE Job_No = job.Job_No), 0) AS 'Labsubtotal',

IFNULL((SELECT ROUND(sum(Quantity*Unit_Price+(Quantity*Unit_Price*+Additional_Cost/100)),2) FROM material WHERE Job_No = job.Job_No), 0) AS 'Matsubtotal',

('Labsubtotal'+'Matsubtotal') AS 'Subtotal',

('Subtotal'*VAT/100) AS 'VATAmount',

('Subtotal'+'VATAmount') AS 'Total'

FROM job




One more thing - there are excessive selects in your query. This one should work better:

SELECT

Job_No,

IFNULL((SELECT ROUND(sum(Invoice),2) FROM labour WHERE Job_No = job.Job_No), 0) AS Labsubtotal,

IFNULL((SELECT ROUND(sum(Quantity*Unit_Price+(Quantity*Unit_Price*+Additional_Cost/100)),2) FROM material WHERE Job_No = job.Job_No), 0) AS Matsubtotal,

(Labsubtotal+Matsubtotal) AS Subtotal,

(Subtotal*VAT/100) AS VATAmount,

(Subtotal+VATAmount) AS Total

FROM job
Sergey Kornilov admin 10/14/2015

You do not need to use quotes but instead of Labtotals in line 5 of your query you need to use the full expression like IFNULL((SELECT ROUND(sum(Invoice),2) FROM labour WHERE Job_No = job.Job_No), 0)
i.e.

SELECT

Job_No,

IFNULL((SELECT ROUND(sum(Invoice),2) FROM labour WHERE Job_No = job.Job_No), 0) AS Labsubtotal,

IFNULL((SELECT ROUND(sum(Quantity*Unit_Price+(Quantity*Unit_Price*+Additional_Cost/100)),2) FROM material WHERE Job_No = job.Job_No), 0) AS Matsubtotal,

(IFNULL((SELECT ROUND(sum(Invoice),2) FROM labour WHERE Job_No = job.Job_No), 0)+IFNULL((SELECT ROUND(sum(Quantity*Unit_Price+(Quantity*Unit_Price*+Additional_Cost/100)),2) FROM material WHERE Job_No = job.Job_No), 0)) AS Subtotal,

...
S
 		swanside author 10/15/2015

Cheers Sergey,

Owe you a pint for that <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=78021&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />



You do not need to use quotes but instead of Labtotals in line 5 of your query you need to use the full expression like IFNULL((SELECT ROUND(sum(Invoice),2) FROM labour WHERE Job_No = job.Job_No), 0)
i.e.

SELECT

Job_No,

IFNULL((SELECT ROUND(sum(Invoice),2) FROM labour WHERE Job_No = job.Job_No), 0) AS Labsubtotal,

IFNULL((SELECT ROUND(sum(Quantity*Unit_Price+(Quantity*Unit_Price*+Additional_Cost/100)),2) FROM material WHERE Job_No = job.Job_No), 0) AS Matsubtotal,

(IFNULL((SELECT ROUND(sum(Invoice),2) FROM labour WHERE Job_No = job.Job_No), 0)+IFNULL((SELECT ROUND(sum(Quantity*Unit_Price+(Quantity*Unit_Price*+Additional_Cost/100)),2) FROM material WHERE Job_No = job.Job_No), 0)) AS Subtotal,

...