使用to_number,to_char转换的连接列易造成错误的执行计划

本文通过实例展示了在Oracle数据库中,当表的某列类型为varchar2但存储数值数据时,使用to_number函数进行连接查询可能导致执行计划错误及性能下降。通过对比正确的执行计划与错误的执行计划,强调了合理设计表结构的重要性,并提供了使用提示来优化执行计划的方法。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

场景:偶尔会遇到一些表的列由于历史原因,本来都是数据的却是varchar2类型,结果只能通过建立to_char函数来进行连接,其结果易造成执行计划的不稳定以及错误的产生,
写SQL以及表设计时需注意;

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> create table t1 as select * from dba_objects  where 1=2;

Table created.

SQL> create table t2 as select * from dba_objects where 1=2;

Table created.

SQL> create table t3 as select * from dba_objects where 1=2;

Table created.

SQL> alter table t3 modify(object_id varchar2(40));

Table altered.

SQL> insert into t1 select * from dba_objects;

75253 rows created.

SQL> l
  1* insert into t1 select * from dba_objects
SQL> c/t1/t2
  1* insert into t2 select * from dba_objects
SQL> /

75253 rows created.

SQL> c/t2/t3
  1* insert into t3 select * from dba_objects
SQL> /

75253 rows created.

SQL> commit;

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NAMESPACE                                          NUMBER
 EDITION_NAME                                       VARCHAR2(30)

SQL> desc t2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NAMESPACE                                          NUMBER
 EDITION_NAME                                       VARCHAR2(30)

SQL> desc t3
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          VARCHAR2(40)               --object_id类型设为varchar2
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NAMESPACE                                          NUMBER
 EDITION_NAME                                       VARCHAR2(30)

SQL> create index idx_t1_object_id on t1(object_id);

Index created.

SQL> create index idx_t2_object_id on t2(object_id);

Index created.

SQL> create index idx_t3_object_id on t3(to_number(object_id));  --t3表列object_id为函数索引

Index created.

SQL> CREATE TABLE T4 AS SELECT * FROM DBA_OBJECTS;

Table created.

SQL> CREATE INDEX IDX_T4_OBJECT_ID ON T4(OBJECT_ID);

Index created.

