Solved

REPLACE function in SQL 6.5

Posted on 2002-05-14
1
949 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now