Oracle sql将日期显示为空格

Question

我刚接触oracle sql，这是我写的查询。

    SELECT dt,
         CASE b.colorcode
            WHEN '#fff9c4' THEN 'COVID +ve'
            WHEN '#ffcdd2' THEN 'COVID Suspect'
            ELSE 'Normal'
         END
            "Ward Type",
         b.code "Ward CD",
         b.name "Ward Name",
         b.cnt "Total Patients",
         NVL (a.cnt, 0) "Total Updates Yesterday"
    FROM (  SELECT TRUNC (wih.whi_doctor_dt) dt,
                   NVL (wat.wat_curr_ward_cd, wat.wat_ward_cd) ward,
                   COUNT (*) cnt
              FROM tmh.w_ip_healthinfo wih,
                   tmh.a_doctor_master_base doc,
                   tmh.w_admission_txn wat
             WHERE     wih.whi_doctor_id = doc.adm_doctor_id
                   AND wih.whi_ip_num = wat.wat_ip_num
                   AND TRUNC (wih.whi_doctor_dt) = TRUNC (SYSDATE - 13) --between to_date('20200831000000','YYYYMMDDHH24MISS') and to_date('20200831235959','YYYYMMDDHH24MISS')
          GROUP BY TRUNC (wih.whi_doctor_dt),
                   NVL (wat.wat_curr_ward_cd, wat.wat_ward_cd)
          ORDER BY NVL (wat.wat_curr_ward_cd, wat.wat_ward_cd)) a
         RIGHT OUTER JOIN
         (  SELECT wardmst.wwm_ward_cd code,
                   wardmst.wwm_ward_desc name,
                   wardmst.wwm_wtype_wgrp_mapid,
                   wardmst.wwm_ward_sequence,
                   cvm1.cvm_value AS wardtype,
                   NVL (
                      (SELECT cvcolor.cvm_value
                         FROM tmh.t_objectmap ob
                              JOIN tmh.t_codevaluemaster CV
                                 ON ob.obm_containerid = CV.cvm_id
                              JOIN tmh.t_codevaluemaster cvcolor
                                 ON cvcolor.cvm_id = ob.obm_containeeid
                        WHERE     ob.obm_mappingtype = 350 --mapping of #color and ward / cabin
                              AND CV.cvm_value = wardmst.wwm_ward_cd
                              AND ob.obm_activeflag = 'Y'
                              AND ROWNUM = 1),
                      '#FFFFFF')
                      AS colorcode,
                   COUNT (*) cnt
              FROM tmh.w_bed_master_base bedmst
                   INNER JOIN tmh.w_ward_master_base wardmst
                      ON wardmst.wwm_ward_cd = bedmst.wbm_ward_cd
                   INNER JOIN tmh.t_objectmap om
                      ON om.obm_id = wardmst.wwm_wtype_wgrp_mapid
                   INNER JOIN tmh.t_codevaluemaster cvm
                      ON     cvm.cvm_id = om.obm_containerid
                         AND cvm.cvm_codetypeid = om.obm_containertype
                   INNER JOIN tmh.t_codevaluemaster cvm1
                      ON     cvm1.cvm_id = om.obm_containeeid
                         AND cvm1.cvm_codetypeid = om.obm_containeetype
                   INNER JOIN tmh.s_strmr_apprve appr
                      ON     appr.s_strmr_appid = bedmst.wbm_ward_cd
                         AND appr.s_strmr_typcd = 'WRD'
                         AND appr.s_strmr_enddt IS NULL
             WHERE     wardmst.wwm_active_flag = 'Y'
                   AND wardmst.wwm_wtype_wgrp_mapid <> 0
                   AND bedmst.wbm_bed_status = NVL ('O', bedmst.wbm_bed_status)
          /*and bedMst.WBM_BED_STATUS = NVL(:BedStatus, bedMst.WBM_BED_STATUS)
          and wardMst.WWM_WARD_CD = NVL(:WardCode, wardMst.WWM_WARD_CD)*/
          GROUP BY wardmst.wwm_ward_cd,
                   wardmst.wwm_ward_desc,
                   wardmst.wwm_wtype_wgrp_mapid,
                   wardmst.wwm_ward_sequence,
                   cvm1.cvm_value
          ORDER BY wardmst.wwm_wtype_wgrp_mapid, wardmst.wwm_ward_sequence) b
            ON b.code = a.ward
ORDER BY b.wwm_wtype_wgrp_mapid, b.wwm_ward_sequence

问题是日期在某些地方显示为空白，这是一个问题，因为日期中的值不是由tableau挑选的。

​

​

