Re: Help with SQL statement and SUBSTR

norcold1_at_yahoo.com schrieb:
> 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
>

You may be hitting bug 435730 , which is actually classified as not a bug. As workaround you can try to use

TO_CHAR(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

in your query.

Best regards

Maxim Received on Fri Sep 30 2005 - 13:05:18 CDT