Complex problem . Nested stuff...I think

[bigracefan]

Here is a synopsis of what I'm trying to do: I need to total part numbers (BOM) in a given time range.
Here is table 1:

ecu_quality_audit

PCB_SERIAL_NUMBER NUMBER(10)
MODEL_NUMBER VARCHAR2(4)
LABEL_SERIAL_NUMBER NUMBER(6)
STATUS VARCHAR2(1)
AUDIT_DATE_TIME DATE
AUDIT_REASON VARCHAR2(100)
AUDITED_BY NUMBER(5)
RELEASE_DATE_TIME DATE
RELEASE_REASON VARCHAR2(100)
RELEASED_BY NUMBER(5)

Here is table 2:
ECU_Prod_detail

PCB_SERIAL_NUMBER NUMBER(14)
STEP_MACHINE NUMBER
MODEL_NUMBER VARCHAR2(4)
MODEL_YEAR NUMBER(4)
LABEL_SERIAL_NUMBER NUMBER(6)
RACK NUMBER(4)
OPERATOR_CODE NUMBER(4)
IPP_NUMBER VARCHAR2(15)
PROD_TYPE VARCHAR2(1)
DISPOSITION VARCHAR2(1)
DESIGN_LEVEL VARCHAR2(4)
PROD_DATE_TIME DATE
STEP_COUNT NUMBER(38)

Here is table 3:

ecu_completions_bom

MODEL_NUMBER NOT NULL VARCHAR2(5)
MODEL_YEAR NOT NULL NUMBER(4)
MODEL_PROD_TYPE NOT NULL VARCHAR2(1)
PROD_DESIGN_LEVEL NOT NULL VARCHAR2(4)
ASMT_BOM VARCHAR2(25)
RH_BOM VARCHAR2(25)
BSMT_BOM VARCHAR2(25)
CONNECTOR_BOM VARCHAR2(25)
FRAME_BOM VARCHAR2(25)
FINAL_BOM VARCHAR2(50)

Now here is what I need to do. I just don't know how to pull it all together.

Select the units that are scraped in ecu_quality_audit

select * from ecu_quality_audit where status = 'S' and AUDIT_DATE_TIME > sysdate - 2

Select the maximum step_machine for each unit in ecu_prod_detail

select max(step_machine) from ecu_prod_detail where pcb_serial_number = 9001045165

Depending on the last step, choose the appropriate BOM

if ecu_prod_detail.last_prod_step is between 10000 and 20000 then
x = ‘ASMT_BOM’
elseif ecu_prod_detail.last_prod_step is between 20001 and 30000 then
x = ‘RH_BOM’
elseif ecu_prod_detail.last_prod_step is between 30001 and 40000 then
x = ‘BSMT_BOM’
elseif ecu_prod_detail.last_prod_step is between 40001 and 50000 then
x = ‘CONNECTOR_BOM’
elseif ecu_prod_detail.last_prod_step is between 50001 and 60000 then
x = ‘FRAME_BOM’
elseif ecu_prod_detail.last_prod_step is between 60001 and 70000 then
x = ‘FINAL_BOM’

select Bom above using the following information from ecu_prod_detail

(select * from ecu_completions_bom where model_number = ‘2602’ and model_year = 2003 and MODEL_PROD_TYPE = ‘T’ and PROD_DESIGN_LEVEL = ‘A540’ )

Final output should show the appropriate count of bom in a given time range based on ecu_quality_audit

Any ideas or help would be greatly appriciated.

Pete

 

[pasammon]

I think what you need to do is break it down into smaller steps

for instance this second query you can combine
-----------------------------------------------
select max(step_machine) from ecu_prod_detail where pcb_serial_number = 9001045165

with this query and create a view
---------------------------------------------------
if ecu_prod_detail.last_prod_step is between 10000 and 20000 then
x = ‘ASMT_BOM’
elseif ecu_prod_detail.last_prod_step is between 20001 and 30000 then
x = ‘RH_BOM’
elseif ecu_prod_detail.last_prod_step is between 30001 and 40000 then
x = ‘BSMT_BOM’
elseif ecu_prod_detail.last_prod_step is between 40001 and 50000 then
x = ‘CONNECTOR_BOM’
elseif ecu_prod_detail.last_prod_step is between 50001 and 60000 then
x = ‘FRAME_BOM’
elseif ecu_prod_detail.last_prod_step is between 60001 and 70000 then
x = ‘FINAL_BOM’


and repeat the steps for each one

you should have something like this
create view asmt_bom_view as
select max(step_machine) criteria from ecu_prod_detail where pcb_serial_number = 9001045165
and criteria between(10000 and 20000);

you'll have to double check the syntax on the between.


then all you need to do is to union your other 2 queries with the view

when you are all done you can write a script with the information you have of doing one and then make it general enough to create all the scipts you need to get at your required data.

P.S. hope this helps. Hope i understood your quandry correctly

In God We Trust
All Others Must Pay Cash