0

Problem: My Query returns NULL Potential issues: Subquery formatted wrong and only works if some data I want to sort out is in the dataset.

Example of today's code:

 SELECT production_order
,SUM(total_working_time_h) - (SELECT SUM(total_working_time_h) FROM {$table} WHERE production_order = '$production_order' AND (station = '出货检验 | OQC' OR production_type = 'Rework')) AS total_working_time_h_edit
,SUM(no_of_defects)         AS no_of_defects_during_production
FROM {$table}
WHERE production_order = '$production_order'

This works great as long as I have either "Rework" and/or "'出货检验 | OQC'" logged in my database for this production order. If not, I won't get any data at all but NULL. So my problem is somewhere in my subquery I think:

 SELECT SUM(total_working_time_h) FROM {$table} WHERE production_order = '$production_order' AND (station = '出货检验 | OQC' OR production_type = 'Rework')

I've tried adding a "0" to make sure it's get 0 and not NULL without success like follows:

(0 + SELECT SUM(total_working_time_h) FROM {$table} WHERE     production_order = '$production_order' AND (station = '出货检验 | OQC' OR     production_type = 'Rework')

Example dataset formated as text:

production_order    part_nr         station                            total_working_time_h 
26135               129-108816B-UL  压接 | Crimping                     1.42
26135               129-108816B     线束组装 | Harness Assembling        7.67
26135               129-108816      测试 | Testing                       0.83
26135               129-108816B     外观全检 | Appearance inspection      0.83

Gives this output:

production_order,total_working_time_h_edit
26135, NULL

I want this output: 

production_order,total_working_time_h_edit
26135, 10,45

If my dataset hold the station I want to sort out or the production_type I want to sort out everything works as it should. But if both of those is missing in the dataset it returns NULL, since what I think is because the subquery returns 0.

An example of different datasets I'm using. If I have the data marked with green cells in the dataset it will work. If not, I get the return NULL as above.

So how to make my subquery to not return NULL?

Data

Johannes Löfgren
  • 43
  • 6
  • `WHERE station = '出货检验 | OQC'` ... are you intending to use pipe as part of the station name? Or do you think this will `OR` together `出货检验` and `OQC` ? – Tim Biegeleisen Sep 08 '16 at 01:55
  • The pipe is part of the name. '出货检验 | OQC' should be seen as a name in total. – Johannes Löfgren Sep 08 '16 at 02:00
  • Show a table with sample data along with your desired output. You most likely have a small problem in the query. – Tim Biegeleisen Sep 08 '16 at 02:03
  • Some examples added. When the data marked in green is included it all works. It I don't have any of this data in my dataset my query won't work, or at least won't return any value. @TimBiegeleisen Dataset 1-4 will work, 5 won't. – Johannes Löfgren Sep 08 '16 at 02:22
  • You're getting closer, now you just need to show us what output you want. Your description was really too long, and not so clear. – Tim Biegeleisen Sep 08 '16 at 02:24
  • Don't post images, post formatted text. Add four spaces in front of each line of the tables. – Tim Biegeleisen Sep 08 '16 at 02:26
  • I want to get the following: (Sum of all time on a production order) - (Sum of all time on this production order logged as Rework OR OQC) This query works just fine IF one of them (Rework or OQC ) exists in the dataset, but it does not work if none of them exists in the dataset. Then I get the result NULL instead of the expected total time as "hours,decimal" Regarding the format as text, I never manage to make a dataset look proper when I paste it. Therefore the pictures to at make it readable. – Johannes Löfgren Sep 08 '16 at 04:05
  • I asked for expected output simply because your description of the problem, and what you want, is not completely clear, and could result in a bad answer. Please post a table below the one you already have showing what output you want. – Tim Biegeleisen Sep 08 '16 at 04:07
  • I have rewritten the whole questions since you down voted me while I was adjusting the whole question since you never understood. I also managed to solve the question myself by using one of the fuctions I listed as one of my problems. @TimBiegeleisen – Johannes Löfgren Sep 08 '16 at 05:37
  • Thanks for spending time with me on this anyway. :) @TimBiegeleisen – Johannes Löfgren Sep 08 '16 at 05:43

1 Answers1

1

Managed to solve the question myself.

Problem: Subquery returned NULL instead of 0.

Solution: Added COALESCE(,0) to my sub querys function SUM() like this:

 COALESCE(SUM(total_working_time_h),0)

By doing so it returns 0 instead of NULL when the SUM() actually wants to return NULL. That solved my problem.

Johannes Löfgren
  • 43
  • 6