PostgreSQL – DataError：类型为numeric的输入语法无效：“Product”

概述所以我不明白为什么它会出现这个错误.是字段值“Product”数字？.. 我有UNION ALL查询,它使第一行显示其他行的总和.我的查询很长,所以我不会发布它(但如果有必要,我会发布它). 首先,我将给出我的代码片段： with w as (SELECT...CASE x.variants WHEN x.variants THEN x.product || ' - ' || x.varia 所以我不明白为什么它会出现这个错误.是字段值“Product”数字？..
我有UNION ALL查询,它使第一行显示其他行的总和.我的查询很长,所以我不会发布它(但如果有必要,我会发布它).

首先,我将给出我的代码片段：

with w as (SELECT...CASE x.variants WHEN x.variants THEN x.product || ' - ' || x.variants else x.product END as product...) SELECT ...'Product' as product,...UNION ALLSELECT * FROM w

所以我使用产品列在第一行显示为’产品'(因为我不需要在第一行的这个字段中计算.但是计算其他字段总数)

当我运行我的查询时,它输出此错误：

DataError: invalID input Syntax for type numeric: "Product"liNE 104: 'Product' as product,

编辑：
Postgresql版本8.4

完整查询：

(with w as (SELECTmin(fld2.ID) AS ID,-- note. Posted code is working Now,but this select part was notfld2.partner as partner,-- the same that is at the bottom (where different first linefld2.product as product,-- is querIEd. So that gave error.fld2.qty1 as qty1,fld2.qty2 as qty2,fld2.q1 as q1,fld2.q2 as q2,CASE WHEN (q1 >= q2 and q2 != 0) THEN ((q1-q2)/q2) * 100 WHEN (q1=0 or q2=0) THEN NulL WHEN (q2 > q1 and q1!=0) THEN ((q1/q2)-1)*100 END as percent_q,fld2.price_q as price_q,fld2.y1  as y1,fld2.y2 as y2,CASE WHEN (y1 >= y2 and y2 != 0) THEN ((y1-y2)/y2) * 100 WHEN (y1=0 or y2=0) THEN NulL WHEN (y2 > y1 and y1!=0) THEN ((y1/y2)-1)*100 END as percent_y,fld2. price_y as price_yFROM(SELECT min(x.ID) AS ID,x.partner as partner,--quarter as quarter,CASE x.variants WHEN x.variants THEN x.product || ' - ' || x.variants else x.product END as product,sum(case when (calcyear=DATE_PART('year',Now()) and x.quarter=3) then x.qty else 0 end) as qty1,Now() - '1 year'::interval) and x.quarter=3) then x.qty else 0 end) as qty2,Now()) and x.quarter=3) then price_unit else 0 end) as q1,Now() - '1 year'::interval) and x.quarter=3) then price_unit else 0 end) as q2,sum(case calcyear when  DATE_PART('year',Now()) then price_unit else 0 end) as y1,sum(case calcyear when DATE_PART('year',Now() - '1 year'::interval) then price_unit else 0 end) as y2,Now()) and x.quarter=3) then price_unit WHEN (calcyear=DATE_PART('year',Now() - '1 year'::interval)  and x.quarter=3) THEN -1*price_unit end) AS price_q,sum(case when calcyear=DATE_PART('year',Now()) then price_unit ELSE -1*price_unit end) AS price_yFROM (  SELECT    min(so.ID) as ID,DATE_PART('year',so.date_order) AS calcyear,DATE_PART('quarter',so.date_order) AS quarter,sol.price_unit as price_unit,rp.name as partner,pt.name as product,pp.variants as variants,sol.product_uom_qty as qtyFROM sale_order AS so INNER JOIN sale_order_line AS sol ON (sol.order_ID = so.ID)INNER JOIN res_partner as rp ON (so.partner_ID = rp.ID)INNER JOIN product_product as pp ON (sol.product_ID=pp.ID)INNER JOIN product_template as pt ON (pp.product_tmpl_ID=pt.ID)WHERE EXISTS(SELECT * FROM  res_partner_category_rel rpclWHERE rpcl.partner_ID=rp.ID and rpcl.category_ID=37and (so.date_order >= date_trunc('year',Now() - '1 year'::interval)::timestamp::date  and so.date_order <= date_trunc('year',Now()+ '1 year'::interval)::timestamp::date-1 )and rp.distributor=True and rp.active=Trueand so.state != 'cancel')GROUP BYvariants,calcyear,quarter,price_unit,partner,product,qty) AS xGROUP BY partner,x.variants,x.productORDER BY  partner)fld2GROUP BYpartner,q1,q2,y1,y2,qty1,qty2,price_q,price_y,percent_q,percent_yORDER BY partner)SELECT1111 as ID,'Viso' as partner,'Product' as product,sum(uf.qty1) as qty1,sum(uf.qty2) as qty2,sum(uf.q1) as q1,sum(uf.q2) as q2,sum(CASE WHEN (uf.q1 >= uf.q2 and uf.q2 != 0) THEN ((uf.q1-uf.q2)/uf.q2) * 100 WHEN (uf.q1=0 or uf.q2=0) THEN NulL WHEN (uf.q2 > uf.q1 and uf.q1!=0) THEN ((uf.q1/uf.q2)-1)*100 END) as percent_q,sum(uf.price_q) as price_q,sum(uf.y1)  as y1,sum(uf.y2) as y2,sum(CASE WHEN (uf.y1 >= uf.y2 and uf.y2 != 0) THEN ((uf.y1-uf.y2)/uf.y2) * 100 WHEN (uf.y1=0 or uf.y2=0) THEN NulL WHEN (uf.y2 > uf.y1 and uf.y1!=0) THEN ((uf.y1/uf.y2)-1)*100 END) as percent_y,sum(uf.price_y) as price_yFROM (SELECT sum(price_q) as price_q,sum(price_y) as price_y,sum(qty1) as qty1,sum(qty2) as qty2,sum(q1) as q1,sum(q2) as q2,sum(y1)  as y1,sum(y2) as y2FROM w)ufUNION ALLSELECT * FROM w) fld

解决方法 好的我发现了什么问题.愚蠢的我：).它给出了这个错误,因为查询的主要部分,选择列顺序是错误的(即使在我的系统中它是使用图形界面进行排序,但在这种情况下,当使用UNION ALL时,列也需要正确排序).所以我将顺序改为与第一行中的顺序相同并且有效. 总结

以上是内存溢出为你收集整理的PostgreSQL – DataError：类型为numeric的输入语法无效：“Product”全部内容，希望文章能够帮你解决PostgreSQL – DataError：类型为numeric的输入语法无效：“Product”所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。