现在要把sql语句从mssqlserver转到oracle下面,对oracle下面sql语法不是很熟悉,请大家帮下忙.
下面是sql语句,执行的时候提示最后order by 那一行的e.mnf_train 标识符无效
SELECT bll_type, bll_no, trn_type, trn_no, self, trn_date, b.vcn_code, b.unit, b.maker, b.price,
SUM(quantity) AS quantity, SUM(b.money) AS money, vcn_name, unt_name, mnf_name, c.vcn_train,
e.mnf_train FROM gatheringinfo a, paybill b, vaccines c, units d, manufactory e
WHERE a.ginf_id = b.inf_id
AND bll_type = '20'
AND b.vcn_code = c.vcn_code
AND b.unit = d.unt_code
AND b.maker = e.mnf_id
AND to_char(a.gth_date,'yyyy-mm-dd') >= '2007-10-01'
AND to_char(a.gth_date,'yyyy-mm-dd') <= '2007-10-08'
GROUP BY bll_type, bll_no, trn_type, trn_no, self, trn_date, b.vcn_code, b.unit, b.maker, b.price,
vcn_name, unt_name, mnf_name, c.vcn_train, e.mnf_train
UNION
SELECT '', '', a.trn_type, a.trn_no, a.self, trn_date, b.vcn_code, b.unit, b.maker, b.price,
SUM(quantity) AS quantity, SUM(b.money) AS money, vcn_name, unt_name, mnf_name, c.vcn_train,
e.mnf_train FROM transh a, transb b, vaccines c, units d, manufactory e
WHERE a.trn_type = b.trn_type
AND a.trn_no = b.trn_no
AND a.self = b.self
AND b.vcn_code = c.vcn_code
AND b.unit = d.unt_code
AND b.maker = e.mnf_id
AND a.trn_type = '21'
AND a.charged = '1'
AND to_char(a.start_date,'yyyy-mm-dd') >= '2007-10-01'
AND to_char(a.start_date,'yyyy-mm-dd') <= '2007-10-08'
GROUP BY a.trn_type, a.trn_no, a.self, trn_date, b.vcn_code, b.unit, b.maker, b.price,
vcn_name, unt_name, mnf_name, c.vcn_train, e.mnf_train
ORDER BY c.vcn_train, e.mnf_train
下面是sql语句,执行的时候提示最后order by 那一行的e.mnf_train 标识符无效
SELECT bll_type, bll_no, trn_type, trn_no, self, trn_date, b.vcn_code, b.unit, b.maker, b.price,
SUM(quantity) AS quantity, SUM(b.money) AS money, vcn_name, unt_name, mnf_name, c.vcn_train,
e.mnf_train FROM gatheringinfo a, paybill b, vaccines c, units d, manufactory e
WHERE a.ginf_id = b.inf_id
AND bll_type = '20'
AND b.vcn_code = c.vcn_code
AND b.unit = d.unt_code
AND b.maker = e.mnf_id
AND to_char(a.gth_date,'yyyy-mm-dd') >= '2007-10-01'
AND to_char(a.gth_date,'yyyy-mm-dd') <= '2007-10-08'
GROUP BY bll_type, bll_no, trn_type, trn_no, self, trn_date, b.vcn_code, b.unit, b.maker, b.price,
vcn_name, unt_name, mnf_name, c.vcn_train, e.mnf_train
UNION
SELECT '', '', a.trn_type, a.trn_no, a.self, trn_date, b.vcn_code, b.unit, b.maker, b.price,
SUM(quantity) AS quantity, SUM(b.money) AS money, vcn_name, unt_name, mnf_name, c.vcn_train,
e.mnf_train FROM transh a, transb b, vaccines c, units d, manufactory e
WHERE a.trn_type = b.trn_type
AND a.trn_no = b.trn_no
AND a.self = b.self
AND b.vcn_code = c.vcn_code
AND b.unit = d.unt_code
AND b.maker = e.mnf_id
AND a.trn_type = '21'
AND a.charged = '1'
AND to_char(a.start_date,'yyyy-mm-dd') >= '2007-10-01'
AND to_char(a.start_date,'yyyy-mm-dd') <= '2007-10-08'
GROUP BY a.trn_type, a.trn_no, a.self, trn_date, b.vcn_code, b.unit, b.maker, b.price,
vcn_name, unt_name, mnf_name, c.vcn_train, e.mnf_train
ORDER BY c.vcn_train, e.mnf_train
解决方案 »
- 有字符的字符串无法执行SQL,急。
- Delphi高手进来,大问题
- 复兴科技高薪诚聘DELPHI开发工程师(深圳南山科技园)
- 快有一年半米有来介里了,瞧瞧滴问一句,还有人认识我吗?--不管咋滴,发米咯,嘿嘿~~
- 怎么在没有安装delphi和fr的机器上运行fr开发的报表程序?
- 请问,可否用DELPHI打开一个没有工具栏和菜单栏的IE浏览器?怎样实现?
- 各位高手帮忙,小弟正在学Delphi,请教做一个咋样实例有助于快速掌握?联系你们实例,谢了
- 有谁知道播放realplay格式的delphi控件?
- 新手上路,送分题。。。。
- 怎么样更改一个已知文件的时间呢?(Delphi)
- VC做的ocx控件在delphi下无法使用
- delhpi 对基类变量的引用
SUM(quantity) AS quantity, SUM(b.money) AS money, vcn_name, unt_name, mnf_name, c.vcn_train,
e.mnf_train FROM transh a, transb b, vaccines c, units d, manufactory e
WHERE a.trn_type = b.trn_type
AND a.trn_no = b.trn_no
AND a.self = b.self
AND b.vcn_code = c.vcn_code
AND b.unit = d.unt_code
AND b.maker = e.mnf_id
AND a.trn_type = '21 '
AND a.charged = '1 '
AND to_char(a.start_date, 'yyyy-mm-dd ') >= '2007-10-01 '
AND to_char(a.start_date, 'yyyy-mm-dd ') <= '2007-10-08 '
GROUP BY a.trn_type, a.trn_no, a.self, trn_date, b.vcn_code, b.unit, b.maker, b.price,
vcn_name, unt_name, mnf_name, c.vcn_train, e.mnf_train
ORDER BY c.vcn_train, e.mnf_train
这段单独执行有没有问题?