master table

Training
Enthusiast

master table

Hi all

 

Whi knows how to convert follow expression to Teradata code:

 

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

4 REPLIES
Senior Apprentice

Re: master table

Hi,

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.

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
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

 

 

Enthusiast

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

from

(

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

as dates from master..spt_values

where type='p' and number between 1 and

DATEDIFF(day,DATEADD(year,@Curryear-1900,0),DATEADD(year,@Curryear-1900+1,0))

) 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)