SQL query exception -

-

the following query excludes accounts amt balance 0.

although trying make exception accounts l1_org_seg_id = 101, 102 , 105 if it's value equal 0.

can point me right direction? thanks!

select 'table' cola     ,o.l1_org_seg_id colb     ,a.l1_acct_seg_id colc     ,a.l2_acct_seg_id cold     ,a.l3_acct_seg_id cole     ,' ' colf     ,a.acct_name colg     ,'usd' colh     ,sum(g.amt) coli     ,' ' colj     ,' ' colk     ,' ' coll     ,' ' colm     ,'4' coln     ,'2015' colo     ,'4/24/15' colp     ,' ' colq     ,' ' colr     ,' ' cols     ,' ' colt     ,' ' colu     ,' ' colv gl_post_sum g inner join acct on a.acct_id = g.acct_id inner join org o on o.org_id = g.org_id g.fy_cd = '2015'     , g.pd_no < 5     , a.s_acct_type_cd in (         'l'         ,'a'         )     , g.org_id not 'j%'     , a.active_fl = 'y'     , o.l1_org_seg_id not in (         '125'         ,'126'         ,'127'         ,'129'         ) group o.l1_org_seg_id     ,a.l1_acct_seg_id     ,a.l2_acct_seg_id     ,a.l3_acct_seg_id     ,a.acct_name having sum(g.amt) <> 0 order 2

something should work

select 'table' cola,o.l1_org_seg_id colb,a.l1_acct_seg_id colc, a.l2_acct_seg_id cold,a.l3_acct_seg_id cole, ' ' colf,a.acct_name colg, 'usd' colh,sum(g.amt) coli,' '  colj,' '  colk,' '  coll,' '  colm,'4' coln,'2015'  colo, '4/24/15'  colp,' '  colq,' '  colr,' '  cols,' '  colt,' '  colu,' '  colv gl_post_sum g  join acct on a.acct_id = g.acct_id join org o on o.org_id = g.org_id g.fy_cd='2015' , g.pd_no < 5 , a.s_acct_type_cd in ('l','a') , g.org_id not 'j%' , a.active_fl = 'y'      , o.l1_org_seg_id not in ('125', '126', '127', '129') group o.l1_org_seg_id,a.l1_acct_seg_id,a.l2_acct_seg_id,a.l3_acct_seg_id,a.acct_name having sum(g.amt) <> 0 or o.l1_org_seg_id in (101, 102, 105) order 2

Comments

Post a Comment

Popular posts from this blog

Fail to load namespace Spring Security http://www.springframework.org/security/tags -

-
i have problem namespace of spring security on xhtml page. don't understand why can't load uri, , have following issue: whitelabel error page application has no explicit mapping /error, seeing fallback. tue jul 21 16:58:12 utc 2015 there unexpected error (type=not found, status=404). please need help! possible uri has been changed? best regards you using incorrect namespace url. correct url http://www.springframework.org/schema/security the error page seeing spring source website since not recognize url. try visiting http://www.springsource.org/security/tags in browser update : spring security tags used in jsp page correct tag lib declaration is: <%@ taglib prefix="sec" uri="http://www.springframework.org/security/tags" %>
Read more

sql - MySQL query optimization using coalesce -

-
can explain me why second query faster first? (first 1 takes around 40 seconds, second 1 less 0.1) select releases.feature_code, releases.platform_id, version, release_date, general_available, uri, filesize, display_name, feature_name.full_name, if ((select exists(select 1 release_exceptions releases.feature_code = release_exceptions.feature_code , releases.platform_id = release_exceptions.platform_id , releases.version = release_exceptions.version) = 1), 1, 0) release_exceptions releases left outer join platform using(platform_id) left outer join feature_name using(feature_code) order version desc; ~~~ query 2 ------------------ ~~~ select feature_code, version, release_date, general_available, platform_id, uri, filesize, display_name, feature_name.full_name, coalesce(release_exceptions, 0) release_exceptions mus.releases left outer join ( select distinct feature_code, version, platform_id, 1 release_exceptions mus.release_exceptions ) release_exceptions using ...
Read more

unity3d - Unity local avoidance in user created world -

-
i'm using unity3d networked multiplayer online game have large complex 3d terrain scene forest, trees, cliff, hills, mountains, bounders, etc. players can build structures sort of minecraft, , put them anywhere in scene, or move them around anytime. aside human controlled players, there automated ai players , objects animals roaming around scene following path. the problem how make these automated ai players , animals, able navigate around static , dynamic player created structures, because path follow can blocked player created structures, or other players , other ai objects, cliffs etc. have find away around them or on track if tumble down off high cliff example. so made navmesh , used navagents, takes care of static, non moving objects, how make ai players navigate around each other , dynamic structures created players can number in hundreds? i thought of adding navmeshobstacle everything, result in being attached hundreds of objects since user created structures b...
Read more