Report Formatting-record in single row.

[Former Member]

Hi,

I want to display records as below (in one line) in crystal report by fatching records from below two tables

SAMPLE_ID            ENT_ON                      NON_COMPLIANCE
-------------------- ----------------------- ----------- --------------
140                  2009-08-27 12:36:43.000            F

-When any records got value other then NULL in Non_compliance column, it should display "F" (program is done to display "F".

when I run the report it displays as below:

SAMPLE_ID            ENT_ON                  NON_COMPLIANCE
-------------------- ----------------------- ----------- --------------
140                  2009-08-27 12:36:43.000 F
140                  2009-08-27 12:36:43.000 NULL
140                  2009-08-27 12:36:43.000 NULL
140                  2009-08-27 12:36:43.000 F
140                  2009-08-27 12:36:43.000 F
140                  2009-08-27 12:36:43.000 NULL

Table-A

SAMPLE_ID            ENT_ON
-------------------- -----------------------
140                   009-08-27 12:36:43.000

Table-B

SAMPLE_ID            PA_N        ST_N        COMPLIANCE  NON_COMPLIANCE
-------------------- ----------- ----------- ----------- --------------
140                  1           39          0           14
140                  2           39          NULL        NULL
140                  3           39          NULL        NULL
140                  4           39          1           6
140                  5           39          1           9
140                  6           39          NULL        NULL

However, when the report is execurited it shows records multiple time. Is there any way to display one record in one row where any of the record match to sample_id from table-b?

SELECT "table-a"."SAMPLE_ID", "table-a"."ENT_ON", "table-b"."NON_COMPLIANCE"
 FROM   "DB"."dbo"."table-a" "table-a" 
		INNER JOIN "DB"."dbo"."table-b" "table-b" 
		ON "table-a"."SAMPLE_ID"="table-b"."SAMPLE_ID"
 WHERE  ("table-a"."ENT_ON">={ts '2009-08-01 00:00:00'} AND "table-a"."ENT_ON"<{ts '2009-09-30 00:00:00'})
		 AND "table-a"."SAMPLE_ID"='140'

<MOVED BY MODERATOR TO THE CORRECT FORUM>

Moderator message - But cross post locked

Edited by: Alvaro Tejada Galindo on Oct 7, 2009 2:53 PM

Edited by: Rob Burbank on Oct 7, 2009 3:00 PM

[Flavio]

Hi OMIC,

I just tried with the following query (in MySQL, so you should adjust it to fit your environment), that proved to be successful:

SELECT DISTINCT `table-a`.`SAMPLE_ID`, `table-a`.`ENT_ON`, @myVar:='F' AS NON_COMPLIANCE
 FROM   `table-a`
    INNER JOIN `table-b`
    ON `table-a`.`SAMPLE_ID`=`table-b`.`SAMPLE_ID`
 WHERE  (`table-a`.`ENT_ON`>={ts '2009-08-01 00:00:00'} 
     AND `table-a`.`ENT_ON`<{ts '2009-09-30 00:00:00'}) AND `table-a`.`SAMPLE_ID`='140' 
     AND `table-b`.`NON_COMPLIANCE` IS NOT NULL

This is the result:

SAMPLE_ID   ENT_ON                   NON_COMPLIANCE  
140         2009-08-27 12:36:43.000  F

Hope this could help.

Flavio