List of field

[mariarita]

HI, I've created with iReport a letter with some fields of a query:

This is the result:

 

Io sottoscritto  SCIBETTA IGNAZIA convoco l'assistito RITORTO MARIA GIUDITTA
che nell'anno 2002 è stato sottoposto ai seguenti interventi: PRESTAZIONE NON SPECIFICATA

where: SCIBETTA IGNAZIA, RITORTO MARIA GIUDITTA, 2002 and PRESTAZIONE NON SPECIFICATA are fields of some table.

The problem is that the field PRE_DESCR (the result into the result is PRESTAZIONE NON SPECIFICATA) assumes different values ​​in the table of database and I want see all valus as a list. For example:

Io sottoscritto  SCIBETTA IGNAZIA convoco l'assistito RITORTO MARIA GIUDITTA
che nell'anno 2002 è stato sottoposto ai seguenti interventi: PRESTAZIONE NON SPECIFICATA, IGIENE PERSONALE; ASSISTENZA

Is it possible?

I hope I was clear

 

Thanks

 

Mariarita

 

[cbarlow3]

Well, I don't know the exact tables and fields in question, but I believe there are one or two ways to reach your goal:  I just read yesterday something about a way to use a List element and have the list display results horizontally.  I don't know whether they would be comma separated, and I don't know that they would look very natural in the middle of a letter.  The method with which I'm more familiar is putting the list together directly in the SQL query itself.  This solution comes from section 6.10 of Anthony Molinaro's SQL Cookbook, published by O'Reilly.  That section is titled "Creating a Delimited List from Table Rows".  He lists different solutions, depending on whethe ryou are using DB2, MySQL, Oracle, PostgreSQL, or SQL Server.  I'm most familiar with DB2, so I've listed a DB2 version.  I'll assume for this example that you have a table called PERSON with a primary key called SERIAL (a system-assigned sequence number) and also fields FIRST_NAME and LAST_NAME, and you're going to output a letter for each PERSON record that meets certain criteria.  I'll also assume that you have a table called PERSON_ATTRIBUTE, which has primary key SERIAL, foreign key PARENT_SERIAL (pointing to a PERSON record), and DESCRIPTION, which might have values like "good hygiene", "conscientious", "punctual", "experienced", etc.  Of course, I don't know if you have a finite list of these that you keep in one table and then have another linking table that lets you represent many-to-many relationships without duplicating the attribute descriptions, but the example is complicated enough without me making complicated assumptions about how you are storing the data.

So...if I do a query like this:

SELECT PERSON.FIRST_NAME, PERSON.LAST_NAME, ATTRIBUTE.DESCRIPTION FROM
  PERSON INNER JOIN ATTRIBUTE ON ATTRIBUTE.PARENT_SERIAL=PERSON.SERIAL

I get a result like this:

FIRST_NAME      LAST_NAME      DESCRIPTION
Dante           Alighieri      poetic
Dante           Alighieri      romantic
Michelangelo    Merisi         artistic
Michelangelo    Merisi         violent
Michelangelo    Merisi         realistic

...

But want to get back this instead...

FIRST_NAME      LAST_NAME      DESCRIPTION_LIST
Dante           Alighieri      poetic,romantic
Michelangelo    Merisi         artistic,violent,realistic

I've pasted the DB2 version of such a SQL query below.  If you are using a different version of SQL, let me know and I'll attempt to convert my example to use one of the other techniques in this section of the book, which I highly recommend, by the way.

Hope that helps.

Carl

 

Code:

