「不憤不啟，不悱不發。舉一隅不以三隅反，則不復也」(Oracle SQL 2013/09/24)

DAY 9

哇咧~夠了(Oracle SQL)系列第 9 篇

資料庫

月半車甫

2013-09-24 17:42:04

2739 瀏覽

分享至

話說這主題，訂的倒比較像是"IT人生"的文...呵呵！反正，這也是我帶人的原則。
記住啊！自己啥都沒準備的、對問題自己都沒嘗試過的，千萬不要直接跑來問我？
那就來個對應主旨的舉一反.....二啦~

以前系統轉換時，進行料號轉檔，遇到過某個欄位直接就是這樣子的:
{Old_Item_No:DCZ-8110F95,AN4-31002-34}--600DR,下蓋,D340黑鏡面烤漆
喔喔，這料號摘要會不會暴露我是哪家公司的？

裡面包含了兩個舊料號，DCZ-8110F95,AN4-31002-34，甚至有3個舊料號的狀況，
它的新料號是N431.000001

問題來了，當出報表時候，User希望這種情況，要變成2筆、甚至3筆來輸出！
什麼！這有可能嗎？當初轉換時候，就該建立好對照表的！哪有事後從資料面反推的啦~

問題是，它就是問題，遇到了總要去解決。怎麼解決？

首先，這是原始資料，以下針對2筆來處理，3筆的狀況有點棘手啦~;

Select 'N431.000001' Segment1, '{Old_Item_No:DCZ-8110F95,AN4-31002-34}--600DR,下蓋,D340黑鏡面烤漆' Long_Description
  From Dual;

結果:
"SEGMENT1","LONG_DESCRIPTION"
"N431.000001","{Old_Item_No:DCZ-8110F95,AN4-31002-34}--600DR,下蓋,D340黑鏡面烤漆"

因為要變成2筆或3筆，需要無中生有，用union吧，用1&2來區分，避免union好心又把它們變成1筆，因為目前資料面分不出來有差異;

Select 1, 'N431.000001' Segment1
, '{Old_Item_No:DCZ-8110F95,AN4-31002-34}--600DR,下蓋,D340黑鏡面烤漆' Long_Description
  From Dual
union
Select 2, 'N431.000001' Segment1
, '{Old_Item_No:DCZ-8110F95,AN4-31002-34}--600DR,下蓋,D340黑鏡面烤漆' Long_Description
  From Dual;

結果:
"1","SEGMENT1","LONG_DESCRIPTION"
1,"N431.000001","{Old_Item_No:DCZ-8110F95,AN4-31002-34}--600DR,下蓋,D340黑鏡面烤漆"
2,"N431.000001","{Old_Item_No:DCZ-8110F95,AN4-31002-34}--600DR,下蓋,D340黑鏡面烤漆"

開始...吸呀給(台語)....;
第1筆的關鍵字在 ":"，第2筆則是在 "}"，透過instrb取得關鍵字位置，再做切割。;

Select 1, 'N431.000001' Segment1 
,  Substrb('{Old_Item_No:DCZ-8110F95,AN4-31002-34}--600DR,下蓋,D340黑鏡面烤漆'
, Instrb('{Old_Item_No:DCZ-8110F95,AN4-31002-34}--600DR,下蓋,D340黑鏡面烤漆', ':') +1, 11) Old_Item_No
, '{Old_Item_No:DCZ-8110F95,AN4-31002-34}--600DR,下蓋,D340黑鏡面烤漆' Long_Description
  From Dual
Union
Select 2, 'N431.000001' Segment1
,  Substrb('{Old_Item_No:DCZ-8110F95,AN4-31002-34}--600DR,下蓋,D340黑鏡面烤漆'
, Instrb('{Old_Item_No:DCZ-8110F95,AN4-31002-34}--600DR,下蓋,D340黑鏡面烤漆', '}') -12, 11) Old_Item_No
, '{Old_Item_No:DCZ-8110F95,AN4-31002-34}--600DR,下蓋,D340黑鏡面烤漆' Long_Description
  From Dual;

結果:
"1","SEGMENT1","OLD_ITEM_NO","LONG_DESCRIPTION"
1,"N431.000001","DCZ-8110F95","{Old_Item_No:DCZ-8110F95,AN4-31002-34}--600DR,下蓋,D340黑鏡面烤漆"
2,"N431.000001","AN4-31002-3","{Old_Item_No:DCZ-8110F95,AN4-31002-34}--600DR,下蓋,D340黑鏡面烤漆"

