Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

MySQL Subquery Nulls

The query below works except for one column 'Last PR'. For that column it returns 'NULL" when t.UM != 'LS'  There is data in the table (pcacpr) so it should not return null. The column is called on Line 12 and the subquery is on Lines 69-79. I've uploaded a mysql dump with tables and data.

SELECT
CONCAT(s.SCCODE," ",s.SCC_DESC) 'Desc',
t.UM 'UoM',
FORMAT(c.POPU, 0) 'Orig Qty',
IF(t.UM = 'LS', 1, ROUND(c.POUEST, 0)) 'Orig PR',
FORMAT(((c.POPU / c.POUEST) * c.POEST), 0) 'Est Cost',
FORMAT(IF(t.UM = 'LS', c.Cost_JTD, Placed_JTD), 0) 'Placed JTD', 
IF(t.UM = 'LS', 1, FORMAT(Placed_JTD/Hours_JTD, 0)) 'Actual PR',
CONCAT('$', FORMAT(c.Cost_JTD, 0)) 'COST JTD',
CONCAT(FORMAT(IF(t.UM = 'LS', ((c.Cost_JTD/(LS_Updated_Variance + c.POPU)) * 100), ((Placed_JTD/Updated_JTD_Qty) * 100)), 0), '%') 'Pct Comp',
FORMAT(IF(t.UM = 'LS', (LS_Updated_Variance + c.POPU), Updated_JTD_Qty), 0) 'Updated JTD Qty',
FORMAT(IF(t.UM = 'LS', 1, Rate_Last), 0) 'Last PR',
IF(t.UM = 'LS' OR t.UM = 'GC', CONCAT('$', FORMAT(SUM(c.POPU/c.POUEST)*c.POEST, 0)), 
   IF(POUEST = 0, 0, CONCAT('$', FORMAT(SUM((Updated_JTD_Qty)/c.POUEST)*c.POEST, 0)))) 'Updated Est Cost',   
CONCAT('$', FORMAT(IF(t.UM = 'LS', (LS_Updated_Variance + c.POPU), (c.Cost_JTD + c.POEST * ((Updated_JTD_Qty-Placed_JTD)/c.POUEST))), 0)) 'PCAC',
CONCAT('$', FORMAT(IF(t.UM = 'LS', LS_Updated_Variance, (c.Cost_JTD + c.POEST * ((Updated_JTD_Qty-Placed_JTD)/c.POUEST)) - SUM((Updated_JTD_Qty)/c.POUEST)*c.POEST), 0)) 'Updated Variance'
FROM ccode c
LEFT JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
LEFT JOIN sctype t ON s.SCTYPE = t.SCTYPE_ID
LEFT JOIN job j ON c.JOB_ID = j.JOB_ID
LEFT JOIN (SELECT SUBSTRING(s.SCCODE,1,5) PLACED, SUM(m.PLACEMENT) Placed_JTD 
   FROM materials m
   JOIN `release` r ON m.RELEASE_ID = r.RELEASE_ID
   JOIN ccode c ON r.CCODE_ID = c.CCODE_ID
   JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
   JOIN sctype t ON s.SCTYPE = t.SCTYPE_ID
   JOIN job j ON m.JOB_ID = j.JOB_ID
   WHERE j.JOB_ID = 7398
   AND m.DATE_PLACE <= '2014-04-20'
   AND m.DELETED = 'N'
   AND c.POPU > 0
GROUP BY SUBSTRING(s.SCCODE,1,5)) sub_query ON SUBSTRING(s.SCCODE,1,5) = sub_query.PLACED
LEFT JOIN (SELECT SUBSTRING(s.SCCODE,1,5) COST, c.COST_JTD Cost_JTD
   FROM ccode c, sccode s
   WHERE s.SCCODE_ID = c.SCCODE_ID
   AND s.STYPE = 0
   AND c.POPU > 0
   AND c.JOB_ID = 7398 
   AND c.DELETED = 'N'
GROUP BY SUBSTRING(s.SCCODE,1,5)) sub_query1 ON SUBSTRING(s.SCCODE,1,5) = sub_query1.COST
LEFT JOIN (SELECT SUBSTRING(s.SCCODE,1,5) UPDATED, c.POPU + SUM(pq.AMOUNT) Updated_JTD_Qty
   FROM pcacqty pq
   JOIN ccode c ON c.CCODE_ID = pq.CCODE_ID
   JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
   JOIN job j ON j.JOB_ID = c.JOB_ID
   AND j.JOB_ID = 7398
   AND pq.DELETED = 'N'
   AND c.POPU > 0
GROUP BY SUBSTRING(s.SCCODE,1,5)) sub_query2 ON SUBSTRING(s.SCCODE,1,5) = sub_query2.UPDATED
LEFT JOIN (SELECT SUBSTRING(s.SCCODE,1,5) HOURS, SUM(l.HOURS) Hours_JTD
   FROM labor l
   LEFT JOIN ccode c ON c.CCODE_ID = l.CCODE_ID
   LEFT JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
   LEFT JOIN job j ON j.JOB_ID = l.JOB_ID
   WHERE l.JOB_ID = 7398
   AND l.DATE_WORK <= '2014-04-20'
   AND l.DELETED = 'N'
   AND s.SCTYPE = s.SCTYPE
GROUP BY SUBSTRING(s.SCCODE,1,5)) sub_query3 ON SUBSTRING(s.SCCODE,1,5) = sub_query3.HOURS
LEFT JOIN (SELECT SUBSTRING(s.SCCODE,1,5) LS, SUM(ls.LUMPSUM) LS_Updated_Variance
   FROM pcacls ls
   JOIN ccode c ON c.CCODE_ID = ls.CCODE_ID
   JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
   JOIN job j ON j.JOB_ID = c.JOB_ID
   AND j.JOB_ID = 7398
   AND ls.DELETED = 'N'
   AND c.POPU > 0
GROUP BY SUBSTRING(s.SCCODE,1,5)) sub_query4 ON SUBSTRING(s.SCCODE,1,5) = sub_query4.LS
LEFT JOIN (SELECT SUBSTRING(s.SCCODE,1,5) RATE, pr.RATE Rate_Last
   FROM pcacpr pr
   LEFT JOIN ccode c ON c.CCODE_ID = pr.CCODE_ID
   LEFT JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
   LEFT JOIN job j ON j.JOB_ID = c.JOB_ID
   AND pr.DELETED = 'N'
   AND pr.TIMESTAMP <= '2014-04-20'
   AND j.JOB_ID = 7398
   GROUP BY SUBSTRING(s.SCCODE,1,5)
   ORDER BY pr.PCACPR_ID DESC
   LIMIT 1) sub_query5 ON SUBSTRING(s.SCCODE,1,5) = sub_query5.RATE
