oracle - case statement with in case statement -

-

i not sure kind of statement need in order need do. add case statement after case statement have. bellow case statement.

case     when locoff.ext_srv_polygon in ('bof', 'cda', 'col', 'dac', 'gos', 'kel', 'klf', 'lag', 'lec', 'med', 'pum', 'rit', 'ros', 'san', 'spo')     'gas'     when locoff.ext_srv_polygon in ('cdc', 'coc', 'dav', 'dpc', 'grc', 'kec', 'lcc', 'otc', 'pac', 'sac', 'spc')     'electric'     else 'missing'   end type,

i if ext_distworktype gc or gt , type electric, give me type says wrong polygon or if ext_distworktype ec, es, or et , type gas give me type wrong polygon.

here full query: 

select wo.wonum "work order",   wo.location   "location",   wo.status     "status",   wo.description,   wo.actfinish "actual finish",   wo.parent    "parent wo",   wo.worktype  "work type",   case     when wo.reportdate null     to_date('29-jan-15 00:00:00', 'dd-mon-yy hh24:mi:ss')     else wo.reportdate   end       "reported date",   wo.siteid "site",   case     when wo.actfinish null     greatest(trunc(to_date(sysdate, 'dd-mon-yy hh24:mi:ss')      - to_date(wo.reportdate, 'dd-mon-yy hh24:mi:ss'), 6), 0)     when wo.status not                                               in ('comp')     greatest(trunc(to_date(sysdate, 'dd-mon-yy hh24:mi:ss')      - to_date(wo.reportdate, 'dd-mon-yy hh24:mi:ss'), 6), 0)     when wo.status                                                   in ('comp')     greatest(trunc(to_date(wo.actfinish, 'dd-mon-yy hh24:mi:ss') - to_date(wo.reportdate, 'dd-mon-yy hh24:mi:ss'), 6), 0)   end "age",   wo.ext_distworktype,   locoff.ext_office,   case     when locoff.ext_srv_polygon in ('bof', 'cda', 'col', 'dac', 'gos', 'kel', 'klf', 'lag', 'lec', 'med', 'pum', 'rit', 'ros', 'san', 'spo')     'gas'     when locoff.ext_srv_polygon in ('cdc', 'coc', 'dav', 'dpc', 'grc', 'kec', 'lcc', 'otc', 'pac', 'sac', 'spc')     'electric'     else 'missing'   end type,   locoff.ext_statecode,   wo.ownergroup,   wo.ext_jobcode locations locoff right join workorder wo on wo.location       = locoff.location wo.status not in ('close', 'waiv', 'can', 'rej', 'revoked') , locoff.siteid    = 'ops' , wo.ext_jobcode not 'a%' , wo.e`enter code here`xt_jobcode not 'b%' , wo.ext_jobcode not in ('k008','k009','i006','i007','i008');

you can nest case expressions if want modify existing column value (rather adding separate column 'wrong polygon' message):

case     when locoff.ext_srv_polygon in ('bof', 'cda', 'col', 'dac', 'gos', 'kel', 'klf', 'lag', 'lec', 'med', 'pum', 'rit', 'ros', 'san', 'spo')             case             when wo.ext_distworktype in ('ec', 'es', 'et')             'wrong polygon'             else 'gas'         end     when locoff.ext_srv_polygon in ('cdc', 'coc', 'dav', 'dpc', 'grc', 'kec', 'lcc', 'otc', 'pac', 'sac', 'spc')             case             when wo.ext_distworktype in ('gc', 'gt')             'wrong polygon'             else 'electric'         end     else 'missing'   end type,

quick sql fiddle demos same made-up data; your original case , this nested case, gives:

ext ex type         --- -- ------------- bof gc gas           cda gt gas           spo es wrong polygon cdc ec electric      coc et electric      spc gc wrong polygon

this bit simpler trying second step interprets type value, because can't refer column alias in same level of query; need make existing query inline view.


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