Max effect date of Self Join Table error

[John1Chr]

Hi all,

I cannot seem to get the max effect date formula to work on my self joined table.

My formula:

(SELECT MAX(EFFDT)
FROM TABLE1_1 TABLE1_12
WHERE TABLE1_12.CHRTFIELD_VALUE = TABLE1_1.CHRTFIELD_VALUE and
TABLE1_12.W_CF_ATTRBUTE = TABLE1_1.W_CF_ATTRBUTE)

Error Received is ODBC error:[Oracle][ODBC][ORA]ORA-00942:table or view does not exist.

I am using crystal 8.5.

 

[lbass]

It looks like Table1_1 is already an alias and SQL expressions access the database directly, so the alias does not exist there. Why not use something like the following (and I think in 8.5 you should specify the table within the summary):

(
SELECT MAX(A.EFFDT)
FROM TABLE1 A
WHERE A.CHRTFIELD_VALUE = TABLE1.CHRTFIELD_VALUE and
A.W_CF_ATTRBUTE = TABLE1.W_CF_ATTRBUTE
)

-LB

 

[John1Chr]

LB,

Yes, it is an alias and I am linking the _1 table to itself by CF_Value field. Are you suggesting that I put that formula in SQL expression fields and put that in the Record Selection formula for the _1 table? I'm a little confused on "specify the table within the summary." What I tried doing is putting the formula you gave me in the Sql expression field and then setting the _1 table to equal that formula in the record selection formula editor. It isn't working doing that. It eliminates the multiple effect date fields. I don't think I'm getting what you're saying.

 

[John1Chr]

I am linking to table 1 by CTValue and that CTValue has many CTAtrribs. I want to grab two of the CTAttribs Activity and Program. Like all of the other CTAtrrbs these have Effective dates and I want the Max Effective date.

I initially thought of selfjoining but not sure that is the best method. So for CT Value 29B3TCI I want Program B10 and Activty 1010. It had changes but I want those two latest results.

What I intially did was a self join in the visual linking expert by CTValue and was thinking of trying to do the formulas for both table 1 and table 1_1 that gets the max effect date for bothe the Program and Activities out of that table based on the CT Value.

 

[John1Chr]

CT Value CTAtrrib CTAtrrib Value Effect Date
29B3TCI Activity 1010 02-Jan-01
29B3TCI Activity 1010 26-Feb-12
29B3TCI Activity 3030 01-Jan-01
29B3TCI Program 10 02-Jan-01
29B3TCI Program 10 01-Jul-11
29B3TCI Program 30 01-Jan-01

I want these values
29B3TCI Activity 1010 26-Feb-12
29B3TCI Program 10 02-Jan-01

I am driving the report by the Program.

 

[lbass]

So is the key thing to return Activity and Program where the last two digits in the Attib Value match? Are you using a parameter for program or do you always want the "10" program/activity?

-LB

 

[John1Chr]

LB,

That is correct, the first two of the program will always equal the first two digits of the activity. I am using a parameter. In addition, I am including the CT Value.

 

[John1Chr]

I don't think I explained myself real well.

I thought of using this table, that has all the info that I want, and linking to itself using the program as a parameter and linking by the CT Value to get the activity. In addition, need to apply the latest effective date to the activity and program. Not sure if it is useful but the first two digits of the program is always the first two of the activity. I am trying to produce a report that will pull this way.

 

[lbass]

Use a record selection formula like this:

left({table.CTAttribValue},2)={?Parameter}

Then insert a group on CT Value. Create two formulas:

//{@actdate}:
if {table.CTAttrib}="Activity" then
{table.effdate}

//{@progdate}:
if {table.CTAttrib}="Program" then
{table.effdate}

Then go to report->edit selection formula->GROUP and enter:

{table.effdate}=maximum({@actdate},{table.CTValue}) or
{table.effdate}=maximum({@progdate},{table.CTValue})

-LB

 

[John1Chr]

Cool....seems to be grabbing what i need. I hate to throw another curveball but how hard is it to group by Program and then Activity with the CTVALUE as the Detail. What is going on is that many CTValues will be Within a Program and activity. Hierarchy for grouping is Program/Activiy/CTValue. I know I didn't explain this well.

 

[John1Chr]

LB,
Here’s the complete picture:
Thinking of linking to table 1 by CTattribvalue. Table0 has description of program and activity which I would like. Table0 does not have a max effect date and it doesn’t have a CTvalue. I would use Table0 as the driver to get the program and I am thinking of getting the activity from either table0 or table1. I need the activity description from Table0. Another complication is that table2 has the description of CTValue which I want. Table2 does have max effect date but I can get rid of that with a maxeffectdate in the edit selection formula using a formula. I will link CTValue to table two from Table1 to get the description of CTValue.
Table0
Activity
Program
Activity Desrcription
Program Description

Table1
Activity
Program
Max effect dt of Act Prog
CT Value

Table2
CTValue
CT Description
CT Max Effect Date

 

[John1Chr]

I ended up driving from Table1 to Table0. I think the report is pulling things correctly thanks to your suggestion LB. I linked Table2 to Table1.

I would like to format differently.

It is coming out like:

GF1.)Program
D.)CT Value

GF1.)Activity
D.)CT Value(same as 1.)

What I would like is

GF1.)Program
GF2.)Activity
D.CT Value - subset of Program/Related Activity(first two digits equal Program)

Somehow, I would like Program and Activity to be separate columns but show Activity as a subset of Program and CTVAlue as a subset of that.

 

[lbass]

Your display doesn't make sense to me GFs do NOT appear above detail sections--ever. GH's do.

-LB

 

[John1Chr]

Oops!...meant GH.