https:

# Archives of the TeradataForum

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

 < Last>>

 Subj: Derive table confusion!!! From: Rudel Simard

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

 < Last>>

Attachments

Library

Quick Reference

Archives

Sample Index

 2016 2007 2015 2006 2014 2005 2013 2004 2012 2003 2011 2002 2010 2001 2009 2000 2008 1999

2003 Indexes

 Jan Jul Feb Aug Mar Sep Apr Oct May Nov Jun Dec

 Top Home Privacy Feedback