MSSQL to Oracle 9i Query Errors

From: berwiki <steveberwick_at_gmail.com>
Date: 14 Jul 2006 08:02:32 -0700
Message-ID: <1152889352.323129.250480@m73g2000cwd.googlegroups.com>

Can anybody help me? This query executes perfectly fine in MSSQL, but I get errors in Oracle.

SELECT CAST(REPLACE(STR(SHCLOG.origtrace,6,0),' ','0')AS CHAR(6)) as FIXEDTRACE,

SHCLOG.SETTLEMENT_DATE,
SHCLOG.MSGTYPE,
SHCLOG.ORIGMSG,

SUBSTRING(SHCLOG.PAN,1,6) AS ISOBIN,
SHCLOG.PCODE,
SHCLOG.TXNTYPE,
CONVERT(char(13), (CAST((SHCLOG.amount) as money)),1) AS FIXEDAMOUNT, ' '+CAST(REPLACE(STR(SHCLOG.origtrace,6,0),' ','0')AS CHAR(6)) AS FIXEDORIGTRACE,
REPLACE(

         (
	  CAST(REPLACE(STR(SHCLOG.origtrace,6,0),' ','0')AS CHAR(6))+ '-' +
SUBSTRING(SHCLOG.PAN,1,6) + '-' +
          CAST(REPLACE(STR(SHCLOG.amount,10,2),' ','0')as char(10))+
'-' +
       	  CASE
            WHEN SHCLOG.respcode<>0
            THEN '999'
              ELSE CAST(REPLACE(STR(SHCLOG.msgtype,3,0),' ','0')AS
CHAR(3)) END
          ) ,' ','') AS FIXEDSEQ,

SUBSTRING(SHCLOG.filler1,1,6) AS FIXEDPCODE, SUBSTRING(SHCLOG.filler1,7,3) AS FIXEDACCTQUAL,

SHCLOG.AMOUNT,
SHCLOG.ORIGDATE,
SHCLOG.TRACE,
SHCLOG.LOCAL_TIME,
SHCLOG.LOCAL_DATE,
SHCLOG.ACQUIRER,
SHCLOG.ISSUER,
SHCLOG.RESPCODE,
SHCLOG.REASON_CODE,
SHCLOG.SHCERROR,
SHCLOG.REVCODE,
SHCLOG.ACCEPTORNAME,
SHCLOG.ACCTNUM,
SHCLOG.FILLER1,
SHCLOG.FILLER2,
SHCLOG.TXN_START_TIME,
SHCLOG.TXN_END_TIME

FROM SHCLOG WHERE (SHCLOG.acquirer='1588888888' OR SHCLOG.issuer='1588888888') AND

SHCLOG.amount>0 AND
SHCLOG.pcode<>400000 AND
SHCLOG.pcode<>410000 AND

SUBSTRING(SHCLOG.pan,1,6)<>'583999' AND
SHCLOG.pcode<>900000
ORDER BY SHCLOG.txn_end_time ASC;

It doesn't seem to like my char(13) function call, any equivalent or suggestions? Received on Fri Jul 14 2006 - 10:02:32 CDT