sql - How to get the sales and collection amount values from a query in Oracle -


hello please have @ query below , see problem oracle query.i have used 2 tables named bill_mas , remit_bill.would appreciate much.

query : 

select x.sc_cd, x.rm_id, x.fm_id, x.bill_no, x.deli_dt, x.sale_net, y.rmt, y.coll (     select sc_cd, rm_id, fm_id, bill_no, deli_dt, sum(sale_net) sale_net     bill_mas      deli_dt between '01-feb-15' , '28-feb-15'     , bill_no = '754105'     , sc_cd = '09'     , sale_cd not in ('rs','in','ex')     having round(sum(sale_net),2) <= 1000     group sc_cd, rm_id, fm_id, bill_no, deli_dt ) x, (     select p.sc_cd, max(rmt_dt) rmt, sum(p.coll_amt) coll     remit_bill p     p.bill_no = '754105'      , p.rmt_dt =     (         select max(rmt_dt) remit_bill p2         p2.bill_no = p.bill_no         , sc_cd = '09'     )     group p.sc_cd, p.rmt_dt,p.coll_amt ) y x.bill_no = y.bill_no , x.sc_cd = y.sc_cd order 1

error message : 

where x.bill_no=y.bill_no                 * error @ line 18: ora-06553: pls-306: wrong number or types of arguments in call 'ogc_y'

tables :

sql>  sql> desc bill_mas  name                            null?    type  ------------------------------- -------- ----  bill_no                         not null varchar2(6)  bill_dt                         not null date  order_no                                 varchar2(6)  order_dt                                 date  fp_id                           not null varchar2(5)  sc_cd                           not null varchar2(2)  dist_cd                                  varchar2(2)  tha_cd                                   varchar2(2)  un_cd                                    varchar2(3)  cl_id                                    varchar2(3)  sale_cd                         not null varchar2(2)  sale_gr                         not null number(10,2)  sale_net                        not null number(10,2)  deli_dt                                  date  fl_mvh                                   varchar2(1)  user_cd                                  varchar2(2)  w_dt                                     date  cancl                                    varchar2(1)  b_vat                                    number(9,2)  rm_id                                    varchar2(5)  fm_id                                    varchar2(5)  bs_cd                                    varchar2(4)  c_typ                                    varchar2(1)  coll_typ                                 varchar2(1)  sql> desc remit_bill  name                            null?    type  ------------------------------- -------- ----  sc_cd                           not null varchar2(2)  rmt_no                          not null varchar2(6)  rmt_dt                                   date  bill_no                         not null varchar2(6)  coll_amt                        not null number(10,2)  sql>

query date column p.rmt_dt :

select t1.bill_no, t2.sc_cd, t2.rmt, t2.coll     remit_bill t1 inner join     (         select p.sc_cd sc_cd, max(rmt_dt) rmt, sum(p.coll_amt) coll         remit_bill p         --where p.bill_no = '754105'          p.rmt_dt =         (             select max(rmt_dt) remit_bill p2             p2.bill_no = p.bill_no              , sc_cd = '09'              , p.rmt_dt between '01-feb-15' , '28-feb-15'         )         group p.sc_cd, p.rmt_dt,p.coll_amt     ) t2     on t1.sc_cd = t2.sc_cd , t1.rmt_dt = t2.rmt

your outermost where clause referencing column bill_no in inner query y, inner query never selected column, suspect won't available. make bill_no column available, can join remit_bill table onto in y query , carry on were. try changing query this:

select x.sc_cd, x.rm_id, x.fm_id, x.bill_no, x.deli_dt, x.sale_net, y.rmt, y.coll (     select sc_cd, rm_id, fm_id, bill_no, deli_dt, sum(sale_net) sale_net     bill_mas      deli_dt between '01-feb-15' , '28-feb-15'     , bill_no = '754105'     , sc_cd = '09'     , sale_cd not in ('rs','in','ex')     having round(sum(sale_net),2) <= 1000     group sc_cd, rm_id, fm_id, bill_no, deli_dt ) x, (     select t1.bill_no, t2.sc_cd, t2.rmt, t2.coll     remit_bill t1 inner join     (         select p.sc_cd sc_cd, max(rmt_dt) rmt, sum(p.coll_amt) coll         remit_bill p         p.bill_no = '754105'          , p.rmt_dt =         (             select max(rmt_dt) remit_bill p2             p2.bill_no = p.bill_no             , sc_cd = '09'         )         group p.sc_cd, p.rmt_dt,p.coll_amt     ) t2     on t1.sc_cd = t2.sc_cd , t1.rmt_dt = t2.rmt     t1.bill_no = '754105' ) y x.bill_no = y.bill_no , x.sc_cd = y.sc_cd order 1

-

Comments

Post a Comment

Popular posts from this blog

c# - Binding a comma separated list to a List<int> in asp.net web api -

the default binder in web api expecst http://url.com/webapi/report/?pageids=3243&pageids=2365 to bind public ihttpactionresult report(list<int> pageids){ // exciting webapi code} i wish bind http://url.com/webapi/report/?pageids=3243,2365 as running out of space in url get . i have created class public class commaseparatedmodelbinder : system.web.http.modelbinding.imodelbinder { public bool bindmodel(httpactioncontext actioncontext, modelbindingcontext bindingcontext) { //binding in here } } and registered in webapiconfig.cs var provider = new simplemodelbinderprovider( typeof(list<int>), new commaseparatedmodelbinder()); config.services.insert(typeof(modelbinderprovider), 0, provider); i have altered method signature use model binder so public ihttpactionresult report( [modelbinder] list<int> pageids){ // exciting webapi code} however break point in binder not being
Read more

Delphi 7 and decode UTF-8 base64 -

in delphi 7, have widestring encoded base64(that received web service widestring result) : pd94bwwgdmvyc2lvbj0ims4wij8+dqo8c3ryaw5nptiq2lpyqjwvc3ryaw5npg== when decoded it, result not utf-8: <?xml version="1.0"?> <string>طھط³طھ</string> but when decoded base64decode.org, result true : <?xml version="1.0"?> <string>تست</string> i have use encddecd unit decodestring function. the problem have using decodestring . function, in delphi 7, treats decoded binary data being ansi encoded. , problem text utf-8 encoded. to continue encddecd unit have couple of options. can switch decodestream . instance, code produce utf-8 encoded text file data: {$apptype console} uses classes, encddecd; const data = 'pd94bwwgdmvyc2lvbj0ims4wij8+dqo8c3ryaw5nptiq2lpyqjwvc3ryaw5npg=='; var input: tstringstream; output: tfilestream; begin input := tstringstream.create(data); try output := tfilestream.
Read more

html - Is there any way to exclude a single element from the style? (Bootstrap) -

so, have img logo, overlaid on div styled background-image: url("...") , , both elements inside 2 div classes .to-color , .to-overlay respectively, black transparent overlay. is there way exclude logo style given these 2 classes? here's jsfiddle example: link html , css example: - html - <div class="to-color"> <div class="to-overlay"> <div class="respo"> <div class="row"> <div class="col-xs-4 col-xs-offset-4 text-center"> <img src="http://ansonalex.com/wp-content/uploads/2011/06/google-logo-768x260.png" alt="..." class="img-responsive"> </div> </div> </div> </div> </div> - css - .to-color { background-color: #000; } .to-overlay { opacity : 0.5; } .respo { background-image: url("https://source.unsplash.com/category/objects/1600x900"); background-position: center center;
Read more