我用union all编写了create view表达式。
create view if not exists V_REGRESULT as select 1 AS jType,
regF.smscid AS SmscID,
regF.eventdate AS EventDate,
regF.smppid AS SmppID,
regF.registereddate AS RegisteredDate,
regF.sendernpid AS SenderNPid,
regF.senderntid AS SenderNTid,
regF.senderdirectionid AS SenderDirectionID,
regF.CgPN AS CgPN,
regF.OriginalCgPn AS OriginalCgPN,
regF.recipientnpid AS RecipientNPid,
regF.recipientntid AS RecipientNTid,
regF.recipientdirectionid AS RecipientDirectionID,
regF.cdpn AS CdPN,
regF.OriginalCdPn AS OriginalCdPN,
regF.ValidityPeriod AS ValidityPeriod,
regF.expireddate AS ExpiredDate,
regF.deliveryreporttypeid AS DeliveryReportTypeID,
regF.umr AS Umr,
regF.smtypeid AS SmTypeID,
regF.uhdiheaderinsm AS UhdiHeaderInsm,
regF.uhdisequenceid AS UhdiSequenceID,
regF.uhdipartstotal AS UhdiPartsTotal,
regF.uhdipartn AS UhdiPartN,
regF.setbackpath AS SetBackPath,
regF.smppencoding AS SmppEncoding,
regF.originalsmppencoding AS OriginalSmppEncoding,
regF.retrycount AS RetryCount,
regF.retrycountsh AS RetryCountsH,
regF.schemeid AS SchemeID,
regF.lasterrortypeid AS LastErrorTypeID,
regF.lastneterror AS LastNetError,
regF.lastsmpperror AS LastSmppError,
regF.lastnativeerror AS LastNativeError,
regF.smstatusid AS SmStatusID,
regF.reportstatus AS ReportStatus,
regF.protocolid AS ProtocolID,
regF.modifiedprotocolid AS ModifiedProtocolID,
regF.dbencoding AS DbEncoding,
regF.messagelen AS MessageLen,
regF.smbody AS SmBody,
regF.senderserviceid AS SenderServiceID,
regF.recipientserviceid AS RecipientServiceID,
regF.imsi AS Imsi,
regF.commutatorgt AS CommutatorGT,
regF.targetimsi AS TargetImsi,
regF.targetcommutatorgt AS TargetCommutatorGT,
regF.eventflag AS EventFlag,
regF.failcode AS FailCode,
regF.ScpGt AS ScpGt,
regF.ResultCode AS ResultCode
from SMCS.V_REGFAIL regF
union all
select 2 AS jType,
reg.SmscId AS SmscID,
reg.EventDate AS EventDate,
reg.SmppID AS SmppID,
reg.RegisteredDate AS RegisteredDate,
reg.SenderNPID AS SenderNPid,
reg.SenderNTID AS SenderNTid,
reg.SenderDirectionID AS SenderDirectionID,
reg.CgPN AS CgPN,
reg.OriginalCgPN AS OriginalCgPN,
reg.RecipientNPid AS RecipientNPid,
reg.RecipientNTid AS RecipientNTid,
reg.RecipientDirectionID AS RecipientDirectionID,
reg.CdPN AS CdPN,
reg.OriginalCdPN AS OriginalCdPN,
reg.ValidityPeriod AS ValidityPeriod,
reg.ExpiredDate AS ExpiredDate,
reg.DeliveryReportTypeID AS DeliveryReportTypeID,
reg.Umr AS Umr,
reg.SmTypeID AS SmTypeID,
reg.UhdiHeaderInsm AS UhdiHeaderInsm,
reg.UhdiSequenceID AS UhdiSequenceID,
reg.UhdiPartsTotal AS UhdiPartsTotal,
reg.UhdiPartN AS UhdiPartN,
reg.SetBackPath AS SetBackPath,
reg.SmppEncoding AS SmppEncoding,
reg.OriginalSmppEncoding AS OriginalSmppEncoding,
reg.RetryCount AS RetryCount,
reg.RetryCountsH AS RetryCountsH,
reg.SchemeID AS SchemeID,
reg.LastErrorTypeID AS LastErrorTypeID,
reg.LastNetError AS LastNetError,
reg.LastSmppError AS LastSmppError,
reg.LastNativeError AS LastNativeError,
reg.SmStatusID AS SmStatusID,
reg.ReportStatus AS ReportStatus,
reg.ProtocolID AS ProtocolID,
reg.ModifiedProtocolID AS ModifiedProtocolID,
reg.DbEncoding AS DbEncoding,
reg.MessageLen AS MessageLen,
reg.SmBody AS SmBody,
reg.SenderServiceID AS SenderServiceID,
reg.RecipientServiceID AS RecipientServiceID,
reg.Imsi AS Imsi,
reg.CommutatorGT AS CommutatorGT,
reg.TargetImsi AS TargetImsi,
reg.TargetCommutatorGT AS TargetCommutatorGT,
'' AS EventFlag,
'' AS FailCode,
reg.ScpGt AS ScpGt,
'' AS ResultCode
from SMCS.V_REGISTRATION reg
;
请求的视图中的所有列均存在。我对这个例外一无所知
Code: 386, e.displayText() = DB::Exception: There is no supertype for types String, DateTime because some of them are String/FixedString and some of them are not (version 19.15.3.6 (official build))
哪里有错误？简单的联合之类的select 1 as one union all select 2 as one作品效果很好。我发现的唯一原因是类型转换时，此错误是由于数组连接引起的。
最初，我以为这是由于EventFlag等进行NULL强制转换，而用''合并并替换NULL所致。但是什么都没有改变
帮助，请
PS对不起我的英语
select now() union all select ''
Code: 386. DB::Exception: Received from localhost:9000.
DB::Exception: There is no supertype for types DateTime, String
because some of them are String/FixedString and some of them are not.
转换为字符串
select toString(now()) union all select ''
┌─toString(now())─────┐
│ 2019-11-19 14:25:13 │
└─────────────────────┘
┌─toString(now())─┐
│ │
└─────────────────┘
“广播”到DateTime
select now() union all select toDateTime(0)
┌───────────────now()─┐
│ 2019-11-19 14:25:48 │
└─────────────────────┘
┌───────────────now()─┐
│ 0000-00-00 00:00:00 │
└─────────────────────┘
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句