Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on Jun 17th, 2012  |  syntax: None  |  size: 1.15 KB  |  hits: 20  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. Extract selective records
  2. Table A            
  3. Customer    InvoiceNo       Region  Type    Amount
  4. A001        10001           Europe  FG      100
  5. B001        10002           Asia    FG      200
  6. C001        10003           America MISC    50
  7. D001        10004           Asia    FG      300
  8. A001        10005           Europe  MISC    20
  9. C001        10006           America MISC    10
  10. B001        10007           Asia    FG      300
  11.        
  12. Customer    InvoiceNo       Type    Sales_Amt MISC
  13. A001        10001           FG      100       0
  14. B001        10002           FG      200       0
  15. C001        10003           MISC    0         50
  16. D001        10004           FG      300       0
  17. A001        10005           MISC    0         20
  18. C001        10006           MISC    0         10
  19. B001        10007           FG      300       0
  20.        
  21. select
  22.     Customer,
  23.     InvoiceNo,
  24.     Type,
  25.     decode(Type, 'MISC', 0, 1) * Amount as Sales_Amt,
  26.     decode(Type, 'MISC', 1, 0) * Amount as Misc
  27. from
  28.     TableA
  29. ;
  30.        
  31. Select Customer,InvoiceNo,Type,
  32.         Case when TYPE!='MISC' Then amount Else 0 End as Sales_Amt,
  33.         Case when TYPE='MISC' Then amount Else 0 End as Misc
  34. From TabalA
create a new version of this paste RAW Paste Data