SELECT Failed. [5628] Column B2.TIMESTMP_RCRD_CRTE not found in csprodviews.B3.

Database

SELECT Failed. [5628] Column B2.TIMESTMP_RCRD_CRTE not found in csprodviews.B3.

Hello,
I hope I'm asking this appropriately. What I am looking to do is pull the most recent instance of CD_ACTVY = 'MYYYY', when it precedes CD_ACTVY = 'MXXXX'. There should always be a CD_ACTVY = 'MYYYY' prior to CD_ACTVY = 'MXXXX', however it is often not the record immeadiately prior and could have been made multiple times both before and after by different users. I've highlighted in BOLD the portion that I'm having trouble with. I don't want to lose the records where the MAX 'MYYYY' date is greater than the MAX 'MXXXX' date. I'd simply want it to pull the record before. However, this way doesn't work as the column is not present. Is there somewhere I can place this that will still include all appropriate records?


FROM cs.dcse_casev AS C
JOIN(
SELECT CD_ACTVY, B1.ID_CASE, B1.TIMESTMP_RCRD_CRTE, OPID_RCRD_CRTE
FROM cs.DCSE_CASE_ACTIVITYV AS B1
     JOIN(
     SELECT B2.ID_CASE, MAX(TIMESTMP_RCRD_CRTE) as EVENT_DATE1
     FROM csprodviews.DCSE_CASE_ACTIVITYV AS B2
     WHERE CD_ACTVY = 'MXXXX'
     GROUP BY B2.ID_CASE) AS B4
                JOIN(
                          SELECT B3.ID_CASE, MAX(TIMESTMP_RCRD_CRTE) as EVENT_DATE2
                         FROM cs.DCSE_CASE_ACTIVITYV AS B3
                          WHERE CD_ACTVY = 'MYYYY'
                          AND B3.TIMESTMP_RCRD_CRTE < B2.TIMESTMP_RCRD_CRTE
                         GROUP BY B3.ID_CASE) AS B5
ON B1.ID_CASE = B5.ID_CASE AND B1.TIMESTMP_RCRD_CRTE = EVENT_DATE2
ON B1.ID_CASE = B4.ID_CASE AND B1.TIMESTMP_RCRD_CRTE = EVENT_DATE1) AS B
ON C.ID_CASE = B.ID_CASE

 


Accepted Solutions
Junior Contributor

Re: SELECT Failed. [5628] Column B2.TIMESTMP_RCRD_CRTE not found in csprodviews.B3.

Ok, it's a bit more complicated, based on two nested OLAP functions:

JOIN
 (
   SELECT *
   FROM
    (
      SELECT dca.*
      FROM DCSE_CASE_ACTIVITYV AS dca
      WHERE CD_ACTVY IN ('MXXXX', 'MYYYY') -- only those activities are needed
      QUALIFY CD_ACTVY = 'MYYYY'           -- looking for MYYYY
                                           -- before the latest MXXXX timestamp
          AND TIMESTMP_RCRD_CRTE < Max(CASE WHEN CD_ACTVY = 'MXXXX' THEN TIMESTMP_RCRD_CRTE end) Over (PARTITION BY ID_CASE)
    ) AS dt
   QUALIFY -- now get the latest row
      Row_Number() Over (PARTITION BY ID_CASE ORDER BY TIMESTMP_RCRD_CRTE DESC) = 1
 ) AS b
ON C.ID_CASE = B.ID_CASE 

There might be other ways to get the same result, but I don't think it can be simplified to a single STATS step in Explain.

 

1 ACCEPTED SOLUTION
8 REPLIES
Junior Contributor

Re: SELECT Failed. [5628] Column B2.TIMESTMP_RCRD_CRTE not found in csprodviews.B3.

Can you show some example data (before you try to apply your logic)?

This is probably a simple task for OLAP functions.

Re: SELECT Failed. [5628] Column B2.TIMESTMP_RCRD_CRTE not found in csprodviews.B3.

Sure. Hopefully, this is what you're looking for. So in the below data, I underlined the record that is being referenced as being compared to. I bolded the first record that I would expect to see in my results since it is the first CD_ACTVY 'MYYYY' record to precede the CD_ACTVY 'MXXXX' record.

ID_CASEOPID_RCRD_CRTESupervisorTIMESTMP_RCRD_CRTECD_ACTVY
00015Worker 2Supervisor 212/8/2005 16:55MRRRR
00015Worker 3Supervisor 37/21/2015 8:28MYYYY
00015Worker 1Supervisor 11/31/2017 10:11MZZZZ
00015Worker 1Supervisor 111/30/2017 15:16MXXXX
00015Worker 1Supervisor 110/12/2018 11:10MYYYY
Highlighted
Teradata Employee

Re: SELECT Failed. [5628] Column B2.TIMESTMP_RCRD_CRTE not found in csprodviews.B3.

Rules are clear but what output are you expecting ? One more column, two rows, other ?

Junior Contributor

Re: SELECT Failed. [5628] Column B2.TIMESTMP_RCRD_CRTE not found in csprodviews.B3.

Ok, it's a bit more complicated, based on two nested OLAP functions:

