-
1. Re: Error deploying VDB, "expected integer, but was biginteger"
Can you provide the definition for VW_QBP.QBP_HIP?
Are there any aggregate values?
-
2. Re: Error deploying VDB, "expected integer, but was biginteger"
I can provide the definition but I'm not sure what that means. Are you looking for the .xmi file?
There is one aggregation being used, a MAX in a correlated subquery.
FYI that isn't the case in the other view table, VW_QBP.VW_ABSTRACT_HIG...
-
3. Re: Error deploying VDB, "expected integer, but was biginteger"
> I can provide the definition but I'm not sure what that means. Are you looking for the .xmi file?
The sql that defines the view. Particularly what defines column 7.
> FYI that isn't the case in the other view table, VW_QBP.VW_ABSTRACT_HIG...
There have been some changes over time in the typing of aggregate values. However it's entirely possible that something else is going on.
-
4. Re: Error deploying VDB, "expected integer, but was biginteger"
Here is the SQL for QBP_HIP.
FYI there are 4 sources involved. VW_AB_ABSTRACT_HIG is a view to a file based source and a SQL Server database (Med2020), VW_QBP.VW_ABSTRACT_HIG is a view to a file based source and a Firebird database (Cactus). The union is then being joined to another SQL Server. VW_AB_ABSTRACT_HIG.AGE has a datatype of "integer", VW_ABSTRACT_HIG.AgeNumber has a datatype of "int", and QBP_HIP.age has datatype "int". Is the issue possibly arising out of the union somehow?
SELECT
S.dbg_acct_number,
S.dbg_unit_number,
S.dbg_er_acct_number,
S.system_name,
S.line_number,
S.hn_status_code,
S.age,
S.gender,
S.mdrx_code,
S.hig_code,
S.hig_weight,
S.entry_code,
S.ed_registration_datetime,
S.admit_datetime,
S.discharge_datetime,
S.discharge_disposition,
S.acute_days,
S.alc_days,
S.institution_from_type,
S.institution_from,
S.institution_to_type,
S.institution_to,
S.intervention_code,
S.intervention_status_code,
S.ooh_indicator_code,
S.intervention_start_time,
S.anaesthetic_technique,
S.mt_patientid,
AV.ServiceDateTime AS ed_registration_datetime_1,
AD.ErDateTime AS ed_disposition_datetime,
ADM.AdmitDateTime AS ed_admit_admission_datetime,
AD.DischargeDateTime AS ed_admit_discharge_datetime,
CASE WHEN ACA.AccountNumber IS NOT NULL THEN ACA.AccountNumber ELSE AV.AccountNumber END AS er_account_number
FROM
(
(
(
(
SELECT
AB.EncounterNumber AS dbg_acct_number,
AB.ChartNumber AS dbg_unit_number,
COALESCE(ER.EncounterNumber, '') AS dbg_er_acct_number,
'MED2020' AS system_name,
'' AS line_number,
CASE WHEN LENGTH(AB.HealthCareNumber) = 10 THEN 'Y' ELSE 'N' END AS hn_status_code,
AB.AgeNumber AS age,
GENDER.DisplayCode AS gender,
DF.CIHIValue AS mdrx_code,
COALESCE(AB.HIG, '') AS hig_code,
COALESCE(AB.hig_weight, '') AS hig_weight,
EC.CIHIValue AS entry_code,
TIMESTAMPCREATE(CAST(ER.AdmitDate AS DATE), CAST(ER.AdmitTime AS TIME)) AS ed_registration_datetime,
TIMESTAMPCREATE(CAST(AB.AdmitDate AS DATE), CAST(AB.AdmitTime AS TIME)) AS admit_datetime,
TIMESTAMPCREATE(CAST(AB.DischargeDate AS DATE), CAST(AB.DischargeTime AS TIME)) AS discharge_datetime,
DISP.CIHIValue AS discharge_disposition,
AB.AcuteLOS AS acute_days,
AB.ALCLOS AS alc_days,
COALESCE(INSTFROM.HospitalType, '') AS institution_from_type,
COALESCE(INSTFROM.CIHIValue, '') AS institution_from,
COALESCE(INSTTO.HospitalType, '') AS institution_to_type,
COALESCE(INSTTO.CIHIValue, '') AS institution_to,
PF.CIHIValue AS intervention_code,
COALESCE(IAS.CIHIValue, '') AS intervention_status_code,
COALESCE(OOH.CIHIValue, '') AS ooh_indicator_code,
CASE WHEN I.IntervTimeIn IS NOT NULL THEN TIMESTAMPCREATE(CAST(COALESCE(I.IntervDateIn, I.IntervDate) AS DATE), CAST(I.IntervTimeIn AS TIME)) END AS intervention_start_time,
COALESCE(ANESTECH.CIHIValue, '') AS anaesthetic_technique,
UN.PatientID AS mt_patientid
FROM
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
VW_QBP.VW_AB_ABSTRACT_HIG AS AB
INNER JOIN SRC_MED2020.H_HospitalProfile AS H ON H.Code = AB.HospitalID
)
INNER JOIN SRC_MED2020.LU_HCNProvince AS PROV ON PROV.Code = AB.HCNProvince
)
INNER JOIN SRC_MED2020.LU_RespPayment AS RESP ON RESP.Code = AB.ResponsiblePayment
)
LEFT OUTER
JOIN SRC_MED2020.AB_Diagnosis AS D ON D.AbstractID = AB.AbstractID
)
LEFT OUTER
JOIN SRC_MED2020.ICD_DiagFile10 AS DF ON DF.Code = D.DiagnosisCode
)
LEFT OUTER
JOIN SRC_MED2020.LU_DiagnosisType AS DT ON D.DiagnosisType = DT.Code
)
LEFT OUTER
JOIN SRC_MED2020.AB_Intervention AS I ON I.AbstractID = AB.AbstractID
)
LEFT OUTER
JOIN SRC_MED2020.ICD_ProcFile10 AS PF ON PF.Code = I.IntervCode
)
LEFT OUTER
JOIN SRC_MED2020.LU_AnaestTechnique AS ANESTECH ON ANESTECH.Code = I.IntervAnaestTechnique
)
LEFT OUTER
JOIN SRC_MED2020.LU_Gender_I AS GENDER ON GENDER.Code = AB.ab_gender
)
LEFT OUTER
JOIN SRC_MED2020.LU_DispositionCode_I AS DISP ON DISP.Code = AB.DispositionCode
)
LEFT OUTER
JOIN SRC_MED2020.LU_Institution_10 AS INSTFROM ON INSTFROM.Code = AB.InstitutionFrom
)
LEFT OUTER
JOIN SRC_MED2020.LU_Institution_10 AS INSTTO ON INSTTO.Code = AB.InstitutionTo
)
LEFT OUTER
JOIN SRC_MED2020.LU_IntervAttStatus AS IAS ON IAS.Code = I.IntervAttribStatus
)
LEFT OUTER
JOIN SRC_MED2020.LU_YesBlank AS OOH ON OOH.Code = I.IntervOOHIndicator
)
LEFT OUTER
JOIN SRC_MED2020.LU_EntryCode AS EC ON EC.Code = AB.EntryCode
)
LEFT OUTER
JOIN SRC_MED2020.AB_Abstract AS ER ON ER.AbstractID IN
(
SELECT
SRC_MED2020.AB_Abstract.AbstractID
FROM SRC_MED2020.AB_Abstract
WHERE (SRC_MED2020.AB_Abstract.ChartNumber = AB.ChartNumber)
AND (SRC_MED2020.AB_Abstract.AdmitDate < AB.AdmitDate)
AND
(
Timestampdiff
(
SQL_TSI_HOUR,
SRC_MED2020.AB_Abstract.AdmitDate,
AB.AdmitDate
)
< convert(48, long)
)
AND (SRC_MED2020.AB_Abstract.CareType = 5)
AND (SRC_MED2020.AB_Abstract.TriageLevel IS NOT NULL)
AND (SRC_MED2020.AB_Abstract.TriageLevel > 0)
ORDER BY SRC_MED2020.AB_Abstract.AdmitDate DESC LIMIT 1
)
)
LEFT OUTER
JOIN /*+ MAKEDEP */ SRC_MT_PAT.MriPatientUnitNumbers AS UN ON UN.UnitNumber = CONCAT
(
SUBSTRING(AB.ChartNumber, 1, 2),
RIGHT(CONCAT('00000000000', SUBSTRING(AB.ChartNumber, 3)), 8)
)
WHERE (H.DisplayCode = 'TIMMINS')
AND (AB.DischargeDate >= convert({d'2014-03-01'}, timestamp))
AND (AB.AgeNumber >= 18)
AND ((PROV.CIHIValue = 'ON') AND (RESP.CIHIValue = '01'))
AND
(
(DT.DisplayCode = 'M')
AND
(
(
(DF.CIHIValue LIKE 'S720%')
AND (DF.CIHIValue NOT LIKE 'S7200%')
)
OR (DF.CIHIValue LIKE 'S721%') OR (DF.CIHIValue LIKE 'S722%')
)
)
AND
(
I.IntervOccurrence =
(
SELECT
MIN(SRC_MED2020.AB_Intervention.IntervOccurrence)
FROM SRC_MED2020.AB_Intervention
INNER JOIN SRC_MED2020.ICD_ProcFile10 ON SRC_MED2020.ICD_ProcFile10.Code = SRC_MED2020.AB_Intervention.IntervCode
WHERE (SRC_MED2020.AB_Intervention.AbstractID = AB.AbstractID)
AND
(
(
SRC_MED2020.ICD_ProcFile10.CIHIValue LIKE '1VA74%'
)
OR
(
SRC_MED2020.ICD_ProcFile10.CIHIValue LIKE '1VA53%'
)
OR
(
SRC_MED2020.ICD_ProcFile10.CIHIValue LIKE '1VC74%'
)
OR
(
SRC_MED2020.ICD_ProcFile10.CIHIValue LIKE '1SQ53%'
)
)
)
)
AND
(
UN.RowUpdateDateTime =
(
SELECT
MAX(SRC_MT_PAT.MriPatientUnitNumbers.RowUpdateDateTime)
FROM SRC_MT_PAT.MriPatientUnitNumbers
WHERE SRC_MT_PAT.MriPatientUnitNumbers.PatientID = UN.PatientID
)
)
UNION
SELECT
AB.DG_ENCOUNTER AS dbg_acct_number,
AB.CHARTNO AS dbg_unit_number,
COALESCE(ER.ENCOUNTER, '') AS dbg_er_acct_number,
'CACTUS' AS system_name,
'' AS line_number,
CASE WHEN LENGTH(AB.HEALTHNO) >= 10 THEN 'Y' ELSE 'N' END AS hn_status_code,
AB.AGE AS age,
AB.SEX AS gender,
VW_QBP.stripPunctuation(SRC_CACTUS.CHDX.DXCODE) AS mdrx_code,
COALESCE(AB.HIG, '') AS hig_code,
COALESCE(AB.hig_weight, '') AS hig_weight,
AB.AB_ENTRY AS entry_code,
ER.REGDATE AS ed_registration_datetime,
AB.ADATE AS admit_datetime,
AB.DISDATE AS discharge_datetime,
AB.DISPOSITION AS discharge_disposition,
AB.LOS AS acute_days,
AB.ALC AS alc_days,
COALESCE(convert(INSTFROM.INSTTYPE, string), '') AS institution_from_type,
COALESCE(INSTFROM.INSTNO, '') AS institution_from,
COALESCE(convert(INSTTO.INSTTYPE, string), '') AS institution_to_type,
COALESCE(INSTTO.INSTNO, '') AS institution_to,
I.CODE AS intervention_code,
COALESCE(SRC_CACTUS.CHPR.STAT, '') AS intervention_status_code,
CASE WHEN SRC_CACTUS.CHEP.OUTF IS NULL THEN '' WHEN SRC_CACTUS.CHEP.OUTF = convert('F', char) THEN '' WHEN SRC_CACTUS.CHEP.OUTF = convert('T', char) THEN 'Y' END AS ooh_indicator_code,
SRC_CACTUS.CHEP.DATEIN AS intervention_start_time,
SRC_CACTUS.CHPR.TECH AS anaesthetic_technique,
UN.PatientID AS mt_patientid
FROM
(
(
(
(
(
(
(
(
(
(
(
VW_QBP.VW_ABSTRACT_HIG AS AB LEFT OUTER
JOIN SRC_CACTUS.CHDX ON SRC_CACTUS.CHDX.CHARTUNIQ = AB.UNIQ
)
LEFT OUTER
JOIN SRC_CACTUS.DXNAME ON SRC_CACTUS.DXNAME.DXNAMEUNIQ = SRC_CACTUS.CHDX.DXNAMEUNIQ
)
LEFT OUTER
JOIN SRC_CACTUS.CHEP ON SRC_CACTUS.CHEP.CHARTUNIQ = AB.UNIQ
)
LEFT OUTER
JOIN SRC_CACTUS.CHPR ON SRC_CACTUS.CHPR.CHEPUNIQ = SRC_CACTUS.CHEP.CHEPUNIQ
)
LEFT OUTER
JOIN SRC_CACTUS.OP_ROOM ON SRC_CACTUS.OP_ROOM.OPNO = SRC_CACTUS.CHEP.ROOM
)
LEFT OUTER
JOIN SRC_CACTUS.INTERV AS I ON I.INTERVUNIQ = SRC_CACTUS.CHPR.INTERVUNIQ
)
LEFT OUTER
JOIN SRC_CACTUS.AN_TECH ON convert
(
SRC_CACTUS.AN_TECH.TECHNO, string
)
= SRC_CACTUS.CHPR.TECH
)
LEFT OUTER
JOIN SRC_CACTUS.INT_PL AS INT_STA ON INT_STA.TABNO = SRC_CACTUS.CHPR.STATUS_ID
AND INT_STA.STAT = SRC_CACTUS.CHPR.STAT
)
LEFT OUTER
JOIN SRC_CACTUS.INST AS INSTTO ON INSTTO.INSTNO = AB.INSTTO
)
LEFT OUTER
JOIN SRC_CACTUS.INST AS INSTFROM ON INSTFROM.INSTNO = AB.INSTFR
)
LEFT OUTER
JOIN SRC_CACTUS.ACABSTRACT AS ER ON ER.UNIQ IN
(
SELECT
SRC_CACTUS.ACABSTRACT.UNIQ
FROM SRC_CACTUS.ACABSTRACT
WHERE (SRC_CACTUS.ACABSTRACT.CHARTNO = AB.CHARTNO)
AND (SRC_CACTUS.ACABSTRACT.REGDATE <= AB.ADATE)
AND
(
Timestampdiff
(
SQL_TSI_HOUR, SRC_CACTUS.ACABSTRACT.REGDATE, AB.ADATE
)
< convert(48, long)
)
AND (SRC_CACTUS.ACABSTRACT.TRIAGE IS NOT NULL)
ORDER BY SRC_CACTUS.ACABSTRACT.REGDATE DESC LIMIT 1
)
)
LEFT OUTER
JOIN /*+ MAKEDEP */ SRC_MT_PAT.MriPatientUnitNumbers AS UN ON UN.UnitNumber = AB.CHARTNO
WHERE (AB.DISDATE >= {ts'2012-04-01 00:00:00.0'})
AND (AB.AGE >= 18)
AND ((AB.HCNO_PROV = 'ON') AND (AB.RFP = '01'))
AND
(
(SRC_CACTUS.CHDX.DXTYPE = convert('M', char))
AND
(
(
(SRC_CACTUS.CHDX.DXCODE LIKE 'S72.0%')
AND (SRC_CACTUS.CHDX.DXCODE NOT LIKE 'S72.00%')
)
OR
(
SRC_CACTUS.CHDX.DXCODE LIKE 'S72.1%'
)
OR (SRC_CACTUS.CHDX.DXCODE LIKE 'S72.2%')
)
)
AND
(
SRC_CACTUS.CHEP.CHEPUNIQ =
(
SELECT
MIN(SRC_CACTUS.CHPR.CHEPUNIQ)
FROM SRC_CACTUS.CHPR
WHERE (SRC_CACTUS.CHPR.CHARTUNIQ = AB.UNIQ)
AND
(
(
SRC_CACTUS.CHPR.CODE LIKE '1.VA.74%'
)
OR
(
SRC_CACTUS.CHPR.CODE LIKE '1.VA.53%'
)
OR
(
SRC_CACTUS.CHPR.CODE LIKE '1.VC.74%'
)
OR (SRC_CACTUS.CHPR.CODE LIKE '1.SQ.53%')
)
)
)
AND
(
SRC_CACTUS.CHPR.OCCUR =
(
SELECT
MIN(SRC_CACTUS.CHPR.OCCUR)
FROM SRC_CACTUS.CHPR
WHERE (SRC_CACTUS.CHPR.CHEPUNIQ = SRC_CACTUS.CHEP.CHEPUNIQ)
AND
(
(
SRC_CACTUS.CHPR.CODE LIKE '1.VA.74%'
)
OR
(
SRC_CACTUS.CHPR.CODE LIKE '1.VA.53%'
)
OR
(
SRC_CACTUS.CHPR.CODE LIKE '1.VC.74%'
)
OR (SRC_CACTUS.CHPR.CODE LIKE '1.SQ.53%')
)
)
)
AND
(
UN.RowUpdateDateTime =
(
SELECT
MAX(SRC_MT_PAT.MriPatientUnitNumbers.RowUpdateDateTime)
FROM SRC_MT_PAT.MriPatientUnitNumbers
WHERE SRC_MT_PAT.MriPatientUnitNumbers.PatientID = UN.PatientID
)
)
)
AS S LEFT OUTER
JOIN /*+ MAKEDEP */ SRC_MT_PAT.AdmVisits AS AV ON AV.PatientID = S.mt_patientid
)
LEFT OUTER
JOIN /*+ MAKEDEP */ SRC_MT_PAT.AdmCanadianAccounts AS ACA ON ACA.VisitID = AV.VisitID
)
LEFT OUTER
JOIN /*+ MAKEDEP */ SRC_MT_PAT.AdmittingData AS ADM ON ADM.VisitID = AV.VisitID
)
LEFT OUTER
JOIN /*+ MAKEDEP */ SRC_MT_PAT.AdmDischarge AS AD ON AD.VisitID = AV.VisitID
WHERE AV.ServiceDateTime =
(
SELECT
MAX(SRC_MT_PAT.AdmVisits.ServiceDateTime)
FROM SRC_MT_PAT.AdmVisits
INNER JOIN SRC_MT_PAT.AdmittingCanadianData AS ACD ON ACD.VisitID = SRC_MT_PAT.AdmVisits.VisitID
WHERE (SRC_MT_PAT.AdmVisits.PatientID = AV.PatientID)
AND (ACD.TriageLevelID IS NOT NULL)
AND (SRC_MT_PAT.AdmVisits.ServiceDateTime < S.admit_datetime)
AND
(
Timestampdiff
(
SQL_TSI_HOUR, SRC_MT_PAT.AdmVisits.ServiceDateTime, S.admit_datetime
)
< convert(48, long)
)
)
-
5. Re: Error deploying VDB, "expected integer, but was biginteger"
What version of Teiid Designer are you on?
It looks like there are some type issues with the int datatype - int and integer in Teiid Designer 8.3
-
-
7. Re: Error deploying VDB, "expected integer, but was biginteger"
It seems like Designer is telling the engine that the int type maps to biginteger, then when Teiid resolves the union we are widening to biginteger. If that's the case, then Designer is not resolving/validating correctly and should be giving you an error at design time. You can confirm with a simpler test case and or add a cast to the int columns to integer.
-
8. Re: Error deploying VDB, "expected integer, but was biginteger"
Adding casts to the problem columns on VW_ABSTRACT_HIG resolves the issue.
I also tried removing the casts and 'reversed' the union around so that the problem column occurred first but that still threw a deploy error. Interestingly enough I then had to cast the column to integer on both sides of the union.
-
9. Re: Error deploying VDB, "expected integer, but was biginteger"
Can you log an issue against Designer? If they are reporting a runtime type of biginteger, then the validation logic on their side should know to raise an error.
-
10. Re: Error deploying VDB, "expected integer, but was biginteger"
These were logged previously related to the same or similar issue
https://issues.jboss.org/browse/TEIIDDES-2018
[TEIIDDES-2256] Confusing data type "integer" and "int" - JBoss Issue Tracker
Ramesh..
-
11. Re: Error deploying VDB, "expected integer, but was biginteger"
Thanks for your help on identifying a workaround!
Issue logged as [TEIIDDES-2438] Deploy error "expected integer, but was biginteger" when VDB table has definition consisting of union of…