Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Help with SQL statement and SUBSTR

Re: Help with SQL statement and SUBSTR

From: IANAL_VISTA <IANAL_Vista_at_hotmail.com>
Date: Fri, 30 Sep 2005 14:41:08 GMT
Message-ID: <Xns96E14E306E49SunnySD@68.6.19.6>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US