Solved

MySQL Subquery Nulls

Posted on 2014-04-29
6
301 Views
Last Modified: 2014-04-30
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
Comment
Question by:hdcowboyaz
  • 2
  • 2
6 Comments
 
LVL 24

Expert Comment

by:chaau
ID: 40031270
You have not uploaded your pcacpr table
0
 
LVL 40

Expert Comment

by:Sharath
ID: 40031307
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
 

Author Comment

by:hdcowboyaz
ID: 40031314
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
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 40031342
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
 

Author Closing Comment

by:hdcowboyaz
ID: 40031401
Thanks
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video discusses moving either the default database or any database to a new volume.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now