select the latest date 3

[stevesd]

I need to select the activity record with the latest record date. The table will have many records for each account, I just want the latest dated transaction. I have not been able to find a date function that reflects what I want to do

 

[synapsevampire]

So you just want one row from the database, or one row for a certain group of rows?

If you supply example data and expected output you might avoid iterative postings to ferret out requirements/solutions.

Try Grouping by Account, and by the activity date (or just sort by the Account).

Place the date field in the Account Group Footer and that will be the most recent date.

-k

 

[Naith]

Alternatively, you can group by Account, and apply a group selection criteria of Maximum({Date},{Account}) which will stop you bringing back the superfluous records in the first place.

All the best,

Naith

 

[inncoggnitto]

i am trying something similar - i need the max id from one of the data fields to only show the last record entered. i have tried the max function in the formula editor i get an error saying "this function cannot be used as it must be evaluated later"

(part of formula)
and {COMM.COMM_ID} = Maximum ({COMM.COMM_ID})

the data is in the details section. i tried using a group on the comm_id and putting a formula field in the group header Maximum ({COMM.COMM_ID}) but it still returns all of the records.

 

[lyanch]

You can do this with a subquery in your where statement. You have to manually add it to the where statement.

Something along the lines of:

mainquerytable.field = (select max(subquerytable.field) from subquerytable.field where subquerytable.linkfield = mainquerytable.linkfiedl)

Lisa

 

[inncoggnitto]

might be a dumb question, but how do i get the maintable linked field into equation. sql does not like ?Pm prop.Prop_id, with or without quotes.

 

[lyanch]

Do you have a SQL tool you can mess around with it in? It has to be just a syntax error. Remember to alias your subquerytable with a different alias than the main query if the same table is used in both.

Lisa

 

[inncoggnitto]

I use TOAD for my oracle tool (a pretty good applicatio). here is what i have that works except i can't figure how to get the parameter?Pm-RA.RA_ID) into the equation for the foreign key.

SELECT
PROP.PROP_VALUE, PROP.PROP_START_DATE
FROM
MUMADM.PROP PROP
WHERE
PROP.PROP_ID = (SELECT MAX(PROP.PROP_ID) FROM PROP WHERE
PROP.PROP_TYPE = 'APPLICATION STATUS' AND
PROP.FK_RA2_ID = 10111012)
ORDER BY
PROP.PROP_ID ASC

 

[lyanch]

Ah, you can't have the parameter in the subquery. You need to put in in the main query..

SELECT
PROP.PROP_VALUE, PROP.PROP_START_DATE
FROM
MUMADM.PROP PROP
WHERE
PROP.PROP_ID = (SELECT MAX(PROP2.PROP_ID) FROM PROP2 WHERE
PROP2.PROP_TYPE = 'APPLICATION STATUS' AND
PROP2.FK_RA2_ID = PROP.FK_RA2_ID)
AND PROP.FK_RA2_ID = {?parameter}
ORDER BY
PROP.PROP_ID ASC

Or something close to this.. You would add the PROP.FK_RA2_ID = {?parameter} in your normal selection formula.

Lisa


Lisa

 

[inncoggnitto]

thanks, i tweaked what you send and it worked, but boy is is sloooooo

http://wwwwww

to run. the select stmt in my oracle tool is fast and so is the report w/o the subreports, but together dog slow. still have to add a couple more subs too. whew.

 

[synapsevampire]

To optimize, try changing the subselect to a separate View or derived table, and join by the PROP.PROP_ID.

If you can, convert the SQL to a Stored Procedure.

I'm not sure what's in your subreports, but make sure that you're only returning the rows required rather than having CR do some of the processing, this is the most common reason for bad performance.

In general, subreports are slow, you may be able to figure out a way to bring back the full data set in one query (main and subreport data).

-k

 

[Naith]

If your query is quick in TOAD, then it sounds like what the database is doing is fine already.

It sounds to me like your subreports are placed in recurring sections in your report, causing them to be executed often, and thus hitting the database quite a few times. Where exactly are these subreports placed? And seeing as you already have a subquery which works, what are you using them for?

Naith

 

[inncoggnitto]

essentially this is a db for tracking information about environmental permits (state agency) and i am building a report to extract information. to do so will require several subqueries. this one in particular is meant to extract the status of the permit. the assumption being that the highest id # is the last entry. will also have other subreports that get the last communication (correspondence), one for contacts (names) w/phone #, one for owner/operator info (addr, phone), and one for info about the location. it is an all in one report for internal use and for requests from the general public and regualted community. we get requests for all of one county, city, region, etc. as of now there are only 7,000+ of these particular types of permits, but a similar permit with 20,000 or more is just getting started.

