Oracle forms Diff Between Post – Change, When-Validate-Item

Diff Between Post – Change, When-Validate-Item

Post-Test-Item” Trigger fires every time when focus moves from one item to another item.

“Post – Change”, ”When-Validate-Item” à Both Triggers Have the same functionality except
that Post-Change doesn’t fire in one case when existing value is changed to NULL While in that case When-Validate-Item trigger fires. And all other functionality of these both triggers are same in the manner that both fires when the status of the item becomes ‘CHANGED’. If status remains ‘UNCHANGED’ then these both trigger doesn’t fires.

Yasser

Oracle PIVOT

SELECT * FROM DailyIncome

VendorId	IncomeDay	IncomeAmount
SPIKE	FRI	100
SPIKE	MON	300
FREDS	SUN	400
SPIKE	WED	500
SPIKE	TUE	200
JOHNS	WED	900
SPIKE	FRI	100
JOHNS	MON	300
SPIKE	SUN	400
JOHNS	FRI	300
FREDS	TUE	500
FREDS	TUE	200
SPIKE	MON	900
FREDS	FRI	900
FREDS	MON	500
JOHNS	SUN	600
SPIKE	FRI	300
SPIKE	WED	500
SPIKE	FRI	300
JOHNS	THU	800
JOHNS	SAT	800
SPIKE	TUE	100
SPIKE	THU	300
FREDS	WED	500
SPIKE	SAT	100
FREDS	SAT	500
FRED	THU	800
JOHNS	TUE	600

Query (A)

SELECT * FROM DailyIncome
PIVOT (SUM (IncomeAmount) FOR IncomeDay IN
([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) AS AvgIncome

Vendorname	Mon	Tue	Wed	Thu	Fri	Sat	Sun
AMIT	475	150	475	300	200	100	400
PANKAJ	360	600	920	800	300	800	600
VINAY	500	350	500	800	900	500	400

Oracle tree hierarchical query

In a hierarchical querytraverse the tree from the branch level to the top

select *
from EMP
start with EMPNO = :x
connect by prior MGR = EMPNO;

Oracle tree queries

SELECT parent_COST_CENTER_ID,COST_CENTER_ID,sum(parent_price) parent_price,lVL
FROM(
        SELECT parent_COST_CENTER_ID,COST_CENTER_ID,
            (TOTAL_ACTUAL_DEBIT) parent_price,lVL
        FROM COST_JOURNALTREES
        WHERE BRANCH_ID = 216
    ) t
GROUP BY parent_COST_CENTER_ID,COST_CENTER_ID,LVL
/

select DISTINCT

   connect_by_root parent_COST_CENTER_ID rootParent,  

   PARENT_SUM,

   COST_CENTER_ID,

   TOTAL_ACTUAL_DEBIT,

   prior TOTAL_ACTUAL_DEBIT prior_TOTAL_ACTUAL_DEBIT,

   level lvl

 FROM

   COST_JOURNALTREES

   WHERE BRANCH_ID = 216 --AND cost_center_id = 407--IN (427,409,407)

   START WITH PARENT_COST_CENTER_ID IS NOT NULL

   connect by prior COST_CENTER_ID = parent_COST_CENTER_ID

/