• 
  ‘a.PK_MATERIAL‘标识符无效’

  SELECT a.CODE，a.PK_MATERIAL  from bd_material b,(SELECT CODE,max(CREATIONTIME) as time  from bd_material GROUP BY CODE ORDER BY COUNT(*) DESC  )a where b.CODE=a.CODE and b.CREATIONTIME=a.time 

  275个浏览 1个回答 2022年12月14日发布
• 
  提示缺失有括号

  SELECT a.CODE，a.CREATIONTIME,a.PK_MATERIAL from bd_material b,(SELECT CODE，max(CREATIONTIME) as time  from bd_material GROUP BY CODE ORDER BY COUNT(*)DESE)a where b.CODE=a.CODE and b.CREATIONTIME=a.time

  235个浏览 2个回答 2022年12月14日发布
• 
  在汇总模式的情况下，日期字段无法通过“某个日期之后”这个选项进行过滤

  544个浏览 2个回答 FineBI 2022年06月10日发布
• 
  两个SQL左右合并后，有些字段内容是一致的，如何通过一个字段体现而内容不重复？

  以下截图我是通过‘||’把两个字段连接在一起了，然后字段内容就重复了；我知道再前台如果把两个数据抽取后，合并依据是一样的话，数据会自动合并，可是我不想通过前台抽取，想直接在SQL语句实现，该如何处理？详见截图

  309个浏览 1个回答 2022年12月09日发布