exec dbms_stats.gather_table_stats('AIKI','T1',CASCADE=>TRUE,METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 1',estimate_percent=>100);
exec dbms_stats.gather_table_stats('AIKI','T2',CASCADE=>TRUE,METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 1',estimate_percent=>100);
exec dbms_stats.gather_table_stats('AIKI','T3',CASCADE=>TRUE,METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 1',estimate_percent=>100);
exec dbms_stats.gather_table_stats('AIKI','T4',CASCADE=>TRUE,METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 1',estimate_percent=>100);

--没有使用转换函数列的正确执行计划
SQL_ID  279zrj0d22082, child number 0
-------------------------------------
select t1.object_id, t1.object_name, T4.status, t2.owner from t1,T4,t2
where t1.object_id=T4.object_id and t2.object_id=T4.object_id and (
t1.object_id in (108,1080,10800) or t2.object_id in(108,1080,10800))

Plan hash value: 1275242484

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                  |      1 |        |      3 |00:00:00.01 |      48 |
|   1 |  CONCATENATION                   |                  |      1 |        |      3 |00:00:00.01 |      48 |
|   2 |   NESTED LOOPS                   |                  |      1 |        |      3 |00:00:00.01 |      27 |
|   3 |    NESTED LOOPS                  |                  |      1 |      3 |      3 |00:00:00.01 |      24 |
|   4 |     NESTED LOOPS                 |                  |      1 |      3 |      3 |00:00:00.01 |      18 |
|   5 |      INLIST ITERATOR             |                  |      1 |        |      3 |00:00:00.01 |       9 |
|   6 |       TABLE ACCESS BY INDEX ROWID| T2               |      3 |      3 |      3 |00:00:00.01 |       9 |
|*  7 |        INDEX RANGE SCAN          | IDX_T2_OBJECT_ID |      3 |      3 |      3 |00:00:00.01 |       6 |
|   8 |      TABLE ACCESS BY INDEX ROWID | T4               |      3 |      1 |      3 |00:00:00.01 |       9 |
|*  9 |       INDEX RANGE SCAN           | IDX_T4_OBJECT_ID |      3 |      1 |      3 |00:00:00.01 |       6 |
|* 10 |     INDEX RANGE SCAN             | IDX_T1_OBJECT_ID |      3 |      1 |      3 |00:00:00.01 |       6 |
|  11 |    TABLE ACCESS BY INDEX ROWID   | T1               |      3 |      1 |      3 |00:00:00.01 |       3 |
|  12 |   NESTED LOOPS                   |                  |      1 |        |      0 |00:00:00.01 |      21 |
|  13 |    NESTED LOOPS                  |                  |      1 |      3 |      0 |00:00:00.01 |      21 |
|  14 |     NESTED LOOPS                 |                  |      1 |      3 |      3 |00:00:00.01 |      16 |
|  15 |      INLIST ITERATOR             |                  |      1 |        |      3 |00:00:00.01 |       8 |
|  16 |       TABLE ACCESS BY INDEX ROWID| T1               |      3 |      3 |      3 |00:00:00.01 |       8 |
|* 17 |        INDEX RANGE SCAN          | IDX_T1_OBJECT_ID |      3 |      3 |      3 |00:00:00.01 |       5 |
|  18 |      TABLE ACCESS BY INDEX ROWID | T4               |      3 |      1 |      3 |00:00:00.01 |       8 |
|* 19 |       INDEX RANGE SCAN           | IDX_T4_OBJECT_ID |      3 |      1 |      3 |00:00:00.01 |       5 |
|* 20 |     INDEX RANGE SCAN             | IDX_T2_OBJECT_ID |      3 |      1 |      0 |00:00:00.01 |       5 |
|  21 |    TABLE ACCESS BY INDEX ROWID   | T2               |      0 |      1 |      0 |00:00:00.01 |       0 |
---------------------------------------------------------------------------------------------------------------

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

   7 - access(("T2"."OBJECT_ID"=108 OR "T2"."OBJECT_ID"=1080 OR "T2"."OBJECT_ID"=10800))
   9 - access("T2"."OBJECT_ID"="T4"."OBJECT_ID")
  10 - access("T1"."OBJECT_ID"="T4"."OBJECT_ID")
  17 - access(("T1"."OBJECT_ID"=108 OR "T1"."OBJECT_ID"=1080 OR "T1"."OBJECT_ID"=10800))
  19 - access("T1"."OBJECT_ID"="T4"."OBJECT_ID")
  20 - access("T2"."OBJECT_ID"="T4"."OBJECT_ID")
       filter((LNNVL("T2"."OBJECT_ID"=108) AND LNNVL("T2"."OBJECT_ID"=1080) AND
              LNNVL("T2"."OBJECT_ID"=10800)))


47 rows selected.

--使用转换函数to_number造成的错误执行计划
SQL> 
SQL> 
SQL> select  
  2  t1.object_id,
  3  t1.object_name,
  4  t3.status,
  5  t2.owner
  6  from t1,t3,t2
  7  where t1.object_id=to_number(t3.object_id)
  8  and t2.object_id=to_number(t3.object_id)
  9  and ( t1.object_id in (108,1080,10800) or t2.object_id in(108,1080,10800)) ;

 OBJECT_ID OBJECT_NAME                                                                                                                      STATUS  OWNER
---------- -------------------------------------------------------------------------------------------------------------------------------- ------- ------------------------------
       108 I_ACCESS1                                                                                                                        VALID   SYS
      1080 I_METAPATHMAP$                                                                                                                   VALID   SYS
     10800 KU$_PLUGTS_CHECKPL_VIEW                                                                                                          VALID   SYS

SQL> @allstat

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0pm99r7nvp84r, child number 0
-------------------------------------
select t1.object_id, t1.object_name, t3.status, t2.owner from t1,t3,t2
where t1.object_id=to_number(t3.object_id) and
t2.object_id=to_number(t3.object_id) and ( t1.object_id in
(108,1080,10800) or t2.object_id in(108,1080,10800))

Plan hash value: 3864638661
--CBO的表T1错误选择全表扫描
-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                  |      1 |        |      3 |00:00:00.07 |    2268 |       |       |          |
|   1 |  CONCATENATION                  |                  |      1 |        |      3 |00:00:00.07 |    2268 |       |       |          |
|*  2 |   HASH JOIN                     |                  |      1 |      3 |      3 |00:00:00.06 |    1136 |   843K|   843K|  611K (0)|
|   3 |    NESTED LOOPS                 |                  |      1 |      3 |      3 |00:00:00.01 |      16 |       |       |          |
|   4 |     INLIST ITERATOR             |                  |      1 |        |      3 |00:00:00.01 |       8 |       |       |          |
|   5 |      TABLE ACCESS BY INDEX ROWID| T2               |      3 |      3 |      3 |00:00:00.01 |       8 |       |       |          |
|*  6 |       INDEX RANGE SCAN          | IDX_T2_OBJECT_ID |      3 |      3 |      3 |00:00:00.01 |       5 |       |       |          |
|   7 |     TABLE ACCESS BY INDEX ROWID | T3               |      3 |      1 |      3 |00:00:00.01 |       8 |       |       |          |
|*  8 |      INDEX RANGE SCAN           | IDX_T3_OBJECT_ID |      3 |      1 |      3 |00:00:00.01 |       5 |       |       |          |
|   9 |    TABLE ACCESS FULL            | T1               |      1 |  81018 |  75253 |00:00:00.02 |    1120 |       |       |          |
|  10 |   NESTED LOOPS                  |                  |      1 |        |      0 |00:00:00.01 |    1132 |       |       |          |
|  11 |    NESTED LOOPS                 |                  |      1 |      3 |      0 |00:00:00.01 |    1132 |       |       |          |
|  12 |     NESTED LOOPS                |                  |      1 |      3 |      3 |00:00:00.01 |    1127 |       |       |          |
|* 13 |      TABLE ACCESS FULL          | T1               |      1 |      3 |      3 |00:00:00.01 |    1119 |       |       |          |
|  14 |      TABLE ACCESS BY INDEX ROWID| T3               |      3 |      1 |      3 |00:00:00.01 |       8 |       |       |          |
|* 15 |       INDEX RANGE SCAN          | IDX_T3_OBJECT_ID |      3 |      1 |      3 |00:00:00.01 |       5 |       |       |          |
|* 16 |     INDEX RANGE SCAN            | IDX_T2_OBJECT_ID |      3 |      1 |      0 |00:00:00.01 |       5 |       |       |          |
|  17 |    TABLE ACCESS BY INDEX ROWID  | T2               |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("T1"."OBJECT_ID"=TO_NUMBER("OBJECT_ID"))
   6 - access(("T2"."OBJECT_ID"=108 OR "T2"."OBJECT_ID"=1080 OR "T2"."OBJECT_ID"=10800))
   8 - access("T2"."OBJECT_ID"="T3"."SYS_NC00016$")
  13 - filter(("T1"."OBJECT_ID"=108 OR "T1"."OBJECT_ID"=1080 OR "T1"."OBJECT_ID"=10800))
  15 - access("T1"."OBJECT_ID"="T3"."SYS_NC00016$")
  16 - access("T2"."OBJECT_ID"="T3"."SYS_NC00016$")
       filter((LNNVL("T2"."OBJECT_ID"=108) AND LNNVL("T2"."OBJECT_ID"=1080) AND LNNVL("T2"."OBJECT_ID"=10800)))

Note
-----
   - dynamic sampling used for this statement (level=2)


47 rows selected.


--由于对表T1到T4的列object_id分布我们都清楚,都中唯一值,过滤性很强
所以可以采用人工HINT去干预,也预示着我们在用to_char,to_number对列值进行转换连接的SQL,
需谨慎,极易造成执行计划的不稳定以及耗能过高;
--加入T1表的OBJECT_ID索引HINT纠正
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  2qfqkjg1fzy0k, child number 0
-------------------------------------
select   /*+ index(t1(object_id) */ t1.object_id, t1.object_name,
t3.status, t2.owner from t1,t3,t2 where
t1.object_id=to_number(t3.object_id) and
t2.object_id=to_number(t3.object_id) and ( t1.object_id in
(108,1080,10800) or t2.object_id in(108,1080,10800))

Plan hash value: 3376884351

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                  |      1 |        |      3 |00:00:00.01 |      48 |
|   1 |  CONCATENATION                   |                  |      1 |        |      3 |00:00:00.01 |      48 |
|   2 |   NESTED LOOPS                   |                  |      1 |        |      3 |00:00:00.01 |      27 |
|   3 |    NESTED LOOPS                  |                  |      1 |      3 |      3 |00:00:00.01 |      24 |
|   4 |     NESTED LOOPS                 |                  |      1 |      3 |      3 |00:00:00.01 |      18 |
|   5 |      INLIST ITERATOR             |                  |      1 |        |      3 |00:00:00.01 |       9 |
|   6 |       TABLE ACCESS BY INDEX ROWID| T2               |      3 |      3 |      3 |00:00:00.01 |       9 |
|*  7 |        INDEX RANGE SCAN          | IDX_T2_OBJECT_ID |      3 |      3 |      3 |00:00:00.01 |       6 |
|   8 |      TABLE ACCESS BY INDEX ROWID | T3               |      3 |      1 |      3 |00:00:00.01 |       9 |
|*  9 |       INDEX RANGE SCAN           | IDX_T3_OBJECT_ID |      3 |      1 |      3 |00:00:00.01 |       6 |
|* 10 |     INDEX RANGE SCAN             | IDX_T1_OBJECT_ID |      3 |      1 |      3 |00:00:00.01 |       6 |
|  11 |    TABLE ACCESS BY INDEX ROWID   | T1               |      3 |      1 |      3 |00:00:00.01 |       3 |
|  12 |   NESTED LOOPS                   |                  |      1 |        |      0 |00:00:00.01 |      21 |
|  13 |    NESTED LOOPS                  |                  |      1 |      3 |      0 |00:00:00.01 |      21 |
|  14 |     NESTED LOOPS                 |                  |      1 |      3 |      3 |00:00:00.01 |      16 |
|  15 |      INLIST ITERATOR             |                  |      1 |        |      3 |00:00:00.01 |       8 |
|  16 |       TABLE ACCESS BY INDEX ROWID| T1               |      3 |      3 |      3 |00:00:00.01 |       8 |
|* 17 |        INDEX RANGE SCAN          | IDX_T1_OBJECT_ID |      3 |      3 |      3 |00:00:00.01 |       5 |
|  18 |      TABLE ACCESS BY INDEX ROWID | T3               |      3 |      1 |      3 |00:00:00.01 |       8 |
|* 19 |       INDEX RANGE SCAN           | IDX_T3_OBJECT_ID |      3 |      1 |      3 |00:00:00.01 |       5 |
|* 20 |     INDEX RANGE SCAN             | IDX_T2_OBJECT_ID |      3 |      1 |      0 |00:00:00.01 |       5 |
|  21 |    TABLE ACCESS BY INDEX ROWID   | T2               |      0 |      1 |      0 |00:00:00.01 |       0 |
---------------------------------------------------------------------------------------------------------------

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

   7 - access(("T2"."OBJECT_ID"=108 OR "T2"."OBJECT_ID"=1080 OR "T2"."OBJECT_ID"=10800))
   9 - access("T2"."OBJECT_ID"="T3"."SYS_NC00016$")
  10 - access("T1"."OBJECT_ID"="T3"."SYS_NC00016$")
  17 - access(("T1"."OBJECT_ID"=108 OR "T1"."OBJECT_ID"=1080 OR "T1"."OBJECT_ID"=10800))
  19 - access("T1"."OBJECT_ID"="T3"."SYS_NC00016$")
  20 - access("T2"."OBJECT_ID"="T3"."SYS_NC00016$")
       filter((LNNVL("T2"."OBJECT_ID"=108) AND LNNVL("T2"."OBJECT_ID"=1080) AND
              LNNVL("T2"."OBJECT_ID"=10800)))


49 rows selected.

SQL> 

如下SQL需查找刷卡卡点资料,刷卡时间在排班时间规定的上班时间点和下班时间点和加班时间点加1分钟范围内,WP_TYPE(1:上班,2:下班),IS_NEXT_DAY(1:次日卡)请协助修改, 并优化oracle如下SQL性能,提高执行效率和减少资源占用: WITH work_s AS( SELECT A.EMP_CODE,A.EMPNAME,A.WORK_DATE,B.WS_NUMBER,B.WS_SIMPLE_NAME,C.BTW_TIME_TOTAL,C.LAST_TIME_TOTAL,C.Last_start_time,C.last_end_time, case when D.WP_TYPE=2 AND D.IS_NEXT_DAY=1 then TO_TIMESTAMP(to_char(A.WORK_DATE,'yyyy/mm/dd')||''||to_char(D.POINT_TIME,'hh24:mi:ss'))+ 1 ELSE TO_TIMESTAMP(to_char(A.WORK_DATE,'yyyy/mm/dd')||''||to_char(D.POINT_TIME,'hh24:mi:ss')) end POINT_TIME, D.ADV_TIME,D.OVER_TIME,D.WP_TYPE,D.IS_NEXT_DAY, case when D.WP_TYPE=2 AND D.IS_NEXT_DAY=1 then CASE WHEN B.WS_NUMBER='035A' OR B.WS_NUMBER='035B' THEN TO_TIMESTAMP(to_char(A.WORK_DATE,'yyyy/mm/dd')||''||to_char(D.POINT_TIME,'hh24:mi:ss'))+ 1 +C.LAST_TIME_TOTAL/24/60+1.5/24 ELSE TO_TIMESTAMP(to_char(A.WORK_DATE,'yyyy/mm/dd')||''||to_char(D.POINT_TIME,'hh24:mi:ss'))+ 1 +C.LAST_TIME_TOTAL/24/60+2/24 END when D.WP_TYPE=2 then CASE WHEN B.WS_NUMBER='035A' OR B.WS_NUMBER='035B' THEN TO_TIMESTAMP(to_char(A.WORK_DATE,'yyyy/mm/dd')||''||to_char(D.POINT_TIME,'hh24:mi:ss'))+C.LAST_TIME_TOTAL/24/60+1.5/24 WHEN B.WS_NUMBER='005A' THEN TO_TIMESTAMP(to_char(A.WORK_DATE,'yyyy/mm/dd')||''||to_char(D.POINT_TIME,'hh24:mi:ss')) +C.LAST_TIME_TOTAL/24/60+1/24 ELSE TO_TIMESTAMP(to_char(A.WORK_DATE,'yyyy/mm/dd')||''||to_char(D.POINT_TIME,'hh24:mi:ss'))+C.LAST_TIME_TOTAL/24/60+2/24 END end OVERPOINT_TIME, CASE when D.WP_TYPE=2 then CASE WHEN B.WS_NUMBER='005A' THEN TO_TIMESTAMP(to_char(A.WORK_DATE,'yyyy/mm/dd')||''||to_char(D.POINT_TIME,'hh24:mi:ss')) +C.LAST_TIME_TOTAL/24/60+2/24+2/24 END end OVERPOINT_TIME2 FROM HCP.v_clk_tz_emp_workday_info A JOIN HCP.v_clk_work_shift B ON A.WS_ID=B.ID JOIN HCP.v_clk_work_block C ON A.WS_ID = C.WS_ID JOIN HCP.v_clk_work_point D ON C.ID = D.WB_ID WHERE A.WORK_DATE= trunc(SYSDATE-1) ), attendance_data AS ( SELECT DECODE(HPB.WORK_PLACE, 0, 'LSP1', 1, 'LSP2', 2, 'LSP3', HPB.WORK_PLACE) AS MASTER_NO, DECODE(HPB.WORK_PLACE, 0, '一园', 1, '二园', 2, '三园', HPB.WORK_PLACE) AS WORK_PLACE, HPB.EMPCODE, HPB.EMPNAME, FF.FGT02, FF.FGT08, SUBSTR(FF.FGT08,3,1) AS card_type, HPB.RANK_NAME, HPB.ORG_NAME, DECODE(H.SALARY_TYPE,1,'月薪',2,'计时',3,'计件',H.SALARY_TYPE) AS SALARY_TYPE FROM FINGER5.FGT_FILE FF JOIN HCP.TZ_EMP_IN HPB ON FF.FGT10 = HPB.EMPCODE JOIN HCP.v_clk_tz_employee H ON FF.FGT10 = H.EMPCODE JOIN work_s A ON A.EMP_CODE=FF.FGT10 WHERE FF.FGT02 >= trunc(SYSDATE-1) AND FF.FGT02 < trunc(SYSDATE-1) + 1 -- 昨天全天范围 AND FF.FGT03 <> '4' -- 排除无效打卡 AND LENGTH(FF.FGT08) = 5 AND SUBSTR(FF.FGT08,3,1) IN ('1','2') -- 只考虑上班卡和下班卡 AND ( (SUBSTR(FF.FGT08,3,1) = '1' -- 上班卡 AND A.WP_TYPE=1 AND FF.FGT02 >= A.POINT_TIME AND FF.FGT02 < A.POINT_TIME + INTERVAL '1' MINUTE ) OR (SUBSTR(FF.FGT08,3,1) = '2' -- 下班卡 AND A.WP_TYPE=2 AND (CASE WHEN H.SALARY_TYPE=1 AND FF.FGT02 >= A.POINT_TIME AND FF.FGT02 < A.POINT_TIME + INTERVAL '1' MINUTE THEN 1 WHEN A.WS_NUMBER='005A' AND ((FF.FGT02 >= A.POINT_TIME AND FF.FGT02 < A.POINT_TIME + INTERVAL '1' MINUTE) OR (FF.FGT02 >= A.OVERPOINT_TIME AND FF.FGT02 < A.OVERPOINT_TIME + INTERVAL '1' MINUTE) OR (FF.FGT02 >= A.OVERPOINT_TIME2 AND FF.FGT02 < A.OVERPOINT_TIME2 + INTERVAL '1' MINUTE)) THEN 1 WHEN (FF.FGT02 >= A.POINT_TIME AND FF.FGT02 < A.POINT_TIME + INTERVAL '1' MINUTE) OR (FF.FGT02 >= A.OVERPOINT_TIME AND FF.FGT02 < A.OVERPOINT_TIME + INTERVAL '1' MINUTE) THEN 1 END)=1 ) ) ), attendance_filtered AS ( SELECT MASTER_NO, WORK_PLACE, EMPCODE, EMPNAME, FGT02, FGT08, card_type, DECODE(card_type, '1', '上班卡', '2', '下班卡') AS status, RANK_NAME, ORG_NAME, ROW_NUMBER() OVER ( PARTITION BY EMPCODE, card_type ORDER BY FGT02 ASC ) AS rank -- 按员工和打卡类型分区,取最早记录 FROM attendance_data ), tz_opr AS ( SELECT KLV.MEANING, KLV.DESCRIPTION, KLV.REVERSE2, KLV.REVERSE1, KLV.REVERSE3 IP, KLV.REVERSE4 FROM HCP.KS_LOOKUP_VALUE KLV WHERE KLV.LOOKUP_TYPE = 'KQ_SEPARATE_MACHINE' AND KLV.ENABLED = 'Y' ) SELECT MASTER_NO, WORK_PLACE AS MASTER_NAME, EMPCODE AS ID_NO_SZ, EMPNAME AS NAME_SZ, FGT02 AS RECORDTIME, FGT08 AS MACHINE_NO, trunc(SYSDATE-1) AS CDAY, -- 修改为TRUNC确保纯日期输出 A.REVERSE4 AS MACHINE_LOC, status AS SHIFTTYPE, RANK_NAME AS TITLE_NO, HCP.SF_LSHR_NO_GET(EMPCODE, 18) AS TITLE_NAME, HCP.SF_LSHR_NO_GET(EMPCODE, 2) AS SEGMENT_NO, HCP.SF_LSHR_NO_GET(EMPCODE, 13) AS SEGMENT_NAME, SUBSTR(HCP.SF_LSHR_NO_GET(EMPCODE, 15), LENGTH(HCP.SF_LSHR_NO_GET(EMPCODE, 15))-5) AS DEPT_NO, -- 取后6位 HCP.SF_LSHR_NO_GET(EMPCODE, 15) AS DEPT_NAME, HCP.SF_LSHR_NO_GET(HCP.SF_LSHR_NO_GET(EMPCODE, 9), 8) AS TOEMAIL, HCP.SF_LSHR_NO_GET(HCP.SF_LSHR_NO_GET(EMPCODE, 10), 8) || ';' || HCP.SF_LSHR_NO_GET(HCP.SF_LSHR_NO_GET(EMPCODE, 11), 8) AS CCEMAIL, '' AS TEMP_COMPANY FROM attendance_filtered JOIN tz_opr A ON FGT08 = A.MEANING WHERE rank = 1 -- 取每个员工每个打卡类型的最早记录 ORDER BY status, FGT02; -- 按打卡类型和时间排序
最新发布
07-30
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值