mariarita Posted February 24, 2012 Share Posted February 24, 2012 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 GIUDITTAche nell'anno 2002 è stato sottoposto ai seguenti interventi: PRESTAZIONE NON SPECIFICATAwhere: 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 GIUDITTAche nell'anno 2002 è stato sottoposto ai seguenti interventi: PRESTAZIONE NON SPECIFICATA, IGIENE PERSONALE; ASSISTENZAIs it possible?I hope I was clear Thanks Mariarita Link to comment Share on other sites More sharing options...
cbarlow3 Posted February 24, 2012 Share Posted February 24, 2012 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.SERIALI get a result like this:FIRST_NAME LAST_NAME DESCRIPTIONDante Alighieri poeticDante Alighieri romanticMichelangelo Merisi artisticMichelangelo Merisi violentMichelangelo Merisi realistic ...But want to get back this instead...FIRST_NAME LAST_NAME DESCRIPTION_LISTDante Alighieri poetic,romanticMichelangelo Merisi artistic,violent,realisticI'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 Link to comment Share on other sites More sharing options...
mariarita Posted February 24, 2012 Author Share Posted February 24, 2012 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_INDIRIZZOFROM "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" = 2order 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 Link to comment Share on other sites More sharing options...
cbarlow3 Posted February 24, 2012 Share Posted February 24, 2012 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.CarlP.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=2Post Edited by cbarlow3 at 02/24/2012 17:50 Link to comment Share on other sites More sharing options...
mariarita Posted February 27, 2012 Author Share Posted February 27, 2012 Thanks very much,but whith this query I've this problem: SQL problems:ORA-32033: alias delle colonne non supportatiMessage: net.sf.jasperreports.engine.JRException: SQL problems:ORA-32033: alias delle colonne non supportatiLevel: SEVEREStack 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? Link to comment Share on other sites More sharing options...
cbarlow3 Posted February 27, 2012 Share Posted February 27, 2012 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.CarlCode: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 Link to comment Share on other sites More sharing options...
mariarita Posted February 27, 2012 Author Share Posted February 27, 2012 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 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now