sql - Group function in select with subquery -

-

i have following select :

select distinct                    max(tctc_cntipcli) "typeofcontract" ,                    max(texe_cncclipu) "contractnumber",                    max(tctc_cndocidc) "clientname",                    max(tsrv_cndesser) "servicename",                    max(texe_cnfuncid) "servicenumber",                    tsrs_cnsubsdc "subservicename",                    texe_cnsubser "subservicenumber",                    tmap_cndesc   "map",         (         select decode(to_char(count(tlof_cnlofrid)), '0', 'na', count(tlof_cnlofrid))         service.kndtlof         tlof_cncclipu = tctc_cncclipu         , tlof_cnservic = texe_cnfuncid         , tlof_cnsubser = texe_cnsubser         , tlof_cnfhalta > trunc (sysdate, 'mm')        ) "volumeoffilesmessages"                           service.kndtctc, service.kndtexe, service.kndtscm, service.kndtsrv, service.kndtsrs, service.kndtmap                           tctc_cncclipu = texe_cncclipu                           , texe_cnfuncid = tsrv_cncveser                           , texe_cnfuncid = tsrs_cncveser                           , texe_cnsubser = tsrs_cnsubser                           , texe_cncclipu = tscm_cncontra                           , tscm_cnmapco = tmap_cnmapco                           , tscm_cnservic = tsrv_cncveser                           , tscm_cnsubser = tsrs_cnsubser                           , tctc_cnestado in ('01', '03')                           , texe_cnestado in ('01', '03')                           , tsrv_cnestado in ('01', '03')                           , tsrs_cnestado in ('01', '03')                           , tscm_cnestado in ('01', '03')                           , tmap_cnestado in ('01', '03')                           group tsrs_cnsubsdc, texe_cnsubser, tmap_cndesc                           order texe_cncclipu;

which outputting :

    c|50000614|lindsey|informationreporting|3050|940previousday|i1|baisamedaywire|n                                   c|50000614|lindsey|wirepayments|3001|wirestatus|s1|mt101swiftstatus|na                                      c|50000614|lindsey|wirepayments|3001|wirepayments|w1|sungard820payment|na

i need use max on columns 1, 2, 3 , duplicating columns 4 , 5 make output below:

c|50000614|lindsey|informationrep|3050|previousday |i1|baisamedaywire   |na                    wirepayments  |3001|wirestatus  |s1|mt101swiftstatus |na                                                                             wirepayments|w1|sungard820payment|na

i have tried max on selected columns in outer query , keep getting "ora 0037 : not single-group group function" error. thanks.

max analytic function. means computes single value based on set of rows. take @ this page documentation on function itself, , this page info on analytic functions. you're going have group data according spec using group by statement, otherwise encounter error ora-00937, before.


Comments

Post a Comment

Popular posts from this blog

r - how do you merge two data frames the best way? -

-
i need merge data.frame s: dput(data) structure(list(hostname = structure(c(8l, 8l, 9l, 5l, 6l, 7l, 1l, 2l, 3l, 4l), .label = c("db01", "db02", "farm01", "farm02", "tom01", "tom02", "tom03", "web01", "web03"), class = "factor"), date = structure(c(6l, 10l, 5l, 3l, 2l, 1l, 8l, 9l, 7l, 4l ), .label = c("10/5/2015 1:15", "10/5/2015 1:30", "10/5/2015 2:15", "10/5/2015 4:30", "10/5/2015 8:30", "10/5/2015 8:45", "10/6/2015 8:15", "10/6/2015 8:30", "9/11/2015 5:00", "9/11/2015 6:00"), class = "factor"), cpubusy = c(31l, 20l, 30l, 20l, 18l, 20l, 41l, 21l, 29l, 24l), usedpercentmemory = c(99l, 98l, 95l, 99l, 99l, 99l, 99l, 98l, 63l, 99l)), .names = c("hostname", "date", "cpubusy", "usedpercentmemory"),
Read more

How to debug "expected android.widget.TextView but found java.lang.string" in Android? -

-
can please take @ code? trying build app personal school project. new android studio. have put simple app display spaces available in parking lot. but trying open second activity screen when click on 1 of lots in listview screen. got error , have no way of solving it. **mainactivity.java** <?xml version="1.0" encoding="utf-8"?> <relativelayout xmlns:tools="http://schemas.android.com/tools" android:layout_width="fill_parent" android:layout_height="fill_parent" > <textview android:id="@+id/lotname" android:layout_width="wrap_content" android:layout_height="wrap_content" /> <textview android:id="@+id/lotspaces" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_below="@+id/lotname"/> </relativelayout> **singlelotview.java** package com.example.sahan.smart
Read more

php - mySQL problems with this code? -

-
i having problems code , hoping can help. basically have issue whereby mysql running out of connections when script run hundreds of times in sequence. https://gist.github.com/anthonyts/b6c46d54ee8bf2d714b8 here database class: https://gist.github.com/anthonyts/89f5a831e31d970fb5e1 here error get: e_error: call member function query() on non-object stack trace: in db::single called @ /var/www/html/scripts/stalk/db.php (68) in db::single called @ /var/www/html/scripts/stalk/beanstalk_check_monitoring_logs.php (88) in {closure} called @ /var/www/html/application/libraries/stalk.php (80) in stalk::background called @ /var/www/html/scripts/stalk/ beanstalk_check_monitoring_logs.php (188) you should put db initialization: $con = mysql_connect(db_host, db_user, db_pass); mysql_select_db(db_database); $db = new db(db_host, db_user, db_pass, db_database); above your: $stalk = new stalk(array(beanstalkd_server, beanstalkd_port)); on top of script, create
Read more