View + PostgreSQL problem

1

I have a problem with a SQL query. Here is the query example:

SELECT
   col.ad_client_id,
   adu.ad_user_id,
   col.ad_org_id,
   col.c_order_id,
   col.created,
   col.createdby,
   col.updated,
   adc.updatedby,
   col.isactive,
   cb.c_bpartner_id,
   mp.m_product_id,
   col.priceentered,
   col.pricelist,
   mpp.pricelimit,
   co.m_pricelist_id 
FROM
   adempiere.c_orderline col 
   JOIN
      adempiere.m_product mp 
      ON col.m_product_id = mp.m_product_id 
   JOIN
      adempiere.m_productprice mpp 
      ON col.m_product_id = mpp.m_product_id 
   JOIN
      adempiere.ad_changelog adc 
      ON adc.record_id = col.c_orderline_id 
      AND adc.ad_table_id = 260::numeric 
      AND adc.ad_column_id = 12875::numeric 
   JOIN
      adempiere.ad_user adu 
      ON adu.ad_user_id = adc.updatedby 
   JOIN
      adempiere.c_order co 
      ON co.c_order_id = col.c_order_id 
   JOIN
      adempiere.c_bpartner cb 
      ON col.c_bpartner_id = cb.c_bpartner_id 
   JOIN
      adempiere.m_pricelist mpl 
      ON co.m_pricelist_id = mpl.m_pricelist_id 
WHERE
   col.ad_org_id = adc.ad_org_id 
   AND col.priceentered < mpp.pricelimit 
   AND co.issotrx = 'Y'::bpchar 
   AND 
   (
      mpp.m_pricelist_version_id = ANY (ARRAY[2000000::numeric, 2000020::numeric, 2000007::numeric, 2000017::numeric, 2000019::numeric, 2000018::numeric, 2000014::numeric, 2000015::numeric, 2000013::numeric])
   )
   AND co.docstatus = 'CO'::bpchar 
   AND co.m_pricelist_id = mpp.m_pricelist_version_id 
   AND col.ad_org_id = 2000003::numeric 
   AND col.updated >= '2016-11-01'
   AND col.updated <= '2016-11-30'
ORDER BY
   col.c_order_id;

This query above returns me 265 records.

So I created a view:

-- View: adempiere.rv_frk_alteracao_preco_abaixo_limite
-- DROP VIEW adempiere.rv_frk_alteracao_preco_abaixo_limite;

CREATE OR REPLACE VIEW adempiere.rv_frk_alteracao_preco_abaixo_limite AS 
 SELECT col.ad_client_id,
    adu.ad_user_id,
    col.ad_org_id,
    col.c_order_id,
    col.created,
    col.createdby,
    col.updated,
    adc.updatedby,
    col.isactive,
    cb.c_bpartner_id,
    mp.m_product_id,
    col.priceentered,
    col.pricelist,
    mpp.pricelimit,
    co.m_pricelist_id
   FROM adempiere.c_orderline col
     JOIN adempiere.m_product mp ON col.m_product_id = mp.m_product_id
     JOIN adempiere.m_productprice mpp ON col.m_product_id = mpp.m_product_id
     JOIN adempiere.ad_changelog adc ON adc.record_id = col.c_orderline_id AND adc.ad_table_id = 260::numeric AND adc.ad_column_id = 12875::numeric
     JOIN adempiere.ad_user adu ON adu.ad_user_id = adc.updatedby
     JOIN adempiere.c_order co ON co.c_order_id = col.c_order_id
     JOIN adempiere.c_bpartner cb ON col.c_bpartner_id = cb.c_bpartner_id
     JOIN adempiere.m_pricelist mpl ON co.m_pricelist_id = mpl.m_pricelist_id
  WHERE col.ad_org_id = mpp.ad_org_id AND col.priceentered < mpp.pricelimit AND co.issotrx = 'Y'::bpchar AND (mpp.m_pricelist_version_id = ANY (ARRAY[2000000::numeric, 2000020::numeric, 2000007::numeric, 2000017::numeric, 2000019::numeric, 2000018::numeric, 2000014::numeric, 2000015::numeric, 2000013::numeric])) AND co.docstatus = 'CO'::bpchar AND co.m_pricelist_id = mpp.m_pricelist_version_id
  ORDER BY col.c_order_id;

ALTER TABLE adempiere.rv_frk_alteracao_preco_abaixo_limite
  OWNER TO adempiere;

Nothing is more than the query I had without the date and organization where clauses.

Dai when doing a query in the view

select * from adempiere.rv_frk_alteracao_preco_abaixo_limite v where v.ad_org_id = 2000003 and v.updated between '01/11/2016' AND '30/11/2016'

This query returns me only 3 RECORDS.

Where is my mistake, can anyone help me?

Thank you

    
asked by anonymous 13.12.2016 / 17:49

0 answers