Click here to Skip to main content
15,891,136 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hello experts,

I am having a stored procedure that was written by someone. I am not quite positive about the stored procedure because I missed the basic understanding of it.
I didn't get anything from it. Now that I have to work on similar kind of requirement I need to understand the Stored procedure. I would appreciate if anyone is interested in explaining me the SP. I know this is kind of weird but I feel much better if I tell it out rather than rumbling with it.

DELIMITER $$

DROP PROCEDURE IF EXISTS `spm_pushittothemax` $$
CREATE DEFINER=`mysqluser`@`` PROCEDURE `spm_pushittothemax`(
  tCust  char(7)
)
begin_label: begin
  declare lysales, tysales, promosales, myperc decimal(10, 2) default 0;

  if year(current_date) <> 2014 then
    leave begin_label;
  end if;

  select
  rmname, rmadd1, rmadd2, rmadd3, rmcity, rmstat, rmzip,
  (case when wccust is null then 'F' else 'T' end) pittm,
  coalesce(sum(lyamount), 0) lyamount,
  coalesce(sum(tyamount), 0) tyamount,
  coalesce(sum(promosales), 0) promosales,
  coalesce(sum(promosales), 0) * 0.5 promo

  from TESTDATA.VARCUST
  left outer join TESTDATA.VXWWCU on wctype = 'PITTM' and wccust = rmcust
  left outer join (select
    sum(case when year(oaordt) = 2013 then (case when oatype = 'O' then obqord else -obqord end) * obpric else 0 end) as lyamount,
    sum(case when year(oaordt) = 2014 then (case when oatype = 'O' then obqord else -obqord end) * obpric else 0 end) as tyamount,
    sum(case when year(oaordt) = 2014 and oaordt between 20141101 and 20141231 then (case when oatype = 'O' then obqord else -obqord end) * obpric else 0 end) as promosales
    from TESTDATA.VCOHEAD
    join TESTDATA.VCODETL on obcmp = 1 and obord = oaord and obbocd = oabocd and obdel = 'A'
#    join TESTDATA.VINITEM on icitem = obitem and icdiv = 1
#    join TESTDATA.VININDEX on i001trln = ictrln #and i001002 in ('Auto Tires', 'Commercial Over the Road Truck Tires', 'Farm Tires', 'Goodride Trailer', 'Light Truck / Recreational Vehicle Tires', 'Medium & Heavy Off-The-Road', 'Trailer')
    where oacmp = 1
    and oadel = 'A'
    and oaprnt <> 'A'
    and oaordt between 20130101 and 20141231
    and oabocd in (0, 50)
    and (oacust = tCust or (tCust = '0599298' /* new */ and oacust = '2001172' /* old */))

    UNION ALL

    select
    sum(case when year(saordt) = 2013 then (case when satype = 'O' then sboqty else -sboqty end) * sbpric else 0 end) as lyamount,
    sum(case when year(saordt) = 2014 then (case when satype = 'O' then sboqty else -sboqty end) * sbpric else 0 end) as tyamount,
    sum(case when year(saordt) = 2014 and saordt between 20141101 and 20141231 then (case when satype = 'O' then sboqty else -sboqty end) * sbpric else 0 end) as promosales
    from TESTDATA.VSAHEAD
    join TESTDATA.VSADETL on sbcmp = 1 and sbord = saord and sbbocd = sabocd and sbindt = saindt
#    join TESTDATA.VINITEM on icitem = sbitem and icdiv = 1
#    join TESTDATA.VININDEX on i001trln = ictrln and i001002 in ('Auto Tires', 'Commercial Over the Road Truck Tires', 'Farm Tires', 'Goodride Trailer', 'Light Truck / Recreational Vehicle Tires', 'Medium & Heavy Off-The-Road', 'Trailer')
    where sacmp = 1
    and saordt between 20130101 and 20141231
    and sabocd in (0, 50)
    and (sacust = tCust or (tCust = '0599298' /* new */ and sacust = '2001172' /* old */))
  ) x on 1=1
  where rmcmp = 1 and rmcust = tCust
  group by rmname, rmadd1;


  set lysales = COALESCE((select scdltd from TESTDATA.VSAANAL where sccmp = 1 and scrtyp = '02' and sccust = tCust), 0);
  set tysales = COALESCE((select
                          sum((case oatype when 'O' then obqshp else -obqshp end) * obpric) as dollars
                          from TESTDATA.VCODLYO
                          join TESTDATA.VCOHEAD on oacmp = 1 and oaord = o17ord and oabocd = o17bocd
                          join TESTDATA.VCODETL on obcmp = 1 and obord = oaord and obbocd = oabocd and obpcod <> 'D'
      where o17cmp = 1 and oacust = tCust), 0)
    + COALESCE((select scdytd from TESTDATA.VSAANAL where sccmp = 1 and scrtyp = '02' and sccust = tCust), 0);
  set promosales = COALESCE((select
                          sum((case oatype when 'O' then obqshp else -obqshp end) * obpric) as dollars
                          from TESTDATA.VCODLYO
                          join TESTDATA.VCOHEAD on oacmp = 1 and oaord = o17ord and oabocd = o17bocd
                          join TESTDATA.VCODETL on obcmp = 1 and obord = oaord and obbocd = oabocd and obpcod <> 'D'
      where o17cmp = 1 and oacust = tCust and current_date + 0 between 20141101 and 20141231), 0)
    +COALESCE((select scd10 + scd11 + scd12 from TESTDATA.VSAANAL where sccmp = 1 and scrtyp = '02' and sccust = tCust), 0);
  set myperc = (case when tysales = 0 then 0 when lysales = 0 then '9999999' else round((tysales + promosales * .5) / lysales * 100, 2) end);

  select
  myperc, count(*) + 1 rank
  from TESTDATA.VSAANAL
  where
  sccmp = 1
  and scrtyp = '02'
  and sccust <> tCust
  and
  (case when scdytd = 0 then 0 when scdltd = 0 then '9999999' else round(
    (scdytd + COALESCE((select sum((case oatype when 'O' then obqshp else -obqshp end) * obpric) as dollars
              from TESTDATA.VCODLYO
              join TESTDATA.VCOHEAD on oacmp = 1 and oaord = o17ord and oabocd = o17bocd
              join TESTDATA.VCODETL on obcmp = 1 and obord = oaord and obbocd = oabocd and obpcod <> 'D'
              where o17cmp = 1 and oacust = sccust), 0)
    ) / scdltd * 100, 2) end)
   > myperc;

end $$

DELIMITER ;
Posted

1 solution

Do you have any idea how much work explaining code line by line is?
Every single line needs a paragraph of explanation! For example, a simple line of C#:
C#
int next = r.Next();

Create a new variable called "next" which can hold a integer value. From the previously declared Random instance "r", call the "Next" method to get a new random number, and assign it to the "next" variable.

Can you imagine how long it would take us to explain even a very short code fragment like your example, line by line?

No. It is not going to happen. If you have a specific problem, then ask a question about it. But think first - would you want to sit down for 45 minutes and type up a line-by-line description for no good reason?
 
Share this answer
 
Comments
sudevsu 6-Jul-15 11:32am    
Yeah that's true. But I really don't need it line by line. I don't understand the part from UnionAll. That's where I was stuck. I know its hard to sit and write line by line. But I would be more than happy to make some kind of effort in trying to make it understand rather than telling it blindly ... But yeah I totally agree with u as well.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900