master table


master table

Hi all


Whi knows how to convert follow expression to Teradata code:


SELECT number FROM master..spt_values where type = 'p' ;  ?

Senior Apprentice

Re: master table


Have you tried ?:

SELECT "number" FROM master.spt_values where type = 'p' ; 


If that doesn't work what is this code trying to do? Then we may be able to convert it for you.





Ward Analytics Ltd - information in motion
Junior Contributor

Re: master table

It's a code snippet accessing an undocument SQL Server system table, probably returning a sequence of numbers.


This might return the same result:

SELECT day_of_calendar -1 AS n FROM sys_calendar.Calendar n BETWEEN n BETWEN 0 AND 2047

But the main question is:

Why do you need it?

It's probably used for processing some logic and it might be easier/different in Teradata




Re: master table

Hi all

This is a full part of code i need to convert:

declare @Curryear=(year(getdate());

select max(dates) as Last_Friday



select dateadd(day,number-1,DATEADD(year,@Curryear-1900,0))

as dates from master..spt_values

where type='p' and number between 1 and


) as t

where DATENAME(weekday,dates)='friday'

group by DATEADD(month,datediff(month,0,dates),0)


Junior Contributor

Re: master table

This query returns the last fridays per month for the current year.


You probably got a calendar table/view in your system, check if there's already a column indicating this.

Otherwise you can run a query similar to:

SELECT Max(calendar_date) AS Last_Friday
FROM sys_calendar.CALENDAR
WHERE calendar_date BETWEEN Trunc(Current_Date, 'y')   -- Jan 1. of current year
             AND Add_Months(Trunc(Current_Date, 'y'),12)-1 -- Dec 31. of current year
  AND day_of_week = 6 -- friday
GROUP BY Extract(MONTH From calendar_date)