'${unitCode}' as unit_code,
a.patient_id ||'|'|| a.visit_id ||'|'|| a.item_no as pk,
a.patient_id ||'|'|| a.visit_id as fk,
a.patient_id as patient_id,
a.visit_id as visit_id,
a.item_no as item_no,
a.item_class as item_class,
a.item_name as item_name,
a.item_code as item_code,
a.item_spec as item_spec,
a.amount as amount,
a.units as units,
a.ordered_by as ordered_by,
a.performed_by as performed_by,
b.doctor as doctor,
a.costs as costs,
a.charges as charges,
a.billing_date_time as billing_date_time
from (select * from inp_bill_detail where patient_id='01165615' and visit_id='1') a left join
(select
t.ordering_dept,
t.doctor from
(select row_number() over(partition by o.ordering_dept order by count(*) desc) rk,
o.ordering_dept,
o.doctor,
count(*) as order_rows
from orders o where o.patient_id = '01165615' and o.visit_id = '1' group by o.ordering_dept,o.doctor) t where t.rk = 1) b
on a.ordered_by=b.ordering_dept;
小弟工作写了段这样的SQL，但是报错00933，我研究了半天，真心发现不了错误，求大神帮忙
10 个解决方案
#1
自顶，自顶，大神帮忙！
#2
自顶，自顶，大神帮忙！
#3
好像是少了一个逗号：
select
'${unitCode}' as unit_code,
a.patient_id ||'|'|| a.visit_id ||'|'|| a.item_no as pk,
a.patient_id ||'|'|| a.visit_id as fk,
a.patient_id as patient_id,
a.visit_id as visit_id,
a.item_no as item_no,
a.item_class as item_class,
a.item_name as item_name,
a.item_code as item_code,
a.item_spec as item_spec,
a.amount as amount,
a.units as units,
a.ordered_by as ordered_by,
a.performed_by as performed_by,
b.doctor as doctor,
a.costs as costs,
a.charges as charges,
a.billing_date_time as billing_date_time
from
(
select *
from inp_bill_detail
where patient_id='01165615' and visit_id='1'
) a
left join
(
select
t.ordering_dept,
t.doctor
from
(
select row_number() over(partition by o.ordering_dept order by count(*) desc),
rk,
o.ordering_dept,
o.doctor,
count(*) as order_rows
from orders o
where o.patient_id = '01165615' and o.visit_id = '1'
group by o.ordering_dept,o.doctor
) t
where t.rk = 1
) b
on a.ordered_by=b.ordering_dept;
select
'${unitCode}' as unit_code,
a.patient_id ||'|'|| a.visit_id ||'|'|| a.item_no as pk,
a.patient_id ||'|'|| a.visit_id as fk,
a.patient_id as patient_id,
a.visit_id as visit_id,
a.item_no as item_no,
a.item_class as item_class,
a.item_name as item_name,
a.item_code as item_code,
a.item_spec as item_spec,
a.amount as amount,
a.units as units,
a.ordered_by as ordered_by,
a.performed_by as performed_by,
b.doctor as doctor,
a.costs as costs,
a.charges as charges,
a.billing_date_time as billing_date_time
from
(
select *
from inp_bill_detail
where patient_id='01165615' and visit_id='1'
) a
left join
(
select
t.ordering_dept,
t.doctor
from
(
select row_number() over(partition by o.ordering_dept order by count(*) desc),
rk,
o.ordering_dept,
o.doctor,
count(*) as order_rows
from orders o
where o.patient_id = '01165615' and o.visit_id = '1'
group by o.ordering_dept,o.doctor
) t
where t.rk = 1
) b
on a.ordered_by=b.ordering_dept;
#4
不好意思，看错了
#5
试试将'${unitCode}'里面的文字去掉，看看还会不会报错。
#6
这个不会影响啦，最多当个字符串。
#7
放到 MSSQL执行看看
#8
这段代码在哪里执行报的错？？？我在Toad中执行没出什么错误啊。
#9
找到问题了，oracle版本的问题，不知道怎么的，left join报错，我换了种方式，用(+)就ok了，谢谢帮忙
#10
我知道了，估计是oracle 9i，呵呵 在左联时，只能用(+),就像sql server 2000的左联接是*=一样。
#1
自顶，自顶，大神帮忙！
#2
自顶，自顶，大神帮忙！
#3
好像是少了一个逗号：
select
'${unitCode}' as unit_code,
a.patient_id ||'|'|| a.visit_id ||'|'|| a.item_no as pk,
a.patient_id ||'|'|| a.visit_id as fk,
a.patient_id as patient_id,
a.visit_id as visit_id,
a.item_no as item_no,
a.item_class as item_class,
a.item_name as item_name,
a.item_code as item_code,
a.item_spec as item_spec,
a.amount as amount,
a.units as units,
a.ordered_by as ordered_by,
a.performed_by as performed_by,
b.doctor as doctor,
a.costs as costs,
a.charges as charges,
a.billing_date_time as billing_date_time
from
(
select *
from inp_bill_detail
where patient_id='01165615' and visit_id='1'
) a
left join
(
select
t.ordering_dept,
t.doctor
from
(
select row_number() over(partition by o.ordering_dept order by count(*) desc),
rk,
o.ordering_dept,
o.doctor,
count(*) as order_rows
from orders o
where o.patient_id = '01165615' and o.visit_id = '1'
group by o.ordering_dept,o.doctor
) t
where t.rk = 1
) b
on a.ordered_by=b.ordering_dept;
select
'${unitCode}' as unit_code,
a.patient_id ||'|'|| a.visit_id ||'|'|| a.item_no as pk,
a.patient_id ||'|'|| a.visit_id as fk,
a.patient_id as patient_id,
a.visit_id as visit_id,
a.item_no as item_no,
a.item_class as item_class,
a.item_name as item_name,
a.item_code as item_code,
a.item_spec as item_spec,
a.amount as amount,
a.units as units,
a.ordered_by as ordered_by,
a.performed_by as performed_by,
b.doctor as doctor,
a.costs as costs,
a.charges as charges,
a.billing_date_time as billing_date_time
from
(
select *
from inp_bill_detail
where patient_id='01165615' and visit_id='1'
) a
left join
(
select
t.ordering_dept,
t.doctor
from
(
select row_number() over(partition by o.ordering_dept order by count(*) desc),
rk,
o.ordering_dept,
o.doctor,
count(*) as order_rows
from orders o
where o.patient_id = '01165615' and o.visit_id = '1'
group by o.ordering_dept,o.doctor
) t
where t.rk = 1
) b
on a.ordered_by=b.ordering_dept;
#4
不好意思，看错了
#5
试试将'${unitCode}'里面的文字去掉，看看还会不会报错。
#6
这个不会影响啦，最多当个字符串。
#7
放到 MSSQL执行看看
#8
这段代码在哪里执行报的错？？？我在Toad中执行没出什么错误啊。
#9
找到问题了，oracle版本的问题，不知道怎么的，left join报错，我换了种方式，用(+)就ok了，谢谢帮忙
#10
我知道了，估计是oracle 9i，呵呵 在左联时，只能用(+),就像sql server 2000的左联接是*=一样。