• 
  fulljoin过程显示‘表名无效’（单独执行A语句、B语句都没问题，合并的时候就出错了）

  SELECT *FROM (select  入账原币金额*财务做账汇率-预提金额 as 调整金额,b.F_VOUCHER_ID as 总账凭证 ,c.F_VOUCHERID_YF as 应付凭证,e.F_JFKMMC as 损益科目,d.F_JXDM as 机型,--1、加会计年度case when b.F_VOUCHER_ID='×' then to_date('2012-01','YYYY-MM') else TO_DATE(substr(b.F_VOUCHER_ID,0,4)||'-'||substr(b.F_VOUCHER_ID,5,2),'YYYY-MM')         end as 会计年度,--2、加民航快报统计口径case when instr(e.F_JFKMMC,'640108')>0 then '起降费'  when instr(e.F_JFKMMC,'640137')>0 then '航务管理费'when instr(e.F_JFKMMC,'640102')>0 then '航油成本'when instr(e.F_JFKMMC,'640116')>0 then '餐食成本'when instr(e.F_JFKMMC,'640137')>0 then '航务管理费'when instr(e.F_JFKMMC,'640119')>0 then '不正常航班费用'when instr(e.F_JFKMMC,'640104')>0 then '民航发展基金' when instr(e.F_JFKMMC,'640135')>0 then '机组费用'when instr(e.F_JFKMMC,'640105')>0 then '非例行检查费'else '其他' end as 民航快报统计口径,--3、加账单类型case when t.F_ZDLX='3' then '核销账单' else '结算账单' end as 账单类型,--4、加机型（客货机）case when t.F_JXID='2' THEN '货机' ELSE '客机' END as 客货机,t.*from (SELECT F_ZDID as 账单编号,F_DWMC as 单位名称,F_ZDLX,F_FSD as 发生地,F_JH as 机号,F_HBH as 航班号 ,F_FSRQ as 航班日期,F_HD   as 航段 , F_JXID,F_ZDMXID,F_FYMXID,F_FYMC as 费用名称,F_ZDSL as 账单数量,F_BZ as 账单标准,F_JE as 账单金额,F_SSSL as 系统数量,F_SSBZ as 系统标准,F_SSJE as 系统金额,F_SSJG as 试算结果,F_ZZSDSL as 审定数量,F_ZZSDBZ as 审定标准,F_ZZSDJE as 审定金额,F_CYCL as 差异处理,F_SHYJ as 审核意见,F_ZZSDJE_CB as 入账原币金额,F_ZZSDJE_ZZS as 增值税,F_CB_ZZSL as 增值税率,  (select nvl(max(f_sjje),0) from VIEW_YT_CBMXB_01  where f_zdmxid=a.f_zdmxid and f_zdid=a.f_zdid) as 预提金额,nvl(f_cwzzhl,0) as 财务做账汇率,F_FSRQ_RWS as 匹配航班日期,F_HBH_RWS as 匹配航班号, F_HD_RWS as 匹配航段,F_JH_RWS as 匹配机号  from  VIEW_JSD a  WHERE  F_FYMC <> 'passenger service charge(代收代付)'  AND  F_FYMC <> 'security screening for passenger and baggage(代收代付)' AND  F_FYMC <> 'International Tourist Tax(代收代付)' AND  F_FYMC <> '机组早餐费' AND  F_FYMC <> '机组正餐费' AND  F_FYMC <> '机组晚餐费' AND  F_FYMC <> '机组夜宵费' AND  F_FYMC <> '机组交通费' AND  F_FYMC <> '机组单间住宿费' AND  F_FYMC <> '机组标间住宿费' AND  F_FYMC <> '机组其它费用') t--5、加总账凭证号left join(select DISTINCT b.f_zdid,b.F_VOUCHER_ID,b.F_PZSCSJ from ZW_PZZB b)b on b.f_zdid=t.账单编号--6、加应付凭证号left join(select DISTINCT c.f_zdid,c.F_VOUCHERID_YF from ZW_PZZB c)c on c.f_zdid=t.账单编号--7、加机型代码（A320/B737等）left join(SELECT DISTINCT d.F_JXDM,d.F_JXID FROM GG_FJJX d)d on d.F_JXID=t.F_JXID--8、加损益类科目left join(SELECT DISTINCT e.F_FYMC,e.F_JFKMMC,e.F_YWLBMC FROM VI_ZW_FYDZ_KM e  where F_YWLBMC='成本预提' )e on e.F_FYMC=t.费用名称where  b.F_VOUCHER_ID is not null and b.F_PZSCSJ>=to_date('2021-1-1','YYYY-MM-DD'))A--合并预提明细full join SELECT *from (SELECT v.*,e.F_JFKMMC as 预提损益类科目,case when instr(e.F_JFKMMC,'640108')>0 then '起降费'  when instr(e.F_JFKMMC,'640137')>0 then '航务管理费'when instr(e.F_JFKMMC,'640102')>0 then '航油成本'when instr(e.F_JFKMMC,'640116')>0 then '餐食成本'when instr(e.F_JFKMMC,'640137')>0 then '航务管理费'when instr(e.F_JFKMMC,'640119')>0 then '不正常航班费用'when instr(e.F_JFKMMC,'640104')>0 then '民航发展基金' when instr(e.F_JFKMMC,'640135')>0 then '机组费用'when instr(e.F_JFKMMC,'640105')>0 then '非例行检查费'else '其他' end as "民航快报统计口径" from(SELECT  F_YRQ,F_RQ_FX,F_RQ as 航班日期,F_FJJH as 机号,F_FJJX as 机型,F_HBBH as 航班号,F_HBXZMC as 航班性质,F_HXBH as 航线名称,F_HXFL as 航线分类,F_HDBH as 航段,F_HDFL as 航段分类,F_FYID,F_FYMC as 费用名称,F_PZFL as 预提分类,F_JSDWQC as 结算单位名称,F_FSD as 发生地,F_FLG_VR1,F_YTSL as 预提数量,F_YTBZ as 预提标准,F_YBJE as 预提原币金额,F_HBMC as 币种,F_RATE as 汇率,F_SJJE as 预提本币金额,F_ZZSL as 增值税率,F_YTMXID,F_TZNY,F_ZDID,F_ZDMXID,F_VOUCHER_ID,F_YCDPZJEfrom  VIEW_YT_CBMXB_01 WHEREF_YRQ>=TO_DATE('2020-01-01','YYYY-MM-DD') AND F_YRQ<=sysdate   and  F_FYMC <> 'passenger service charge(代收代付)'  AND  F_FYMC <> 'security screening for passenger and baggage(代收代付)' and  F_FYMC <> 'International Tourist Tax(代收代付)' and  F_PZFL is not nulland  F_YCDPZJE=0 )v--加损益类科目left join(SELECT DISTINCT e.F_FYMC,e.F_JFKMMC,e.F_YWLBMC FROM VI_ZW_FYDZ_KM e  where F_YWLBMC='成本预提' )e on e.F_FYMC=v.费用名称)Bon A.F_ZDMXID=B.F_ZDMXID AND A.账单编号=B.F_ZDID

  252个浏览 2个回答 2022年12月02日发布
• 
  截图框中的语句哪里有问题，没法正常运行

  249个浏览 3个回答 2022年12月01日发布