WHERE j.JOB_ID = 7398
AND c.DELETED = 'N'
AND c.POPU > 0
GROUP BY SUBSTRING(s.SCCODE,1,5)
ORDER BY s.SCTYPE, s.SCCODE;

Open in new window

jds-s-0427.sql
0
hdcowboyaz
Asked:
hdcowboyaz
  • 2
  • 2
1 Solution
 
chaauCommented:
You have not uploaded your pcacpr table
0
 
SharathData EngineerCommented:
Display t.UM,Rate_Last data in SELECT clause and check if what results it is returing. Check if t.UM is LS or not.
SELECT
CONCAT(s.SCCODE," ",s.SCC_DESC) 'Desc',
t.UM 'UoM',
FORMAT(c.POPU, 0) 'Orig Qty',
IF(t.UM = 'LS', 1, ROUND(c.POUEST, 0)) 'Orig PR',
FORMAT(((c.POPU / c.POUEST) * c.POEST), 0) 'Est Cost',
FORMAT(IF(t.UM = 'LS', c.Cost_JTD, Placed_JTD), 0) 'Placed JTD', 
IF(t.UM = 'LS', 1, FORMAT(Placed_JTD/Hours_JTD, 0)) 'Actual PR',
CONCAT('$', FORMAT(c.Cost_JTD, 0)) 'COST JTD',
CONCAT(FORMAT(IF(t.UM = 'LS', ((c.Cost_JTD/(LS_Updated_Variance + c.POPU)) * 100), ((Placed_JTD/Updated_JTD_Qty) * 100)), 0), '%') 'Pct Comp',
FORMAT(IF(t.UM = 'LS', (LS_Updated_Variance + c.POPU), Updated_JTD_Qty), 0) 'Updated JTD Qty',
FORMAT(IF(t.UM = 'LS', 1, Rate_Last), 0) 'Last PR',
IF(t.UM = 'LS' OR t.UM = 'GC', CONCAT('$', FORMAT(SUM(c.POPU/c.POUEST)*c.POEST, 0)), 
   IF(POUEST = 0, 0, CONCAT('$', FORMAT(SUM((Updated_JTD_Qty)/c.POUEST)*c.POEST, 0)))) 'Updated Est Cost',   
