decode oracle script into sql script

• paul.starr

  SSCrazy

  Points: 2505

  More actions

  October 31, 2008 at 11:08 am

  #193062

  what am i doing wrong

  --ROW_NUMBERset pages 0 lines 999 trimspool on head off veri off echo off feedb off

  --spool C:\Arun\BuyDesign\InitialLoad_Customer.txt

  select case orig.orig_system

  when null then 'ORACLE'

  when orig.orig_system then 'ORACLE'

  else 'orig.orig_system'

  END,

  ca.account_number, ca.attribute10, hp.party_name, ca.status, hp.category_code,

  ca.customer_class_code, str(ca.account_established_date,'MM/DD/YYYY')

  ,ca.attribute3

  from Oracle11i..AR.HZ_CUST_ACCOUNTS CA, Oracle11i..AR.HZ_PARTIES HP, Oracle11i..AR.HZ_ORGANIZATION_PROFILES ORG,

  Oracle11i..AR.HZ_ORIG_SYS_REFERENCES ORIG, Oracle11i..AR.HZ_CUST_ACCT_SITES_ALL SI

  where hp.party_id = ca.party_id

  and str(ca.attribute10,9,3) = '086'

  and ca.cust_account_id = si.cust_account_id

  and si.bill_to_flag in ('P','Y')

  and org.party_id = hp.party_id

  and isnull(org.status,'A') ='A'

  --and between GetDate()org.effective_start_date and GetDate() org.effective_end_date

  and orig.owner_table_name ='HZ_CUST_ACCOUNTS'

  and orig.owner_table_id = ca.cust_account_id

  and (orig.orig_system is null or orig.orig_system not in ('ALUMINUM'))

  order by ca.account_number, ca.attribute10;

  --spool off