Solved

REPLACE function in SQL 6.5

Posted on 2002-05-14
1
970 Views
Last Modified: 2008-01-09
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
Comment
Question by:jasonboetcher
1 Comment
 
LVL 4

Accepted Solution

by:
gencross earned 50 total points
ID: 7016856
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question