Adding column to view

Question

I am working in Adempiere and now in pgAdmin III with database from adempiere. I got this code and I am just learning SQL 3 days.

So I have this code:

SELECT t.m_transaction_id, t.ad_client_id, t.ad_org_id, t.movementtype, 
t.movementdate, t.movementqty, t.m_attributesetinstance_id, 
asi.m_attributeset_id, asi.serno, asi.lot, asi.m_lot_id, asi.guaranteedate,
t.m_product_id, p.value, p.name, p.description, p.upc, p.sku, p.c_uom_id,
p.m_product_category_id, p.classification, p.weight, p.volume, p.versionno,
t.m_locator_id, l.m_warehouse_id, l.x, l.y, l.z, t.m_inventoryline_id,
il.m_inventory_id, t.m_movementline_id, ml.m_movement_id, t.m_inoutline_id,
iol.m_inout_id, t.m_productionline_id, prdl.m_productionplan_id, prdp.m_production_id,
t.c_projectissue_id, pjl.c_project_id, 
COALESCE(il.line, ml.line, iol.line, prdl.line, pjl.line) 
AS line,
daysbetween(
(T .movementdate):: TIMESTAMP WITH TIME ZONE,
getdate()
)AS movementagedays

FROM adempiere.m_transaction t
JOIN adempiere.m_locator l ON t.m_locator_id = l.m_locator_id
JOIN adempiere.m_product p ON t.m_product_id = p.m_product_id
LEFT JOIN adempiere.m_attributesetinstance asi ON t.m_attributesetinstance_id =
asi.m_attributesetinstance_id
LEFT JOIN adempiere.m_inventoryline il ON t.m_inventoryline_id = il.m_inventoryline_id
LEFT JOIN adempiere.m_movementline ml ON t.m_movementline_id = ml.m_movementline_id
LEFT JOIN adempiere.m_inoutline iol ON t.m_inoutline_id = iol.m_inoutline_id
LEFT JOIN adempiere.m_productionline prdl ON t.m_productionline_id =
prdl.m_productionline_id
LEFT JOIN adempiere.m_productionplan prdp ON prdl.m_productionplan_id =
prdp.m_productionplan_id
LEFT JOIN adempiere.c_projectissue pjl ON t.c_projectissue_id = pjl.c_projectissue_id;

which ends in a beatiful table (view rv_transaction):

table http://imageshack.us/a/img829/3558/tablerl.png

Last column is movementagedays which tells us how many days there was no manipulation with product.

I just want to add one column "islager" that will end as 'more than 90 days' if the value is less than -90 in corresponding row in the movementagedays column.

So i wrote something like

CASE WHEN movementagedays < -90 THEN 'more than 90 days' 
END AS isLager
FROM rv_transaction

but I don't know if it's right and where to put it in there. Also, idk if the big code is well writen, optimized in performance etc.

I will be glad if somebody can help me. Sorry my SQL skills are bad as well as my english

mvp · Accepted Answer · 2012-10-25 07:38:12Z

2

You got it almost right. Correct statement for you should be:

SELECT *,
    CASE WHEN movementagedays < -90
         THEN 'more than 90 days'
         ELSE NULL END AS isLager
FROM rv_transaction

answered Oct 25, 2012 at 7:38

mvp

118k15 gold badges132 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding column to view

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related