Sub-Query Sorting

General
General covers Articles, Reference documentation, FAQs, Downloads and Blogs that do not belong to a specific subject area. General-purpose Articles about everything and anything
Enthusiast

Sub-Query Sorting

I have a sub-query below that is filtering based on rows in the same table that have a closed status. Ideally, I would like to only see the rows with a closed status for the most recent record. FYI, there can be multiple records with a closed status for a case number. My thought was to sort by date then only take the first record but I found out I am not allowed to do that in a sub-query. Does anyone else have any ideas as to how I could display this info instead of the filter I currently have. The filter I currently have doesn't give me the date of the closed record, it just tells me that there is a record closed record in the table with the same case number within the date constraints I have specified.

 

 

SELECT
B.CLM_CASENUMBER,
B.BNFT_CASENUMBER,
B.BNFT_CASE_ACTION_LASTUPDATE,
B.BNFT_CASE_ACTION_STATUS,
P.PAYMENT_DRAFT_ISSUE_DTE
FROM
P_FIR_BI_VW.V_BNFT_CASE_ACTIONS B
INNER JOIN
P_FIR_BI_VW.V_PAY_HISTORY_SCHEDULED P
on
B.CLM_CASENUMBER = P.CLM_CASENUMBER
WHERE
B.BNFT_CASE_ACTION_STATUS = 'Reopen' AND
EXTRACT(month from P.PAYMENT_DRAFT_ISSUE_DTE) = EXTRACT(month from B.BNFT_CASE_ACTION_LASTUPDATE) AND
EXTRACT(year from P.PAYMENT_DRAFT_ISSUE_DTE) = EXTRACT(year from B.BNFT_CASE_ACTION_LASTUPDATE) AND
P.PAYMENT_DRAFT_ISSUE_DTE >= B.BNFT_CASE_ACTION_LASTUPDATE AND
B.CLM_CASENUMBER in (SELECT
C.CLM_CASENUMBER
FROM
P_FIR_BI_VW.V_BNFT_CASE_ACTIONS C
WHERE C.BNFT_CASE_ACTION_STATUS = 'Closed' AND
C.CLM_CASENUMBER = B.CLM_CASENUMBER AND
EXTRACT(month from P.PAYMENT_DRAFT_ISSUE_DTE) = EXTRACT(month from C.BNFT_CASE_ACTION_LASTUPDATE) AND
EXTRACT(year from P.PAYMENT_DRAFT_ISSUE_DTE) = EXTRACT(year from C.BNFT_CASE_ACTION_LASTUPDATE) AND
P.PAYMENT_DRAFT_ISSUE_DTE <= C.BNFT_CASE_ACTION_LASTUPDATE)


Accepted Solutions
Teradata Employee

Re: Sub-Query Sorting

Remove your "B" alias of P_FIR_BI_VW.V_BNFT_CASE_ACTIONS :

  select np.*
    from nPath(          on P_FIR_BI_VW.V_BNFT_CASE_ACTIONS
               partition by BNFT_CASENUMBER
                   order by BNFT_CASE_ACTION_LASTUPDTE_TSP asc
                      using
                       mode ( nonoverlapping )
                    symbols ( BNFT_CASE_ACTION_STATUS = 'Reopen' as R
                            , BNFT_CASE_ACTION_STATUS = 'Closed' as C
                            )
                    pattern ( 'R.C' )
                     result ( first(CLM_CASENUMBER                 of R) as Claim_Number
                            , first(BNFT_CASENUMBER                of R) as Benefit_Number
                            , first(BNFT_CASE_ACTION_STATUS        of R) as Status_Reopen
                            , first(BNFT_CASE_ACTION_LASTUPDTE_TSP of R) as UpdateDate_Reopen
                            , first(BNFT_CASE_ACTION_STATUS        of C) as Status_Closed
                            , first(BNFT_CASE_ACTION_LASTUPDTE_TSP of C) as UpdateDate_Closed
                            )
              ) as np;
1 ACCEPTED SOLUTION
20 REPLIES 20
Teradata Employee

Re: Sub-Query Sorting

Hi lodoherty,

 

It seems like a good candidate to npath.

 

Are you using 16.20 by any chance ?

You can check with :

select * from dbc.dbcInfoV;
Highlighted
Enthusiast

Re: Sub-Query Sorting

VERSION 16.10.02.13
RELEASE 16.10.02.11

Teradata Employee

Re: Sub-Query Sorting

In 16.10 it's a bit limited.

Can you provide some datas (around 10 rows) ?

Enthusiast

Re: Sub-Query Sorting

090970000301 is a really good example of multiple reopens and closes within the same month. But I want to pair up each reopen with most recent following close.