正如你所看到的，日期应该是空白的。这些空白的原因可能是什么？？

这些空格会在tableau中造成问题，因为这些空格不会拾取日期

Answer 1

右外部连接只是一个向后写入的左外部连接。我的建议是避免使用它们，并将所有的外部连接编写为left join。

(此外，inner和outer关键字也不是必须的。一些人认为它们让事情变得更清晰，另一些人则认为它们是多余的杂乱。我属于“少即是多”阵营，但这取决于你。)

select *
from   wxyz
       right join abcd on abcd.id = wxyz.id

可以更清晰地重写为

select *
from   abcd
       right join wxyz on wxyz.id = abcd.id

将其应用到您的查询中会得到以下结果：

select a.dt
     , case b.colorcode
           when '#fff9c4' then 'COVID +ve'
           when '#ffcdd2' then 'COVID Suspect'
           else 'Normal'
        end "Ward Type"
      , b.code "Ward CD"
      , b.name "Ward Name"
      , b.cnt "Total Patients"
      , nvl(a.cnt, 0) "Total Updates Yesterday"
from    ( select wardmst.wwm_ward_cd code
               , wardmst.wwm_ward_desc name
               , wardmst.wwm_wtype_wgrp_mapid
               , wardmst.wwm_ward_sequence
               , cvm1.cvm_value as wardtype
               , nvl((select cvcolor.cvm_value
                      from   tmh.t_objectmap ob
                             join tmh.t_codevaluemaster cv on ob.obm_containerid = cv.cvm_id
                             join tmh.t_codevaluemaster cvcolor on cvcolor.cvm_id = ob.obm_containeeid
                      where  ob.obm_mappingtype = 350 --mapping of #color and ward / cabin
                      and    cv.cvm_value = wardmst.wwm_ward_cd
                      and    ob.obm_activeflag = 'Y'
                      and    rownum = 1), '#FFFFFF') as colorcode
               , count(*) cnt
          from   tmh.w_bed_master_base bedmst
                 join tmh.w_ward_master_base wardmst on wardmst.wwm_ward_cd = bedmst.wbm_ward_cd
                 join tmh.t_objectmap om on om.obm_id = wardmst.wwm_wtype_wgrp_mapid
                 join tmh.t_codevaluemaster cvm on cvm.cvm_id = om.obm_containerid and cvm.cvm_codetypeid = om.obm_containertype
                 join tmh.t_codevaluemaster cvm1 on cvm1.cvm_id = om.obm_containeeid and cvm1.cvm_codetypeid = om.obm_containeetype
                 join tmh.s_strmr_apprve appr on appr.s_strmr_appid = bedmst.wbm_ward_cd
          where  wardmst.wwm_active_flag = 'Y'
          and    wardmst.wwm_wtype_wgrp_mapid <> 0
          and    appr.s_strmr_typcd = 'WRD'
          and    appr.s_strmr_enddt is null
          and    bedmst.wbm_bed_status = nvl('O', bedmst.wbm_bed_status)
          group by
                 wardmst.wwm_ward_cd
               , wardmst.wwm_ward_desc
               , wardmst.wwm_wtype_wgrp_mapid
               , wardmst.wwm_ward_sequence
               , cvm1.cvm_value ) b
        left join
        ( select trunc(wih.whi_doctor_dt) dt
               , nvl(wat.wat_curr_ward_cd, wat.wat_ward_cd) ward
               , count(*) cnt
          from   tmh.w_ip_healthinfo wih
                 join tmh.a_doctor_master_base doc on doc.adm_doctor_id = wih.whi_doctor_id
                 join tmh.w_admission_txn wat on wat.wat_ip_num = wih.whi_ip_num
          where  trunc(wih.whi_doctor_dt) = trunc(sysdate - 13)
          group by trunc(wih.whi_doctor_dt), nvl(wat.wat_curr_ward_cd, wat.wat_ward_cd) ) a
       on a.ward = b.code
order by b.wwm_wtype_wgrp_mapid, b.wwm_ward_sequence

我认为这使得b是驱动程序而a是外连接集变得更加清晰，这就解释了为什么a-prefixed列可以为null。(也许您还可以切换a和b标签，以便将驱动集标记为a。)

顺便说一下,

nvl('O', bedmst.wbm_bed_status)

可能应该是

nvl(bedmst.wbm_bed_status,'O')

Answer 2

在b查询中，您没有dt列。您使用右外部连接连接查询a和b。因此，对于那些来自b的、在a- dt列中没有匹配记录的recors，它是null。解决方案取决于您的需求：

1. 将dt列添加到b查询，如果您有一个或两个
2. 将右外部连接更改为等同连接或两者都有