Converting date format in a Query Element

Sometimes the embedded SQL code options in parts of Tessitura are more limited in syntax options than TSQL itself: I'm often stymied in my attempts to do something clever (most recently setting up disable clauses in report parameters).  So I would then go with a view, as you have.  Now, the output set elements are displaying properly within Tessitura, right?  The first thing I'd think of with Wordfly rejecting an element is that Wordfly will not accept elements that may result in multiple rows.  Is that the case here, or is the element configured in such a way that it looks like it might return multiple rows?  Is the whole output set failing to be picked up in Wordfly or only that column?

Sometimes the embedded SQL code options in parts of Tessitura are more limited in syntax options than TSQL itself: I'm often stymied in my attempts to do something clever (most recently setting up disable clauses in report parameters).  So I would then go with a view, as you have.  Now, the output set elements are displaying properly within Tessitura, right?  The first thing I'd think of with Wordfly rejecting an element is that Wordfly will not accept elements that may result in multiple rows.  Is that the case here, or is the element configured in such a way that it looks like it might return multiple rows?  Is the whole output set failing to be picked up in Wordfly or only that column?

The original view I created is rendering properly in Tessitura -- the data fields are pulling in -- but they aren't showing up in WordFly.

The fields from the new view I created -- to try to convert the date field without breaking the view that was working -- aren't showing up in Tessitura, so it makes me feel like there is another level of permission that I'm missing.

I'm only seeing one line per constituent in the output of my list, but I'm also using a filter -- is that the issue? I'm starting to think that we may not be able to do this until v16. I need to pull in the most recent membership expiration date for lapsed members. This should be easy in v16, but maybe it isn't possible in v15 (although I feel like someone else must be doing this).

And the documentation says you can use CONVERT in Query Elements, which is the only reason I tried it.

Anne M Robichaux said:

but I'm also using a filter -- is that the issue?

Yes, I'm pretty sure that's the problem.  If the field is capable of returning more than one row Wordfly will reject it.

Anne M Robichaux said:

I need to pull in the most recent membership expiration date for lapsed members.

Not trivial but not impossible.  I have a view that does this.  If you have multiple membership organizations, you might need to build views/output sets for each.

We only have one membership organization. Would you be willing to share your code for the view? 

We had to create a custom "Membership - Most Recent" element for v15. We have several membership orgs with the same start date and that caused issues with the standard Membership - Most Recent view for us. What is the function of the filter  you mentioned?

Going to look for it as we speak!

My query element is looking at all past memberships, so the filter is on the expiration date. But a Most Recent membership element would be just what we need. Would you be willing to share your code?

We had to create a recent membership VIEW as well with a few corresponding output elements.  We have two membership organizations, as well, but we were able to make it work in the end with a little bit of playing around.

Okay, so my code is doing a bit more than I remember, and also doesn't work the way I expected it to (namely using row_number/partition).

It first looks for the last "standard" membership, that is not pending, merged or deactivated.  If it finds such a membership it returns it, but there is an additional column called "final_expr_dt" that is normally just the expr_dt, but if there is a pending membership for that customer/membership organization, then the last pending expr_dt is used.  If such a membership is not found, then it will find the last membership with a pending, merged or deactivated status and supply that: desired for my purpose but probably not anyone else's.