JOIN
 (
   SELECT *
   FROM
    (
      SELECT dca.*
      FROM DCSE_CASE_ACTIVITYV AS dca
      WHERE CD_ACTVY IN ('MXXXX', 'MYYYY') -- only those activities are needed
      QUALIFY CD_ACTVY = 'MYYYY'           -- looking for MYYYY
                                           -- before the latest MXXXX timestamp
          AND TIMESTMP_RCRD_CRTE < Max(CASE WHEN CD_ACTVY = 'MXXXX' THEN TIMESTMP_RCRD_CRTE end) Over (PARTITION BY ID_CASE)
    ) AS dt
   QUALIFY -- now get the latest row
      Row_Number() Over (PARTITION BY ID_CASE ORDER BY TIMESTMP_RCRD_CRTE DESC) = 1
 ) AS b
ON C.ID_CASE = B.ID_CASE 

There might be other ways to get the same result, but I don't think it can be simplified to a single STATS step in Explain.

 

Re: SELECT Failed. [5628] Column B2.TIMESTMP_RCRD_CRTE not found in csprodviews.B3.

This is exactly what I needed. I was getting tripped up with a standard OLAP and stacking subqueries... and seeing this makes much more sense. Thank you!

Teradata Employee

Re: SELECT Failed. [5628] Column B2.TIMESTMP_RCRD_CRTE not found in csprodviews.B3.


 There might be other ways to get the same result, but I don't think it can be simplified to a single STATS step in Explain.

I've got 5 steps with your query using those datas :

 

create multiset volatile table DCSE_CASE_ACTIVITYV, no log
( ID_CASE               char(5)
, OPID_RCRD_CRTE        varchar(10)
, Supervisor            varchar(12)
, TIMESTMP_RCRD_CRTE    timestamp(0)
, CD_ACTVY              char(5)
)
primary index (ID_CASE)
on commit preserve rows;

insert into DCSE_CASE_ACTIVITYV values ('00015', 'Worker 2', 'Supervisor 2', timestamp '2005-12-08 16:55:00', 'MRRRR');
insert into DCSE_CASE_ACTIVITYV values ('00015', 'Worker 2', 'Supervisor 2', timestamp '2013-12-08 16:55:00', 'MYYYY');
insert into DCSE_CASE_ACTIVITYV values ('00015', 'Worker 3', 'Supervisor 3', timestamp '2015-07-21 08:28:00', 'MYYYY');
insert into DCSE_CASE_ACTIVITYV values ('00015', 'Worker 1', 'Supervisor 1', timestamp '2017-01-31 10:11:00', 'MZZZZ');
insert into DCSE_CASE_ACTIVITYV values ('00015', 'Worker 1', 'Supervisor 1', timestamp '2017-11-30 15:16:00', 'MXXXX');
insert into DCSE_CASE_ACTIVITYV values ('00015', 'Worker 1', 'Supervisor 1', timestamp '2018-10-12 11:10:00', 'MYYYY');

I've got another 5-steps query with shorter SQL albeit harder to understand :

  select *
    from DCSE_CASE_ACTIVITYV
   where CD_ACTVY in ('MXXXX', 'MYYYY')
 qualify TIMESTMP_RCRD_CRTE < min(case CD_ACTVY when 'MXXXX' then TIMESTMP_RCRD_CRTE end) over(partition by ID_CASE)
     and TIMESTMP_RCRD_CRTE = max(case CD_ACTVY when 'MYYYY' then TIMESTMP_RCRD_CRTE end) over(partition by ID_CASE order by TIMESTMP_RCRD_CRTE desc reset when CD_ACTVY = 'MXXXX');
  -- and row_number() over(partition by ID_CASE order by TIMESTMP_RCRD_CRTE desc reset when CD_ACTVY = 'MXXXX') = 2;
  -- the previous row is similar to the one before, choose the one you understand better

 

And I've got this 3-steps query with an exists / qualify, and it may be the easiest solution to re-read :

 

 

  select t1.*
    from DCSE_CASE_ACTIVITYV as t1
   where t1.CD_ACTVY = 'MYYYY'
     and exists (select null
                   from DCSE_CASE_ACTIVITYV as t2
                  where t2.ID_CASE            = t1.ID_CASE
                    and t2.TIMESTMP_RCRD_CRTE > t1.TIMESTMP_RCRD_CRTE
                    and t2.CD_ACTVY           = 'MXXXX')
qualify row_number() over(partition by t1.ID_CASE order by t1.TIMESTMP_RCRD_CRTE desc) = 1;

 

 

 

Junior Contributor

Re: SELECT Failed. [5628] Column B2.TIMESTMP_RCRD_CRTE not found in csprodviews.B3.

As I said, two STATS steps, I didn't care about the other steps :-)

The EXISTS might be fast, if the number of rows per ID_CASE is low and it's the PI.

I just usually prefer OLAP because it works without additional access to the table (the naming of DCSE_CASE_ACTIVITYV seems to refer to a view and you never know how complex it is)

Re: SELECT Failed. [5628] Column B2.TIMESTMP_RCRD_CRTE not found in csprodviews.B3.

This is great, too. I'm saving that 3-step query as that one seems like the easiest to explain to a couple of other individuals I work with. Very helpful.