怎样用语句截去每级单位(传入的单位编码是不知道的)后面的0 ?

Question

请问你的意思是把0100000截成01吗？
如果是的话，就比较简单,用rtrim函数就可以了。
e.g.
SELECT RTRIM('BROWNINGyxXxy','xy') "RTRIM e.g."
     FROM DUAL;
 
RTRIM e.g
-------------
BROWNINGyxX上面的语句把BROWNINGyxXxy右边所有的xy去掉了。

Answer 1

RTRIM('单位','0')
例如: select RTRIM('010000','0') "R" FROM DUAL;
R
--
01

Answer 2

解决发分：
  select a.customerid,a.name,a.cardno,a.outid,a.idcardno,a.cardtype,a.oddfare,a.sumfare,a.opendt,a.status,
              decode(length(a.custdept),2,b.dpname1,4,b.dpname1||b.dpname2,7,b.dpname1||b.dpname2||b.dpname3,b.dpname1||b.dpname2||b.dpname3||b.dpname4) as custdept,
              sum(c.opfare),count(c.opcount)
              from base_customers a,base_custdept b,rec_cust_acc c
              where decode(length(a.custdept),2,a.custdept||'00000000',4,a.custdept||'000000',7,a.custdept||'000',a.custdept)
                    = b.dpcode1||b.dpcode2||b.dpcode3||b.dpcode4
              and substr(a.custdept,0,2)='01'
              and a.customerid=c.customerid
              --and a.customerid = 7749
              group by a.custdept,
              a.customerid,a.name,a.cardno,a.outid,a.idcardno,a.cardtype,a.oddfare,a.sumfare,a.opendt,a.status,
              decode(length(a.custdept),2,b.dpname1,4,b.dpname1||b.dpname2,7,b.dpname1||b.dpname2||b.dpname3,b.dpname1||b.dpname2||b.dpname3||b.dpname4);
               --a.custdept=decode(Length(custdept) ,7,custdept||'000',10,custdept)

Answer 3