WITH  x (PersonSerial, AttributeCount, DescriptionList, AttributeSerial, ListLength) AS (SELECT  ATTRIBUTE.PARENT_SERIAL,  COUNT(*) OVER (PARTITION BY ATTRIBUTE.PARENT_SERIAL),  CAST(ATTRIBUTE.DESCRIPTION AS VARCHAR(500)),  ATTRIBUTE.SERIAL,  1FROM  ATTRIBUTEUNION ALLSELECT  x.PersonSerial,  x.AttributeCount,  x.DescriptionList || ',' || ATTRIBUTE.DESCRIPTION,  ATTRIBUTE.SERIAL,  x.ListLength+1FROM  ATTRIBUTE, xWHERE  ATTRIBUTE.PARENT_SERIAL=x.PersonSerial AND  ATTRIBUTE.SERIAL>x.AttributeSerial)SELECT  PERSON.FIRST_NAME,  PERSON.LAST_NAME,  x.DescriptionList AS SHARE_LISTFROM  PERSON INNER JOIN  x ON x.PersonSerial=PERSON.SERIALWHERE  ListLength=AttributeCount

[mariarita]

Thanks for the answer but I do not understand how to rewrite the query.

 

My query is :

SELECT distinct
     CARTELLA."COGNOME" AS CARTELLA_COGNOME,
     CARTELLA."NOME" AS CARTELLA_NOME,
     INTPRE."PRE_ANNO" AS INTPRE_PRE_ANNO,
     INTPRE."PRE_DES_PREST" AS INTPRE_PRE_DES_PREST,
     OPERATORI."COGNOME" AS OPERATORI_COGNOME,
     OPERATORI."NOME" AS OPERATORI_NOME,
     OPERATORI."INDIRIZZO" AS OPERATORI_INDIRIZZO
FROM
     "SINSNT_TEST"."INTPRE" INTPRE INNER JOIN "SINSNT_TEST"."CARTELLA" CARTELLA ON INTPRE."PRE_CARTELLA" = CARTELLA."N_CARTELLA"
     INNER JOIN "SINSNT_TEST"."OPERATORI" OPERATORI ON CARTELLA."COD_OPERATORE" = OPERATORI."CODICE"

where CARTELLA."N_CARTELLA" = 2

order by INTPRE."PRE_DES_PREST"

 

where CARTELLA."COGNOME", CARTELLA."NOME", INTPRE."PRE_ANNO", OPERATORI."COGNOME", OPERATORI."NOME", OPERATORI."INDIRIZZO" assume the same value and INTPRE."PRE_DES_PREST" assume different values.

 

How do I change the query?

Thanks very much

[cbarlow3]

Which version of SQL are you using?  As near as I can make it out, it sounds like OPERATORI is a parent record of CARTELLA, which is a parent record of INTPRE.  For a specific CARTELLA (where N_CARTELLA=2), it is only associated with a single OPERATORI record, but is also associated with one or more INTPRE records, correct?  I only know enough Italian to find my way around train stations and restaurants, so as soon as your table and field names are abbreviated, it all becomes completely theoretical for me! :)   If I am correct so far, then I want a single result row for that CARTELLA, and I want it to list the various values of INTPRE.PRE_DES_PREST in a single text field.  I don't see how I can also get back INTPRE.PRE_ANNO, since there would be more than one of these also.  For now I will give you a solution that does not return PRE_ANNO until I understand how you want that handled.  If you actually want a result row for each distinct value of PRE_ANNO, that's possible, but I'll start with a version that just omits PRE_ANNO.  The code below is the same DB2 solution as before, but this time with your table and column names.

Carl

P.S.: At least in DB2, recursive common table expressions (CTE--the "WITH" clause) have lots of restrictions, like not being able to do an INNER JOIN or ORDER BY in the recursive half of the UNION ALL, for example.  That's why I've used a Cartesian JOIN and a WHERE in that second half.  The sorting of the PRE_DES_PREST is handled for you automatically by the ">" comparison in the WHERE clause of that second half of the CTE.  Essentially, the first half lists all values of PRE_DES_PREST (and gets a count of how many there are per CARTELLA), while the second half first lists all pairs of PRE_DES_PREST where the second value is greater than the first value, then lists all triplets of PRE_DES_PREST where the third value is greater than the second value, etc.  In the end, only the rows that have the same number of values in the "List" as the total number calculated for that CARTELLA in the non-recursive first half of the CTE are selected.  These will be rows where the lowest value of PRE_DES_PREST for each CARTELLA is listed first, followed by the next lowest value, etc. all the way to the hightest value.

Code:

