Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Help with SQL statement and SUBSTR
norcold1_at_yahoo.com wrote in
news:1128090206.858726.146480_at_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
>
>
here is a free clue "'29-Sep-2005'" are STRINGS not dates.
TO_DATE('29-Sep-2005','DD-Mon-YYYY') will produce a DATE Received on Fri Sep 30 2005 - 09:41:08 CDT