I want to achieve such a query phones belonging function:
In A query table after the phone, tell he is landline or mobile phone number, if it is A phone number is associated ADPHOE field in the table B, if it is machine number to be associated in the table B ADCODE I should how to implement will be good?

CodePudding user response:

Table and table field, are listed separately, or someone else is bad to write SQL statements,

CodePudding user response:

1. Call A table corresponding to which field?
(2) is associated with A table of the phone B table ADPHOE fields or ADCODE field?
Usually pay more attention to the way of your questions,

CodePudding user response:

A caller field phone Numbers are stored in the table, table B adphone and adcode is to distinguish the fixed phone number and the number belongs to

CodePudding user response:

Ok, first post also don't ask questions... Mainly leftjoin dynamic judgment when you need this phone is the and adphoe or adcode match

LEFT the JOIN AD_ATTRIBUTION aa ON
CASE
The WHEN LENGTH (rtrim (n.c alling_pty))=12 THEN
The RTRIM (aa. ADCODE)
- SUBSTR (RTRIM (n.c alling_pty), 1, 4)

The WHEN LENGTH (rtrim (n.c alling_pty))=11 THEN
(
CASE
The WHEN SUBSTR (RTRIM (n.c alling_pty), 1, 1)='0' THEN
The RTRIM (aa. ADCODE)
- SUBSTR (rtrim (n.c alling_pty), 1, 4)
The ELSE
The RTRIM (aa. ADPHONE)
- SUBSTR (RTRIM (n.c alling_pty), 1, 7)
END
)
END=CASE
The WHEN LENGTH (rtrim (n.c alling_pty))=12 THEN

The SUBSTR (RTRIM (n.c alling_pty), 1, 4)

The WHEN LENGTH (rtrim (n.c alling_pty))=11 THEN
(
CASE
The WHEN SUBSTR (RTRIM (n.c alling_pty), 1, 1)='0' THEN
The SUBSTR (rtrim (n.c alling_pty), 1, 4)
The ELSE
The SUBSTR (RTRIM (n.c alling_pty), 1, 7)
END
)
END
I judge now with this method, very slow want to know is there a more efficient way

CodePudding user response:

 
Select a. * 
From a 
Left on the join b a.c aller=decode (regexp_instr (a.c aller, '^ 1 [3458] \ d {9} $'), 1, b.a dphone, b.a dcode); 

Or:
SQL] [p-code=
Select a. *
From a
Left on the join b a.c aller=decode (regexp_instr (a.c aller, '^ (010 | 02 \ d | 0 [3-9] \ d {2})? \ d {6, 8} $'), 1, b.a dcode, b.a dphone);
[/code]

CodePudding user response:

Or:

 
Select a. * 
From a 
Left on the join b a.c aller=decode (regexp_instr (a.c aller, '^ (010 | 02 \ d | 0 [3-9] \ d {2})? \ d {6, 8} $'), 1, b.a dcode, b.a dphone); 

CodePudding user response:

Decode (regexp_instr (a.c aller, '^ 1 [3458] \ d {9} $'), 1, b.a dphone, b.a dcode); Want to ask here is match return 1 of the match string? Should not

CodePudding user response:

No outside connection is good