What Is DERV?

Database
TDW
Enthusiast

What Is DERV?

Hi everyone,

New to Teradata (and this forum), though I have some DB background (Paradox for DOS) over 15 years ago.

I have adopted code from a former consultant at my new job. I am updating the code to run for this year; pretty much straighforward changes. I am also trying to get a deeper understanding of the code itself, adding many comments and formatting the BTQ for readability.

I came across this one area of code that is confusing. I belive it is generating a derived table, though I don't quite understand entirely what that is. I Googled DERV but to no avail. Is derv a SQL command/clause/etc.? If this is a table name chosen by the coder, what are the parenthesis for?

------------------------------

DROP TABLE    D.Step_3A ;

CREATE TABLE  D.Step_3A

(   TPS    Varchar(9),

    REL Varchar(9)

)

PRIMARY INDEX (TPS);

INSERT INTO D.Step_3A

SELECT DISTINCT

 TPS,

 REL FROM D.Step_3  A

JOIN PRODIRS.BInd B

 ON  A.TPS = B.PrmNmbr

 AND A.REL = B.SpsNmbrTxt

 AND EXTRACT(YEAR FROM B.Prd_Dte) = 2012

 AND Src_Code        = '2'

WHERE A.TPS IN

( SELECT A

 FROM

 ( SELECT DISTINCT

   TPS,

   REL  FROM D.Step_3  A

 )

 derv(A,B)

 HAVING COUNT(B) > 1

 GROUP BY 1

)

------------------------------

Thanks in advance for your help.

God Bless,

Genesius

2 REPLIES
Junior Contributor

Re: What Is DERV?

DERV is a name for a Derived Table, i.e. the previous select in brackets.

In Paradox this syntax didn't exist, but you could do something similar:

"select ... from filename" where filename was a path to a flat file with a select in it.

Using Dervied Tables you can easily nest SQL.

And (a,b) are alias names for the TPS and REL columns. In fact you could do the same by TPS AS a, REL AS b and omit this part.

The rules when and how column aliases are defined are similar to a CREATE VIEW.

Check the "SQL Data Manipulation Language" manual

Chapter 1: The SELECT Statement

Derived Tables

Dieter

TDW
Enthusiast

Re: What Is DERV?

My apologies, Dieter.

It has been a bit crazy at my new job and I did not give myself the chance to thank you for your comment/help. I sort of understand what you replied, but not fully.

Thanks again.

God Bless,

Genesius