CR XI equivalent of SQL join (temporary file) on tf.A=a.r AND tf.B=b.c

[PWD]

Can this be done? In SQL I have a temporary file (#da)that selects the first (earliest) occurrence of a date field and a membership number. When running the full select I can join it

join #da ON #da.membership_no = m.membership_no and #da.[Date Altered] = uh.date_altered
--Only find exact matches to 1st occurrence of Fellow appointment

So I need to be able to do the same thing to produce the Crystal Report. There is no other way in SQL (that we know of) to select the unique occurrence from these tables; can I replicate this in Crystal? What would I need to do?

Thanks in advance,

Des.

 

[lbass]

Do you mean you want the earliest date per membership for linking?

You can create a SQL expression {%mindt} like this:

(
select min(`date_altered`)
from table A
where A.`membership_no` = table.`membership_no`
)

Punctuation is specific to your datasource. Do not reference the table name within the summary (at least in XI). Then in report->selection formula->record, use:

{uh.date_altered} = {%mindt}

This will return only the first date per membership no.

-LB

 

[PWD]

This is the code that I've used in SQL

		select --Get 1st occurrence of Fellow appointment

		min(uh.date_altered)AS 'Date Altered'
		,m.membership_no

		into #da
		--drop table #da

		from update_history uh
		join member m on m.member_ref = uh.table_key
		join lookup l on uh.lookup_key = l.lookup_ref

		where upper(uh.table_altered) = 'MEMBER'
		and upper(uh.column_altered) = 'MEMBER_CLASS'
		and l.lookup_code like ('F%')-- Any Fellow Class

to select (into a temporary file) the 1st instance of amendments to the MEMBER table, MEMBER CLASS column concerning their Fellowship status. I then create the main selection and ONLY want to include the details that give an exact match to the data in the temporary table.

select 

m.membership_no
,uh.date_altered
--,
--*
from update_history uh
join lookup l on uh.lookup_key = l.lookup_ref
join member m on m.member_ref = uh.table_key
join hist.dbo.rosh_individual ri ON ri.individual_ref = m.individual_ref

join #da ON #da.membership_no = m.membership_no and #da.[Date Altered] = uh.date_altered
--Only find exact matches to 1st occurrence of Fellow appointment

where upper(uh.table_altered) = 'MEMBER'
and uh.column_altered = 'member_class'
and l.lookup_code like ('F%')-- Any Fellow Class
and uh.date_altered < '2008-02-27'--Records only started on 27/2/2008!!
and ri.mem_stat_code = 'ACT'--Active Member
and ri.type_code = 'MEM' --Membership type of Member

order by m.membership_no
,uh.date_altered

I've looked at what you've said & tried to create an Expression

(
select min(uh.DATE_ALTERED) , M.MEMBER_REF
FROM UPDATE_HISTORY UH JOIN MEMBER M ON M.MEMBER_REF = UH.TABLE_KEY
where upper("UPDATE_HISTORY"."TABLE_ALTERED") = "MEMBER"
and upper("UPDATE_HISTORY"."COLUMN_ALTERED")= "MEMBER_CLASS"
)

but it errors

"Invalid column name 'MEMBER'"

Then I tried

(
select min(uh.DATE_ALTERED) , M.MEMBER_REF
FROM UPDATE_HISTORY UH JOIN MEMBER M ON M.MEMBER_REF = UH.TABLE_KEY
where upper(UH.TABLE_ALTERED) = 'MEMBER'
and upper(UH.COLUMN_ALTERED)= 'MEMBER_CLASS'
GROUP BY M.MEMBER_REF
)

but it errors

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

I appear to be well & truly stuck

Des.

 

[IanWaterman]

Why don't you just use a command and enter your query directly in there.

In database expert do not select any tables, select command and enter your SQL into the command edit box

Ian

 

[PWD]

Hi Ian, I'm sorry to sound (be) thick but I don't understand your question. This is a report I've inherited with 12 sub-reports, each of which will require this new data to be displayed alongside the current data. It will be signed off & handed to users to run when completed.

The following Expression code appears to be agreeable to CR

(
select min(uh.date_altered)
from update_history uh
join lookup l on uh.lookup_key = l.lookup_ref
where upper(uh.table_altered) = 'MEMBER'
		and upper(uh.column_altered) = 'MEMBER_CLASS'
		and l.lookup_code like ('F%')

)

(BTW what is the difference between '%' & '*' in this context?)
It would appear to me that the issue I have with the Expression is that it can only hold one column. Is that so? What I need, effectively, is 2 queries and only select the records that have an exact match on Membership Number & Date Altered. The first one should select the Membership Number AND Date Altered as per the criteria above - e.g. the first instance of the record being changed in the Fellow (F%) Category. In SQL I join the resultant temporary table (#da)

ON #da.membership_no = m.membership_no and #da.[Date Altered] = uh.date_altered

This will ensure that of all the amendments to a member's record, I only select the first instance as per the criteria of the temporary table. Is any of this making sense?

Thanks,
Des.

 

[lbass]

I don't know why you are introducing all of the other elements into the mix. If member is not in the same table as the date, then set it up the SQL expression {%mindt} like this exactly:

(
select min(date_altered)
from update_history A, member B
where A.table_key = B.member_ref and
B.membership_no = member.membership_no
)

This will return the earliest date per member. Add this to your selection formula as suggested earlier and the other selection criteria will limit which members appear, but this will limit the data to the earliest date per member.

SQL expressions are created in the field explorer->sql expression, and can only return one value (per report or per faux group), not a dataset.

-LB

 

[PWD]

Now that's confused me, as 'member' IS 'B'? The update_history table contains any number of date_altered lines, with any type of alteration so I need to find the first occurrence of an alteration to do with Fellowship. I don't necessarily need the date_altered data for my report, in fact (for a reason that I don't know) the following SQL code seems to give exactly the same data as the two-stage process and is exactly what I want.

	select 
						m.membership_no

						from update_history uh
						join member m on m.member_ref = uh.table_key
						join lookup l on uh.lookup_key = l.lookup_ref
						join hist.dbo.rosh_individual ri ON ri.individual_ref = m.individual_ref

						where upper(uh.table_altered) = 'MEMBER'
						and upper(uh.column_altered) = 'MEMBER_CLASS'
						and l.lookup_code like ('F%')
						and uh.date_altered < '2008-02-27'
						and ri.mem_stat_code = 'ACT'
						and ri.type_code = 'MEM' 

						group by m.membership_no

The trouble is that Crystal doesn't like that.

Subquery returned more than one value. This is not permitted when the subquery follows =, < etc. or when the subquery is used as an expression.

I tried to eliminate the line causing this error but without success.

The trouble is, I want to get data that is outside the selection criteria of the main report - which restricts the data to members who have received a 'Fellow Invite Letter' as the user wants a comparison of the two types of routes that we took to convert members to Fellows.

If it can't be done I need to know so I can just get on and try to produce a separate report for these earlier 'converts' to Fellowship.

Many thanks,

Des.

 

[lbass]

SQL expressions go directly to the database and are unaffected by the selection criteria for the report, so you have to build in any criteria that would affect the result per group. The A and B are alias table names used just within the expression to create a faux group and establish the link.

Please show some sample data that would illustrate which row you want to select. You can build a filter into the SQL expression like this:

(
select min(date_altered)
from update_history A, member B
where A.table_key = B.member_ref and
B.membership_no = member.membership_no and
upper(A.TABLE_ALTERED) = 'MEMBER'
and upper(A.COLUMN_ALTERED)= 'MEMBER_CLASS'
)

-LB

 

[PWD]

I've been working on approaching this another way now. I'll keep the main report selection criteria generic to Fellows, then create a formula for each sub-selection criterion. I'm going to try, anyway.

Des.

 

[PWD]

Sorry hadn't seen your latest post.

Des.

 

[PWD]

Have just tried your code & get,

 "The multi-part identifier "member.membership_no" could not be bound."

Des.

 

[lbass]

Please show the content of the expression exactly as you created it.

-LB

 

[PWD]

Hi, exactly as you'd posted.

 (
select min(date_altered)
from update_history A, member B 
where A.table_key = B.member_ref and
B.membership_no = member.membership_no and
upper(A.TABLE_ALTERED) = 'MEMBER'
and upper(A.COLUMN_ALTERED)= 'MEMBER_CLASS'
)

But to be honest, just selecting the earliest date wouldn't appear to do me a lot of good without a corresponding membership number. This version of the SQL code seems to give me what I want,

select 
m.membership_no

from update_history uh
join member m on m.member_ref = uh.table_key
join lookup l on uh.lookup_key = l.lookup_ref
join hist.dbo.rosh_individual ri ON ri.individual_ref = m.individual_ref

where upper(uh.table_altered) = 'MEMBER'
and upper(uh.column_altered) = 'MEMBER_CLASS'
and l.lookup_code like ('F%')
and uh.date_altered < '2008-02-27'
and ri.mem_stat_code = 'ACT'
and ri.type_code = 'MEM' 
group by m.membership_no
order by m.membership_no

When creating the Expression I can probably put in all the likely combinations of

 and l.lookup_code like ('F%')

but would still have problems with

 uh.date_altered < '2008-02-27'

Des.

 

[lbass]

If you got the sql expression working and limited the dates in the selection formula using the sql expression, there would be one date per membership number, so you would have your membership number. The membership number records would be further limited by your other selection criteria.

I'm not sure about the error message. Can you confirm that you are using CR XI (as in your title) and that "date_altered" is not present in both tables? You can't add the table name into the summary in CR XI (this was fixed in 2008 and also was not true before CR 9. You can, however, build the table name in if you are using a command. So you could build the same expression into your SQL query if you are using it in the command area, and there you could specify the alias name of the table within the summary:

(
select min(A.`date_altered)
from //etc.
) as mindt

-LB

 

[PWD]

Yes CR XI; date_altered is only in update_history. This Expression seems to save with no errors,

 (
Select min(uh.date_altered)
from update_history uh
join member  on member.member_ref = uh.table_key
where member.member_class in ('6564','1775')
and upper(uh.table_altered) = 'MEMBER'
and upper(uh.column_altered) = 'MEMBER_CLASS'


) AS MinDt

So how do I then get to use that in the Selection Criteria as I need to evaluate the earliest appropriate transaction for each member? I just can't see it working as it seems to just select the earliest value in the table.

Des.

 

[lbass]

You HAVE to add the faux group, so if it is accepting the above SQL expression {%mindt} (in the SQL expression area), try this:

(
select min(A.date_altered)
from update_history A, member B
where A.table_key = B.member_ref and
B.membership_no = member.membership_no and
B.member_class in ('6564','1775') and
upper(A.TABLE_ALTERED) = 'MEMBER' and
upper(A.COLUMN_ALTERED)= 'MEMBER_CLASS'
)

I'm surprised it is accepting the table name in the summary--maybe you have a service pack that corrected this issue. This will return the minimum date per member if you add to your selection formula:

{%mindt} = {uh.date_altered}

-LB