Jump to content
We've recently updated our Privacy Statement, available here ×

dianaj59

Members
  • 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

  1. 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. 

     

  2. 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  
  3. 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.

  4. 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
  5. 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.
  6. 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
  7. 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
  8. 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.

×
×
  • Create New...