dianaj59

Okay...I see how it is...Your subselect works fine. THANKS.

Here is my current FROM statement: FROM /* return all REPORT_LIST and (corresponding REPORT_LIST_CLIENT records or null)*/ "IR"."REPORT_LIST" REPORT_LIST LEFT OUTER JOIN "IR"."REPORT_LIST_CLIENT" REPORT_LIST_CLIENT ON REPORT_LIST."REPORT_LIST_ID" = REPORT_LIST_CLIENT."REPORT_LIST_ID" /* return any CLIENT record that correspond to above REPORT_LIST_CLIENT (or null)*/ LEFT OUTER JOIN "DM_OWNER"."CLIENT" CLIENT ON REPORT_LIST_CLIENT."CLIENT_ID" = CLIENT."CLIENT_ID" /* return any IMMUN_FACT records that correspond to above CLIENT_DEMOGRAPHIC (or null)*/ LEFT OUTER JOIN "DM"."IMMUN" IMMUN ON CLIENT."CLIENT_ID" = IMMUN."CLIENT_ID" /* return any VACCINE_A records that correspond to above IMMUN_FACT (or null)*/ LEFT OUTER JOIN "DM"."VACCINE" VACCINE_A ON IMMUN."VACCINE_ID" = VACCINE_A."VACCINE_ID" Where do I place: left outer join ( select max(IMMUN."VACCINATION_DATE") from DM.IMMUN group by client_id ) maxImmun on (IMMUN."CLIENT_ID" = CLIENT."CLIENT_ID") I tried placing it at the end of the FROM statement, but the query ran on for at least 15 minutes, so I killed it.

I have an sql statement I will post below. It returns: JohnDoe1 JohnDoe2 09/27/2010 JohnDoe2 JohnDoe3 JohnDoe4 JohnDoe5 JohnDoe6 09/30/2010 JohnDoe6 JohnDoe7 Where the NAME is duplicated, I only want to see the name WITH a date. However, I DO still want the names with NO date that are NOT duplicates. I've looked at this too long, I can not see how to do it. Thanks. Code:SELECT DISTINCT CLIENT_DEMOGRAPHIC."FIRST_NAME"||' '||CLIENT_DEMOGRAPHIC."LAST_NAME" AS NAME, case WHEN VACCINE_ID IN (21,23,79,127,130) AND IMMUN."VACCINATION_DATE" BETWEEN to_date('2010/08/01','yyyy/mm/dd') AND to_date('2010/10/20','yyyy/mm/dd') THEN max(IMMUN."VACCINATION_DATE") else Null end AS MaxDate FROM "IR"."REPORT_LIST" REPORT_LIST LEFT OUTER JOIN "IR"."REPORT_LIST_CLIENT" REPORT_LIST_CLIENT ON REPORT_LIST."REPORT_LIST_ID" = REPORT_LIST_CLIENT."REPORT_LIST_ID" LEFT OUTER JOIN "DM"."CLIENT_DEMOGRAPHIC" CLIENT_DEMOGRAPHIC ON REPORT_LIST_CLIENT."CLIENT_ID" = CLIENT_DEMOGRAPHIC."CLIENT_ID" LEFT OUTER JOIN "DM"."IMMUN_FACT" IMMUN ON CLIENT_DEMOGRAPHIC."CLIENT_ID" = IMMUN."CLIENT_ID" LEFT OUTER JOIN "DM"."VACCINE" VACCINE_A ON IMMUN."VACCINE_ID" = VACCINE_A."VACCINE_ID" WHERE REPORT_LIST_NAME = 'mdhIt' GROUP BY CLIENT_DEMOGRAPHIC."FIRST_NAME"||' '||CLIENT_DEMOGRAPHIC."LAST_NAME", IMMUN."VACCINATION_DATE", VACCINE_ID ORDER BY Name,MaxDate

I have this problem as well. For instance if I want the report to show 33%, how can I use the text field property pattern box to do this? If I leave the % in, it reflects as 3300%; if I take it out and leave the box blank, it shows as 33. I want it to be 33%. Is this possible? I think there must be a better way than to leave it 33 and add a text box with a % symbol in it. Thanks.

Yep, and it would not accept MaxDate. I had to use max(IMMUN_FACT."VACCINATION_DATE") in the CASE statement. It is working in PLsql, I am sure I can get it to work in iReports now....Thanks.

WE GOT IT.... This is what was needed. See code below: THANK YOU...so much for the guidance! Code:SELECT CLIENT_DEMOGRAPHIC."FIRST_NAME"||' '||CLIENT_DEMOGRAPHIC."LAST_NAME" AS NAME, max(IMMUN_FACT."VACCINATION_DATE") AS MaxDate, case WHEN max(IMMUN_FACT."VACCINATION_DATE") BETWEEN to_date('2010/08/01','yyyy/mm/dd')AND to_date('2010/10/20','yyyy/mm/dd') THEN 1 else 0 end AS inDateRange

