常用业务SQL

zhou66

（1）查询请购单待执行入库数量
SELECT po_praybill_b.pk_reqstor pk_reqstor,
       po_praybill_b.pk_material pk_material,
       po_praybill_b.nnum praynnum,
       po_praybill_b.browclose prayrowclose,
       po_praybill_b.naccumulatenum accordernum,
       po.pounexecnum,
       bd_mat.def6 price
  FROM po_praybill po_praybill
INNER JOIN po_praybill_b po_praybill_b
    ON po_praybill.pk_praybill = po_praybill_b.pk_praybill
INNER join bd_material bd_mat on po_praybill_b.pk_material = bd_mat.pk_material
LEFT outer JOIN (
      select po_order_b.cpraybillbid,po_order_b.cpraybillhid
             ,sum(case when po_order_b.bstockclose='Y' then 0 else po_order_b.nnum - nvl(po_order_b.naccumstorenum,0) + nvl(po_order_b.nbackstorenum,0) end) as pounexecnum
      FROM po_order_b po_order_b
      INNER JOIN po_order po_order
        ON po_order_b.pk_order = po_order.pk_order
       AND po_order.dr = 0
       AND po_order.bislatest = 'Y'
      where po_order_b.fisactive IN (0, 1)
       AND po_order_b.dr = 0
      group by po_order_b.cpraybillbid,po_order_b.cpraybillhid
  ) po on po.cpraybillbid = po_praybill_b.pk_praybill_b
  where po_praybill.dr=0 and po_praybill_b.dr = 0
        --  and bd_mat.code like '04%'  
        -- and po_praybill.pk_praybill <> '~'
       -- and po_praybill_b.pk_reqstor in ('')

zhou66

如何利用XML的功能，实现字符串的分组拼接。
如：在物料档案中，查询，各分类下，分别有哪些物料编码，用逗号分隔。
SQL如下：

1. select m.pk_marbasclass,
   
2.       XMLCast(XMLQuery('/root/code' PASSING (XMLELEMENT("root",XMLAGG(XMLELEMENT("code",m.code || ',')))) RETURNING CONTENT)  AS VARCHAR2(4000))
   
3.    AS codelist
   
4. 
   
5. from bd_material m
   
6. group by m.pk_marbasclass

复制代码