• 
  关于设定数据源部分数据更新的讨论

  针对效益日报的报送形式绑定了数据源，可是在实际业务中，针对数据源中的某些字段的金额又需要进行手工调整，这样会导致原先绑定的数据源以及相关联的仪表盘数据不能直接使用，如此导致有一个疑惑，那前期进行数据的处理以及仪表盘的制作是不是就没有意义。困惑中、

  335个浏览 4个回答 FineBI 2022年07月12日发布
• 
  应付单和付款单根据‘单据标识\\\'字段单独求和的时候金额一致，左右合并后，付款金额多一倍

  详见附件，以下是SQLSELECT 应付单号,SUM(贷方原币金额),sum(借方原币金额),应付单据标识 from(SELECT PK_BUSIDATA as 主键,PK_BILL as 应付单据标识, pk_item  AS 应付单行标识 ，PK_CORBILL as 付款单标识,PK_CORITEM as 付款单行标识,BILLNO AS 应付单号,BILLCLASS AS 单据大类,CORBILLCLASS AS 对应单据大类，DEF8 AS 摘要， local_money_cr  AS  贷方本币金额，money_cr  AS 贷方原币金额，pk_busidata  AS 业务处理明细标识， pk_org  AS 财务组织， pk_currtype   AS 币种，  pk_tradetypeid     AS 交易类型ID , prepay AS  预收付标识 , tallydate       AS  记账日期  ，  verifyfinishdate   AS  核销完成日期  ，   pk_dealnum        AS  业务处理批次号  FROM arap_tally h --从下面添加付款单信息 WHERE  nvl( busitype, 0 ) <> 411 and MONEY_CR>0 and billno='应付_20220221023895') aleft join （SELECT SUM(借方原币金额) AS 借方原币金额，对应单据标识 from(SELECT PK_CORBILL as 对应单据标识 ,BILLNO AS 付款单号,DEF8 AS 摘要，MONEY_DE AS 借方原币金额 FROM arap_tally WHERE  nvl( busitype, 0 ) <> 411 and MONEY_DE<0 and billno='付款_20220307024806'）group by 对应单据标识) b ona.应付单据标识=b.对应单据标识group by 应付单号,应付单据标识--抽取应付单中贷方原币金额大于0的记录即应付单信息--从这里开始通过应付单据标识抽取付款信息详细描述.zip

  358个浏览 1个回答 2022年11月07日发布
• 
  子查询返回多行？（SQL要如何修改）

  select f_zzsdje_cb*F_CWZZHL-f_ytje as tzje,t.* from (SELECT F_DJSJ,F_ZDID,F_DWMC,F_ZDLX,F_FSD,F_ZDZT,F_JH,F_HBH,F_HBXZ,F_FSRQ,F_HD,F_HX,F_JXID,F_FSSJ,F_ZDMXID,F_BZID,F_FYMXID,F_FYMC,F_ZDSL,F_BZ,F_JE,F_SSSL,F_SSBZ,F_SSJE,F_SSJG,F_ZZSDSL,F_ZZSDBZ,F_ZZSDJE,F_CYCL,F_SHYJ,F_ZZSDJE_CB,F_ZZSDJE_ZZS,F_CB_ZZSL,(select nvl(max(f_sjje),0) from VIEW_YT_CBMXB_01  where f_zdmxid=v.f_zdmxid and f_zdid=v.f_zdid) as F_YTJE,(select  F_VOUCHER_ID from ZW_PZZB   where f_zdid=v.f_zdid ) AS F_ZZPZ,nvl(f_cwzzhl,0) as f_cwzzhl,F_SFTZ,F_FSRQ_RWS ,F_HBH_RWS, F_HD_RWS,F_JH_RWS from  VIEW_JSD v WHEREF_DJSJ>=TO_DATE('2021-01-01','YYYY-MM-DD') AND  F_FYMC <> 'passenger service charge(代收代付)'  AND  F_FYMC <> 'security screening for passenger and baggage(代收代付)' AND  F_FYMC <> 'International Tourist Tax(代收代付)' AND  F_FYMC <> '机组早餐费' AND  F_FYMC <> '机组正餐费' AND  F_FYMC <> '机组晚餐费' AND  F_FYMC <> '机组夜宵费' AND  F_FYMC <> '机组交通费' AND  F_FYMC <> '机组单间住宿费' AND  F_FYMC <> '机组标间住宿费' AND  F_FYMC <> '机组其它费用') t where  F_ZZPZ!='null'

  200个浏览 1个回答 2022年11月02日发布
