Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

问一下关于积木报表用行转列的方式，默认将查询条件如果是无，就显示全部。解析失败的原因 #3402

Open
328046002 opened this issue Jan 17, 2025 · 1 comment
Open

问一下关于积木报表用行转列的方式，默认将查询条件如果是无，就显示全部。解析失败的原因 #3402

328046002 opened this issue Jan 17, 2025 · 1 comment

Comments

@328046002
Copy link

问一下关于积木报表用行转列的方式，默认将查询条件如果是无，就显示全部。报表当中解析失败的原因

with gp as
(select t.shul,
t.jiag,
r.poid,
p.trademodeid,
b.maxpri,
b.minpri,
to_char(t.toid) toid,
t.meiz,
t.pinp,
t.chumdwmc,
t.qianysj,
t.worktypeid,
p.direction,
r.toquality
from soa_mvecp_xm.v_ecp_het_all t
left join (select *
from soa_mvecp_xm.tradeorder o
) r
on t.toid = r.toid
left join (select e.poid,e.direction,
max(e.poprice) as maxjiag,
min(e.poprice) as minjiag,
trademodeid
from soa_mvecp_xm.postorder e
group by poid, trademodeid,direction) p
on p.poid = r.poid
left join (select a.poid,
max(a.aoprice) as maxpri,
min(a.aoprice) as minpri
from soa_mvecp_xm.acceptorder a

           group by poid) b
  on p.poid = b.poid

where t.toid is not null
and p.trademodeid = '1'),

jj as
(select t.shul,
t.jiag,
r.poid,
p.trademodeid,
b.maxpri,
b.minpri,
to_char(r.toid) toid,
t.meiz,
t.pinp,
t.chumdwmc,
t.qianysj,
t.worktypeid,
p.direction,
r.toquality
from soa_mvecp_xm.v_ecp_het_all t
left join (select max(toid) as toid,
max(o.toprice) as maxtojiag,
min(o.toprice) as mintojiag,
o.poid,o.toquality
from soa_mvecp_xm.tradeorder o
group by o.toid, poid,toquality) r
on t.toid = r.toid
left join (select e.poid,e.direction,
max(e.poprice) as maxjiag,
min(e.poprice) as minjiag,
trademodeid
from soa_mvecp_xm.postorder e
group by poid, trademodeid,direction) p
on p.poid = r.poid
left join (select a.poid,
max(a.aoprice) as maxpri,
min(a.aoprice) as minpri
from soa_mvecp_xm.acceptorder a

           group by poid) b
  on p.poid = b.poid

where t.toid is not null
and p.trademodeid = '3'),

zqht as
(select t.shul,
t.jiag,
0 as poid,
33 as trademodeid,
0 as maxpri,
0 as minpri,
to_char(t.toid) toid,
t.meiz,
t.pinp,
t.chumdwmc,
t.qianysj,
t.worktypeid,
'S' as direction,
0 as toquality
from soa_mvecp_xm.v_ecp_het_all t
where t.dongzbm = '33'
and t.zhuangt = '4' and jiag <> 0 or t.worktypeid <> 0),

cxht as
(select t.shul,
t.jiag as jiag,
0 as poid,
32 as trademodeid,
0 as maxpri,
0 as minpri,
to_char(t.toid) toid,
t.meiz,
t.pinp,
t.chumdwmc,
t.qianysj,
t.worktypeid,
'S' as direction,
0 as toquality
from soa_mvecp_xm.v_ecp_het_all t
where t.dongzbm = '32'
and t.zhuangt = '4')

