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

how to prompt save As Box in Excel Interlop c# MVC 4 -

i want export excel sheet using excel interloop... i have datatable convert excel sheet... now there way save file using save dialogue box in mvc 4 here code public string datatabletoexcel(datatable dt, string htmlheading, string key) { try { microsoft.office.interop.excel.application excel; microsoft.office.interop.excel.workbook excelworkbook; microsoft.office.interop.excel.worksheet excelsheet; microsoft.office.interop.excel.range excelcellrange; excel = new microsoft.office.interop.excel.application(); excel.visible = false; excel.displayalerts = false; excelworkbook = excel.workbooks.add(type.missing); excelsheet = (microsoft.office.interop.excel.worksheet)excelworkbook.activesheet; excelsheet.name = "sheet1"; dataset ...
Read more

xslt 1.0 - How to access or retrieve mets content of an item from another item? -

my use case have item has link in item. example, item 123456789/152 has metadata field dc.relation.hasversion=123456789/717 . within item view of 123456789/152 , how can retrieve values of metadata of 123456789/717 ? example, want retrieve dc.language.iso value of 123456789/717 123456789/152 . i looked @ related items feature in dspace don't know how metadata displayed in related items list pulled items in list. i using dspace version 5.3 mirage 2 theme . edit based on schweerelos answer, below actual code. using custom metadata field dc.relation.languageversion . basically, want link other version instead of displaying value, display dc.language.iso of other version in item-view.xsl . i've incorporated answer of schweerelos question in code just displaying value of dc.relation.languageversion . sample values of dc.relation.languageversion 10665.1/9843 ; 10665.1/9844 ie not complete uri handle. thanks in advance! actual code <xsl:template name=...
Read more