0

I need help only retrieving one record per job in this dataset. There are over 900 rows in the actual result set so this is a small sample but all rows look like the below. I am already using Distinct in the query but I get more than one record for the same job,suffix,and part. I need to be able to pull only one job,suffix,part but I need each value for workcenter,hours_estimated, and hours_actual associated with each job. I'm not sure if this should/could be done in the query or by using array methods in PHP. I almost found what I am looking for here but pervasive doesn't use CTE. Can anyone assist me in trying to get my data to look like below?

Sample Data

|Job   |suffix| part |PL|qty|  seq |workcenter|hours_estimated|hours_actual|
----------------------------------------------------------------------------
|A02043| 001  |913036|01| 2 |000400|   0710   |     0.7491    |   2.5700   |
|A02043| 001  |913036|01| 2 |000402|   0805   |     0.6420    |   0.0000   |
|A02043| 001  |913036|01| 2 |000500|   0901   |     16.1290   |   33.1600  |
|A02043| 001  |913036|01| 2 |000600|   1520   |     0.5000    |   0.0000   |
|A02900| 001  |913104|01| 1 |000500|   0710   |     0.5280    |   1.2000   |
|A02900| 001  |913104|01| 1 |000600|   0650   |     0.8540    |   0.0000   |

What I need my PDO object/array to look like

|Job   |suffix| part |PL|qty|   workcenter      |     hours_estimated         | hours_actual             |
-------------------------------------------------------------------------------------------------------------------
|A02043| 001  |913036|01| 2 |0710,0805,0901,1520| 0.7491, 0.6420,16.1290,0.500| 2.57,0,33.16,0  2.5700   |
|A02900| 001  |913104|01| 1 |0710,0650          |0.5280,0.8540                |1.200,0.0000   |

Query:

select distinct v_job_header.job,v_job_header.suffix,v_job_header.part,v_job_header.product_line,v_job_header.qty_order,v_job_operations_wc.seq,v_job_operations_wc.LMO,v_job_operations_wc.workcenter,v_job_operations_wc.hours_estimated,v_job_operations_wc.hours_actual,v_job_operations_wc.flag_closed,gab_source_cause_codes.source,gab_source_cause_codes.cause
from v_job_header
left join v_job_operations_wc on v_job_operations_wc.job = v_job_header.job and v_job_header.suffix = v_job_operations_wc.suffix
left join gab_source_cause_codes on gab_source_cause_codes.job = v_job_operations_wc.job and gab_source_cause_codes.suffix = v_job_operations_wc.suffix and gab_source_cause_codes.seq = v_job_operations_wc.seq
where v_job_header.product_line = '01' and v_job_header.date_closed < '2019-01-01' and v_job_operations_wc.LMO = 'L' and v_job_operations_wc.seq < '99000'
SkylarP
  • 39
  • 1
  • 8
  • Look into using group_concat so for example workcenter would be group_concat(table.workcenter) as workcenter ….. – Yeak Jun 23 '22 at 03:43
  • @Yeak Thank you for the suggestion. While pervasive doesn't have a group_concat, I was able to find a few people who had a similar issue and I was able to work out a solution. Thanks! – SkylarP Jun 24 '22 at 16:29
  • Glad you got it worked out - sorry must of skipped the part of pervasive and assumed mysql. – Yeak Jun 24 '22 at 16:34
  • add `TOP 1` , see: https://docs.actian.com/psql/psqlv11/wwhelp/wwhimpl/js/html/wwhelp.htm#href=sqlref/syntaxref.03.71.html – Luuk Jul 06 '22 at 17:29

1 Answers1

0

I had to change my query

select concat(concat(v_job_header.job,'-'),v_job_header.suffix) as Job,v_job_header.part,v_job_header.qty_order,
sum(case when v_job_operations_wc.workcenter = '0750' then v_job_operations_wc.hours_actual end) as WaterJet,
sum(case when v_job_operations_wc.workcenter IN ('0705','0710','0715') then v_job_operations_wc.hours_actual end) as Laser,
sum(case when v_job_operations_wc.workcenter IN ('0600','0610','1006','0650','1315') then v_job_operations_wc.hours_actual end) as Prep,
sum(case when v_job_operations_wc.workcenter IN ('1310','0755') then v_job_operations_wc.hours_actual end) as Machining,
sum(case when v_job_operations_wc.workcenter IN ('1515','1000','1002','1003','0901','1270') then v_job_operations_wc.hours_actual end) as Fab,
sum(case when v_job_operations_wc.workcenter = '1100' then v_job_operations_wc.hours_actual end) as Paint,
sum(case when v_job_operations_wc.workcenter = '1000' then v_job_operations_wc.hours_actual end) as Belts,
sum(case when v_job_operations_wc.workcenter = '1001' then v_job_operations_wc.hours_actual end) as Electrical,
sum(case when v_job_operations_wc.workcenter = '1520' then v_job_operations_wc.hours_actual end) as Crating_Skids,
sum(case when v_job_operations_wc.workcenter IN ('1004','1005','1350','1201') then v_job_operations_wc.hours_actual end) as Final_Assy,
sum(case when v_job_operations_wc.workcenter = '4330' then v_job_operations_wc.hours_actual end) as Shipping,
sum(v_job_operations_wc.hours_estimated) as total_hours_estimated,
gab_source_cause_codes.source,gab_source_cause_codes.cause
from v_job_header
left join v_job_operations_wc on v_job_operations_wc.job = v_job_header.job and v_job_header.suffix = v_job_operations_wc.suffix
left join gab_source_cause_codes on gab_source_cause_codes.job = v_job_operations_wc.job and gab_source_cause_codes.suffix = v_job_operations_wc.suffix and gab_source_cause_codes.seq = v_job_operations_wc.seq
where v_job_header.product_line = '01' and v_job_header.date_closed < '2019-01-01' and v_job_operations_wc.LMO = 'L' and v_job_operations_wc.seq < '99000'
group by Job,v_job_header.part,v_job_header.qty_order,gab_source_cause_codes.source,gab_source_cause_codes.cause
SkylarP
  • 39
  • 1
  • 8