Error when tranferring data from Oracle to SQL Server using SISS or openquery

I keep receiving the error attached
errorapproximately 75% into transferring the view from Oracle to SQL Server 2016. I have tried using
INSERT INTO BA_VIEW_WO_SUMMARY
SELECT * FROM OPENQUERY(QUANTUM, 'SELECT * FROM BA_VIEW_WO_SUMMARY')

Open in new window

and via SISS package using Microsoft OLE DB provider for Oracle. The SQL in the oracle view is below. Any idea what can be the cause?

select woo.si_number wo_number
, woo.wo_type
, decode(woo.tear_down,'T','Y','N') teardown
, syscm.company_name acct_company
, dpt.dept_name
, case when dpt.dept_name like '%SUB%' then 'SUB' else case when  dpt.dept_name like '%GSE%' then 'GSTE' else 'IN-HOUSE' end end SHOP_TYPE
, trunc(woo.entry_date) entry_date
, pnm.pn
, pnm.description
, (select serial_number from stock where rcd_auto_key=(select min(rcd_auto_key) from rc_detail where woo_auto_key=woo.woo_auto_key)) serial_number
, mfg.mfg_code
, woo.last_status_chg
, wos.description status
, decode(woo.released,'T','Y','N') RELEASED
, (select min(stamptime) from audit_trail where source_table='WOO' and source_field='RELEASED' and orig_value='F' and new_value='T' and source_ak=woo.woo_auto_key) RELEASE_DATE
, cmp.company_name customer_name
, cmp.company_code customer_code
, woo.company_ref_number CUSTOMER_PO
, decode(cmp.cv_udf_002,'T','Y','N') PBH_CUSTOMER
, case when (select count(stm_complete.pn) from stock stm_complete left join wo_stm_complete
on wo_stm_complete.stm_auto_key=stm_complete.stm_auto_key where wo_stm_complete.woo_auto_key=woo.woo_auto_key)<=1 then (select max(stm_complete.pn) from stock stm_complete left join wo_stm_complete
on wo_stm_complete.stm_auto_key=stm_complete.stm_auto_key where wo_stm_complete.woo_auto_key=woo.woo_auto_key) else 'MULTIPLE' end part_number_out
, case when (select count(stm_complete.serial_number) from stock stm_complete left join wo_stm_complete
on wo_stm_complete.stm_auto_key=stm_complete.stm_auto_key where wo_stm_complete.woo_auto_key=woo.woo_auto_key)<=1 then (select max(stm_complete.serial_number) from stock stm_complete left join wo_stm_complete
on wo_stm_complete.stm_auto_key=stm_complete.stm_auto_key where wo_stm_complete.woo_auto_key=woo.woo_auto_key) else 'MULTIPLE' end serial_number_out
, (select min(nvl(rch.arrival_date,rch.entry_date)) from rc_header rch inner join rc_detail rcd on rch.rch_auto_key=rcd.rch_auto_key where rcd.woo_auto_key=woo.woo_auto_key) received_date
, woo.due_date
, wwt.work_type work_requested
, wfw.code work_performed
, case when ctd.tat_days is not null then ctd.tat_days else case when dept_name like '%GSE%' then 18 else null end end contract_tat
, woo.manual_ecd
, trunc(woo.close_date) close_date
, trunc(nvl((select max(smh.ship_date) from sm_header smh where smh.woo_auto_key=woo.woo_auto_key),Null)) ship_date
, nvl((select max(smh.tracking_number) from sm_header smh where smh.woo_auto_key=woo.woo_auto_key),Null) tracking_number
, ctd.line_item contract_item
, cth.contract_number
, cth.SDF_CTH_001 CONTRACT_TYPE
, soh.so_number
, (select sum(total_amount) from wip_log where wip_log.WOO_AUTO_KEY = woo.WOO_AUTO_KEY and operational_type<>'Main Comp') total_WO_costs
, ba_wo_first_quote_sent(woo.woo_auto_key) first_quote_sent
, BA_WO_LAST_QUOTE_SENT(woo.woo_auto_key) last_quote_sent
, ba_wo_last_quote_approved(woo.woo_auto_key) last_quote_approved
, BA_WO_LAST_QUOTE(WOO.WOO_AUTO_KEY) LAST_QUOTE
, BA_WO_LAST_QUOTE_STATUS(WOO.WOO_AUTO_KEY) LAST_QUOTE_STATUS
, BA_WO_QUOTE_PRICE(WOO.WOO_AUTO_KEY) QUOTED_AMOUNT
, BA_WO_LAST_RO(WOO.WOO_AUTO_KEY) LAST_RO
, BA_WO_LAST_RO_VENDOR_INFO(WOO.WOO_AUTO_KEY) LAST_RO_VENDOR_INFO
, WOO.WO_UDF_010 WARRANTY_REQUESTED
, WOO.WARRANTEE_FLAG WARRANTY_GRANTED
, WOO.WO_UDF_016 WARRANTY_COMMENTS
, WOO.WO_UDF_007 SCHEDULED_REMOVAL
, WOO.WO_UDF_008 NFF_FLAG
, WOO.WO_UDF_012 HRC_FLAG
, WOO.WO_UDF_011 LRC_FLAG
, WOO.WO_UDF_015 HRC_CODE
, decode((select count(*) from audit_trail where source_table='WOO' and source_field='STATUS' and new_value='PARTS SHORTAGE' and source_ak=woo.woo_auto_key),0,'N','Y') WAS_PARTS_SHORTAGE
, ba_time_in_parts_shortage(woo.woo_auto_key)+decode(wos.description,'PARTS_SHORTAGE',round(sysdate-woo.last_status_chg),0) time_in_parts_shortage
, decode((SELECT COUNT(*) FROM WO_TASK WOT WHERE WOT.WOO_AUTO_KEY = WOO.WOO_AUTO_KEY AND WOT.REWORK_FLAG = 'T'),0,'N','Y') INTERNAL_REJECTION
, sysur.first_name||' '||sysur.last_name CSM
, woo.woo_auto_key
, woo.syscm_auto_key
from wo_operation woo
inner join parts_master pnm
on woo.pnm_auto_key=pnm.pnm_auto_key
left join manufacturer mfg
on pnm.mfg_auto_key=mfg.mfg_auto_key
left join wo_status wos
on woo.wos_auto_key=wos.wos_auto_key
left join companies cmp
on woo.cmp_auto_key=cmp.cmp_auto_key
left join department dpt
on woo.dpt_auto_key=dpt.dpt_auto_key
inner join sys_companies syscm
on woo.syscm_auto_key=syscm.syscm_auto_key
left join contract_detail ctd
on woo.ctd_auto_key=ctd.ctd_auto_key
left join contract_header cth
on ctd.cth_auto_key=cth.cth_auto_key
left join so_detail sod
on woo.woo_auto_key=sod.woo_auto_key
left join so_header soh
on sod.soh_auto_key=soh.soh_auto_key
left join WO_WORK_TYPE wwt
on woo.wwt_auto_key=wwt.wwt_auto_key
left join work_flows wfw
on woo.wfw_auto_key=wfw.wfw_auto_key
left join sys_users sysur
on case when woo.syscm_auto_key=2 then cmp.cv_udf_016 else case when woo.syscm_auto_key=1 then cmp.cv_udf_017 else case when woo.syscm_auto_key=3 then cmp.cv_udf_018 else '' end end end = sysur.user_name
where woo.wo_type not in ('Mfg','Kit') and woo.entry_date>sysdate-1*365

Open in new window

maximus1974Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
have u executed this select in oracle?
There are many inline queries, one of the them is returning multiple rows and we are expected one.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
MSSQL, SSIS or openquery are not related. This is an error in the original Oracle view, and needs to get resolved there (in Oracle). It is difficult to say for sure, but the only part I can see causing it is
, (select serial_number from stock where rcd_auto_key=(select min(rcd_auto_key) from rc_detail where woo_auto_key=woo.woo_auto_key)) serial_number

Open in new window

everything else should retrieve a single row per subselect and main row.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.