i do notice that in the bottom right of the cr window i get an initial count of 8 (using a county w/small # of permits) and then is sequences through all 7,000+ for each of the subqueries. the link to the subreport is a simple id from a parent table to a foreign key in the child.

 

[Naith]

The SQL generated by your subreports is not passing to the database. Crystal is calling all the records which meet the basic SQL passed from the subreports and then separating the wheat from the chaff once the database returns all the relevant - and irrelevant - rows.

Check out your selection criteria from your subreports and then compare it to the SQL they generate, from Database/Show SQL. Try to get the SQL to explicitly do what the selection criteria specifies as much as possible.

If you're having trouble doing this, post your SQL and your selection criteria, and someone around here will try to put you on track.

Naith

 

[inncoggnitto]

ok, here is the cr sql that is generated by the main and subreport and the link between them. it does go through all 7802 records each time sits for about 20 seconds, then moves on to the next one

this is the main report sql
SELECT
"LOC"."LOC_NAME", "LOC"."ACCESS_DESC_TXT", "RA"."RA_ID", "RA"."BUSINESS_PROCESS", "RA"."PROG_ID",
"ADDR"."ADDR_TYP_TXT", "ADDR"."CITY_NAME", "ADDR"."STREET_NUM", "ADDR"."PRE_DIR", "ADDR"."ST_NAME_TXT",
"ADDR"."ST_TYP", "ADDR"."POST_DIR", "ADDR"."ZIP_CD", "ADDR"."ZIP_CD_EXT", "ADDR"."TNRCC_REGION_CD",
"FEA"."FEA_START_DATE", "LU_COUNTY"."COUNTY_NAME"
FROM
"MUMADM"."LOC" "LOC",
"MUMADM"."LOC_ADDR" "LOC_ADDR",
"MUMADM"."LOC_RA_FEA" "LOC_RA_FEA",
"MUMADM"."RA" "RA",
"MUMADM"."ADDR" "ADDR",
"MUMADM"."FEA" "FEA",
"MUMADM"."LU_COUNTY" "LU_COUNTY"
WHERE
"LOC"."LOC_GIN" = "LOC_ADDR"."FK_LOCLOC_GIN" AND
"LOC"."LOC_GIN" = "LOC_RA_FEA"."FK_LOC1_GIN" AND
"LOC_RA_FEA"."FK_RA1_ID" = "RA"."RA_ID" AND
"LOC_ADDR"."FK_ADDRADDR_ID" = "ADDR"."ADDR_ID" AND
"LOC_RA_FEA"."FK_FEA2_ID" = "FEA"."FEA_ID" AND
"ADDR"."CNTY_CD" = "LU_COUNTY"."COUNTY_CODE"(+) AND
"ADDR"."ADDR_TYP_TXT" = 'PHYSICAL' AND
"RA"."BUSINESS_PROCESS" = 'STORM WATER DISCHARGE' AND
"LU_COUNTY"."COUNTY_NAME" = 'MAVERICK'
ORDER BY
"RA"."PROG_ID" ASC,
"ADDR"."TNRCC_REGION_CD" ASC

this is the subreport sql meant to return the max id, which it does - the prop table links via the fk_ra_id up to the ra table id
SELECT
"PROP"."PROP_ID", "PROP"."PROP_VALUE", "PROP"."FK_RA2_ID"
FROM
MUMADM.PROP PROP
WHERE
PROP.PROP_ID = (SELECT MAX(PROP2.PROP_ID) FROM PROP PROP2 WHERE PROP2.PROP_TYPE = 'APPLICATION STATUS' AND
PROP2.FK_RA2_ID = PROP.FK_RA2_ID)

This is the link from main report to subreport
{PROP.FK_RA2_ID} = {?Pm-RA.RA_ID}

 

[lyanch]

This is the link from main report to subreport
{PROP.FK_RA2_ID} = {?Pm-RA.RA_ID}

This parameter is not being sent to the DB. Try viewing just the subreport and checking to see if the parameter is being sent.

You might want to remove the link.. save the link change.. reopen the link manager and relinking.

If that doesn't work, you might want to remove the subquery (temporarily) add and remove the link as a bove... check the SQL to make sure the parameter is being passed, then adding the subquery back in again.

Lisa

 

[inncoggnitto]

tried your suggestion of deleting and resetting the link. the sql is hard coded because i need the subselect to find the max id. the parameter does not show up in the sql, but it appears in the select expert of the subreport as {PROP.FK_RA2_ID} = {?Pm-RA.RA_ID}

when i view the sql after running the report the sql statement is as i entered it w/o the parameter inserted in it (i.e., 10075152 is one of the ids for the fk).

when i run the sql in toad it is almost instantaneous for both the subselect by itself and the whole sql.

 

[lyanch]

You need to get Crystal to start inserting the parameter in the subreport.

Sometimes.. you need to remove the hard coded SQL.. then recreate the link.. then re add your hard code to ge Crystal to "get it right" and send the parameter in the SQL. I know you can use hard code in subreports (because I use it).

Lisa