asked on

Need to combine the values of two columns into one

Need to combine the values of two columns(ro_dept and wo_dept) into one column DEPT_NAME with their corresponding record.

Ex. Instead of:
SM_NUMBER ORDER_TYPE WO_DEPT RO_DEPT
1000 WO SH ELE
1100 RO PURCH

Need to see:
SM_NUMBER ORDER_TYPE DEPT_NAME
1000 WO SH ELE
1100 RO PURCH

Any help will be appreciated.

SELECT
    sm_header.sm_number,
    DECODE(sm_header.woo_auto_key,'',DECODE(sm_header.soh_auto_key,'',DECODE(sm_header.roh_auto_key,'',DECODE(sm_header.whs_from,'',''
,'WHS'),'RO'),'SO'),'WO') order_type,
    DECODE(sm_header.woo_auto_key,'',DECODE(sm_header.soh_auto_key,'',DECODE(sm_header.roh_auto_key,'','',ro_header.ro_number),so_header
.so_number),wo_operation.si_number) order_number,
    sm_status.description status,
    sm_header.entry_date entry_date,
    sm_header.date_created,
    sm_header.ship_date ship_date,
    sm_header.ship_timestamp,
    sm_header.ship_name,
    ship_via_codes.ship_via_code,
    sm_header.tracking_number,
    sm_header.number_of_packages,
    so_header.dpt_auto_key,
    sm_header.total_weight,
    sm_header.ship_date - sm_header.entry_date time_to_ship_days,
    round( (sm_header.ship_timestamp - sm_header.date_created) * 24,2) time_to_ship_hours,
    warehouse_from.description whs_from,
    warehouse_to.description whs_to,
    parts_master.pn,
    parts_master.description,
    sm_header.smh_auto_key,
    sm_detail.smd_auto_key,
    so_header.open_flag open,
    sm_header.soh_auto_key,
    so_header.due_date,
    ro_header.roh_auto_key,
    wo_operation.wo_type,
    wo_operation.priority,
    sm_header.sysur_auto_key,
    qctl.department.dept_name ro_dept,
    department1.dept_name wo_dept
FROM
    sm_header left
    JOIN so_header ON sm_header.soh_auto_key = so_header.soh_auto_key
    LEFT JOIN sm_detail ON sm_header.smh_auto_key = sm_detail.smh_auto_key
    LEFT JOIN parts_master ON sm_detail.pnm_auto_key = parts_master.pnm_auto_key
    LEFT JOIN ro_header ON sm_header.roh_auto_key = ro_header.roh_auto_key
    LEFT JOIN wo_operation ON sm_header.woo_auto_key = wo_operation.woo_auto_key
    LEFT JOIN sm_status ON sm_header.sms_auto_key = sm_status.sms_auto_key
    LEFT JOIN warehouse warehouse_from ON sm_header.whs_from = warehouse_from.whs_auto_key
    LEFT JOIN warehouse warehouse_to ON sm_header.whs_to = warehouse_to.whs_auto_key
    LEFT JOIN ship_via_codes ON sm_header.svc_auto_key = ship_via_codes.svc_auto_key
    LEFT JOIN companies ON sm_header.cmp_auto_key = companies.cmp_auto_key
    LEFT JOIN qctl.department ON ro_header.dpt_auto_key = qctl.department.dpt_auto_key
    LEFT JOIN qctl.department department1 ON wo_operation.dpt_auto_key = department1.dpt_auto_key

Open in new window

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

D B

Paul's code assumes that the department that does not have a value is NULL. If the missing department is just an empty string, it will not work because an empty string is not NULL.
If the missing depatartment is an empty string, use:

select ...
, NVL(NULLIF(WO_DEPT, '') ,NULLIF(RO_DEPT, '')) as DEPT_NAME

Open in new window

johnsone

This is posted to an Oracle topic. Oracle does not distinguish between an empty string and NULL. In Oracle an empty string is treated as NULL.

Try it:

select nvl2('', 'NOT NULL','NULL') from dual;
select nvl('','Parameter is null') from dual;
select coalesce('','Parameter is null') from dual;

D B

Guess that's why I'm primarily a SQL Server developer. NULL is an unknown. Am empty strong isn't.