dianaj59
-
Posts
10 -
Joined
-
Last visited
Content Type
Profiles
Forum
Events
Featured Visualizations
Knowledge Base
Documentation (PDF Downloads)
Blog
Documentation (Test Area)
Documentation
Dr. Jaspersoft Webinar Series
Downloads
Posts posted by dianaj59
-
-
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.
sql help
in Products
Posted
Okay...I see how it is...Your subselect works fine.
THANKS.