选择在哪里(使用ListAgg之后)

Question

我想从oracle数据库中的不同表中运行此SQL

tabel a" shipment_line"

ORDERQTY_SHIP_ID    SHIP_LINE_ID
0                   SLM4298700
1                   SLM4286868
12                  SLM4162661
19                  SLM4162693

TABEL B =" PCKWRK"

SHIP_LINE_ID    WRKREF
SLM4298700  
SLM4286868  W00003HAEM
SLM4162661  W00003DRGM
SLM4162693  W00003DRHO
SLM4162693  W00003DRHP

Tabel C =" Invdtl"

WRKREF          SUBNUM
W00003DRHP  S00004979375
W00003DRHP  S00004979358
W00003DRHP  S00004979365
W00003HAEM  CTN2379829
W00003DRGM  S00004980093
W00003DRHO  S00004830248
W00003DRHP  S00004979369
W00003DRHP  S00004979346
W00003DRHP  S00004979333

   select a.ORDERQTY_SHIP_ID,a.SHIP_LINE_ID,a.wrkref,
                (CASE WHEN ORDERQTY_SHIP_ID>0  THEN 
                       (CASE WHEN INSTR(a.wrkref, ',', 1)=0 then  
                       (select LISTAGG(subnum, ''',''') WITHIN GROUP(ORDER BY wrkref) subnum 
                       from invdtl 
                       where wrkref in  ( a.wrkref )) --a.wrkref = "W00003DRGM"
                       else
                       (select LISTAGG(subnum, ''',''') WITHIN GROUP(ORDER BY wrkref)subnum 
                        from invdtl 
                       where wrkref in  (a.wrkref))                                
"a.wrkref  "W00003DRHO','W00003DRHP""
                      end)
                   else NULL
              end)SUBNUM
    from(select a.ORDERQTY_SHIP_ID,a.SHIP_LINE_ID,
           ( SELECT  LISTAGG(wrkref, ''',''') WITHIN GROUP(ORDER BY wrkref)wrkref
             FROM pckwrk
             WHERE SHIP_LINE_ID =a.SHIP_LINE_ID AND PRTNUM NOT LIKE'KITPART'
             GROUP BY SHIP_LINE_ID) as wrkref
    from (select a.ORDERQTY_SHIP_ID,a.SHIP_LINE_ID
          from SHIPMENT_LINE a)a)a

结果

ORDERQTY_SHIP_ID   SHIP_LINE_ID  WRKREF                   SUBNUM
0              SLM4298700    NULL                     NULL
1              SLM4286868    W00003HAEM           CTN2379829
12             SLM4162661    W00003DRGM           S00004980093
19             SLM4162693    W00003DRHO','W00003DRHP  NULL

在SQL where a.wrkref=W00003DRHO','W00003DRHP (select LISTAGG(subnum, ''',''') WITHIN GROUP(ORDER BY wrkref)subnum from invdtl where wrkref in (a.wrkref))的这一部分是结果NULL，而不是S00004979333','S00004979346','S00004979358','S00004979365','‌​S00004979369','S0000‌​4979375，我该如何在Valeue a.wrkref

Answer 1

我的猜测是您正在尝试使用in谓词将字符串与子字符串匹配，该谓词只有在表达式相等时才能成功。尝试like，演示

select 1 from dual
where ''''||'W00003DRHO'',''W00003DRHP'||'''' like '%'''||'W00003DRHO'||'''%'

所以用

替换CASE WHEN INSTR(a.wrkref, ',', 1)=0 then ... end

(select LISTAGG(subnum, ''',''') WITHIN GROUP(ORDER BY wrkref) subnum 
 from invdtl 
 where  ''''+a.wrkref+'''' like '%'''||wrkref||'''%' )

Answer 2

 (CASE WHEN INSTR(a.wrkref, ',', 1)=0 then  
                   (select LISTAGG(subnum, ''',''') WITHIN GROUP(ORDER BY wrkref) subnum 
                   from invdtl 
                   where wrkref in  ( a.wrkref )) --a.wrkref = "W00003DRGM"

结果=确定 别的 (选择listagg(subnum，'''，''(组(wrkref订单(subnum 来自Invdtl wrkref in(a.wrkref((
" a.wrkref" w00003drho'，'w00003drhp"结果= null(我要添加'for a.wrkref和之后(，以获得'w00003drho'，'w00003drhp' 结束(