-
-
恩智浦凯文 初学数据Lv2
发表于2020-11-5 15:30
悬赏1
已解决
楼主
你好！我是一个初入小白。以下是我的问题， 请帮忙解答！感谢！
SQL输入里面的字段多人帮我一起检查过没有问题，包括你们的代理。不缺少右括号，但是运行就会有以下提示。
后期我们想不用SQL转而使用主题表引入的方式来创建。但是不知道为什么log format这个主题表里的数据无法导入（原来SQL部分）
SQL输入里面的字段多人帮我一起检查过没有问题，包括你们的代理。不缺少右括号，但是运行就会有以下提示。
后期我们想不用SQL转而使用主题表引入的方式来创建。但是不知道为什么log format这个主题表里的数据无法导入（原来SQL部分）
最近看过此主题的会员
15个回答
凹凸曼 发表于 2020-11-9 15:37
找到对应的数据库连接池然后查看库表里
或者你要是系统库表直接在这里点开数据库管理或者查看库表
在数据库里运行没有问题的。
现在运行出现新的报错如下： ORA-00904: "T0"."END_TIME_MASTER_ZH330": 标识符无效
也可以看出来是链接组建出了问题。
但是在SQL输入1中并没有任何相关"END_TIME_MASTER_ZH330"的语句。却在链接组件中导入了，并且运行报错，这个问题可以解答一下吗？
以下是SQL1的语句：
SELECT al2.trak_oper,
al5.pkgcode,
al5.alot_number,
al5.devc_number,
al1.step_name,
al1.start_quantity,
al2.start_time,
al5.trakrouting,
al3.component_part_id Compound,
al5.status,
al4.component_part_id LF,
al5.pkit_name,
al1.process_start_time,
al6.proc_signature,
al1.proc_name,
Instr(al6.proc_signature,'BE TO TEST2',1,1),
Instr(al6.proc_signature,'DEJUNK',1,1),
Instr(al6.proc_signature,'BE TO TEST1',1,1),
Instr(al6.proc_signature,'BE TO KESBI',1,1),
Instr(al6.proc_signature,'BE TO KESFT',1,1),
Instr(al6.proc_signature,'BE TO TTJBI',1,1),
Instr(al6.proc_signature,'REFLOW',1,1),
Instr(al6.proc_signature,'VM_CHECK',1,1),
Min(al7.start_time) Start_Time1,
Max(al7.end_time),
Min(al8.start_time) Start_Time2
FROM genstaging.mview$_aolot_hists al1,
genstaging.mview$_alot_oper_hists al2,
genstaging.mview$_bom_hard_links al3,
genstaging.mview$_bom_hard_links al4,
genstaging.mview$_ao_lots al5,
genstaging.mview$_processes al6,
genstaging.mview$_alot_oper_hists al7,
genstaging.mview$_alot_oper_hists al8
WHERE (al5.alot_number = al7.alot_number (+)
AND al5.alot_number = al1.alot_number
AND al5.alot_number = al2.alot_number
AND al5.pkit_name = al3.target_part_id
AND al5.pkit_name = al4.target_part_id
AND al6.proc_name = al1.proc_name
AND al8.alot_number = al5.alot_number)
AND ((al2.current_flag = 'Y'
AND al1.current_flag = 'Y'
AND al2.trak_oper IN ('CA110',
'ZH300',
'ZH310',
'ZH320',
'ZH330',
'ZH350',
'ZH380',
'ZH3A0',
'ZH450',
'ZH550',
'ZH800')
AND al3.location_cd = 'CT'
AND al3.comp_type_cd IN ('MOLD',
'MOLDG')
AND al4.location_cd = 'CT'
AND al4.comp_type_cd IN ('FRAME',
'FRCUD',
'FRCUF',
'FRCUG',
'FRCUH')
AND al5.pkgcode IN ('003X',
'006P',
'007Y',
'0098',
'009K',
'00A0',
'00DG',
'00E4',
'00E5',
'00KY',
'00M5',
'00N1',
'00TT',
'00UG',
'2003',
'2009',
'2011',
'2013',
'2016',
'2017',
'2018',
'2019',
'2020',
'2025',
'6016',
'6089',
'6117',
'6300',
'8256',
'8260',
'8274',
'8281',
'8426')
AND al7.trak_oper (+) = 'ZH380'))
GROUP BY al2.trak_oper,
al5.pkgcode,
al5.alot_number,
al5.devc_number,
al1.step_name,
al1.start_quantity,
al2.start_time,
al5.trakrouting,
al3.component_part_id,
al5.status,
al4.component_part_id,
al5.pkit_name,
al1.process_start_time,
al6.proc_signature,
al1.proc_name,
Instr(al6.proc_signature,'BE TO TEST2',1,1),
Instr(al6.proc_signature,'DEJUNK',1,1),
Instr(al6.proc_signature,'BE TO TEST1',1,1),
Instr(al6.proc_signature,'BE TO KESBI',1,1),
Instr(al6.proc_signature,'BE TO KESFT',1,1),
Instr(al6.proc_signature,'BE TO TTJBI',1,1),
Instr(al6.proc_signature,'REFLOW',1,1),
Instr(al6.proc_signature,'VM_CHECK',1,1)
恩智浦凯文 发表于 2020-11-11 09:55
在数据库里运行没有问题的。
现在运行出现新的报错如下： ORA-00904: "T0"."END_TIME_MASTER_ZH330": 标 ...
ORA-00904得检查一下sql中查询的信息是否在库表中存在，然后再注意单双引号以及大小写的应用
xxxl 发表于 2020-11-11 10:18
ORA-00904得检查一下sql中查询的信息是否在库表中存在，然后再注意单双引号以及大小写的应用 ...
单双引号，大小写等等都没有动过，在数据库中运行都没有问题啊。我们现在认为SQL输入没有问题了，问题在于连接组件。
在SQL输入1中并没有任何相关"END_TIME_MASTER_ZH330"的语句。却在链接组件中导入了，并且运行报错，这个问题可以解答一下吗？
恩智浦凯文 发表于 2020-11-11 12:31
单双引号，大小写等等都没有动过，在数据库中运行都没有问题啊。我们现在认为SQL输入没有问题了，问题在 ...
此问题已由同事对接，查出是sql输入组件中未进行初始化设置，导致sql语句中的字段没有和组件中的字段对应上，才会在运行时出现报错。
xxxl 发表于 2020-11-5 15:30
此问题已由同事对接，查出是sql输入组件中未进行初始化设置，导致sql语句中的字段没有和组件中的字段对应 ...
我进行了初始化之后再输入SQL，解决了输入和连接组件导入不对应的问题。但是现在又报错，如下：
2020-11-13 13:36:24 执行ETL过程[ETL过程1603785875580]出现异常：com.esen.exception.RuntimeException4I18N: 获取表[
SELECT T0.TRAK_OPER AS TRAK_OPER,T0.PKGCODE AS PKGCODE,T0.ALOT_NUMBER AS ALOT_NUMBER,T0.DEVC_NUMBER AS DEVC_NUMBER,T0.STEP_NAME AS STEP_NAME,T0.START_QUANTITY AS START_QUANTITY,T0.START_TIME AS START_TIME,T0.TRAKROUTING AS TRAKROUTING,T0.COMPOUND AS COMPOUND,T0.STATUS AS STATUS,T0.LF AS LF,T0.PKIT_NAME AS PKIT_NAME,T0.PROCESS_START_TIME AS PROCESS_START_TIME,T0.PROC_SIGNATURE AS PROC_SIGNATURE,T0.PROC_NAME AS PROC_NAME,T0."INSTR(AL6.PROC_SIGNATURE,'BETOTEST2',1,1)" AS "INSTR(AL6.PROC_SIGNATURE,'BETOTEST2',1,1)",T0."INSTR(AL6.PROC_SIGNATURE,'DEJUNK',1,1)" AS "INSTR(AL6.PROC_SIGNATURE,'DEJUNK',1,1)",T0."INSTR(AL6.PROC_SIGNATURE,'BETOTEST1',1,1)" AS "INSTR(AL6.PROC_SIGNATURE,'BETOTEST1',1,1)",T0."INSTR(AL6.PROC_SIGNATURE,'BETOKESBI',1,1)" AS "INSTR(AL6.PROC_SIGNATURE,'BETOKESBI',1,1)",T0."INSTR(AL6.PROC_SIGNATURE,'BETOKESFT',1,1)" AS "INSTR(AL6.PROC_SIGNATURE,'BETOKESFT',1,1)",T0."INSTR(AL6.PROC_SIGNATURE,'BETOTTJBI',1,1)" AS "INSTR(AL6.PROC_SIGNATURE,'BETOTTJBI',1,1)",T0."INSTR(AL6.PROC_SIGNATURE,'REFLOW',1,1)" AS "INSTR(AL6.PROC_SIGNATURE,'REFLOW',1,1)",T0."INSTR(AL6.PROC_SIGNATURE,'VM_CHECK',1,1)" AS "INSTR(AL6.PROC_SIGNATURE,'VM_CHECK',1,1)",T0.START_TIME1 AS START_TIME1,T0."MAX(AL7.END_TIME)" AS "MAX(AL7.END_TIME)",T0.START_TIME2 AS START_TIME2
FROM (
SELECT T0.TRAK_OPER AS TRAK_OPER,T0.PKGCODE AS PKGCODE,T0.ALOT_NUMBER AS ALOT_NUMBER,T0.DEVC_NUMBER AS DEVC_NUMBER,T0.STEP_NAME AS STEP_NAME,T0.START_QUANTITY AS START_QUANTITY,T0.START_TIME AS START_TIME,T0.TRAKROUTING AS TRAKROUTING,T0.COMPOUND AS COMPOUND,T0.STATUS AS STATUS,T0.LF AS LF,T0.PKIT_NAME AS PKIT_NAME,T0.PROCESS_START_TIME AS PROCESS_START_TIME,T0.PROC_SIGNATURE AS PROC_SIGNATURE,T0.PROC_NAME AS PROC_NAME,T0."INSTR(AL6.PROC_SIGNATURE,'BETOTEST2',1,1)" AS "INSTR(AL6.PROC_SIGNATURE,'BETOTEST2',1,1)",T0."INSTR(AL6.PROC_SIGNATURE,'DEJUNK',1,1)" AS "INSTR(AL6.PROC_SIGNATURE,'DEJUNK',1,1)",T0."INSTR(AL6.PROC_SIGNATURE,'BETOTEST1',1,1)" AS "INSTR(AL6.PROC_SIGNATURE,'BETOTEST1',1,1)",T0."INSTR(AL6.PROC_SIGNATURE,'BETOKESBI',1,1)" AS "INSTR(AL6.PROC_SIGNATURE,'BETOKESBI',1,1)",T0."INSTR(AL6.PROC_SIGNATURE,'BETOKESFT',1,1)" AS "INSTR(AL6.PROC_SIGNATURE,'BETOKESFT',1,1)",T0."INSTR(AL6.PROC_SIGNATURE,'BETOTTJBI',1,1)" AS "INSTR(AL6.PROC_SIGNATURE,'BETOTTJBI',1,1)",T0."INSTR(AL6.PROC_SIGNATURE,'REFLOW',1,1)" AS "INSTR(AL6.PROC_SIGNATURE,'REFLOW',1,1)",T0."INSTR(AL6.PROC_SIGNATURE,'VM_CHECK',1,1)" AS "INSTR(AL6.PROC_SIGNATURE,'VM_CHECK',1,1)",T0.START_TIME1 AS START_TIME1,T0."MAX(AL7.END_TIME)" AS "MAX(AL7.END_TIME)",T0.START_TIME2 AS START_TIME2
FROM (SELECT al2.trak_oper,
al5.pkgcode,
al5.alot_number,
al5.devc_number,
al1.step_name,
al1.start_quantity,
al2.start_time,
al5.trakrouting,
al3.component_part_id Compound,
al5.status,
al4.component_part_id LF,
al5.pkit_name,
al1.process_start_time,
al6.proc_signature,
al1.proc_name,
Instr(al6.proc_signature,'BE TO TEST2',1,1),
Instr(al6.proc_signature,'DEJUNK',1,1),
Instr(al6.proc_signature,'BE TO TEST1',1,1),
Instr(al6.proc_signature,'BE TO KESBI',1,1),
Instr(al6.proc_signature,'BE TO KESFT',1,1),
Instr(al6.proc_signature,'BE TO TTJBI',1,1),
Instr(al6.proc_signature,'REFLOW',1,1),
Instr(al6.proc_signature,'VM_CHECK',1,1),
Min(al7.start_time) Start_Time1,
Max(al7.end_time),
Min(al8.start_time) Start_Time2
FROM genstaging.mview$_aolot_hists al1,
genstaging.mview$_alot_oper_hists al2,
genstaging.mview$_bom_hard_links al3,
genstaging.mview$_bom_hard_links al4,
genstaging.mview$_ao_lots al5,
genstaging.mview$_processes al6,
genstaging.mview$_alot_oper_hists al7,
genstaging.mview$_alot_oper_hists al8
WHERE (al5.alot_number = al7.alot_number (+)
AND al5.alot_number = al1.alot_number
AND al5.alot_number = al2.alot_number
AND al5.pkit_name = al3.target_part_id
AND al5.pkit_name = al4.target_part_id
AND al6.proc_name = al1.proc_name
AND al8.alot_number = al5.alot_number)
AND ((al2.current_flag = 'Y'
AND al1.current_flag = 'Y'
AND al2.trak_oper IN ('CA110',
'ZH300',
'ZH310',
'ZH320',
'ZH330',
'ZH350',
'ZH380',
'ZH3A0',
'ZH450',
'ZH550',
'ZH800')
AND al3.location_cd = 'CT'
AND al3.comp_type_cd IN ('MOLD',
'MOLDG')
AND al4.location_cd = 'CT'
AND al4.comp_type_cd IN ('FRAME',
'FRCUD',
'FRCUF',
'FRCUG',
'FRCUH')
AND al5.pkgcode IN ('003X',
'006P',
'007Y',
'0098',
'009K',
'00A0',
'00DG',
'00E4',
'00E5',
'00KY',
'00M5',
'00N1',
'00TT',
'00UG',
'2003',
'2009',
'2011',
'2013',
'2016',
'2017',
'2018',
'2019',
'2020',
'2025',
'6016',
'6089',
'6117',
'6300',
'8256',
'8260',
'8274',
'8281',
'8426')
AND al7.trak_oper (+) = 'ZH380'))
GROUP BY al2.trak_oper,
al5.pkgcode,
al5.alot_number,
al5.devc_number,
al1.step_name,
al1.start_quantity,
al2.start_time,
al5.trakrouting,
al3.component_part_id,
al5.status,
al4.component_part_id,
al5.pkit_name,
al1.process_start_time,
al6.proc_signature,
al1.proc_name,
Instr(al6.proc_signature,'BE TO TEST2',1,1),
Instr(al6.proc_signature,'DEJUNK',1,1),
Instr(al6.proc_signature,'BE TO TEST1',1,1),
Instr(al6.proc_signature,'BE TO KESBI',1,1),
Instr(al6.proc_signature,'BE TO KESFT',1,1),
Instr(al6.proc_signature,'BE TO TTJBI',1,1),
Instr(al6.proc_signature,'REFLOW',1,1),
Instr(al6.proc_signature,'VM_CHECK',1,1)) T0) T0]元数据信息出现异常
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:873)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1491)
at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:406)
at com.esen.jdbc.pool.PooledStatement.getQureyResultSet(PooledStatement.java:54)
at com.esen.jdbc.pool.impl.oracle.OraclePooledStatement.executeQuery(OraclePooledStatement.java:22)
at com.esen.jdbc.SqlExecuter.excuteQurey(SqlExecuter.java:535)
at com.esen.jdbc.SqlExecuter.executeSql(SqlExecuter.java:155)
at com.esen.jdbc.SqlExecuter.executeQuery(SqlExecuter.java:108)
at com.esen.jdbc.dialect.impl.DialectImpl.getResultMetaData(DialectImpl.java:365)
at com.esen.jdbc.dialect.impl.DialectImpl.getResultMetaData(DialectImpl.java:335)
at com.esen.jdbc.dialect.impl.DialectImpl.getQueryResultMetaData(DialectImpl.java:406)
at com.esen.ecore.util.JdbcUtils.getMetaData(JdbcUtils.java:191)
at com.esen.ecore.util.JdbcUtils.getMetaData(JdbcUtils.java:175)
at com.esen.edf.data.migrate.common.buffer.BufferedJdbcMigrateManager.execute(BufferedJdbcMigrateManager.java:59)
at com.esen.edf.data.migrate.DataMigrationUtils.createTableByCopyData(DataMigrationUtils.java:395)
at com.esen.edf.data.migrate.DataMigrationUtils.migrateIntoTempTable(DataMigrationUtils.java:335)
at com.esen.edf.data.migrate.DataMigrationUtils.migrateIntoTempTable(DataMigrationUtils.java:317)
at com.esen.edf.domain.job.EtlJob.migrateIntoTargetDataSource(EtlJob.java:790)
at com.esen.edf.domain.job.EtlJob.processMultiinputWidget(EtlJob.java:760)
at com.esen.edf.domain.job.EtlJob.beforeWidgetProcess(EtlJob.java:738)
at com.esen.edf.domain.job.EtlJob.processEtlJob(EtlJob.java:548)
at com.esen.edf.domain.job.EtlJob.process(EtlJob.java:445)
at com.esen.edf.domain.job.EtlJobRequestTask.call(EtlJobRequestTask.java:69)
at com.esen.edf.domain.job.EtlJobRequestTask.call(EtlJobRequestTask.java:26)
at com.esen.ethreadpool.req.RequestTaskFutureImpl.call(RequestTaskFutureImpl.java:100)
at com.esen.scheduling.LocaleTask.call(LocaleTask.java:56)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: com.esen.jdbc.pool.impl.oracle.OraclePooledSQLException: ORA-00972: 标识符过长
... 39 more