Claim NumberBenefit NumberStatusDate
090960000100090960000101Closed08/18/2009 1:17:47 PM
090960000200090960000201Closed07/13/2009 9:27:20 AM
090960000300090960000301Closed04/24/2009 2:52:05 PM
090960000400090960000401Closed04/28/2009 8:13:12 AM
090960000500090960000501Closed04/13/2009 9:29:19 AM
090960000600090960000601Closed04/21/2009 2:23:09 PM
090970000200090970000201Closed04/20/2009 12:06:22 PM
090970000300090970000301Closed04/28/2009 8:28:09 AM
090970000300090970000301Reopen04/25/2009 4:05:59 PM
090970000300090970000301Closed04/26/2009 3:19:26 PM
090970000300090970000301Closed04/15/2009 9:12:28 AM
090970000300090970000301Reopen04/27/2009 9:43:16 AM
090970000400090970000401Closed05/18/2009 10:44:01 AM
090970000500090970000501Closed05/07/2009 1:41:00 PM
090970000500090970000501Reopen06/03/2009 7:45:35 AM
090970000500090970000501Closed06/04/2009 11:01:58 AM
090970000600090970000601Closed04/22/2009 9:06:42 AM
090970000700090970000701Closed07/13/2009 9:36:18 AM
090970000800090970000801Closed06/16/2009 1:32:34 PM
090970000900090970000901Closed05/08/2009 7:32:04 AM
Teradata Employee

Re: Sub-Query Sorting

Maybe you can use a RESET WHEN.

 

Datas

create multiset volatile table mvt_datas, no log
( Claim_Number     varchar(12)
, Benefit_Number   varchar(12)
, Status           varchar( 6)
, UpdateDate       timestamp(0)
)
primary index (Benefit_Number)
on commit preserve rows;

insert into mvt_datas values ('090960000100', '090960000101', 'Closed', timestamp '2009-08-18 13:17:47');
insert into mvt_datas values ('090960000200', '090960000201', 'Closed', timestamp '2009-07-13 09:27:20');
insert into mvt_datas values ('090960000300', '090960000301', 'Closed', timestamp '2009-04-24 14:52:05');
insert into mvt_datas values ('090960000400', '090960000401', 'Closed', timestamp '2009-04-28 08:13:12');
insert into mvt_datas values ('090960000500', '090960000501', 'Closed', timestamp '2009-04-13 09:29:19');
insert into mvt_datas values ('090960000600', '090960000601', 'Closed', timestamp '2009-04-21 14:23:09');
insert into mvt_datas values ('090970000200', '090970000201', 'Closed', timestamp '2009-04-20 12:06:22');
insert into mvt_datas values ('090970000300', '090970000301', 'Closed', timestamp '2009-04-28 08:28:09');
insert into mvt_datas values ('090970000300', '090970000301', 'Reopen', timestamp '2009-04-25 16:05:59');
insert into mvt_datas values ('090970000300', '090970000301', 'Closed', timestamp '2009-04-26 15:19:26');
insert into mvt_datas values ('090970000300', '090970000301', 'Closed', timestamp '2009-04-15 09:12:28');
insert into mvt_datas values ('090970000300', '090970000301', 'Reopen', timestamp '2009-04-27 09:43:16');
insert into mvt_datas values ('090970000400', '090970000401', 'Closed', timestamp '2009-05-18 10:44:01');
insert into mvt_datas values ('090970000500', '090970000501', 'Closed', timestamp '2009-05-07 13:41:00');
insert into mvt_datas values ('090970000500', '090970000501', 'Reopen', timestamp '2009-06-03 07:45:35');
insert into mvt_datas values ('090970000500', '090970000501', 'Closed', timestamp '2009-06-04 11:01:58');
insert into mvt_datas values ('090970000600', '090970000601', 'Closed', timestamp '2009-04-22 09:06:42');
insert into mvt_datas values ('090970000700', '090970000701', 'Closed', timestamp '2009-07-13 09:36:18');
insert into mvt_datas values ('090970000800', '090970000801', 'Closed', timestamp '2009-06-16 13:32:34');
insert into mvt_datas values ('090970000900', '090970000901', 'Closed', timestamp '2009-05-08 07:32:04');

collect stats column (Benefit_Number) on mvt_datas;

Query

  select t.*
       , row_number() over(partition by Benefit_Number order by UpdateDate asc
                             reset when Status = 'Reopen') as rn
    from mvt_datas as t
   where Status in ('Reopen', 'Closed')
 qualify Status = 'Reopen'
      or rn     = 2
order by Benefit_Number asc, UpdateDate asc;

