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'.
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
(SELECT DISTINCT issue_dt FROM DEV_IBSWIN..lc lc
WHERE refno = liabhist.postfol_refno)
WHEN SUBSTRING(liabhist.postfol
(SELECT DISTINCT issue_dt FROM DEV_IBSWIN..standby standby
WHERE refno = liabhist.postfol_refno)
WHEN SUBSTRING(liabhist.postfol
(SELECT DISTINCT finance_dt FROM DEV_IBSWIN..finance finance
WHERE refno = liabhist.postfol_refno)
END),
(CASE
WHEN SUBSTRING(liabhist.postfol
(SELECT DISTINCT expiry_dt FROM DEV_IBSWIN..lc lc
WHERE refno = liabhist.postfol_refno)
WHEN SUBSTRING(liabhist.postfol
(SELECT DISTINCT expiry_dt FROM DEV_IBSWIN..standby standby
WHERE refno = liabhist.postfol_refno)
WHEN SUBSTRING(liabhist.postfol
(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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.