SELECT c.amount_price,c.Km2,d.net_amount
FROM (SELECT a.buy_qty_due*a.sale_unit_price amount_price,a.buy_qty_due*a.conv_factor Km2,a.date_entered date_entered
FROM CUSTOMER_ORDER_DELI a
WHERE a.line_state<>'Invoiced/Closed'
AND a.line_state<>'Cancelled'
AND a.contract='PPHZ'
AND extract(MONTH FROM a.date_entered)=extract(MONTH FROM DATE SYSDATE)
AND extract(YEAR FROM a.date_entered)=extract(YEAR FROM SYSDATE)) c,
(SELECT b.net_dom_amount net_amount,b.Invoiced_Date invoiced_date
FROM CUSTOMER_ORDER_INV_ITEM b
WHERE extract(MONTH FROM b.invoiced_date)=extract(MONTH FROM DATE SYSDATE)
AND extract(YEAR FROM b.invoiced_date)=extract(YEAR FROM SYSDATE)
AND b.company='10') d
第一个VIEW取出的是没有开发票的订单,有金额和总长度千平方米,第二个VIEW是已经开发票的订单金额,现在问题是第二个VIEW取出的数据正常,但是第一个VIEW取出的数据总是重复出现。我的意思就是这两个VIEW中取出的数据都互不影响,没有联系。还有就是要求运行语句后出现的是这个月以来的订单情况。单独运行其中一个都没问题,就是在一起就不行。谢谢大家!
FROM (SELECT a.buy_qty_due*a.sale_unit_price amount_price,a.buy_qty_due*a.conv_factor Km2,a.date_entered date_entered
FROM CUSTOMER_ORDER_DELI a
WHERE a.line_state<>'Invoiced/Closed'
AND a.line_state<>'Cancelled'
AND a.contract='PPHZ'
AND extract(MONTH FROM a.date_entered)=extract(MONTH FROM DATE SYSDATE)
AND extract(YEAR FROM a.date_entered)=extract(YEAR FROM SYSDATE)) c,
(SELECT b.net_dom_amount net_amount,b.Invoiced_Date invoiced_date
FROM CUSTOMER_ORDER_INV_ITEM b
WHERE extract(MONTH FROM b.invoiced_date)=extract(MONTH FROM DATE SYSDATE)
AND extract(YEAR FROM b.invoiced_date)=extract(YEAR FROM SYSDATE)
AND b.company='10') d
第一个VIEW取出的是没有开发票的订单,有金额和总长度千平方米,第二个VIEW是已经开发票的订单金额,现在问题是第二个VIEW取出的数据正常,但是第一个VIEW取出的数据总是重复出现。我的意思就是这两个VIEW中取出的数据都互不影响,没有联系。还有就是要求运行语句后出现的是这个月以来的订单情况。单独运行其中一个都没问题,就是在一起就不行。谢谢大家!
你的查询的最后应该有where c.XXX=d.XXX等这样的条件限制。.....