• 
  groupby报错：“民航快报统计口径”标识符无效，要怎么修改SQL？

  SELECT 会计年度，会计期间,民航快报统计口径（科目),sum(借方本币金额) from(SELECT *from (SELECT A.ACCOUNTCODE as 科目代码,A.PERIODV as 会计期间,A.NOV as 凭证号,A.YEARV as 会计年度,A.LOCALCREDITAMOUNT as 贷方本币金额,A.LOCALDEBITAMOUNT as  借方本币金额,A.EXPLANATION as 摘要,A.PK_VOUCHERTYPEV,A.ASSID,A.PK_DETAIL,A.PK_ACCOUNTINGBOOK,A.PK_ORG,A.PK_ACCASOA,A.PK_ACCCHART,N.DISPNAME as 科目名称,O.user_name AS 制单人, B.NAME AS 财务组织, C.NAME AS 凭证类别, D.FILE_ID AS 数据交换平台传递编号,F.NAME as 收支项目,G.NAME as 部门辅助,H.NAME as 预算机构,I.NAME AS 机型辅助,M.name as 根会计科目,M.BEGINPERIOD as 开始时间, case when I.NAME in ('A319-100','A320-200','A321-251NX') then '客机' when I.NAME='B737-300' then '货机' else '客机' end as 机型,CASE WHEN D.FILE_ID IS NULL THEN 'NC模块来源'when  length(nvl(D.FILE_ID,''))=16 then '收入结算系统' else '成本结算系统' end  as 单据来源系统,CASE WHEN instr(D.FILE_ID,'CBYT')>0 then '计提' else '调整' end as "计提/调整",case when D.FILE_ID in ('SY01_HXFJ20220400054','SY01_HXFJ20220100096','SY01_HXFJ20210700040','SY01_HXFJ20200700276') then  '核销账单' when instr(D.FILE_ID,'SY01_HX')>0  then '核销账单' else '结算账单' end as 账单类型,case when instr(A.ACCOUNTCODE,'640108')>0 then '起降费'  when instr(A.ACCOUNTCODE,'640137')>0 then '航务管理费'when instr(A.ACCOUNTCODE,'640102')>0 then '航油成本'when instr(A.ACCOUNTCODE,'640116')>0 then '餐食成本'when instr(A.ACCOUNTCODE,'640137')>0 then '航务管理费'when instr(A.ACCOUNTCODE,'640119')>0 then '不正常航班费用'when instr(A.ACCOUNTCODE,'640104')>0 then '民航发展基金' when instr(A.ACCOUNTCODE,'640135')>0 then '机组费用'when instr(A.ACCOUNTCODE,'640105')>0 then '非例行检查费'else '其他' end as "民航快报统计口径（科目)" FROM GL_DETAIL ALEFT JOIN (SELECT B.PK_CORP,B.NAME FROM ORG_CORP B)B ON B.PK_CORP=A.PK_ORGLEFT JOIN (SELECT C.PK_VOUCHERTYPE,C.NAME from  BD_VOUCHERTYPE C )C ON C.PK_VOUCHERTYPE=A.PK_VOUCHERTYPEVLEFT JOIN (SELECT D.FILE_ID,D.PK_DETAIL from V_GL_DETAIL D)D ON D.PK_DETAIL= A.PK_DETAILLEFT JOIN  GL_DOCFREE1 E on  A.ASSID=E.ASSID LEFT JOIN  BD_INOUTBUSICLASS F ON  E.F8=F.PK_INOUTBUSICLASSLEFT JOIN  ORG_DEPT  G ON  E.F1=G.PK_DEPTLEFT JOIN  ORG_DEPT  H ON  E.F26=H.PK_DEPTLEFT JOIN  BD_DEFDOC  I ON  E.F22=I.PK_DEFDOC left join (SELECT M.BEGINPERIOD,M.PK_ACCCHART,M.name FROM  BD_ACCCHART M )M ON A.PK_ACCCHART=M.PK_ACCCHARTLEFT JOIN (SELECT N.PK_ACCASOA,N.DISPNAME from BD_ACCASOA N)N ON N.PK_ACCASOA= A.PK_ACCASOA LEFT JOIN (SELECT O.user_name,O.cuserid from sm_user O)O ON O.cuserid= A.PK_PREPAREDV)WHERE PK_ORG='00016810000000001RZ7' and 根会计科目='长龙科目体系_根科目表' and 开始时间='2022-01-01'  and 会计年度='2022' and  单据来源系统<>'收入结算系统' and 凭证号<>'0')group by 会计年度，会计期间 

  247个浏览 3个回答 2022年10月27日发布

70

6

70

10