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