CONCAT('$', FORMAT(IF(t.UM = 'LS', (LS_Updated_Variance + c.POPU), (c.Cost_JTD + c.POEST * ((Updated_JTD_Qty-Placed_JTD)/c.POUEST))), 0)) 'PCAC',
CONCAT('$', FORMAT(IF(t.UM = 'LS', LS_Updated_Variance, (c.Cost_JTD + c.POEST * ((Updated_JTD_Qty-Placed_JTD)/c.POUEST)) - SUM((Updated_JTD_Qty)/c.POUEST)*c.POEST), 0)) 'Updated Variance'
,t.UM,Rate_Last
FROM ccode c
LEFT JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
LEFT JOIN sctype t ON s.SCTYPE = t.SCTYPE_ID
LEFT JOIN job j ON c.JOB_ID = j.JOB_ID
LEFT JOIN (SELECT SUBSTRING(s.SCCODE,1,5) PLACED, SUM(m.PLACEMENT) Placed_JTD 
   FROM materials m
   JOIN `release` r ON m.RELEASE_ID = r.RELEASE_ID
   JOIN ccode c ON r.CCODE_ID = c.CCODE_ID
   JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
   JOIN sctype t ON s.SCTYPE = t.SCTYPE_ID
   JOIN job j ON m.JOB_ID = j.JOB_ID
   WHERE j.JOB_ID = 7398
   AND m.DATE_PLACE <= '2014-04-20'
   AND m.DELETED = 'N'
   AND c.POPU > 0
GROUP BY SUBSTRING(s.SCCODE,1,5)) sub_query ON SUBSTRING(s.SCCODE,1,5) = sub_query.PLACED
LEFT JOIN (SELECT SUBSTRING(s.SCCODE,1,5) COST, c.COST_JTD Cost_JTD
   FROM ccode c, sccode s
   WHERE s.SCCODE_ID = c.SCCODE_ID
   AND s.STYPE = 0
   AND c.POPU > 0
   AND c.JOB_ID = 7398 
   AND c.DELETED = 'N'
GROUP BY SUBSTRING(s.SCCODE,1,5)) sub_query1 ON SUBSTRING(s.SCCODE,1,5) = sub_query1.COST
LEFT JOIN (SELECT SUBSTRING(s.SCCODE,1,5) UPDATED, c.POPU + SUM(pq.AMOUNT) Updated_JTD_Qty
   FROM pcacqty pq
   JOIN ccode c ON c.CCODE_ID = pq.CCODE_ID
   JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
   JOIN job j ON j.JOB_ID = c.JOB_ID
   AND j.JOB_ID = 7398
   AND pq.DELETED = 'N'
   AND c.POPU > 0
GROUP BY SUBSTRING(s.SCCODE,1,5)) sub_query2 ON SUBSTRING(s.SCCODE,1,5) = sub_query2.UPDATED
LEFT JOIN (SELECT SUBSTRING(s.SCCODE,1,5) HOURS, SUM(l.HOURS) Hours_JTD
   FROM labor l
   LEFT JOIN ccode c ON c.CCODE_ID = l.CCODE_ID
   LEFT JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
   LEFT JOIN job j ON j.JOB_ID = l.JOB_ID
   WHERE l.JOB_ID = 7398
   AND l.DATE_WORK <= '2014-04-20'
   AND l.DELETED = 'N'
   AND s.SCTYPE = s.SCTYPE
GROUP BY SUBSTRING(s.SCCODE,1,5)) sub_query3 ON SUBSTRING(s.SCCODE,1,5) = sub_query3.HOURS
LEFT JOIN (SELECT SUBSTRING(s.SCCODE,1,5) LS, SUM(ls.LUMPSUM) LS_Updated_Variance
   FROM pcacls ls
   JOIN ccode c ON c.CCODE_ID = ls.CCODE_ID
   JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
   JOIN job j ON j.JOB_ID = c.JOB_ID
   AND j.JOB_ID = 7398
   AND ls.DELETED = 'N'
   AND c.POPU > 0
GROUP BY SUBSTRING(s.SCCODE,1,5)) sub_query4 ON SUBSTRING(s.SCCODE,1,5) = sub_query4.LS
LEFT JOIN (SELECT SUBSTRING(s.SCCODE,1,5) RATE, pr.RATE Rate_Last
   FROM pcacpr pr
   LEFT JOIN ccode c ON c.CCODE_ID = pr.CCODE_ID
   LEFT JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
   LEFT JOIN job j ON j.JOB_ID = c.JOB_ID
   AND pr.DELETED = 'N'
   AND pr.TIMESTAMP <= '2014-04-20'
   AND j.JOB_ID = 7398
   GROUP BY SUBSTRING(s.SCCODE,1,5)
   ORDER BY pr.PCACPR_ID DESC
   LIMIT 1) sub_query5 ON SUBSTRING(s.SCCODE,1,5) = sub_query5.RATE