结果如下：
1        1001A110000000004AW1        08010020085,08020010002,08020010001,08020000033,08020000032,08020000031,08020000030,08020000029,08020000028,08020000027,08020000026,08020000025,08020000024,08020000023,08020000022,08020000021,08020000020,08020000019,08020000018,08020000017,08020000016,08020000015,08020000014,08020000013,08020000012,08020000011,08020000010,08020000009,08020000008,08020000007,08020000006,08020000005,08020000004,08020000003,08020000002,08020000001,
2        1001A110000000004AW3        08040010001,08040010004,08040010003,08040010002,
3        1001A110000000004AW6        09010000001,09010000003,09010000002,
4        1001A110000000004AW7        09020000001,09020000003,09020000002,
5        1001A110000000004AW8        09030000001,09030000005,09030000004,09030000003,09030000002,
6        1001A110000000004AW9        09040000001,09040010003,09040010002,09040010001,09040000013,09040000012,09040000011,09040000010,09040000009,09040000008,09040000007,09040000006,09040000005,09040000004,09040000003,09040000002,
7        1001A110000000004AWA        09050000001,09050000003,09050000002,
8        1001A110000000004AWB        09060000001,09060010002,09060010001,09060000026,09060000025,09060000024,09060000023,09060000022,09060000021,09060000020,09060000019,09060000018,09060000017,09060000016,09060000015,09060000014,09060000013,09060000012,09060000011,09060000010,09060000009,09060000008,09060000007,09060000006,09060000005,09060000004,09060000003,09060000002,
9        1001A110000000004AWC        10010000001,10010000067,10010000066,10010000065,10010000064,10010000063,10010000062,10010000061,10010000060,10010000059,10010000058,10010000057,10010000056,10010000055,10010000054,10010000053,10010000052,10010000051,10010000050,10010000049,10010000048,10010000047,10010000046,10010000045,10010000044,10010000043,10010000042,10010000041,10010000040,10010000039,10010000038,10010000037,10010000036,10010000035,10010000034,10010000033,10010000032,10010000031,10010000030,10010000029,10010000028,10010000027,10010000026,10010000025,10010000024,10010000023,10010000022,10010000021,10010000020,10010000019,10010000018,10010000017,10010000016,10010000015,10010000014,10010000013,10010000012,10010000011,10010000010,10010000009,10010000008,10010000007,10010000006,10010000005,10010000004,10010000003,10010000002,
10        1001A110000000004AWF        10040000001,10040000007,10040000006,10040000005,10040000004,10040000003,10040000002,
11        1001A110000000004AWG        10050000001,10050000003,10050000002,
12        1001A110000000004AWH        10060000001,10060000003,10060000002,
13        1001A110000000004AWI        11010000001,11010000043,11010000042,11010000041,11010000040,11010000039,11010000038,11010000037,11010000036,11010000035,11010000034,11010000033,11010000032,11010000030,11010000029,11010000028,11010000027,11010000026,11010000025,11010000024,11010000023,11010000022,11010000021,11010000020,11010000019,11010000018,11010000017,11010000016,11010000015,11010000014,11010000013,11010000012,11010000011,11010000010,11010000009,11010000008,11010000007,11010000006,11010000005,11010000004,11010000003,11010000002,
14        1001A110000000004AWJ        11020000001,11020000083,11020000082,11020000081,11020000080,11020000079,11020000078,11020000077,11020000076,11020000075,11020000074,11020000073,11020000072,11020000071,11020000070,11020000069,11020000068,11020000067,11020000066,11020000065,11020000064,11020000063,11020000062,11020000061,11020000060,11020000059,11020000058,11020000057,11020000056,11020000055,11020000054,11020000053,11020000052,11020000051,11020000050,11020000049,11020000048,11020000047,11020000046,11020000045,11020000044,11020000043,11020000042,11020000041,11020000040,11020000039,11020000038,11020000037,11020000036,11020000035,11020000034,11020000033,11020000031,11020000030,11020000029,11020000028,11020000027,11020000026,11020000025,11020000024,11020000023,11020000022,11020000021,11020000020,11020000019,11020000018,11020000017,11020000016,11020000015,11020000014,11020000013,11020000012,11020000011,11020000010,11020000009,11020000008,11020000007,11020000006,11020000005,11020000004,11020000003,11020000002,
15        1001A110000000004AWK        11030000001,11030000203,11030000202,11030000201,11030000008,11030000007,11030000006,11030000005,11030000004,11030000003,11030000002,
16        1001A110000000004E74        01010010001,01010010015,01010010014,01010010013,01010010012,01010010011,01010010010,01010010009,01010010008,01010010007,01010010006,01010010005,01010010004,01010010003,01010010002,
17        1001A110000000004E75        01010020001,01010020011,01010020010,01010020009,01010020008,01010020007,01010020006,01010020005,01010020004,01010020003,01010020002,
18        1001A110000000004E76        01010030001,01010030006,01010030005,01010030004,01010030003,01010030002,
19        1001A110000000004E77        01010040001,01010040007,01010040006,01010040005,01010040004,01010040003,01010040002,
20        1001A110000000004E78        01010050001,01010050004,01010050003,01010050002,
21        1001A110000000004E79        01010060001,01010060002,
22        1001A110000000004E7A        01010070001,01010070004,01010070003,01010070002,
23        1001A110000000004E7B        01010080001,01010080012,01010080011,01010080010,01010080009,01010080008,01010080007,01010080006,01010080005,01010080004,01010080003,01010080002,

zhou66

（1） 查询会计月份
select * From bd_accperiodmonth

（2）查询最大关账月份
select closeorgpks, MAX(yearmth) yearmth
  from org_closeacc a
INNER JOIN (SELECT pk_accperiodmonth, yearmth
               FROM bd_accperiodmonth
              where isadj = 'N'
                and pk_accperiodscheme = '0001Z000000000000001'
                and yearmth >= '2021-06') b
    on a.pk_accperiodmonth = b.pk_accperiodmonth
