Home » SQL & PL/SQL » SQL & PL/SQL » Problem with Equijoin in SQL
Problem with Equijoin in SQL [message #221966] Thu, 01 March 2007 05:52 Go to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
All,

I have an issue with a query (Oracle 10.2.0.3.0) and specifically this join:-

AND DNE.CARD_NO = SUBSTR(SES.NAS_PORT,4,2)
AND DNE.PORT_NO = SUBSTR(SES.NAS_PORT,2,2)

DNE.CARD_NO NUMBER
DNE.PORT_NO NUMBER

SES.NAS_PORT VARCHAR2(20)

The query fails with ORA-01722: invalid number.

The substrings of NAS_PORT should be numbers but, maybe the data is not clean.

Is my logic incorrect? Do I need to TO_NUMBER the substrings - I don't think that I do.

Also does anybody have a script which will search for non-numeric characters within a substring of columns in a table?

As always, any help is gladly received.

Ken.
Re: Problem with Equijoin in SQL [message #221974 is a reply to message #221966] Thu, 01 March 2007 06:25 Go to previous messageGo to next message
friendarora
Messages: 24
Registered: February 2007
Junior Member

Hi,


If you are sure that the function SUBSTR(SES.NAS_PORT,4,2)
always returns a number than you dont need to use to_number for this as this conversion will be handled implicitly


Since you are receiving an error for your query there must be some data issue which can be found out by this query


select * from SES where regexp_like (SUBSTR(SES.NAS_PORT,4,2),'[^a...z]');

This will give information about all those rows having alphabets



Nitin

[Updated on: Thu, 01 March 2007 06:25]

Report message to a moderator

Re: Problem with Equijoin in SQL [message #221986 is a reply to message #221966] Thu, 01 March 2007 07:00 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
Hi Nitin,

I am assured that the substr.... contains only numbers and when I run the regexp_like command which you kindly provided I get the following:-

58
19
69
52
31
33
31
27
02
27
65
05
01
02
15
50
50
68
57
38
56
01


The above look like numbers yet are being returned by the query. Any idea why? The column is a VARCHAR2 column.

Thanks for your help,

Ken.
Re: Problem with Equijoin in SQL [message #221988 is a reply to message #221966] Thu, 01 March 2007 07:04 Go to previous messageGo to next message
friendarora
Messages: 24
Registered: February 2007
Junior Member

are u able to execute your original query if you remove these specified rows
Re: Problem with Equijoin in SQL [message #221989 is a reply to message #221966] Thu, 01 March 2007 07:16 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
Here is the full query:-

SELECT DNE.DIRECTORY_NUMBER,
SUBSTR(SES.USERNAME,1,12),
MAX(SES.END_TIME)
FROM XCOM_XTNS.DN_TO_EQUIPMENT_REF DNE,
RADACCT.SESSIONS SES,
RADACCT.DSLAM_IP DIP
WHERE DNE.SITE_ID = DIP.EXCHANGE_ID
AND DNE.DSLAM_NO = DIP.DSLAM
AND SES.NAS_IP = DIP.IP_ADDRESS32K
AND DNE.CARD_NO = TO_NUMBER(SUBSTR(SES.NAS_PORT,4,2))
AND DNE.PORT_NO = TO_NUMBER(SUBSTR(SES.NAS_PORT,2,2))
AND SES.TUNNEL_END = 0
GROUP BY DNE.DIRECTORY_NUMBER,
SUBSTR(SES.USERNAME,1,12);

Here are the relevant datatypes:-

XCOM_XTNS.DN_TO_EQUIPMENT_REF

SITE_ID NOT NULL VARCHAR2(100)
DSLAM_NO NUMBER
CARD_NO NUMBER
PORT_NO NUMBER

RADACCT.SESSIONS

NAS_IP NUMBER(10)
NAS_PORT VARCHAR2(20)
TUNNEL_END NUMBER(10)
USERNAME VARCHAR2(60)

RADACCT.DSLAM_IP

EXCHANGE_ID VARCHAR2(Cool
DSLAM NUMBER(1)
IP_ADDRESS32K NUMBER(10)

The query runs as below (returns 660 million rows):-

SELECT DNE.DIRECTORY_NUMBER,
SUBSTR(SES.USERNAME,1,12),
MAX(SES.END_TIME)
FROM XCOM_XTNS.DN_TO_EQUIPMENT_REF DNE,
RADACCT.SESSIONS SES,
RADACCT.DSLAM_IP DIP
WHERE DNE.SITE_ID = DIP.EXCHANGE_ID
--AND DNE.DSLAM_NO = DIP.DSLAM
AND SES.NAS_IP = DIP.IP_ADDRESS32K
AND DNE.CARD_NO = --TO_NUMBER(SUBSTR(SES.NAS_PORT,4,2))
AND DNE.PORT_NO = --TO_NUMBER(SUBSTR(SES.NAS_PORT,2,2))
AND SES.TUNNEL_END = 0
GROUP BY DNE.DIRECTORY_NUMBER,
SUBSTR(SES.USERNAME,1,12);

I had a look at the DSLAMs and they were numbers. I will run a regexp_like on them.

Thanks,

Ken.
Re: Problem with Equijoin in SQL [message #221991 is a reply to message #221966] Thu, 01 March 2007 07:19 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
1 select DSLAM_NO from XCOM_XTNS.DN_TO_EQUIPMENT_REF
2* where regexp_like (DSLAM_NO,'[^a...z]')
SQL> /
where regexp_like (DSLAM_NO,'[^a...z]')
*
ERROR at line 2:
ORA-01722: invalid number
Re: Problem with Equijoin in SQL [message #221996 is a reply to message #221966] Thu, 01 March 2007 07:36 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
Correction to post abve. The query runs as:-

The query runs as below (returns 660 million rows):-

SELECT DNE.DIRECTORY_NUMBER,
SUBSTR(SES.USERNAME,1,12),
MAX(SES.END_TIME)
FROM XCOM_XTNS.DN_TO_EQUIPMENT_REF DNE,
RADACCT.SESSIONS SES,
RADACCT.DSLAM_IP DIP
WHERE DNE.SITE_ID = DIP.EXCHANGE_ID
--AND DNE.DSLAM_NO = DIP.DSLAM
AND SES.NAS_IP = DIP.IP_ADDRESS32K
--AND DNE.CARD_NO = TO_NUMBER(SUBSTR(SES.NAS_PORT,4,2))
--AND DNE.PORT_NO = TO_NUMBER(SUBSTR(SES.NAS_PORT,2,2))
AND SES.TUNNEL_END = 0
GROUP BY DNE.DIRECTORY_NUMBER,
SUBSTR(SES.USERNAME,1,12);
Re: Problem with Equijoin in SQL [message #221999 is a reply to message #221966] Thu, 01 March 2007 07:39 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
I am running the below at this moment:-

1 SELECT DNE.DIRECTORY_NUMBER,
2 SUBSTR(SES.USERNAME,1,12),
3 MAX(SES.END_TIME)
4 FROM XCOM_XTNS.DN_TO_EQUIPMENT_REF DNE,
5 RADACCT.SESSIONS SES,
6 RADACCT.DSLAM_IP DIP
7 WHERE DNE.SITE_ID = DIP.EXCHANGE_ID
8 --AND DNE.DSLAM_NO = DIP.DSLAM
9 AND SES.NAS_IP = DIP.IP_ADDRESS32K
10 AND DNE.CARD_NO = TO_NUMBER(SUBSTR(SES.NAS_PORT,4,2))
11 AND DNE.PORT_NO = TO_NUMBER(SUBSTR(SES.NAS_PORT,2,2))
12 AND SES.TUNNEL_END = 0
13 GROUP BY DNE.DIRECTORY_NUMBER,
14* SUBSTR(SES.USERNAME,1,12)
SQL> /

The query has not thrown an error yet.

The DSLAMS are both numbers. I will check for alphanumerics in there.
Re: Problem with Equijoin in SQL [message #222002 is a reply to message #221966] Thu, 01 March 2007 07:45 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
It is a problem with the DSLAM numbers. The query below runs ok and returns 32000 rows:-

SELECT DNE.DIRECTORY_NUMBER,
SUBSTR(SES.USERNAME,1,12),
MAX(SES.END_TIME)
FROM XCOM_XTNS.DN_TO_EQUIPMENT_REF DNE,
RADACCT.SESSIONS SES,
RADACCT.DSLAM_IP DIP
WHERE DNE.SITE_ID = DIP.EXCHANGE_ID
--AND DNE.DSLAM_NO = DIP.DSLAM
AND SES.NAS_IP = DIP.IP_ADDRESS32K
AND DNE.CARD_NO = TO_NUMBER(SUBSTR(SES.NAS_PORT,4,2))
AND DNE.PORT_NO = TO_NUMBER(SUBSTR(SES.NAS_PORT,2,2))
AND SES.TUNNEL_END = 0
GROUP BY DNE.DIRECTORY_NUMBER,
SUBSTR(SES.USERNAME,1,12)
/
Re: Problem with Equijoin in SQL [message #222005 is a reply to message #221966] Thu, 01 March 2007 07:51 Go to previous messageGo to next message
friendarora
Messages: 24
Registered: February 2007
Junior Member

Hi,


good catch

can u check the datatypes of DNE.DSLAM_NO and DIP.DSLAM

and also run query regexp_like.. to check if there are any alphabets in the columns
Re: Problem with Equijoin in SQL [message #222006 is a reply to message #221966] Thu, 01 March 2007 07:53 Go to previous messageGo to next message
friendarora
Messages: 24
Registered: February 2007
Junior Member

select * from XCOM_XTNS.DN_TO_EQUIPMENT_REF DNE where regexp_like (DNE.DSLAM_NO ,'[^a...z]');


select * from RADACCT.DSLAM_IP DIP
where regexp_like (DIP.DSLAM ,'[^a...z]');
Re: Problem with Equijoin in SQL [message #222007 is a reply to message #221966] Thu, 01 March 2007 07:54 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
Hi,
SQL> desc XCOM_XTNS.DN_TO_EQUIPMENT_REF

DSLAM_NO NUMBER

SQL> desc RADACCT.DSLAM_IP

DSLAM NUMBER(1)

REGEXP_LIKE does not work for numbers so I need to check for non-numeric numbers - any ideas?

THanks,

Ken.

Re: Problem with Equijoin in SQL [message #222010 is a reply to message #221966] Thu, 01 March 2007 08:01 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
A common way to do datatype checking is to use a to_number function in combination with an exception handler. If the to_number (or to_date, etc whatever the case may be) fails to work, it means that the value passed wasn't a number, and it will raise the exception which you can catch and deal with however you see fit.

Regular Expressions are nice as well. I've mainly used them in perl but the concept should be the same. But the conversion function plus exception handler was a common practice before regular expressions in oracle were allowed.
Re: Problem with Equijoin in SQL [message #222013 is a reply to message #221966] Thu, 01 March 2007 08:10 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
Guys,

Sorry to be a pain but I do not understand why this is failing. Here are the DSLAM numbers:-

SQL> select distinct(DSLAM_NO) from XCOM_XTNS.DN_TO_EQUIPMENT_REF;

DSLAM_NO
----------
6
5
1
3
2
4

6 rows selected.

SQL> select distinct(DSLAM) from RADACCT.DSLAM_IP;

DSLAM
----------
6
5
1
2
3
4

6 rows selected.

I can see no non-numerics here. Any ideas?

Regards,

Ken.
Re: Problem with Equijoin in SQL [message #222016 is a reply to message #221966] Thu, 01 March 2007 08:19 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
The problem is with the view DN_TO_EQUIPMENT_REF. Here is the sqltext for this view:-

SELECT customer_site.contacts as directory_number
, exchange.site_hum_id as site_id
, to_number(replace(replace(dslam_front_shelf.descr , 'STINGER FRONT '), 'ST
INGER FRONT', '01')) as dslam_no
, to_number(dslam_card.slot) as card_no
, to_number(dslam_port.port_hum_id) as port_no
, dslam_port.status as port_status
FROM site_inst customer_site
, epa cpe_port
, equip_inst cpe_equipment
, circ_path_element dslam_port_path_element
, epa dslam_port
, card_inst dslam_card

, equip_inst dslam_front_shelf
, site_inst exchange
WHERE cpe_equipment.site_inst_id = customer_site.site_inst_id
AND customer_site.num = 'DSL CUSTOMER'
AND cpe_port.equip_inst_id = cpe_equipment.equip_inst_id
AND dslam_port_path_element.sequence = 1
AND dslam_port_path_element.circ_path_inst_id = NVL (cpe_port.circ_path_inst_i
d, cpe_port.next_path_inst_id)
AND dslam_port.port_inst_id = dslam_port_path_element.port_inst_id
AND dslam_card.card_inst_id = dslam_port.card_inst_id
AND dslam_front_shelf.type = 'DSLAM SHELF'
AND dslam_card.equip_inst_id = dslam_front_shelf.equip_inst_id
AND dslam_front_shelf.site_inst_id = exchange.site_inst_id

I am looking at the underlying objects now to ascertain the issue.
Re: Problem with Equijoin in SQL [message #222021 is a reply to message #221966] Thu, 01 March 2007 08:32 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
The sqltext for the view runs ok.

But when I try to isolate the DSLAM query then it fails with the below error:-

SQL> select to_number(replace(replace(descr,'STINGER FRONT '),'STINGER FRONT','01')) from xcom.equip_inst;
select to_number(replace(replace(descr,'STINGER FRONT '),'STINGER FRONT','01')) from xcom.equip_inst
*
ERROR at line 1:
ORA-01722: invalid number

The view query runs ok though. I do not understand this.

DESCR NOT NULL VARCHAR2(100)

Any ideas or help gratefully received.

Cheers,

Ken.
Re: Problem with Equijoin in SQL [message #222030 is a reply to message #221966] Thu, 01 March 2007 08:45 Go to previous messageGo to next message
puneet.kakkar
Messages: 11
Registered: February 2007
Junior Member
Can you try this:

select * from DN_TO_EQUIPMENT_REF;

if it does not run meaning that DSLAM_NO of this viw has some non numeric data.chk for the data in that case.

Puneet
Re: Problem with Equijoin in SQL [message #222031 is a reply to message #221966] Thu, 01 March 2007 08:48 Go to previous messageGo to next message
puneet.kakkar
Messages: 11
Registered: February 2007
Junior Member
Chk for the output of this query:

select replace(replace(descr,'STINGER FRONT '),'STINGER FRONT','01') from xcom.equip_inst

The o/p should not contain any non numeric data.

--Puneet
Re: Problem with Equijoin in SQL [message #222055 is a reply to message #221966] Thu, 01 March 2007 09:41 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
Puneet,

View is OK.

Here is the output for the query with replace etc etc:-

SKY-144525-1
SKY-144557-1
SKY-144558-1
SKY-144583-1
SKY-144603-1
SKY-144619-1
M-3628441-1
SKY-144621-1
SKY-144624-1
SKY-144628-1
SKY-144642-1
SKY-144646-1
SKY-144650-1

Not looking good, is it?
Ken.
Re: Problem with Equijoin in SQL [message #222135 is a reply to message #221966] Fri, 02 March 2007 00:18 Go to previous messageGo to next message
friendarora
Messages: 24
Registered: February 2007
Junior Member

what is the output of

select dslam_no from XCOM_XTNS.DN_TO_EQUIPMENT_REF;
Re: Problem with Equijoin in SQL [message #222175 is a reply to message #221966] Fri, 02 March 2007 03:05 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
Hi,

1* select distinct(dslam_no) from XCOM_XTNS.DN_TO_EQUIPMENT_REF
SQL> /

6
5
1
3
2
4

Thanks for your help and time,

Ken.
Re: Problem with Equijoin in SQL [message #222176 is a reply to message #221966] Fri, 02 March 2007 03:07 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
Hi,

My research:-

The join which is erroring is DNE.DSLAM_NO = DIP.DSLAM. The datatype for DNE.DSLAM_NO is NUMBER and for DIP.DSLAM is NUMBER(1)

The error is being thrown by the column DSLAM_NO in the XCOM view DN_TO_EQUIPMENT_REF. The sqltext for this view is as below:-

SELECT customer_site.contacts as directory_number
, exchange.site_hum_id as site_id
, to_number(replace(replace(dslam_front_shelf.descr , 'STINGER FRONT '), 'STINGER FRONT', '01')) as dslam_no
, to_number(dslam_card.slot) as card_no
, to_number(dslam_port.port_hum_id) as port_no
, dslam_port.status as port_status
FROM site_inst customer_site
, epa cpe_port
, equip_inst cpe_equipment
, circ_path_element dslam_port_path_element
, epa dslam_port
, card_inst dslam_card
, equip_inst dslam_front_shelf
, site_inst exchange
WHERE cpe_equipment.site_inst_id = customer_site.site_inst_id
AND customer_site.num = 'DSL CUSTOMER'
AND cpe_port.equip_inst_id = cpe_equipment.equip_inst_id
AND dslam_port_path_element.sequence = 1
AND dslam_port_path_element.circ_path_inst_id = NVL (cpe_port.circ_path_inst_id, cpe_port.next_path_inst_id)
AND dslam_port.port_inst_id = dslam_port_path_element.port_inst_id
AND dslam_card.card_inst_id = dslam_port.card_inst_id
AND dslam_front_shelf.type = 'DSLAM SHELF'
AND dslam_card.equip_inst_id = dslam_front_shelf.equip_inst_id
AND dslam_front_shelf.site_inst_id = exchange.site_inst_id

The dslam_no from the view is picked up from equip_inst.descr and the datatype for this column is VARCHAR2(100).

My belief is that the invalid number error is being thrown due to non-numerics being present in the equip_inst.descr which my query does not filter out.

What are your thoughts?

Thanks,

Ken.
Re: Problem with Equijoin in SQL [message #222177 is a reply to message #221966] Fri, 02 March 2007 03:16 Go to previous messageGo to next message
friendarora
Messages: 24
Registered: February 2007
Junior Member

i think the error is somewhere else
as the view contains to_number funstion
it will always return a number

can you check the DIP.DSLAM column values ?

or try removing some other join condition and uncommenting this condition to view the output
Re: Problem with Equijoin in SQL [message #222184 is a reply to message #221966] Fri, 02 March 2007 03:53 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
SQL> select distinct(dslam) from RADACCT.DSLAM_IP;

DSLAM
----------
6
5
1
2
3
4

6 rows selected.
Re: Problem with Equijoin in SQL [message #222185 is a reply to message #221966] Fri, 02 March 2007 03:58 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
Hi,

After a suggestion from a colleague, I have created a temp table (XCOM_XTNS.DN_TO_EQUIPMENT_REF_TEMP) to drive the query (as select * from the view DNE) and have excluded any results with NULL DIRECTORY_NUMBERs. The query now looks as follows:-

SELECT DNE.DIRECTORY_NUMBER,
SUBSTR(SES.USERNAME,1,12),
MAX(SES.END_TIME)
FROM RADACCT.SESSIONS SES,
RADACCT.DSLAM_IP DIP,
XCOM_XTNS.DN_TO_EQUIPMENT_REF_TEMP DNE
WHERE DNE.SITE_ID = DIP.EXCHANGE_ID
AND DNE.DSLAM_NO = DIP.DSLAM
AND SES.NAS_IP = DIP.IP_ADDRESS32K
AND DNE.CARD_NO = TO_NUMBER(SUBSTR(SES.NAS_PORT,4,2))
AND DNE.PORT_NO = TO_NUMBER(SUBSTR(SES.NAS_PORT,2,2))
AND DNE.DIRECTORY_NUMBER IS NOT NULL
AND SES.TUNNEL_END = 0
GROUP BY DNE.DIRECTORY_NUMBER,
SUBSTR(SES.USERNAME,1,12);

The query now runs ok but only returns 19 942 records so there may now be something amiss in the logic.

Thanks,

Ken.
Re: Problem with Equijoin in SQL [message #222187 is a reply to message #221966] Fri, 02 March 2007 04:02 Go to previous messageGo to next message
puneet.kakkar
Messages: 11
Registered: February 2007
Junior Member
Can you get me the explain plan for the whole query which is erroring out?

Puneet
Re: Problem with Equijoin in SQL [message #222192 is a reply to message #221966] Fri, 02 March 2007 04:12 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
Hi Puneet,

-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 1040 | 35790 (4)| 00:07:10 | | |
| 1 | HASH GROUP BY | | 5 | 1040 | 35790 (4)| 00:07:10 | | |
| 2 | NESTED LOOPS | | 5 | 1040 | 35789 (4)| 00:07:10 | | |
| 3 | NESTED LOOPS | | 5 | 890 | 35779 (4)| 00:07:10 | | |
|* 4 | HASH JOIN | | 5 | 840 | 35769 (4)| 00:07:10 | | |
|* 5 | TABLE ACCESS BY INDEX ROWID | CIRC_PATH_ELEMENT | 1 | 15 | 4 (0)| 00:00:01 | | |
| 6 | NESTED LOOPS | | 1 | 158 | 11977 (3)| 00:02:24 | | |
|* 7 | HASH JOIN | | 6 | 858 | 11960 (3)| 00:02:24 | | |
| 8 | TABLE ACCESS BY INDEX ROWID | EPA | 13 | 195 | 4 (0)| 00:00:01 | | |
| 9 | NESTED LOOPS | | 132 | 12672 | 4781 (2)| 00:00:58 | | |
| 10 | NESTED LOOPS | | 10 | 810 | 4741 (2)| 00:00:57 | | |
|* 11 | HASH JOIN | | 7 | 476 | 4720 (2)| 00:00:57 | | |
| 12 | NESTED LOOPS | | 1322 | 46270 | 2656 (1)| 00:00:32 | | |
| 13 | TABLE ACCESS FULL | DSLAM_IP | 1322 | 22474 | 7 (0)| 00:00:01 | | |
| 14 | TABLE ACCESS BY INDEX ROWID| SITE_INST | 1 | 18 | 2 (0)| 00:00:01 | | |
|* 15 | INDEX UNIQUE SCAN | SITE_NAME_IDX | 1 | | 1 (0)| 00:00:01 | | |
|* 16 | TABLE ACCESS FULL | EQUIP_INST | 10637 | 342K| 2063 (3)| 00:00:25 | | |
| 17 | TABLE ACCESS BY INDEX ROWID | CARD_INST | 1 | 13 | 3 (0)| 00:00:01 | | |
|* 18 | INDEX RANGE SCAN | CARD_SLOT_IDX | 1 | | 2 (0)| 00:00:01 | | |
|* 19 | INDEX RANGE SCAN | CARD_PORT_IDX | 13 | | 2 (0)| 00:00:01 | | |
| 20 | PARTITION RANGE ALL | | 953K| 42M| 7158 (3)| 00:01:26 | 1 | 2 |
|* 21 | TABLE ACCESS FULL | SESSIONS | 953K| 42M| 7158 (3)| 00:01:26 | 1 | 2 |
|* 22 | INDEX RANGE SCAN | IND_VAL_EQUIP_ID | 1 | | 2 (0)| 00:00:01 | | |
| 23 | TABLE ACCESS FULL | EPA | 7973K| 76M| 23615 (5)| 00:04:44 | | |
| 24 | TABLE ACCESS BY INDEX ROWID | EQUIP_INST | 1 | 10 | 2 (0)| 00:00:01 | | |
|* 25 | INDEX UNIQUE SCAN | PK_EQUIP_ISNT | 1 | | 1 (0)| 00:00:01 | | |
|* 26 | TABLE ACCESS BY INDEX ROWID | SITE_INST | 1 | 30 | 2 (0)| 00:00:01 | | |
|* 27 | INDEX UNIQUE SCAN | PK_SITE_INST | 1 | | 1 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("DSLAM_PORT_PATH_ELEMENT"."CIRC_PATH_INST_ID"=NVL("CPE_PORT"."CIRC_PATH_INST_ID","CPE_PORT"."NEXT_PATH_
INST_ID"))
5 - filter("DSLAM_PORT_PATH_ELEMENT"."SEQUENCE"=1)
7 - access("SES"."NAS_IP"="DIP"."IP_ADDRESS32K" AND TO_NUMBER("DSLAM_CARD"."SLOT")=TO_NUMBER(SUBSTR("SES"."NAS_POR
T",4,2)) AND TO_NUMBER("DSLAM_PORT"."PORT_HUM_ID")=TO_NUMBER(SUBSTR("SES"."NAS_PORT",2,2)))
11 - access("DIP"."DSLAM"=TO_NUMBER(REPLACE(REPLACE("DSLAM_FRONT_SHELF"."DESCR",'STINGER FRONT '),'STINGER
FRONT','01')) AND "DSLAM_FRONT_SHELF"."SITE_INST_ID"="EXCHANGE"."SITE_INST_ID")
15 - access("EXCHANGE"."SITE_HUM_ID"="DIP"."EXCHANGE_ID")
16 - filter("DSLAM_FRONT_SHELF"."TYPE"='DSLAM SHELF')
18 - access("DSLAM_CARD"."EQUIP_INST_ID"="DSLAM_FRONT_SHELF"."EQUIP_INST_ID")
19 - access("DSLAM_CARD"."CARD_INST_ID"="DSLAM_PORT"."CARD_INST_ID")
21 - filter("SES"."TUNNEL_END"=0)
22 - access("DSLAM_PORT"."PORT_INST_ID"="DSLAM_PORT_PATH_ELEMENT"."PORT_INST_ID")
filter("DSLAM_PORT_PATH_ELEMENT"."PORT_INST_ID" IS NOT NULL)
25 - access("CPE_PORT"."EQUIP_INST_ID"="CPE_EQUIPMENT"."EQUIP_INST_ID")
26 - filter("CUSTOMER_SITE"."NUM"='DSL CUSTOMER')
27 - access("CPE_EQUIPMENT"."SITE_INST_ID"="CUSTOMER_SITE"."SITE_INST_ID")


Thanks,

Ken.

n.b. How do I put code in a code box?
Re: Problem with Equijoin in SQL [message #222249 is a reply to message #222192] Fri, 02 March 2007 08:52 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
http://www.orafaq.com/forum/?section=readingposting&t=help_index&66800/#style
Re: Problem with Equijoin in SQL [message #222373 is a reply to message #221966] Sat, 03 March 2007 08:39 Go to previous message
puneet.kakkar
Messages: 11
Registered: February 2007
Junior Member
Hi,

Can u try this approach:

Step1) Create a function safe_to_number

create function safe_to_number(txt in varchar2) return number is
begin
return to_number(txt);
exception when value_error then
return null;
end safe_to_number;
/

Step2) In the query you are using a view, where in you are using this select:
----------------------------------------------------------
to_number(replace(replace(dslam_front_shelf.descr , 'STINGER FRONT '), 'ST
----------------------------------------------------------

replace to_number with function:safe_to_number. recreate the view and verify the o/p of your query.

I hope it will resolve your problem.

Puneet
Previous Topic: simple but knowledgeable pls help
Next Topic: sweep to delete rcords from big table
Goto Forum:
  


Current Time: Wed Dec 07 14:32:34 CST 2016

Total time taken to generate the page: 0.10654 seconds