WHERE j.JOB_ID = 7398
AND c.DELETED = 'N'
AND c.POPU > 0
GROUP BY SUBSTRING(s.SCCODE,1,5)
ORDER BY s.SCTYPE, s.SCCODE;

Open in new window

0
 
hdcowboyazAuthor Commented:
Actually, 3 tables had to be added; pcacls, pcacqty & pcacpr They are now attached as a MySQL dump.

Use this query as the job, JOB_ID = 6746 has pcacpr data.

SELECT
CONCAT(s.SCCODE," ",s.SCC_DESC) 'Desc',
t.UM 'UoM',
FORMAT(c.POPU, 0) 'Orig Qty',
IF(t.UM = 'LS', 1, ROUND(c.POUEST, 0)) 'Orig PR',
FORMAT(((c.POPU / c.POUEST) * c.POEST), 0) 'Est Cost',
FORMAT(IF(t.UM = 'LS', c.Cost_JTD, Placed_JTD), 0) 'Placed JTD', 
IF(t.UM = 'LS', 1, FORMAT(Placed_JTD/Hours_JTD, 0)) 'Actual PR',
CONCAT('$', FORMAT(c.Cost_JTD, 0)) 'COST JTD',
CONCAT(FORMAT(IF(t.UM = 'LS', ((c.Cost_JTD/(LS_Updated_Variance + c.POPU)) * 100), ((Placed_JTD/Updated_JTD_Qty) * 100)), 0), '%') 'Pct Comp',
FORMAT(IF(t.UM = 'LS', (LS_Updated_Variance + c.POPU), Updated_JTD_Qty), 0) 'Updated JTD Qty',
FORMAT(IF(t.UM = 'LS', 1, Rate_Last), 0) 'Last PR',
IF(t.UM = 'LS' OR t.UM = 'GC', CONCAT('$', FORMAT(SUM(c.POPU/c.POUEST)*c.POEST, 0)), 
   IF(POUEST = 0, 0, CONCAT('$', FORMAT(SUM((Updated_JTD_Qty)/c.POUEST)*c.POEST, 0)))) 'Updated Est Cost',   