問題還沒結束？！因為未必會有第2筆啊！
所以第2筆要加上判斷條件，當Instrb找不到特定對象時，就不要有結果！
這裡要注意的是，只能用"}"位置來判斷，而不是找不到，
這個案例，只有1個舊料號時，則"}"出現的位置大都在28的位置;

Select 1, 'N431.000001' Segment1 
,  Substrb('{Old_Item_No:DCZ-8110F95,AN4-31002-34}--600DR,下蓋,D340黑鏡面烤漆'
, Instrb('{Old_Item_No:DCZ-8110F95,AN4-31002-34}--600DR,下蓋,D340黑鏡面烤漆', ':') +1, 11) Old_Item_No
, '{Old_Item_No:DCZ-8110F95,AN4-31002-34}--600DR,下蓋,D340黑鏡面烤漆' Long_Description
  From Dual
Union
Select 2, 'N431.000001' Segment1
,  Substrb('{Old_Item_No:DCZ-8110F95,AN4-31002-34}--600DR,下蓋,D340黑鏡面烤漆'
, Instrb('{Old_Item_No:DCZ-8110F95,AN4-31002-34}--600DR,下蓋,D340黑鏡面烤漆', '}') -12, 11) Old_Item_No
, '{Old_Item_No:DCZ-8110F95,AN4-31002-34}--600DR,下蓋,D340黑鏡面烤漆' Long_Description
  From Dual
 where Instrb('{Old_Item_No:DCZ-8110F95,AN4-31002-34}--600DR,下蓋,D340黑鏡面烤漆', '}') > 30 ;

好了，把SQL改成對應正式table 吧！

Select 1,  Segment1 
,      Substrb(Long_Description, Instrb(Long_Description, ':') +1, 11) Old_Item_No
,      Long_Description
  From Mtl_System_Items_Vl
 Where Segment1 in ('N431.000001', 'N431.000002')
   and organization_id = 86
Union
Select 2, Segment1
,      Substrb(Long_Description, Instrb(Long_Description, '}') -12, 11) Old_Item_No
,      Long_Description
  From Mtl_System_Items_Vl
 Where Segment1 in ('N431.000001', 'N431.000002')
   and organization_id = 86
   And Instrb(Long_Description, '}') > 30 
 order by 2, 1, 3;

驗證結果:

"1","SEGMENT1","OLD_ITEM_NO","LONG_DESCRIPTION"
1,"N431.000001","DCZ-8110F95","{Old_Item_No:DCZ-8110F95,AN4-31002-34}--600DR,下蓋,D340黑鏡面烤漆"
2,"N431.000001","AN4-31002-3","{Old_Item_No:DCZ-8110F95,AN4-31002-34}--600DR,下蓋,D340黑鏡面烤漆"
1,"N431.000002","AN4-3101-34","{Old_Item_No:AN4-3101-34}--**,外殼上蓋板(CASE),D340黑鏡面漆"

一、Oracle SQL 從 Select * From Dual; 開始囉~(Oracle SQL 2013/09/16)
http://ithelp.ithome.com.tw/question/10126717
二、單一SQL好，還是PL/SQL好？最好是再多問一句:哪一種寫法比較Smart！(Oracle SQL 2013/09/17)
http://ithelp.ithome.com.tw/question/10127060
三、(續)單一SQL好，還是PL/SQL好？(Oracle SQL 2013/09/18)
http://ithelp.ithome.com.tw/question/10127328
四、Oracle 日期之我轉、我轉、我轉..轉..轉......(Oracle SQL 2013/09/19)
http://ithelp.ithome.com.tw/question/10127536
五、數字轉成等比例的符號(Oracle SQL 2013/09/20)
http://ithelp.ithome.com.tw/question/10128037
六、戲法人人會變(Oracle SQL 2013/09/21)
http://ithelp.ithome.com.tw/question/10128274
七、轉檔的小技巧(Oracle SQL 2013/09/22)
http://ithelp.ithome.com.tw/question/10128601
八、上旬！中旬、下旬，是否有規則可循？真是懷念台灣的紅蟳...趕緊去尋...(Oracle SQL 2013/09/23)
http://ithelp.ithome.com.tw/question/10128897