My sql statement works without the case statement. It brings back a column of names and maximum dates. I am using PLSQL to test the sql statements outside of iReports. When I put the "case" piece into the select statement I get an error of... "ORA-00904:"MAXDATE": Invalid Identifier It seems to want a string instead of a data. Please look at the SELECT statement below and see if that is what you'd expect it to look like. I have the dates in there for PLSQL instead of the iReport field names, but either way it works without the case statement. Code:SELECT CLIENT_DEMOGRAPHIC."FIRST_NAME"||' '||CLIENT_DEMOGRAPHIC."LAST_NAME" AS NAME, max(IMMUN_FACT."VACCINATION_DATE") AS MaxDate case WHEN MaxDate BETWEEN to_date('2010/08/01','yyyy/mm/dd')AND to_date('2010/10/20','yyyy/mm/dd') THEN 1 else 0 end AS inDateRangeFROM.....ETC.

ALSO... The code below is what I tried and got the "FROM keyword not where expected" error. I also tried to put a comma after max(IMMUN_FACT."VACCINATION_DATE") AS MaxDate and then got an error of "SQL not properly ended. My sql knowledge has been very basic to this point. Thanks... Code:SELECT CLIENT_DEMOGRAPHIC."FIRST_NAME"||' '||CLIENT_DEMOGRAPHIC."LAST_NAME" AS NAME, max(IMMUN_FACT."VACCINATION_DATE") AS MaxDate case WHEN maxDate BETWEEN $P{DATE_START} AND $P{DATE_END} THEN 1 else 0 end AS inDateRange FROM "IR_DM_OWNER"."IMMUN_FACT" as tFROM "MASTER"."SCHOOL_REPORT_LIST" SCHOOL_REPORT_LIST LEFT OUTER JOIN "MASTER"."SCHOOL_REPORT_LIST_CLIENT" SCHOOL_REPORT_LIST_CLIENT ON SCHOOL_REPORT_LIST."SCHOOL_REPORT_LIST_ID" = SCHOOL_REPORT_LIST_CLIENT."SCHOOL_REPORT_LIST_ID" LEFT OUTER JOIN "DM"."CLIENT_DEMOGRAPHIC" CLIENT_DEMOGRAPHIC ON SCHOOL_REPORT_LIST_CLIENT."CLIENT_ID" = CLIENT_DEMOGRAPHIC."CLIENT_ID" LEFT OUTER JOIN "DM"."IMMUN_FACT" IMMUN_FACT ON CLIENT_DEMOGRAPHIC."CLIENT_ID" = IMMUN_FACT."CLIENT_ID" LEFT OUTER JOIN "DM"."VACCINE" VACCINE_A ON IMMUN_FACT."VACCINE_ID" = VACCINE_A."VACCINE_ID"WHERE SCHOOL_REPORT_LIST_NAME = 'schoolNameTest' AND VACCINE_ID IN (21,23,79,127,130)GROUP BY CLIENT_DEMOGRAPHIC."FIRST_NAME"||' '||CLIENT_DEMOGRAPHIC."LAST_NAME"ORDER BY Name,MaxDate

Thank you. You understand exactly what I need. I was on the right track, however I can not seem to see where the "case" piece fits into my sql. I'm attaching my sql statement. If I put it in the select statement with it's own FROM, I get this error "FROM keyword not found where expected". Where should this case piece be placed given my sql as it is right now? Code:SELECT CLIENT_DEMOGRAPHIC."FIRST_NAME"||' '||CLIENT_DEMOGRAPHIC."LAST_NAME" AS NAME, max(IMMUN_FACT."VACCINATION_DATE") AS MaxDateFROM "MASTER"."SCHOOL_REPORT_LIST" SCHOOL_REPORT_LIST LEFT OUTER JOIN "MASTER"."SCHOOL_REPORT_LIST_CLIENT" SCHOOL_REPORT_LIST_CLIENT ON SCHOOL_REPORT_LIST."SCHOOL_REPORT_LIST_ID" = SCHOOL_REPORT_LIST_CLIENT."SCHOOL_REPORT_LIST_ID" LEFT OUTER JOIN "DM"."CLIENT_DEMOGRAPHIC" CLIENT_DEMOGRAPHIC ON SCHOOL_REPORT_LIST_CLIENT."CLIENT_ID" = CLIENT_DEMOGRAPHIC."CLIENT_ID" LEFT OUTER JOIN "DM"."IMMUN_FACT" IMMUN_FACT ON CLIENT_DEMOGRAPHIC."CLIENT_ID" = IMMUN_FACT."CLIENT_ID" LEFT OUTER JOIN "DM"."VACCINE" VACCINE_A ON IMMUN_FACT."VACCINE_ID" = VACCINE_A."VACCINE_ID"WHERE SCHOOL_REPORT_LIST_NAME = 'schoolNameTest' AND VACCINE_ID IN (21,23,79,127,130)GROUP BY CLIENT_DEMOGRAPHIC."FIRST_NAME"||' '||CLIENT_DEMOGRAPHIC."LAST_NAME"ORDER BY Name,MaxDate

I am using iReports 3.0.0 and jasperReports 3.1.2. I need to count all the records for the report as well as count a selected number of records. I am able to use the REPORT_COUNT variable for the count of all the records, no problem. However, I don't know how to use this variable to find the records between Date_Start and Date_End. My report looks like: NAME maxDATE jon doe 07/01/2010 jane doe 09/01/2010 The variable REPORT_COUNT will count the Names just fine and displays the total count(int) in the header as desired. I want to county only the dates between say, 08/01/2010 and 10/01/2010 and display this count(int) in the header as well. I can't seem to find how to do this in the sql statement OR the Report_count variable. Thanks for any solutions.