CONCAT('$', FORMAT(IF(t.UM = 'LS', (LS_Updated_Variance + c.POPU), (c.Cost_JTD + c.POEST * ((Updated_JTD_Qty-Placed_JTD)/c.POUEST))), 0)) 'PCAC',
CONCAT('$', FORMAT(IF(t.UM = 'LS', LS_Updated_Variance, (c.Cost_JTD + c.POEST * ((Updated_JTD_Qty-Placed_JTD)/c.POUEST)) - SUM((Updated_JTD_Qty)/c.POUEST)*c.POEST), 0)) 'Updated Variance'
FROM ccode c
LEFT JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
LEFT JOIN sctype t ON s.SCTYPE = t.SCTYPE_ID
LEFT JOIN job j ON c.JOB_ID = j.JOB_ID
LEFT JOIN (SELECT SUBSTRING(s.SCCODE,1,5) PLACED, SUM(m.PLACEMENT) Placed_JTD 
   FROM materials m
   JOIN `release` r ON m.RELEASE_ID = r.RELEASE_ID
   JOIN ccode c ON r.CCODE_ID = c.CCODE_ID
   JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
   JOIN sctype t ON s.SCTYPE = t.SCTYPE_ID
   JOIN job j ON m.JOB_ID = j.JOB_ID
   WHERE j.JOB_ID = 6746
   AND m.DATE_PLACE <= '2014-04-20'
   AND m.DELETED = 'N'
   AND c.POPU > 0
GROUP BY SUBSTRING(s.SCCODE,1,5)) sub_query ON SUBSTRING(s.SCCODE,1,5) = sub_query.PLACED
LEFT JOIN (SELECT SUBSTRING(s.SCCODE,1,5) COST, c.COST_JTD Cost_JTD
   FROM ccode c, sccode s
   WHERE s.SCCODE_ID = c.SCCODE_ID
   AND s.STYPE = 0
   AND c.POPU > 0
   AND c.JOB_ID = 6746 
   AND c.DELETED = 'N'
GROUP BY SUBSTRING(s.SCCODE,1,5)) sub_query1 ON SUBSTRING(s.SCCODE,1,5) = sub_query1.COST
LEFT JOIN (SELECT SUBSTRING(s.SCCODE,1,5) UPDATED, c.POPU + SUM(pq.AMOUNT) Updated_JTD_Qty
   FROM pcacqty pq
   JOIN ccode c ON c.CCODE_ID = pq.CCODE_ID
   JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
   JOIN job j ON j.JOB_ID = c.JOB_ID
   AND j.JOB_ID = 6746
   AND pq.DELETED = 'N'
   AND c.POPU > 0
GROUP BY SUBSTRING(s.SCCODE,1,5)) sub_query2 ON SUBSTRING(s.SCCODE,1,5) = sub_query2.UPDATED
LEFT JOIN (SELECT SUBSTRING(s.SCCODE,1,5) HOURS, SUM(l.HOURS) Hours_JTD
   FROM labor l
   LEFT JOIN ccode c ON c.CCODE_ID = l.CCODE_ID
   LEFT JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
   LEFT JOIN job j ON j.JOB_ID = l.JOB_ID
   WHERE l.JOB_ID = 6746
   AND l.DATE_WORK <= '2014-04-20'
   AND l.DELETED = 'N'
   AND s.SCTYPE = s.SCTYPE
GROUP BY SUBSTRING(s.SCCODE,1,5)) sub_query3 ON SUBSTRING(s.SCCODE,1,5) = sub_query3.HOURS
LEFT JOIN (SELECT SUBSTRING(s.SCCODE,1,5) LS, SUM(ls.LUMPSUM) LS_Updated_Variance
   FROM pcacls ls
   JOIN ccode c ON c.CCODE_ID = ls.CCODE_ID
   JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
   JOIN job j ON j.JOB_ID = c.JOB_ID
   AND j.JOB_ID = 6746
   AND ls.DELETED = 'N'
   AND c.POPU > 0
GROUP BY SUBSTRING(s.SCCODE,1,5)) sub_query4 ON SUBSTRING(s.SCCODE,1,5) = sub_query4.LS
LEFT JOIN (SELECT SUBSTRING(s.SCCODE,1,5) RATE, pr.RATE Rate_Last
   FROM pcacpr pr
   LEFT JOIN ccode c ON c.CCODE_ID = pr.CCODE_ID
   LEFT JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
   LEFT JOIN job j ON j.JOB_ID = c.JOB_ID
   AND pr.DELETED = 'N'
   AND pr.TIMESTAMP <= '2014-04-20'
   AND j.JOB_ID = 6746
   GROUP BY SUBSTRING(s.SCCODE,1,5)
   ORDER BY pr.PCACPR_ID DESC
   LIMIT 1) sub_query5 ON SUBSTRING(s.SCCODE,1,5) = sub_query5.RATE
WHERE j.JOB_ID = 6746
AND c.DELETED = 'N'
AND c.POPU > 0
GROUP BY SUBSTRING(s.SCCODE,1,5)
ORDER BY s.SCTYPE, s.SCCODE;

Open in new window

pcac-0429.sql
0
 
chaauCommented:
I see what you are trying to do. You are trying to retrieve the latest Rate from your pcacpr table for each of SCCCODE. You are using an incorrect technique. By using LIMIT 1 you will restrict the sub_query5 to one record, and it may not be the one that is "current" in the main query. Instead, I recommend changing the sub_query5 to this:

   SELECT SUBSTRING(s.SCCODE,1,5) RATE, pr.RATE Rate_Last
   FROM pcacpr pr
   LEFT JOIN ccode c ON c.CCODE_ID = pr.CCODE_ID
   LEFT JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
   LEFT JOIN job j ON j.JOB_ID = c.JOB_ID
   LEFT JOIN
     ( SELECT SUBSTRING(s.SCCODE,1,5) RATE, MAX(PCACPR_ID) Last_PCACPR_ID
   FROM pcacpr pr LEFT JOIN ccode c ON c.CCODE_ID = pr.CCODE_ID
   LEFT JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID AND pr.DELETED = 'N'
   AND pr.TIMESTAMP <= '2014-04-20'
  GROUP BY SUBSTRING(s.SCCODE,1,5)) Last_pr ON SUBSTRING(s.SCCODE,1,5) = Last_pr.RATE AND pr.PCACPR_ID = Last_pr.Last_PCACPR_ID
   AND j.JOB_ID = 6746
   GROUP BY SUBSTRING(s.SCCODE,1,5)

Open in new window


Replace the statement above as your sub_query5 and see if it makes any difference
0
 
hdcowboyazAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now