产品目录SQL语句

 

---crm
select name as "公司型号-对内",PROFIT_RANK_CD as "销售类型",t1.ALIAS_NAME as "原装型号",t3.ATTRIB_39 from siebel.S_PROD_INT t1 ,siebel.S_PROD_INT_X t3 where
t1.row_id=t3.par_row_id(+)
and t1.BU_ID<>'1-1E5L'--祛除微电子数据
and t1.PROFIT_RANK_CD<>'KEY'            --销售类型：内销/外销

 

 

--ec
SELECT NINESTAR_IN_CODE,SALES_TYPE,OEM_CODE,COLOR FROM PRO_ITEM_LIST_HEADER

--1 内销
---2外销

 

 

    select b.cin,b.type,b.inid,b.color from ec_temp_up1 b where not exists(select null from (
       select b.cin,b.type,b.inid from ec_temp_up1  b join pro_item_list_header a
       on TRIM(a.NINESTAR_IN_CODE)=b.cin
       and TRIM(a.SALES_TYPE)=b.type
       and TRIM(a.OEM_CODE)=b.inid)a where a.cin=b.cin and a.type=b.type and a.inid=b.inid)
      
merge into pro_item_list_header a
  using(select cin,type,inid,color from ec_temp_up1) b
  on (TRIM(a.NINESTAR_IN_CODE)=b.cin
      and TRIM(a.SALES_TYPE)=b.type
      and TRIM(a.OEM_CODE)=b.inid)
      when matched then
       update set A.COLOR=b.color