procedure PROC_GETCUSTINFO_month (Prmcustdept in VARCHAR2,IO_ALLREC out ref_cursor,prmtype INTEGER,Prmcust VARCHAR2,PrmDate VARCHAR) as
     i number;
         sCustDept VARCHAR(10);
      BEGIN
      --显示客户月消费明细
   
          IF Prmcust <> '111111111111111111' THEN
          i:=5;
          else
          i:=0;
          end if;
          if(i=0) then
       CASE LENGTH(Prmcustdept)
          WHEN 2 THEN
          OPEN IO_ALLREC FOR
          select a.customerid,a.name,a.cardno,a.outid,a.idcardno,a.cardtype,a.oddfare,a.sumfare,a.opendt,a.status,
                 decode(length(a.custdept),2,b.dpname1,4,b.dpname1||b.dpname2,7,b.dpname1||b.dpname2||b.dpname3,b.dpname1||b.dpname2||b.dpname3||b.dpname4) as custdept,
                 sum(c.opfare),count(c.opcount)
                 from base_customers a,base_custdept b,rec_cust_acc c
                 where
                decode(length(a.custdept),2,a.custdept||'00000000',4,a.custdept||'000000',7,a.custdept||'000',a.custdept)
                       = b.dpcode1||b.dpcode2||b.dpcode3||b.dpcode4 
                 and substr(a.custdept,0,2)=Prmcustdept
                 and a.customerid=c.customerid and
                 trunc(c.opdt,'mm')=TO_date(PrmDate,'yyyymm')
                 group by a.custdept,
                 a.customerid,a.name,a.cardno,a.outid,a.idcardno,a.cardtype,a.oddfare,a.sumfare,a.opendt,a.status,
                 decode(length(a.custdept),2,b.dpname1,4,b.dpname1||b.dpname2,7,b.dpname1||b.dpname2||b.dpname3,b.dpname1||b.dpname2||b.dpname3||b.dpname4);
          WHEN 4 THEN
          OPEN IO_ALLREC FOR
          select a.customerid,a.name,a.cardno,a.outid,a.idcardno,a.cardtype,a.oddfare,a.sumfare,a.opendt,a.status,
                decode(length(a.custdept),2,b.dpname1,4,b.dpname1||b.dpname2,7,b.dpname1||b.dpname2||b.dpname3,b.dpname1||b.dpname2||b.dpname3||b.dpname4) as custdept,
                 sum(c.opfare),count(c.opcount)
                 from base_customers a,base_custdept b,rec_cust_acc c
                 where 
                decode(length(a.custdept),2,a.custdept||'00000000',4,a.custdept||'000000',7,a.custdept||'000',a.custdept)
                       = b.dpcode1||b.dpcode2||b.dpcode3||b.dpcode4 
                 and substr(a.custdept,0,4)=Prmcustdept
                 and a.customerid=c.customerid and
                 trunc(c.opdt,'mm')=TO_date(PrmDate,'yyyymm')
                 group by a.custdept,
                 a.customerid,a.name,a.cardno,a.outid,a.idcardno,a.cardtype,a.oddfare,a.sumfare,a.opendt,a.status,
                 decode(length(a.custdept),2,b.dpname1,4,b.dpname1||b.dpname2,7,b.dpname1||b.dpname2||b.dpname3,b.dpname1||b.dpname2||b.dpname3||b.dpname4);
          WHEN 7 THEN
          OPEN IO_ALLREC FOR
          select a.customerid,a.name,a.cardno,a.outid,a.idcardno,a.cardtype,a.oddfare,a.sumfare,a.opendt,a.status,
                decode(length(a.custdept),2,b.dpname1,4,b.dpname1||b.dpname2,7,b.dpname1||b.dpname2||b.dpname3,b.dpname1||b.dpname2||b.dpname3||b.dpname4) as custdept,
                 sum(c.opfare),count(c.opcount)
                 from base_customers a,base_custdept b,rec_cust_acc c
                 where 
                decode(length(a.custdept),2,a.custdept||'00000000',4,a.custdept||'000000',7,a.custdept||'000',a.custdept)
                       = b.dpcode1||b.dpcode2||b.dpcode3||b.dpcode4 
                 and substr(a.custdept,0,7)=Prmcustdept
                 and a.customerid=c.customerid and
                 trunc(c.opdt,'mm')=TO_date(PrmDate,'yyyymm')
                 group by a.custdept,
                 a.customerid,a.name,a.cardno,a.outid,a.idcardno,a.cardtype,a.oddfare,a.sumfare,a.opendt,a.status,
                decode(length(a.custdept),2,b.dpname1,4,b.dpname1||b.dpname2,7,b.dpname1||b.dpname2||b.dpname3,b.dpname1||b.dpname2||b.dpname3||b.dpname4);
          WHEN 10 THEN
          OPEN IO_ALLREC FOR
          select a.customerid,a.name,a.cardno,a.outid,a.idcardno,a.cardtype,a.oddfare,a.sumfare,a.opendt,a.status,
                decode(length(a.custdept),2,b.dpname1,4,b.dpname1||b.dpname2,7,b.dpname1||b.dpname2||b.dpname3,b.dpname1||b.dpname2||b.dpname3||b.dpname4) as custdept,
                 sum(c.opfare),count(c.opcount)
                 from base_customers a,base_custdept b,rec_cust_acc c
                 where 
                 decode(length(a.custdept),2,a.custdept||'00000000',4,a.custdept||'000000',7,a.custdept||'000',a.custdept)
                       = b.dpcode1||b.dpcode2||b.dpcode3||b.dpcode4 
                 and a.custdept=Prmcustdept
                 and a.customerid=c.customerid and
                 trunc(c.opdt,'mm')=TO_date(PrmDate,'yyyymm')
                 group by a.custdept,
                 a.customerid,a.name,a.cardno,a.outid,a.idcardno,a.cardtype,a.oddfare,a.sumfare,a.opendt,a.status,
                 decode(length(a.custdept),2,b.dpname1,4,b.dpname1||b.dpname2,7,b.dpname1||b.dpname2||b.dpname3,b.dpname1||b.dpname2||b.dpname3||b.dpname4);
              END CASE;
           ELSE
               OPEN IO_ALLREC FOR
               select a.customerid,a.name,a.cardno,a.outid,a.idcardno,a.cardtype,a.oddfare,a.sumfare,a.opendt,a.status,
                  (CASE LENGTH(a.custdept) WHEN 2 THEN b.dpname1
                                           WHEN 4 THEN b.dpname1||b.dpname2
                                           WHEN 7 THEN b.dpname1||b.dpname2||b.dpname3
                                           WHEN 10 THEN b.dpname1||b.dpname2||b.dpname3||b.dpname4
                                           END) as custdept,
                  sum(c.opfare),count(c.opcount)
                  from base_customers a,base_custdept b,rec_cust_acc c
                  where 
                  decode(length(a.custdept),2,a.custdept||'00000000',4,a.custdept||'000000',7,a.custdept||'000',a.custdept)
                       = b.dpcode1||b.dpcode2||b.dpcode3||b.dpcode4 
                  and a.outid like '' ||Prmcust|| '%'
                  and a.customerid=c.customerid and
                  trunc(c.opdt,'mm')=TO_date(PrmDate,'yyyymm')
                  group by a.custdept,
                  a.customerid,a.name,a.cardno,a.outid,a.idcardno,a.cardtype,a.oddfare,a.sumfare,a.opendt,a.status,
                  (CASE LENGTH(a.custdept) WHEN 2 THEN b.dpname1
                                           WHEN 4 THEN b.dpname1||b.dpname2
                                           WHEN 7 THEN b.dpname1||b.dpname2||b.dpname3
                                           WHEN 10 THEN b.dpname1||b.dpname2||b.dpname3||b.dpname4
                                           END);
           end if;
           
   END;