select case when count(1) = 0 then 1 else count(1) end as intotal ,
count(case
WHEN trademodeid = 1 then
1
end) as gpcount,
round((count(case
WHEN trademodeid = 1 then
1
end))/case when count(1) = 0 then 1 else count(1) end * 100,2) as gpzb,

   round(sum(case
               when trademodeid = 1 then
                shul
             end) / 10000,
         2) as gpsum,
   max(case
         when trademodeid = 1 then
          jiag
       end) as gpzgdj, 
   min(case
         when trademodeid = 1 then
          jiag
       end) as gpzddj, 
   round((sum(case
          when trademodeid = 1 then
           jiag*shul
        end) / sum(case
                      when trademodeid = 1 then
                       shul
                    end)),2) as meanjiag, 
   round(sum(case
               when trademodeid = 1 then
                shul
             end) * sum(case
                          when trademodeid = 1 then
                           jiag
                        end) / 1000000,
         2) as jinge,         
         
         
   count(case
           WHEN trademodeid = 3 then
            3
         end) as jjcount, 
   round((count(case
           WHEN trademodeid = 3 then
            1
         end))/case when count(1) = 0 then 1 else count(1) end * 100,2) as jjzb, 
   round(sum(case
               when trademodeid = 3 then
                shul
             end) / 10000,
         2) as jjsum, 
   max(case
         when trademodeid = 3 then
          jiag
       end) as jjzgdj, 
   min(case
         when trademodeid = 3 then
          jiag
       end) as jjzddj, 
    round((sum(case
          when trademodeid = 3 then
           jiag*shul
        end) / sum(case
                      when trademodeid = 3 then
                       shul
                    end)),2) as meanbiddingjiag,
    round(sum(case
               when trademodeid = 3 then
                shul
             end) * sum(case
                          when trademodeid = 3 then
                           jiag
                        end) / 1000000,
         2) as biddingjinge,            
            
   count(case
           WHEN trademodeid = 33 then
            33
         end) as zqcount,
         
   round((count(case
           WHEN trademodeid = 33 then
            1
         end))/case when count(1) = 0 then 1 else count(1) end * 100,2) as zqzb,
   
   round(sum(case
               when trademodeid = 33 then
                shul
             end) / 10000,
         2) as zqsum, 
   max(case
         when trademodeid = 33 then
          case when jiag = 0 then worktypeid when jiag is null then worktypeid else jiag end
       end) as zqzgdj, 
   min(case
         when trademodeid = 33 then
          case when jiag = 0 then worktypeid when jiag is null then worktypeid else jiag end
       end) as zqzddj, 
    round(sum(case when trademodeid = 33 then case when jiag = 0 then worktypeid when jiag is null then worktypeid else jiag end end)/ sum(case
           WHEN trademodeid = 33 then
            shul
         end),2)  as sidejiag,        
    round(sum(case when trademodeid = 33 then case when jiag = 0 then worktypeid*shul when jiag is null then worktypeid*shul else jiag*shul end end) /  sum( case
           WHEN trademodeid = 33 then
            shul
         end),
         2) as zqpjjg, 
      round(sum(case
               when trademodeid = 33 then
                shul
             end) * sum(case when trademodeid = 33 then case when jiag = 0 then worktypeid when jiag is null then worktypeid else jiag end end) / 1000000,
         2) as sidejinge, 
   count(case
           WHEN trademodeid = 32 then
            32
         end) as cxcount, 
   round((count(case
           WHEN trademodeid = 32 then
            1
         end))/case when count(1) = 0 then 1 else count(1) end * 100,2) as cxzb,
   round(sum(case
               when trademodeid = 32 then
                shul
             end) / 10000,
         2) as cxsum, 
    max(case
         when trademodeid = 32 then
          case when jiag = 0 then worktypeid when jiag is null then worktypeid else jiag end
       end) as cxzgdj, 
   min(case
         when trademodeid = 32 then
          case when jiag = 0 then worktypeid when jiag is null then worktypeid else jiag end
       end) as cxzddj, 
   round(sum(case when trademodeid = 32 then case when jiag = 0 then worktypeid*shul when jiag is null then worktypeid*shul else jiag*shul end end) /  sum( case
           WHEN trademodeid = 32 then
            shul
         end),
         2) as cxjiag,  
    round(sum(case
               when trademodeid = 32 then
                shul
             end) * sum(case when trademodeid = 32 then case when jiag = 0 then worktypeid when jiag is null then worktypeid else jiag end end) / 1000000,
         2) as cxjinge, 
     
    nvl(sum(case
           WHEN trademodeid = 1 and  (qianysj<='${date2}' and qianysj >= '${date1}') and ((chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then
            toquality
         end),0) as bqgpcount,  
   nvl(round((count(case
           WHEN trademodeid = 1 and (qianysj<='${date2}' and qianysj >= '${date1}') and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then
            1
         end))/case when count(1) = 0 then 1 else count(1) end * 100,2),0) as bqgpzb, 
   
   nvl(round(sum(case
               when trademodeid = 1 and (qianysj<='${date2}' and qianysj >= '${date1}') and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then
                shul
             end) / 10000,
         2),0) as bqgpsum, 
   nvl(max(case
         when trademodeid = 1 and (qianysj<='${date2}' and qianysj >= '${date1}') and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then
          jiag
       end),0) as bqgpzgdj, 
   nvl(min(case
         when trademodeid = 1 and (qianysj<='${date2}' and qianysj >= '${date1}') and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then
          jiag
       end),0) as bqgpzddj, 
   nvl(round((sum(case
          when trademodeid = 1 and (qianysj<='${date2}' and qianysj >= '${date1}') and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then
           jiag*shul
        end) / sum(case
                      when trademodeid = 1 and (qianysj<='${date2}' and qianysj >= '${date1}') and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then
                       shul
                    end)),2),0) as bqmeanjiag, 
   nvl(round(sum(case
               when trademodeid = 1 then
                shul
             end) * sum(case
                          when trademodeid = 1 and (qianysj<='${date2}' and qianysj >= '${date1}' ) and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then
                           jiag
                        end) / 1000000,
         2),0) as bqjinge,        
         
         
   nvl(sum(case
           WHEN trademodeid = 3 and (qianysj<='${date2}' and qianysj >= '${date1}') and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then
            toquality
         end),0) as bqjjcount, 
   nvl(round((count(case
           WHEN trademodeid = 3 and (qianysj<='${date2}' and qianysj >= '${date1}') and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then
            1
         end))/case when count(1) = 0 then 1 else count(1) end * 100,2),0) as bqjjzb, 
   nvl(round(sum(case
               when trademodeid = 3 and (qianysj<='${date2}' and qianysj >= '${date1}') and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then
                shul
             end) / 10000,
         2),0) as bqjjsum, 
   nvl(max(case
         when trademodeid = 3 and (qianysj<='${date2}' and qianysj >= '${date1}') and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then
          jiag
       end),0) as bqjjzgdj, 
   nvl(min(case
         when trademodeid = 3 and (qianysj<='${date2}' and qianysj >= '${date1}') and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then
          jiag
       end),0) as bqjjzddj, 
   nvl(round((sum(case
          when trademodeid = 3 and (qianysj<='${date2}' and qianysj >= '${date1}') and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then
           jiag*shul
        end) / sum(case
                      when trademodeid = 3 and (qianysj<='${date2}' and qianysj >= '${date1}') and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then
                       shul
                    end)),2),0) as bqmeanbiddingjiag,   
    nvl(round(sum(case
               when trademodeid = 3 and (qianysj<='${date2}' and qianysj >= '${date1}') and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then
                shul
             end) * sum(case
                          when trademodeid = 3 and (qianysj<='${date2}' and qianysj >= '${date1}') and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then
                           jiag
                        end) / 1000000,
         2),0) as bqbiddingjinge,         
            
   count(case
           WHEN trademodeid = 33 and (qianysj<='${date2}' and qianysj >= '${date1}') and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then
            33
         end) as bqzqcount, 
         
   round((count(case
           WHEN trademodeid = 33 and (qianysj<='${date2}' and qianysj >= '${date1}') and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then
            1
         end))/case when count(1) = 0 then 1 else count(1) end * 100,2) as bqzqzb,
   
   nvl(round(sum(case
               when trademodeid = 33 and (qianysj<='${date2}' and qianysj >= '${date1}') and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then
                shul
             end) / 10000,
         2),0) as bqzqsum, 
   nvl(max(case
         when trademodeid = 33 and (qianysj<='${date2}' and qianysj >= '${date1}') and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then
          case when jiag = 0 then worktypeid when jiag is null then worktypeid else jiag end
       end),0) as bqzqzgdj,
   nvl(min(case
         when trademodeid = 33 and (qianysj<='${date2}' and qianysj >= '${date1}') and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then
          case when jiag = 0 then worktypeid when jiag is null then worktypeid else jiag end
       end),0) as bqzqzddj, 
   nvl(round(sum(case
          when trademodeid = 33 and (qianysj<='${date2}' and qianysj >= '${date1}') and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then 
            case when jiag = 0 then worktypeid when jiag is null then worktypeid else jiag end end)                
         / sum(case
                      when trademodeid = 33 and (qianysj<='${date2}' and qianysj >= '${date1}') and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then
                       shul
                    end),2),0) as bqsidejiag,  
    nvl(round(sum(case
               when trademodeid = 33 and (qianysj<='${date2}' and qianysj >= '${date1}') and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null)  and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then
                shul
             end) * sum(case
          when trademodeid = 33 and (qianysj<='${date2}' and qianysj >= '${date1}') and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then 
            case when jiag = 0 then worktypeid when jiag is null then worktypeid else jiag end end)  / 1000000,
         2),0) as bqsidejinge,     
   count(case
           WHEN trademodeid = 32 and (qianysj<='${date2}' and qianysj >= '${date1}') and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then
            32
         end) as bqcxcount, 
   round((count(case
           WHEN trademodeid = 32 and (qianysj<='${date2}' and qianysj >= '${date1}') and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then
            1
         end))/case when count(1) = 0 then 1 else count(1) end * 100,2) as bqcxzb,
   nvl(round(sum(case
               when trademodeid = 32 and (qianysj<='${date2}' and qianysj >= '${date1}') and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then
                shul
             end) / 10000,
         2),0) as bqcxsum, 
    nvl(max(case
         when trademodeid = 32 and (qianysj<='${date2}' and qianysj >= '${date1}') and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then
          jiag
       end),0) as bqcxzgdj, 
   nvl(min(case
         when trademodeid = 32 and (qianysj<='${date2}' and qianysj >= '${date1}') and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then
          jiag
       end),0) as bqcxzddj, 
   nvl(round(sum(case
          when trademodeid = 32 and (qianysj<='${date2}' and qianysj >= '${date1}') and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then 
            case when jiag = 0 then worktypeid when jiag is null then worktypeid else jiag end end)                
         / sum(case
                      when trademodeid = 32 and (qianysj<='${date2}' and qianysj >= '${date1}') and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then
                       shul
                    end),2),0) as bqcxjiag, 
   nvl(round(sum(case
               when trademodeid = 32 and (qianysj<='${date2}' and qianysj >= '${date1}') and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then
                shul
             end) * sum(case
          when trademodeid = 32 and (qianysj<='${date2}' and qianysj >= '${date1}') and (chumdwmc = '${xiaoshname}' or '${xiaoshname}' is null) and ((meiz = '${meiz}' or '${meiz}' is null) or '${meiz}' is null) then 
            case when jiag = 0 then worktypeid when jiag is null then worktypeid else jiag end end)  / 1000000,
         2),0) as bqcxjinge

from (select *
from gp
union all
select *
from jj
union all
select *
from zqht
union all
select * from cxht) total where 1=1
@jeecgos
Copy link
Collaborator

jeecgos commented Jan 20, 2025

后台报错日志贴一下。
请按照格式要求提问，附带版本号，问题，复现步骤。

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@328046002 @jeecgos