Re: MSSQL to Oracle 9i Query Errors

berwiki wrote:
> 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?

Are we supposed to guess at the error message?

I'll guess it is ORA-01403 NO_DATA_FOUND.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org