[teiid-issues] [JBoss JIRA] (TEIID-4259) "TEIID30259 Cannot introduce new expressions" when using formattimestamp

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-4259?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins closed TEIID-4259.
---------------------------------


> "TEIID30259 Cannot introduce new expressions" when using formattimestamp
> ------------------------------------------------------------------------
>
>                 Key: TEIID-4259
>                 URL: https://issues.jboss.org/browse/TEIID-4259
>             Project: Teiid
>          Issue Type: Bug
>    Affects Versions: 8.11.3
>            Reporter: Bram Gadeyne
>            Assignee: Steven Hawkins
>
> A certain query executes fine when using the normal timestamp column (w.valuetime). When this is changed to formattimestamp(w.valuetime, 'dd/MM/yyyy HH:mm') we get an exception complaining about a case statement that is part of the select a few columns after the particular valuetime column.
> 14:00:00,055 WARN  [org.teiid.PROCESSOR] (Worker17615_QueryProcessorQueue95374) 7OLMH/JCvk3W TEIID30020 Processing exception for request 7OLMH/JCvk3W.48 'TEIID30259 Cannot introduce new expressions [CASE WHEN convert(anon_grp2.agg2, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date) THEN 'Gisteren' ELSE CASE WHEN convert(anon_grp2.agg2, date) = convert(now(), date) THEN 'Vannacht' ELSE '' END END, CASE WHEN anon_grp2.agg3 IS NULL THEN 'Niet gemeten' ELSE 'OK' END, CASE WHEN anon_grp2.agg4 > 0 THEN 'OK' ELSE 'Ontbreekt' END] in duplicate removal.'. Originally QueryPlannerException RuleAssignOutputElements.java:407. Enable more detailed logging to see the entire stacktrace.
> This was the query that caused the problem:
> select w.patientid as Patientid,
> formattimestamp(w.valuetime,'yyyy-MM-dd HH:mm') as t2,
> w.valuetime as Tijdstip,
> max(w.bed) as Bed, max(w.Naam) as Naam,
> case when convert(max(w.opname), date) = timestampadd(SQL_TSI_DAY, -1, convert(now() , date)) then 'Gisteren'
>         else
>         case when convert(max(w.opname), date) =  convert(now(), date) then 'Vannacht' else
>         '' end end as Opname,
> case when count(w.MondZorgAantal) is null then 'Niet gemeten' else 'OK' end as Comfort,
> case when count(w.EvaluatieMond) > 0 then 'OK' else 'Ontbreekt' end as NRS
> from
>         (
>         select pi.patientid as Patientid, null as ValueTime, pi.bedabbr as Bed, concat(pi.PatientLastName, concat(', ',pi.PatientFirstName)) as Naam, pi.AdmTime as opname, null as MondZorgAantal, null as EvaluatieMond, patientStatusID
>                 from prod_PV_PatientInfo pi
>                 where pi.PatientStatusID = 1 and PatGroupAbbr like 'PICU%' and cast(pi.admTime as date) <> cast(now() as date)
>         union
>         select v.PatientID as Patientid, v.ValueTime, null as Bed, null as Naam, null as opname,
>                 v.c as MondZorgAantal, null as EvaluatieMond, v.PatientStatusID from
>                         (select obs1.PatientID, obs1.ValueTime, count(*) as c , pi.PatientStatusID
>                         from prod_PV_DerivedVariables obs1
>                         join prod_PV_PatientInfo pi on pi.PatientID = obs1.PatientID and pi.PatientStatusID = 1 and cast(pi.admTime as date) <> cast(now() as date) and PatGroupAbbr like 'PICU%'
>                         where obs1.variableid = 30010003
>                                 and cast(valuetime as date) = timestampadd(SQL_TSI_DAY, -1, cast(now() as date))
>                         group by obs1.patientid,  obs1.ValueTime, timestampadd(SQL_TSI_DAY, -1, cast(now() as date)), pi.PatientStatusID)v
>         union
>         select v.PatientID as Patientid, v.ValueTime, null as Bed, null as Naam, null as opname, null as MondZorgAantal, v.c  as EvaluatieMond, v.PatientStatusID from
>                         (select obs1.PatientID, obs1.ValueTime, count(*) as c , pi.PatientStatusID
>                         from prod_PV_ObservationVariables obs1
>                         join prod_PV_PatientInfo pi on pi.PatientID = obs1.PatientID and pi.PatientStatusID = 1 and cast(pi.admTime as date) < cast(now() as date) and PatGroupAbbr like 'PICU%'
>                         where obs1.variableid = 15005534
>                                 and cast(obs1.valuetime as date) = timestampadd(SQL_TSI_DAY, -1, cast(now() as date))
>                         group by obs1.patientid,  obs1.ValueTime, timestampadd(SQL_TSI_DAY, -1, cast(now() as date)), pi.PatientStatusID)v
>         ) w
> group by w.patientid, w.ValueTime
> order by max(w.bed)



--
This message was sent by Atlassian JIRA
(v7.2.2#72004)