Link to home
Start Free TrialLog in
Avatar of jasonboetcher
jasonboetcher

asked on

REPLACE function in SQL 6.5

Does the replace function not work in SQL 6.5?

Here is what I am trying?

/* Delete existing data */
DELETE FROM DEV_INTERFACES..AFS

INSERT INTO DEV_INTERFACES..AFS
     (name,
     address,
     phone,
     rating,
     officer_name,
     change_amount,
     liab_amount,
     effective_dt,
     maturity_dt,
     log_time,
     obligor,
     entity_type,
     ssn,
     sic)

SELECT  customer.name,
     REPLACE(customer.addr, char(13) + char(10), '!'),
     customer.phone1,
     customer.scf_credit_rating,
     customer.acct_officer,
     liabhist.amt,
     liabhist.total_amt,
     (CASE
          WHEN SUBSTRING(liabhist.postfol_refno, 1, 3) IN('IGT', 'INO', 'IBL', 'ELC', 'IPP') THEN
               (SELECT DISTINCT issue_dt FROM DEV_IBSWIN..lc lc
               WHERE refno = liabhist.postfol_refno)
          WHEN SUBSTRING(liabhist.postfol_refno, 1, 3) IN('OSG', 'OSB', 'PPS', 'OBL', 'IIC') THEN
               (SELECT DISTINCT issue_dt FROM DEV_IBSWIN..standby standby
               WHERE refno = liabhist.postfol_refno)
          WHEN SUBSTRING(liabhist.postfol_refno, 1, 3) IN('BAF', 'BAB', 'DPB') THEN
               (SELECT DISTINCT finance_dt FROM DEV_IBSWIN..finance finance
               WHERE refno = liabhist.postfol_refno)
     END),
     (CASE
          WHEN SUBSTRING(liabhist.postfol_refno, 1, 3) IN('IGT', 'INO', 'IBL', 'ELC', 'IPP') THEN
               (SELECT DISTINCT expiry_dt FROM DEV_IBSWIN..lc lc
               WHERE refno = liabhist.postfol_refno)
          WHEN SUBSTRING(liabhist.postfol_refno, 1, 3) IN('OSG', 'OSB', 'PPS', 'OBL', 'IIC') THEN
               (SELECT DISTINCT expiry_dt FROM DEV_IBSWIN..standby standby
               WHERE refno = liabhist.postfol_refno)
          WHEN SUBSTRING(liabhist.postfol_refno, 1, 3) IN('BAF', 'BAB', 'DPB') THEN
               (SELECT DISTINCT maturity_dt FROM DEV_IBSWIN..finance finance
               WHERE refno = liabhist.postfol_refno)
          END),
     liabhist.logtime,
     customer.scf_obligor,
     customer.scf_entity_type,
     customer.scf_ssn,
     customer.scf_sic
FROM      DEV_IBSWIN..liabhist liabhist INNER JOIN DEV_IBSWIN..customer customer ON liabhist.mnem = customer.mnem
WHERE      liabhist.logtime >= '04/01/02'
AND     liabhist.logtime <= '04/30/02'
ORDER BY liabhist.logtime

Here are the errors I am getting:

Msg 195, Level 15, State 10
'REPLACE' is not a recognized built-in function name.
Msg 156, Level 15, State 1
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1
Incorrect syntax near the keyword 'END'.
Msg 156, Level 15, State 1
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1
Incorrect syntax near the keyword 'END'.
ASKER CERTIFIED SOLUTION
Avatar of gencross
gencross

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial