Help with SQL statement and SUBSTR

From: <norcold1_at_yahoo.com>
Date: 30 Sep 2005 07:23:26 -0700
Message-ID: <1128090206.858726.146480@o13g2000cwo.googlegroups.com>

I need some help understanding how to make this SQL work. Below works fine:

'****

SELECT
AH.AUDITED_RECORD_KEY,
AH.KEYWORD,

PCH.CLAIM_NUMBER,
PCD.LINE_NUMBER,
PCH.INSERT_DATETIME

FROM

DM.AUDIT_HDR AH,
DM.AUDIT_CHANGE_DTL ACD,
DM.SVC_CLAIM_HEADER PCH,
DM.SVC_CLAIM_DETAIL PCD

WHERE
LENGTH(AH.AUDITED_RECORD_KEY)<='14' AND
AH.SEQ_AUDIT_ID = ACD.SEQ_AUDIT_ID AND
PCD.SEQ_CLAIM_ID=PCH.SEQ_CLAIM_ID AND
PCD.SEQ_CLAIM_ID = SUBSTR(AH.AUDITED_RECORD_KEY, 1, InStr(AH.AUDITED_RECORD_KEY,'|',1,1)-1) AND PCD.LINE_NUMBER = SUBSTR(AH.AUDITED_RECORD_KEY, InStr(AH.AUDITED_RECORD_KEY,'|',1,1)+1,InStr(AH.AUDITED_RECORD_KEY,'|',1,2)-InStr(AH.AUDITED_RECORD_KEY,'|',1,1)-1) AND

PCH.SUBMITTED_AUTH_NUMBER IS NULL AND
PCD.CLAIM_STATUS NOT LIKE 'D' AND
ACD.AUDITED_COLUMN_NAME = 'NOT_COVERED_REASON'

'****

But if add in date parameters, I get ORA-01722: Invalid Number (#1722).

'****

SELECT
AH.AUDITED_RECORD_KEY,
AH.KEYWORD,
PCH.CLAIM_NUMBER,
PCD.LINE_NUMBER
FROM

DORIS.AUDIT_HDR AH,
DORIS.AUDIT_CHANGE_DTL ACD,
DORIS.PROFSVC_CLAIM_HEADER PCH,
DORIS.PROFSVC_CLAIM_DETAIL PCD

WHERE
LENGTH(AH.AUDITED_RECORD_KEY)<='14' AND
AH.SEQ_AUDIT_ID = ACD.SEQ_AUDIT_ID AND
PCD.SEQ_CLAIM_ID=PCH.SEQ_CLAIM_ID AND
PCD.SEQ_CLAIM_ID = SUBSTR(AH.AUDITED_RECORD_KEY, 1, InStr(AH.AUDITED_RECORD_KEY,'|',1,1)-1) AND PCD.LINE_NUMBER = SUBSTR(AH.AUDITED_RECORD_KEY, InStr(AH.AUDITED_RECORD_KEY,'|',1,1)+1,InStr(AH.AUDITED_RECORD_KEY,'|',1,2)-InStr(AH.AUDITED_RECORD_KEY,'|',1,1)-1) AND

PCH.SUBMITTED_AUTH_NUMBER IS NULL AND
PCD.CLAIM_STATUS NOT LIKE 'D' AND
ACD.AUDITED_COLUMN_NAME = 'NOT_COVERED_REASON' AND
PCH.INSERT_DATETIME >='29-Sep-2005' AND
PCH.INSERT_DATETIME <='30-Sep-2005'

'****

I've alse tried date formats such as:
TO_CHAR(PCH.INSERT_DATETIME,'MM-DD-YYYY HH24:MI:SS') > '09-29-2005 00:00:00' and TO_CHAR(PCH.INSERT_DATETIME,'MM-DD-YYYY HH24:MI:SS') < '09-30-2005 00:00:00'

'****

What do I need to do differently? I imagine it might have something to do with the order in which Oracle is executing the statment.

Thanks,
CT Received on Fri Sep 30 2005 - 09:23:26 CDT