Strange results with in Oracle Apex using Pivot showing nulls and n

Question

Im running a oracle 11g with Apex 4.2.6. Im trying to run a script but giving back nulls in apex but showing correct results in SQL developer

  select "ENG_ID","ENG_ID1","roles","Region","1","2","3","4","5","6","7","8","9","10","11","12","13","14","15"    ,"16","17","18","19","20","21","22","23","24","25","26","27","28","29","30","31" from (
   select M.ENG_ID as ENG_ID,
M.ENG_ID as ENG_ID1,
e.ROLE_ID as "roles",
  e.REGION_AREA_ID as "Region",
EXTRACT(DAY FROM M.MS_DATE) as DOM,
   MD.MD_ID 
      --MD.JOB_TYPE_ID
from MD_TS_DETAIL MD,
MD_TS_MAST M,MAN_ENGINEERS e
where
m.eng_id = 542 and
M.ENG_ID = e.ENG_ID and
M.MAST_ID=MD.MD_ID and
M.MS_DATE between trunc(sysdate,'MM') and last_day(sysdate)
)pivot (
max(MD_ID) 
for DOM in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31)
)

which gives me the correct result of

SQL Dev view

Yet running the same script within Apex you get nulls.

apex view.

I'm completely stumped do you guys have any ideas

I think im on to something but still stumped, I ran select max(MAST_ID) from MD_TS_MAST on the database directly and got 90367. running the same script in APEX I got 69738 — jase sykes

jase sykes jase sykes · Accepted Answer · 2014-10-27T17:17:44

Ok after checking what the max records where both records where both on APEX and the database directly. . In SQL workshop > selected MD_TS_MAST > statistics > analyse > estimate statistics change to 100% > next >finish.

all records came back. Why apex did not run from the database directly I don't know. So basicily if doing inserts in SQL developer, You must do a update via analyse.

Strange results with in Oracle Apex using Pivot showing nulls and n

1 Answers