Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1001
  • Last Modified:

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'.
0
jasonboetcher
Asked:
jasonboetcher
1 Solution
 
gencrossCommented:
I don't think replace is valid in Sql 6.5.  Here is a list of functions from Transact SQL 6.5 Help

Aggregate Functions

Aggregate functions return summary values. These are the aggregate functions:

AVG     COUNT(*)     MIN
COUNT     MAX     SUM
Date Functions

Date functions compute datetime values and their components, dataparts. These are the date functions:

DATEADD     DATENAME     GETDATE
DATEDIFF     DATEPART    
Mathematical Functions

Mathematical functions perform operations on numeric data. These are the mathematical functions:

ABS     DEGREES     RAND
ACOS     EXP     ROUND
ASIN     FLOOR     SIGN
ATAN     LOG     SIN
ATN2     LOG10     SQRT
CEILING     PI     TAN
COS     POWER    
COT     RADIANS    
Niladic Functions

Niladic functions allow a system-supplied value to be inserted into a table when no value is specified. ANSI-standard niladic functions are used in DEFAULT constraints. These niladic functions are supported:

CURRENT_TIMESTAMP     SYSTEM_USER
CURRENT_USER     USER
SESSION_USER    
For details about these functions, see the CREATE TABLE and ALTER TABLE statements.

String Functions

String functions perform operations on binary data, character strings, or expressions. These are the string functions:

+     LTRIM     SOUNDEX
ASCII     PATINDEX     SPACE
CHAR     REPLICATE     STR
CHARINDEX     REVERSE     STUFF
DIFFERENCE     RIGHT     SUBSTRING
LOWER     RTRIM     UPPER
System Functions

System functions return special information from the database. These are the system functions:

COALESCE     HOST_NAME     OBJECT_NAME
COL_LENGTH     IDENT_INCR     STATS_DATE
COL_NAME     IDENT_SEED     SUSER_ID
DATALENGTH     INDEX_COL     SUSER_NAME
DB_ID     ISNULL     USER_ID
DB_NAME     NULLIF     USER_NAME
GETANSINULL     HOST_ID     OBJECT_ID

Hope this helps
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now