3个表,a表(aa),b表(aa,bb),c表(aa,cc) a表中有个字段是aa,b表中有个字段是aa,c表中有个字段是aa
select
t1.aa,
t2.bb,
t3.cc
from t1,t2,t3
where t1.aa (+)= t2.aa
and t1.aa (+) = t3.aa
搜索 在oracle数据库中如上写法,
报错如下: ora-01417:a table may be outer joined to at most one other table
我需要获取的是以T2为主表的所有50000条数据
t2与T3表也有个字段关联
难道只能让T2和T3先进行LEFT JOIN 然后在LEFT JOIN T1吗
select
t1.aa,
t2.bb,
t3.cc
from t1,t2,t3
where t1.aa (+)= t2.aa
and t1.aa (+) = t3.aa
搜索 在oracle数据库中如上写法,
报错如下: ora-01417:a table may be outer joined to at most one other table
我需要获取的是以T2为主表的所有50000条数据
t2与T3表也有个字段关联
难道只能让T2和T3先进行LEFT JOIN 然后在LEFT JOIN T1吗
解决方案 »
- 有没有比较好的 生成数据库表结构关系图 的工具?
- 求一查询语句
- 如何创建中间表,创建中间表有什么要注意的吗?
- 郁闷。一个简单的分页显示的存储过程,怎么一直 编译错误!帮忙给看看啊@_@
- oracle中如何查看不同用户所占的系统资源
- 执行函数,发生“字符串缓冲区太小”的错误,请求帮忙!
- 这种查询怎么分类啊??
- 把操作oracle的操作封装成。so文件可行吗?下面错在那?
- 一个复杂的计算SQL。。。求救于各位高手
- 本地计算机无法启动OracleOraHome81ManagentServer服务依存服务不存在,或已被标记为删除
- db2 和 sql server中怎么对单个查询语句设置并行度
- oracle用户管理
(select t.system_name,
t.segment1,
t.org_name,
t.agent_name,
t.vendor_num,
qjv.jc_vendor_num,
t.vendor_name, --
case
when instr(ASCIISTR(t.type_lookup_code), '') > 0 then
t.type_lookup_code
else
gmit.obj_code
end TYPE_LOOKUP_CODE, --
t.po_amount, --
t.framework_num, --
t.framework_name,
gmi.obj_code VENDOR_PUR_TYPE, --
t.tax_code, --
GFCM.GROUP_FC_NAME REFERENCE2, --
t.reference3,
t1.item_number,
t1.item_description,
t1.quantity,
t1.unit_price,
t1.product_catalog_name_1,
t1.product_catalog_code_1,
t1.product_catalog_name_2,
t1.product_catalog_code_2,
t.creation_date,
t.approved_date,
t.syn_date,
t.period,
t.import_date,
t.syn_flag,
t.check_flag,
t1.header_pri_key || '_' || t1.pri_key RESULT_ID,
t.pri_key,
t.check_result
from Group_Po_Header_Info t,
group_po_line_info t1,
(SELECT *
FROM GROUP_MAP_INFO m
where m.category_code = 'TYPE_LOOKUP_CODE') gmit,
(SELECT *
FROM GROUP_MAP_INFO m
where m.category_code = 'VENDOR_PUR_TYPE') gmi,
QPO_JC_VENDORS qjv,
--(select * from group_category_map where length(group_code) = 3) gcm,
GROUP_FRAME_CONTRACT_MAP GFCM
where t.pri_key = t1.header_pri_key(+)
and t.system_name = gmit.system_name(+)
and t.type_lookup_code = gmit.src_code(+)
and t.vendor_pur_type = gmi.src_code(+)
and t.system_name = gmi.system_name(+)
and qjv.jc_vendor_name(+) = t.vendor_name
and t.framework_num = GFCM.Province_Fc_code(+))
--and t1.product_catalog_name_1 = replace(gcm.category_name(+), '\', '')
--and ASCIISTR(gmi.obj_code) = ASCIISTR(gcm.purchase_level(+))
order by t.SYN_FLAG,t.CHECK_FLAG,t.approved_date desc就是这段SQL 需要关联进去的是 --(select * from group_category_map where length(group_code) = 3) gcm,
select t2.system_name,
t2.segment1,
t2.org_name,
t2.agent_name,
t2.vendor_num,
t2.jc_vendor_num,
t2.vendor_name, --
t2.TYPE_LOOKUP_CODE, --
t2.po_amount, --
t2.framework_num, --
t2.framework_name,
t2.VENDOR_PUR_TYPE, --
t2.tax_code, --
t2.REFERENCE2, --
t2.reference3,
t2.item_number,
t2.item_description,
t2.quantity,
t2.unit_price,
t2.product_catalog_name_1,
gcm.group_code,
t2.product_catalog_name_2,
gcm1.group_code,
t2.creation_date,
t2.approved_date,
t2.syn_date,
t2.period,
t2.import_date,
t2.syn_flag,
t2.check_flag,
t2.RESULT_ID,
t2.pri_key,
t2.check_result
from ((select t.system_name,
t.segment1,
t.org_name,
t.agent_name,
t.vendor_num,
qjv.jc_vendor_num,
t.vendor_name, --
case
when instr(ASCIISTR(t.type_lookup_code), '') > 0 then
t.type_lookup_code
else
gmit.obj_code
end TYPE_LOOKUP_CODE, --
t.po_amount, --
t.framework_num, --
t.framework_name,
gmi.obj_code VENDOR_PUR_TYPE, --
t.tax_code, --
GFCM.GROUP_FC_NAME REFERENCE2, --
t.reference3,
t1.item_number,
t1.item_description,
t1.quantity,
t1.unit_price,
t1.product_catalog_name_1,
t1.product_catalog_code_1,
t1.product_catalog_name_2,
t1.product_catalog_code_2,
t.creation_date,
t.approved_date,
t.syn_date,
t.period,
t.import_date,
t.syn_flag,
t.check_flag,
t1.header_pri_key || '_' || t1.pri_key RESULT_ID,
t.pri_key,
t.check_result
from Group_Po_Header_Info t,
group_po_line_info t1,
(SELECT *
FROM GROUP_MAP_INFO m
where m.category_code = 'TYPE_LOOKUP_CODE') gmit,
(SELECT *
FROM GROUP_MAP_INFO m
where m.category_code = 'VENDOR_PUR_TYPE') gmi,
QPO_JC_VENDORS qjv,
GROUP_FRAME_CONTRACT_MAP GFCM
where t.pri_key = t1.header_pri_key(+)
and t.system_name = gmit.system_name(+)
and t.type_lookup_code = gmit.src_code(+)
and t.vendor_pur_type = gmi.src_code(+)
and t.system_name = gmi.system_name(+)
and qjv.jc_vendor_name(+) = t.vendor_name
and t.framework_num = GFCM.Province_Fc_code(+))) t2,
(select * from group_category_map where length(group_code) = 3) gcm,
(select * from group_category_map where length(group_code) = 5) gcm1
where t2.product_catalog_name_1 = replace(gcm.category_name(+), '\', '')
and substr(ASCIISTR(t2.VENDOR_PUR_TYPE),0,5) = ASCIISTR(gcm.purchase_level(+))
and '\' || t2.product_catalog_name_1 || '\' || t2.product_catalog_name_2 = gcm1.category_name(+)
and substr(ASCIISTR(t2.VENDOR_PUR_TYPE),0,5) = ASCIISTR(gcm1.purchase_level(+))
order by t2.SYN_FLAG, t2.CHECK_FLAG, t2.approved_date desc
[/co
难道只能这样