It uses VXS_CUST_MEMBERSHIP, so that users will only see results from membership organizations they have access to (we're a consortium) but if you have access to more than one membership organization it you will only see information for the one that has the latest date.  (Hmm.  Might brake if two different org memberships have an identical expiration date.)

You could cut that off by specifying a membership org in all the references to VXS_CUST_MEMBERSHIP, or better a starting clause to the CTE whiich does that and then use that in place of VXS_CUST_MEMBERSHIP further on.

/*	view created by Gawain Lavers 2021/07/28 */
/*	View of the "last relevant" membership for a customer.
	This is basically: any current membership, if no current then the
	last pending membership, and if non then the latest inactive membership
	and if no inactive memberships then the last of any merged or
	deactivated memberships.
	A field called final_expr_dt was added, such that current memberships
	and also see the end of any further pending memberships.

*/


USE [impresario]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

IF OBJECT_ID('[dbo].[LVS_UCB_LATEST_MEMBERSHIP]', 'V') IS NOT NULL
	DROP VIEW [dbo].[LVS_UCB_LATEST_MEMBERSHIP]
GO
CREATE VIEW [dbo].[LVS_UCB_LATEST_MEMBERSHIP]
as

	with deactivated_m as (
		select xcm.*
		from VXS_CUST_MEMBERSHIP as xcm
		where xcm.current_status in (8,9) --merged or deactivated
			and not exists (
				select 1
				from TX_CUST_MEMBERSHIP as xcm1
				where xcm1.customer_no = xcm.customer_no
					and xcm1.memb_org_no = xcm.memb_org_no
					and xcm1.expr_dt > xcm.expr_dt
					--and xcm1.cust_memb_no > xcm.cust_memb_no
				)
	), pending_m as (
		select xcm.*
		from VXS_CUST_MEMBERSHIP as xcm
		where xcm.current_status in (3) --pending
			and not exists (
				select 1
				from TX_CUST_MEMBERSHIP as xcm1
				where xcm1.customer_no = xcm.customer_no
					and xcm1.memb_org_no = xcm.memb_org_no
					and xcm1.expr_dt > xcm.expr_dt
			)
	), pending_expr as (
		select xcm.customer_no, xcm.memb_org_no, MAX(xcm.expr_dt) as expr_dt
		from VXS_CUST_MEMBERSHIP as xcm
		where xcm.current_status in (3) --pending
		group by xcm.customer_no, xcm.memb_org_no
	), nonstandard_m as (
		select * from deactivated_m as m
			where not exists (
				select 1
				from pending_m as p
				where p.customer_no = m.customer_no
					and p.memb_org_no = m.memb_org_no
				)
		union all
		select * from pending_m
	), last_nonstandard_m as (
		select
			m.*
		from nonstandard_m as m
		where
			not exists (
				select 1
				from nonstandard_m as m1
				where m1.customer_no = m.customer_no
					and m1.memb_org_no = m.memb_org_no
					and m1.cust_memb_no > m.cust_memb_no
			)
	), standard_m as (
		select xcm.*
		from VXS_CUST_MEMBERSHIP as xcm
		where xcm.current_status not in (3,8,9) --pending, merged or deactivated
	), last_standard as (
		select m.*
		from standard_m as m
		where not exists (
			select 1
			from standard_m as m1
			where m1.customer_no = m.customer_no
				and m1.memb_org_no = m.memb_org_no
				and m1.expr_dt > m.expr_dt
		)
	), last_m as (
		select
			m.*,
			case when pe.expr_dt > m.expr_dt then pe.expr_dt
				else m.expr_dt
			end as final_expr_dt
		from last_standard as m
			left outer join pending_expr as pe on pe.customer_no = m.customer_no
				and pe.memb_org_no = m.memb_org_no
		where not exists (
			select 1
			from last_standard as m1
			where m1.customer_no = m.customer_no
				and m1.memb_org_no = m.memb_org_no
				and m1.cust_memb_no > m.cust_memb_no
			)
		union all
		select m.*, m.expr_dt as final_expr_dt
		from last_nonstandard_m as m
		where not exists (
			select 1
			from standard_m as m1
			where m1.customer_no = m.customer_no
				and m1.memb_org_no = m.memb_org_no
			)
	)
	select * from last_m
GO
GRANT SELECT ON [dbo].[LVS_UCB_LATEST_MEMBERSHIP] TO [ImpUsers], [Tessitura_app]

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

Also a reminder to myself that I have to update all of these views to work with v16!

Oh, and for TR_QUERY_ELEMENT, make sure that the elements have "Single Row" checked.  But also you do need a view that guarantees that.