Claim_Number  Benefit_Number  Status  UpdateDate           rn
------------  --------------  ------  -------------------  --
090970000300  090970000301    Reopen  2009-04-25 16:05:59   1
090970000300  090970000301    Closed  2009-04-26 15:19:26   2
090970000300  090970000301    Reopen  2009-04-27 09:43:16   1
090970000300  090970000301    Closed  2009-04-28 08:28:09   2
090970000500  090970000501    Reopen  2009-06-03 07:45:35   1
090970000500  090970000501    Closed  2009-06-04 11:01:58   2

 

Teradata Employee

Re: Sub-Query Sorting

And the nPath solution (it may work in 16.10, worth the try), datas are represented a bit in a different maner but it should be useful for you.

  select np.*
    from nPath(          on mvt_datas
               partition by Benefit_Number
                   order by UpdateDate asc
                      using
                       mode ( nonoverlapping )
                    symbols ( Status = 'Reopen' as R
                            , Status = 'Closed' as C
                            )
                    pattern ( 'R.C' )
                     result ( first(Claim_Number   of R) as Claim_Number
                            , first(Benefit_Number of R) as Benefit_Number
                            , first(Status         of R) as Status_Reopen
                            , first(UpdateDate     of R) as UpdateDate_Reopen
                            , first(Status         of C) as Status_Closed
                            , first(UpdateDate     of C) as UpdateDate_Closed
                            )
              ) as np;

claim_number  benefit_number  status_reopen  updatedate_reopen    status_closed  updatedate_closed
------------  --------------  -------------  -------------------  -------------  -------------------
090970000300  090970000301    Reopen         2009-04-25 16:05:59  Closed         2009-04-26 15:19:26
090970000300  090970000301    Reopen         2009-04-27 09:43:16  Closed         2009-04-28 08:28:09
090970000500  090970000501    Reopen         2009-06-03 07:45:35  Closed         2009-06-04 11:01:58
Enthusiast

Re: Sub-Query Sorting

The way you have the data laid out with the npath option is ultimately what I was looking for. I did adjust the query you gave me, however, I am getting an error message "[Teradata][ODBC Teradata Driver][Teradata Database] Syntax error: Expecting the word PARTITION. (42000,-3706)". I did check the query against some documenation I found for 16.10 and it seems to be using the exact same syntax and functions. Just curious if you are able to spot any additional syntax issues? If not, I will move forward with the first option and continue to research npath in 16.10 in the future.

 

select np.*
from nPath( on P_FIR_BI_VW.V_BNFT_CASE_ACTIONS B
partition by B.BNFT_CASENUMBER
order by B.BNFT_CASE_ACTION_LASTUPDTE_TSP asc
using
mode ( nonoverlapping )
pattern ( 'R.C' )
symbols ( B.BNFT_CASE_ACTION_STATUS = 'Reopen' as R,
B.BNFT_CASE_ACTION_STATUS = 'Closed' as C
)
result ( first(B.CLM_CASENUMBER of R) as Claim_Number,
first(B.BNFT_CASENUMBER of R) as Benefit_Number,
first(B.BNFT_CASE_ACTION_STATUS of R) as Status_Reopen,
first(B.BNFT_CASE_ACTION_LASTUPDTE_TSP of R) as UpdateDate_Reopen,
first(B.BNFT_CASE_ACTION_STATUS of C) as Status_Closed,
first(B.BNFT_CASE_ACTION_LASTUPDTE_TSP of C) as UpdateDate_Closed
)
) as np;

Teradata Employee

Re: Sub-Query Sorting

Remove your "B" alias of P_FIR_BI_VW.V_BNFT_CASE_ACTIONS :

  select np.*
    from nPath(          on P_FIR_BI_VW.V_BNFT_CASE_ACTIONS
               partition by BNFT_CASENUMBER
                   order by BNFT_CASE_ACTION_LASTUPDTE_TSP asc
                      using
                       mode ( nonoverlapping )
                    symbols ( BNFT_CASE_ACTION_STATUS = 'Reopen' as R
                            , BNFT_CASE_ACTION_STATUS = 'Closed' as C
                            )
                    pattern ( 'R.C' )
                     result ( first(CLM_CASENUMBER                 of R) as Claim_Number
                            , first(BNFT_CASENUMBER                of R) as Benefit_Number
                            , first(BNFT_CASE_ACTION_STATUS        of R) as Status_Reopen
                            , first(BNFT_CASE_ACTION_LASTUPDTE_TSP of R) as UpdateDate_Reopen
                            , first(BNFT_CASE_ACTION_STATUS        of C) as Status_Closed
                            , first(BNFT_CASE_ACTION_LASTUPDTE_TSP of C) as UpdateDate_Closed
                            )
              ) as np;
Enthusiast

Re: Sub-Query Sorting

That fixed the issue but unfortunately the version of Teradata that I am querying from does not have the analytical extensions implemented so I won't be able to use npath. I appreciate your help though!