Help with SQL statement and SUBSTR

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

norcold1@yahoo.com - 30 Sep 2005 15:23 GMT

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

Reply to this Message

IANAL_VISTA - 30 Sep 2005 15:41 GMT

> I need some help understanding how to make this SQL work. Below works
> fine:
[quoted text clipped - 68 lines]
> 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

Reply to this Message

Sybrand Bakker - 30 Sep 2005 15:43 GMT

>I've alse tried date formats such as:
>TO_CHAR(PCH.INSERT_DATETIME,'MM-DD-YYYY HH24:MI:SS') > '09-29-2005
[quoted text clipped - 5 lines]
>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.

The to_char is required around the hardcoded literal.
The order of execution has nothing to do with it.

--
Sybrand Bakker, Senior Oracle DBA

Reply to this Message

norcold1@yahoo.com - 30 Sep 2005 16:22 GMT

Ok, I am just not getting it. I've tried
TO_DATE(PCH.INSERT_DATETIME,'DD-Mon-YYYY') >='29-SEP-2005'
and
TO_DATE(PCH.INSERT_DATETIME,'DD-Mon-YYYY') >=
TO_DATE('29-Sep-2005','DD-Mon-YYYY')

but neither returns any results. I've run it without the date
parameters and there are records.

I didn't understand what you meant Sybrand.

Thanks,
CT

Reply to this Message

IANAL_VISTA - 30 Sep 2005 16:27 GMT

> Ok, I am just not getting it. I've tried
> TO_DATE(PCH.INSERT_DATETIME,'DD-Mon-YYYY') >='29-SEP-2005'
[quoted text clipped - 9 lines]
> Thanks,
> CT

PCH.INSERT_DATETIME >= TO_DATE('29-Sep-2005','DD-Mon-YYYY')

Reply to this Message

norcold1@yahoo.com - 30 Sep 2005 16:30 GMT

PCH.INSERT_DATETIME >= TO_DATE('29-Sep-2005','DD-Mon-YYYY')

Returns the Invaild Number error

Reply to this Message

Moritz Klein - 30 Sep 2005 17:01 GMT

norcold1@yahoo.com schrieb:

> PCH.INSERT_DATETIME >= TO_DATE('29-Sep-2005','DD-Mon-YYYY')
>
> Returns the Invaild Number error

Please post the DDL for the PCH-Table.

Reply to this Message

norcold1@yahoo.com - 30 Sep 2005 17:07 GMT

How would i get that? I'm not a DBA just a user.

Reply to this Message

Moritz Klein - 30 Sep 2005 17:13 GMT

norcold1@yahoo.com schrieb:

> How would i get that? I'm not a DBA just a user.

Use "desc DM.SVC_CLAIM_HEADER" from within SQL*Plus and post the output
here.

Reply to this Message

norcold1@yahoo.com - 30 Sep 2005 17:18 GMT

Thanks for the help...this is the output

CREATE TABLE SVC_CLAIM_HEADER
(
SEQ_CLAIM_ID NUMBER(9) NOT NULL,
CLAIM_NUMBER VARCHAR2(16) NOT NULL,
PRIMARY_SVC_DATE DATE NOT NULL,
CLAIM_THRU_DATE DATE,
SECONDARY_AUTH VARCHAR2(3),
DATE_RECEIVED DATE,
USER_DEFINED_1 VARCHAR2(15),
USER_DEFINED_2 VARCHAR2(15),
BATCH_NUMBER VARCHAR2(9),
SECURITY_CODE CHAR(1) DEFAULT '0',
INSERT_DATETIME DATE,
INSERT_USER VARCHAR2(8),
INSERT_PROCESS VARCHAR2(8),
UPDATE_DATETIME DATE,
UPDATE_USER VARCHAR2(8),
UPDATE_PROCESS VARCHAR2(8),
)

Reply to this Message

Maxim Demenko - 30 Sep 2005 19:05 GMT

norcold1@yahoo.com schrieb:

> I need some help understanding how to make this SQL work. Below works
> fine:
[quoted text clipped - 67 lines]
> 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

Reply to this Message

norcold1@yahoo.com - 30 Sep 2005 19:16 GMT

Thanks for the suggestion but still no luck. I guess that it must be
something in the substr expression, because I can return records when I
don't use substr in other queries.

Reply to this Message

norcold1@yahoo.com - 30 Sep 2005 19:32 GMT

WhooHoo!! YES!!!..Thanks Maxim. I changed that line and the one above
it and it worked. I used PCH.INSERT_DATETIME >='29-Sep-2005'. So do you
have any more info for this bug (bug 435730) so I can give to other
users and our SysAdmin.

Thanks,
CT

Reply to this Message

Maxim Demenko - 30 Sep 2005 20:04 GMT

norcold1@yahoo.com schrieb:

> WhooHoo!! YES!!!..Thanks Maxim. I changed that line and the one above
> it and it worked. I used PCH.INSERT_DATETIME >='29-Sep-2005'. So do you
[quoted text clipped - 3 lines]
> Thanks,
> CT

Your dba can look up in the Metalink using the bug number above or the
Note 1016476.102.

Best regards

Maxim

Reply to this Message