WITH  x (PreCartella, IntPreCount, PreDesPrestList, PreDesPrest, ListLength) AS (SELECT  INTPRE.PRE_CARTELLA,  COUNT(*) OVER (PARTITION BY INTPRE.PRE_CARTELLA),  CAST(INTPRE.PRE_DES_PREST AS VARCHAR(500)),  INTPRE.PRE_DES_PREST,  1FROM  SINSNT_TEST.INTPRE INTPREUNION ALLSELECT  x.NCartella,  x.IntPreCount,  x.PreDesList || ',' || INTPRE.PRE_DES_PREST,  INTPRE.PRE_DES_PREST,  x.ListLength+1FROM  SINSNT_TEST.INTPRE INTPRE, xWHERE  INTPRE.PRE_CARTELLA=x.PreCartella AND  INTPRE.PRE_DES_PREST>x.PreDesPrest)SELECT  CARTELLA.COGNOME AS CARTELLA_COGNOME,  CARTELLA.NOME AS CARTELLA_NOME,  x.PreDesPrestList AS PRE_DES_PREST_LIST,  OPERATORI.COGNOME AS OPERATORI_COGNOME,  OPERATORI.NOME AS OPERATORI_NOME,  OPERATORI.INDIRIZZO AS OPERATORI_INDIRIZZOFROM  SINSNT_TEST.CARTELLA CARTELLA INNER JOIN  x ON x.PreCartella=CARTELLA.N_CARTELLA INNER JOIN  SINSNT_TEST.OPERATORI OPERATORI ON     CARTELLA.COD_OPERATORE=OPERATORI.CODICEWHERE  ListLength=AttributeCount AND  CARTELLA.N_CARTELLA=2

Post Edited by cbarlow3 at 02/24/2012 17:50

[mariarita]

Thanks very much,

but whith this query I've this problem:

 

SQL problems:
ORA-32033: alias delle colonne non supportati

Message:
    net.sf.jasperreports.engine.JRException: SQL problems:
ORA-32033: alias delle colonne non supportati
Level:
    SEVERE
Stack Trace:
SQL problems: ORA-32033: alias delle colonne non supportati
    com.jaspersoft.ireport.designer.data.fieldsproviders.SQLFieldsProvider.getFields(SQLFieldsProvider.java:435)
    com.jaspersoft.ireport.designer.connection.JDBCConnection.readFields(JDBCConnection.java:470)
    com.jaspersoft.ireport.designer.wizards.ConnectionSelectionWizardPanel.validate(ConnectionSelectionWizardPanel.java:146)
    org.openide.WizardDescriptor$7.run(WizardDescriptor.java:1357)
    org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:572)
    org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:997)

 

Why?

 

[cbarlow3]

Based on your error message, I assume that you are using Oracle rather than DB2, and Oracle doesn't yet support the WITH clause I used in my example.  SQL Cookbook has an Oracle solution to the problem as well, but it uses a SYS_CONNECT_BY_PATH function, a START WITH clause, and a CONNECT BY clause, none of which I'm familiar with, and of course since I'm not running Oracle here, I couldn't test any of it to be sure I understood how to change the author's example to meet your needs.  I have pasted his actual example query below (except I've changed the table and field names to unabbreviated versions so it is easier to tell what the example is about), but he also has a page and a half of explanation, which I won't copy for copyright and laziness reasons. :)

I hope studying the Oracle example he gives helps get you to the next stage where you can use a similar technique in your own query.  Code is pasted below.

Carl

Code:

select deptartmentNumber,       ltrim(sys_connect_by_path(employeeName,','),',') employees from (select departmentNumber,       employeeName,       row_number() over                (partition by departmentNumber order by employeeNumber) rowNumber,       count(*) over                (partition by departmentNumber) counter       from employee            )       where level = counter       start with rowNumber = 1      connect by prior departmentNumber = departmentNumber and prior rowNumber = rowNumber - 1

[mariarita]

I could get the desired result even with a sub report, but in iReport 4.5.0 I can not find the button for subreport.

I saw examples of this, but used iReport 3.0.0