sql - My query isn't populating data in Apex Charts but it works in Toad -

- 

    select     null link,     a.time_frame "start_date",     (a.row_number*(regr_slope(a.failures, a.row_number) on (partition a.state)) + (regr_intercept(a.failures, a.row_number) on (partition a.state))) trendline          (     select row_number() on (order (case :p1_date_chooser                       when 'daily' trunc(start_time)                      when 'weekly' trunc(start_time, 'ww')                      when 'monthly' trunc(start_time, 'mm')                  end))  row_number,      (case :p1_date_chooser                       when 'daily' trunc(start_time)                      when 'weekly' trunc(start_time, 'ww')                      when 'monthly' trunc(start_time, 'mm')                  end) time_frame,       count(job_id) failures, state     --from apps.ni_infa_activity_log_v@util.world     ni_infa_activity_log_v         state = 1     ,     (     :p1_job_type_chooser = 'workflow' , infa_type_code = 'workflow'     or     :p1_job_type_chooser = 'dss' , infa_type_code = 'dss'     or     :p1_job_type_chooser = 'mtt' , infa_type_code = 'mtt'     or     :p1_job_type_chooser not in ('workflow','dss','mtt')     )      group (case :p1_date_chooser                       when 'daily' trunc(start_time)                      when 'weekly' trunc(start_time, 'ww')                      when 'monthly' trunc(start_time, 'mm')                  end), state     order state,(case :p1_date_chooser                       when 'daily' trunc(start_time)                      when 'weekly' trunc(start_time, 'ww')                      when 'monthly' trunc(start_time, 'mm')                  end)     )     group a.row_number, a.time_frame,a.failures, a.state     order a.state,a.row_number;

in middle source. query works in toad when move oracle apex app developer chart won't populate data. change made switch clauses around in middle. know "apps.ni_infa_activity_log_v@util.world" works source because i'm using inside other queries in app. if rid of case/whens , switch them single line trunc(start_time) itself, data populates. appreciated.

table , sample data:

create table ni_infa_activity_log  ( task_id        varchar2(30 byte) , object_name    varchar2(1000 byte) , job_id         varchar2(30 byte) not null , infa_type_code varchar2(10 byte) , run_id         number(10) , start_time     date , end_time       date  , state          number(1) );  insert ni_infa_activity_log (task_id, object_name, job_id, infa_type_code, run_id, start_time, end_time, state) values ( '000t0w0n0000000000000u' , 'campsync_erp_sfdc_v1.2' , '000t0wc1000000001i6w' , 'workflow' , null , to_date('5/22/2015 11:20:10','mm/dd/yyyy hh24:mi:ss') , to_date('5/22/2015 11:21:02','mm/dd/yyyy hh24:mi:ss') , 1 );  insert ni_infa_activity_log (task_id, object_name, job_id, infa_type_code, run_id, start_time, end_time, state) values ( '000t0w0n00000000000h' , 'pmdm_ora_ora_clnload_v1.0' , '000t0wc1000000001j5x' , 'workflow' , null , to_date('5/24/2015 10:30:11','mm/dd/yyyy hh24:mi:ss') , to_date('5/24/2015 10:32:34','mm/dd/yyyy hh24:mi:ss') , 1 );  commit;

