Archives of the TeradataForum

Message Posted: Fri, 28 Mar 2003 @ 16:35:34 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I'm very, very confuse about the understanding of derive table!!!

Does anybody can help!! Thank you!!

I'm trying to show you somes steps to explain my problem :

Number of record for table T1 for the period between 1998-01-01 and 1998-01-02, answer is good.

SELECT COUNT(*)
FROM   DB.T1
WHERE  DAT_SERV BETWEEN '1998-01-01' AND '1998-01-02'
;
   Count(*)
-----------
     122736

Request to obtain the detail of T1. Answer is good.

SELECT COUNT(*)

FROM  DB.T1 DEM

LEFT JOIN DB.T2 DETL ON
          DETL.NO_INDIV_BEN = DEM.NO_INDIV_BEN AND
          DETL.NO_RAMQ_DEM_MED = DEM.NO_RAMQ_DEM_MED

WHERE  DEM.DAT_SERV BETWEEN '1998-01-01' AND '1998-01-02'
;

   Count(*)
-----------
     122736

!!!!!!!!!!!!!!!!!!!!
I try the request with derive table but the result is not good!!!
!!!!!!!!!!!!!!!!!!!!

SELECT COUNT(*)

FROM (SELECT DEM.NO_INDIV_BEN,DEM.NO_RAMQ_DEM_MED
      FROM   DB.T1 DEM
      WHERE  DEM.DAT_SERV BETWEEN '1998-01-01' AND '1998-01-02') TD_DEM

LEFT JOIN DB.T2 DETL ON
          DETL.NO_INDIV_BEN = TD_DEM.NO_INDIV_BEN AND
          DETL.NO_RAMQ_DEM_MED = TD_DEM.NO_RAMQ_DEM_MED
;
   Count(*)
-----------
      40726

The number of row extract with the derive table is really 122736 :

SELECT COUNT(*)

FROM (SELECT DEM.NO_INDIV_BEN,DEM.NO_RAMQ_DEM_MED
      FROM   DONNE.TDEM_PAIMT_MED DEM
      WHERE  DEM.DAT_SERV BETWEEN '1998-01-01' AND '1998-01-02') TD_DEM
;

         *** Query completed. 122736 rows found. 2 columns returned.
         *** Total elapsed time was 58 seconds.

The number of distinct row of t1 =
(SMED_NO_INDIV_BEN,SMED_NO_RAMQ_DEM_MED) is good too :

        SELECT NO_INDIV_BEN,NO_RAMQ_DEM_MED
        FROM   DB.T1
        WHERE  DAT_SERV BETWEEN '1998-01-01' AND '1998-01-02'
        GROUP BY 1,2;

         *** Query completed. 122736 rows found. 2 columns returned.
         *** Total elapsed time was one minute and 5 seconds.

Rudel Simard
Régie de l'assurance-maladie du Québec
DBA de l'environnement informationnel

	<Prev	Next>		<<First	<Prev	Next>	Last>>