where moduleid = '2014'
   and closeorgpks in
       ('1001ZZ1000000000EDPW1001ZZ100000000045ZF',
        '1001ZZ1000000000EDPW1001ZZ100000000045ZM',
        '1001ZZ1000000000EDPW1001ZZ100000000045ZT',
        '1001ZZ1000000000EDPW1001ZZ100000000045Z5',
        '1001ZZ1000000000EDPW1001ZZ10000000004609',
        '1001ZZ1000000000EDPW1001ZZ10000000004600',
        '1001ZZ1000000000EDPW1001A81000000000HAUO')
   AND a.isendacc = 'Y'
GROUP BY closeorgpks

zhou66

（2） 查询最大关账年月

select closeorgpks, MAX(yearmth) yearmth
  from org_closeacc a
INNER JOIN (SELECT pk_accperiodmonth, yearmth
               FROM bd_accperiodmonth
              where isadj = 'N'
                and pk_accperiodscheme = '0001Z000000000000001'
                and yearmth >= '2021-06') b
    on a.pk_accperiodmonth = b.pk_accperiodmonth
where moduleid = '2014'
   and closeorgpks in
       ('1001ZZ1000000000EDPW1001ZZ100000000045ZF',
        '1001ZZ1000000000EDPW1001ZZ100000000045ZM',
        '1001ZZ1000000000EDPW1001ZZ100000000045ZT',
        '1001ZZ1000000000EDPW1001ZZ100000000045Z5',
        '1001ZZ1000000000EDPW1001ZZ10000000004609',
        '1001ZZ1000000000EDPW1001ZZ10000000004600',
        '1001ZZ1000000000EDPW1001A81000000000HAUO')
   AND a.isendacc = 'Y'
GROUP BY closeorgpks

zhou66

检查资产卡片编号重复的触发器：

1. create or replace trigger CHECK_FA_CARD_DUPLICATE
   
2.   before insert
   
3.   on FA_CARD
   
4.   for each row
   
5. declare
   
6.   v_count number(5);
   
7. begin
   
8.   select count(*) into v_count from FA_CARD where dr=0 and card_code=:new.card_code;
   
9.   if (v_count>0) then
   
10.     raise_application_error(-20000,'卡片编号重复，请检查');
    
11.   end if;
    
12.   
    
13.   select count(*) into v_count from FA_CARD where dr=0 and asset_code=:new.asset_code;
    
14.   if (v_count>0) then
    
15.     raise_application_error(-20000,'资产编号重复，请检查');
    
16.   end if;
    
17.   
    
18. end CHECK_FA_CARD_DUPLICATE;

复制代码

zhou66

--查询自定义字段设置

select * from bd_userdefitem udi
inner join  bd_userdefrule udr on udi.pk_userdefrule  = udr.pk_userdefrule
inner join bd_userdefruleref udrr on udr.pk_userdefrule = udrr.pk_userdefrule
where udrr.refclass in (select id from md_class where name ='so_saleorder')
      and (case when '~'= nvl(udrr.prefix,'~') then 'vdef' else udrr.prefix end ) || udi.propindex ='vdef1'   --自定义字段名

lizongbao

这才是干货，顶起来！

cxz_8210

这才是干货，顶起来！

zhou66

lizongbao 发表于 2022-4-7 14:08
这才是干货，顶起来！

也希望回复干货。

zhou66

查询NCC系统表名，记录数，模块名，组件名，类全名。

1. select ut.TABLE_NAME,ut.NUM_ROWS, cls.defaulttablename,cls.displayname,cls.fullclassname,comp.name as componentname,comp.ownmodule as modulename,comp.displayname as componentdisplayname
   
2. from user_tables ut
   
3. left join md_class cls on upper(cls.defaulttablename) =ut.TABLE_NAME
   
4. left join md_component comp on cls.componentid =comp.id
   
5. order by comp.ownmodule ,cls.componentid
   

复制代码

ilyy

感谢楼主分享， 收藏起来

淡淡烟雨淡淡楼

学习一下

aksuper

学习一下~

rollbin

谢谢分享！

lb9208

金币金币金币金币金币