i found solution. i'm not entirely why made work in apex though:

             select              null link,              tf.time_frame label,                (tf.row_number*(regr_slope(count(data.job_id), tf.row_number) on (partition data.state)) + (regr_intercept(count(data.job_id), tf.row_number) on (partition data.state))) trendline                  apps.ni_infa_activity_log_v@util.world data                 ,                 (         select rownum row_number, time_frame                        (                  select                   case :p1_date_chooser                           when 'daily' trunc(start_time)                          when 'weekly' trunc(start_time, 'ww')                          when 'monthly' trunc(start_time, 'mm')                      end time_frame               apps.ni_infa_activity_log_v@util.world              group case :p1_date_chooser                           when 'daily' trunc(start_time)                          when 'weekly' trunc(start_time, 'ww')                          when 'monthly' trunc(start_time, 'mm')                      end              order 1              )         ) tf                  tf.time_frame = (case :p1_date_chooser                           when 'daily' trunc(data.start_time)                          when 'weekly' trunc(data.start_time, 'ww')                          when 'monthly' trunc(data.start_time, 'mm')                      end)         ,         state = 1                 ,                (                 :p1_job_type_chooser = 'workflow' , data.infa_type_code =  'workflow'                 or                 :p1_job_type_chooser = 'dss' , data.infa_type_code = 'dss'                 or                 :p1_job_type_chooser = 'mtt' , data.infa_type_code = 'mtt'                 or                 :p1_job_type_chooser not in ('workflow','dss','mtt')               )         group tf.time_frame, tf.row_number, data.state


Comments

Post a Comment

Popular posts from this blog

c# - Better 64-bit byte array hash -

-
i need hash algorithm produces 64-bit hash code ( long ) fewer collisions string.gethashcode() , fast (no expensive calls cryptographic functions). here's implementation of fnv still shows 3% of collisions after testing 2 million random strings. need number way lower. void main() { const string chars = "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz!#@$%^&*()_+}{\":?><,./;'[]0123456789\\"; const int n = 2000000; var random = new random(); var hashes = new hashset<long>(); int collisions = 0; for(int = 0; < n; i++) { var len = random.next(chars.length); var str = new char[len]; (int j = 0; j < len; j++) { str[j] = chars[random.next(chars.length)]; } var s = new string(str); if(!hashes.add(get64bithash( s ))) collisions++; } console.writeline("collision percentage after " + n + " random strings: " + ((doubl
Read more

webrtc - Which ICE candidate am I using and why? -

-
[questions in bold below] i have setup kurento media server 5.1.3 in datacenter behind firewall running os ubuntu 14.04. has 2 network cards: 222.222.222.222 (eth0 - private ip) 111.111.111.111 (eth1 - public ip) attached below sdp (setremotedescription) when browser connected kurento media server type: answer, sdp: v=0 o=- 5487318114793304426 0 in ip4 0.0.0.0 s=kurento media server c=in ip4 0.0.0.0 t=0 0 a=group:bundle audio video m=audio 59068 rtp/savpf 111 0 c=in ip4 111.111.111.111 a=rtpmap:111 opus/48000/2 a=rtpmap:0 pcmu/8000 a=sendrecv a=rtcp:59068 in ip4 111.111.111.111 a=rtcp-mux a=ssrc:669011897 cname:user39019747@host-6e83e4c2 a=extmap:3 http://www.webrtc.org/experiments/rtp-hdrext/abs-send-time a=mid:audio b=as:20 a=ice-ufrag:ymdk a=ice-pwd:lylifk5ueqzpwm91ddj37e a=fingerprint:sha-256 ff:0f:81:8c:41:4e:b4:b6:c6:d8:36:f3:d6:5f:09:fd:5f:af:13:b3:9d:fc:12:66:ac:f3:56:d6:5b:0a:73:5d a=candidate:1 1 udp 2013266431 111.111.111.111 55239 typ host a=candidate:2 1 udp
Read more

php - Zend Framework / Skeleton-Application / Composer install issue -

-
i trying create zend framework application using skeleton in netbeans. when run composer install, following error message: "c:\php\php.exe" "c:\composer\composer.phar" "--ansi" "--no-interaction" "update" "--dev" using deprecated option "dev". dev packages installed default now. loading composer repositories package information updating dependencies (including require-dev) requirements not resolved installable set of packages. problem 1 - package requires php >=5.5 php version (5.4.42) not satisfy requirement. problem 2 - installation request zendframework/zendframework 2.5.1 -> satisfiable zendframework/zendframework[2.5.1]. - zendframework/zendframework 2.5.1 requires php >=5.5 -> php version (5.4.42) not satisfy requirement. done. apparently, zend framework only needs php 5.3+ contradicts above. need use php 5.4. my composer.json following: { "name": "
Read more