1
I have a problem with an SQL query. Follow the example of the query:
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 above query 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;
It’s nothing more than the query I had without the date and organization Where clauses.
Give 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, someone can help me?
Thank you
to select * from Adempiere.rv_frk_alteracao_preco_abaixo_limite v Where v.ad_org_id = 2000003 and col.updated >= '2016-11-01' AND col.updated <= '2016-11-30' what happens ?
– Motta
It does not generate any error, returns me records, but returns me only 3 records...and in the normal query...the same used within the view, returns 265 records...should this query there return all of them....
– Geizon T.
Yes, but how do I mount SQL returns the 3 records ? It could be a matter of date and time.
– Motta
Solved here...it was my own error... note that in the SQL query there is a condition "where col.ad_org_id = adc.ad_org_id " and in the View, I thought I had fixed, but it was going unnoticed and was with the wrong condition "where col.ad_org_id = mpp.ad_org_id "
– Geizon T.
If you want you can put this explanation of the comment in a reply
– rray
Tip: Answer your question not to be open
– AnselmoMS