I am trying to replicate some code from SQL, and need to create a custom table insert... my SQL query uses a temp table #ELL. When I try to deploy & execute, it does not understand the temp table. Can temp tables be used here? IE. #ELL.. I just need to have it hold some info that later in the query will populate the new custom table in TimeXtender.
Best answer by Thomas Lind
View original
+5
Hi Purnima
Sure you can. I made this article about splitting out currencies on individual dates. Hover I found, despite the suggested method being true that there was issues with the first date. So I had to make my script differently.
In the end I worked out that I needed to make it as an StoredProcedure, like so.
CREATE PROCEDURE [DSA].[spExchangeRates]
@iCurrencyCode int = NULL,
@iMaxCurrencyCode int = NULL
AS
BEGIN
CREATE TABLE #TempTable
(
[CurrencyCode] int,
[ExchangeRate] decimal(38,3),
[FromDate] datetime,
[ToDate] datetime
)
WHILE(@iCurrencyCode <= @iMaxCurrencyCode)
BEGIN
INSERT INTO #TempTable
(
[CurrencyCode],
[ExchangeRate],
[FromDate],
[ToDate]
)
SELECT [CurrencyCode],
ISNULL([ExchangeRate],0) AS [ExchangeRate],
ISNULL(LAG([ToDate]) OVER (ORDER BY [DW_Id]),DATEADD(yy, DATEDIFF(yy, 0, [ToDate]), 0)) AS [FromDate],
[ToDate]
FROM [ExchangeRates]
WHERE [CurrencyCode] = @iCurrencyCode
SET @iCurrencyCode = @iCurrencyCode + 1
END
SELECT * FROM #TempTable
DROP TABLE #TempTable
END
In there I make a #TempTable, which I use to make a while loop.
To use my stored procedure I had a custom table with a custom table insert statement.
DECLARE @1 as INT = (SELECT MIN([CurrencyCode]) FROM [ExchangeRates_V])
DECLARE @2 as INT = (SELECT MAX([CurrencyCode]) FROM [ExchangeRates_V])
DECLARE @Temp TABLE
(
[CurrencyCode] int,
[ExchangeRate] decimal(38,3),
[FromDate] datetime,
[ToDate] datetime
)
INSERT @Temp EXEC [DSA].[spExchangeRates]
@iCurrencyCode = @1,
@iMaxCurrencyCode = @2
SELECT [CurrencyCode],
[ExchangeRate],
[DateValue] AS [Date]
FROM @Temp
LEFT JOIN [Date_V] AS D ON [FromDate] <= D.[DateValue] AND [ToDate] > D.[DateValue]
This also uses a temp table, here I use @Temp instead. I start by declaring it and then using that to run my stored procedure.
I don't think there is any limits to where you can use these, but I do believe I had some issues using them in a view, but test out your scripts in management studio, if they work there you can make them work in our program as well.
Thanks Tomas.
I need to cycle through a table to find latest records.. so I'm currently doing this via CURSOR function. Can I do that in TimeXtender?
Here's the code I'm trying t replicate:
declare @ELL as varchar(100)
declare @PRS as tinyint
declare @personID as int
declare @startDate as datetime
declare @PREV_ELL as varchar(100)
declare @PREV_PRS as tinyint
declare @PrevPersonID as int
create table #ELL
(
ELL varchar(100) not null,
ParentRefused tinyint,
personID int not null,
startDate datetime not null
)
DECLARE service_cursor CURSOR FOR
--select *
--from ICSQL2.boulder_customdevelopment.DW_ell_history
SELECT ELL, prs, personID, startDate
FROM DWSTAGE.dbo.staging_etl_history_ell
WHERE processStep = 1 --added with changes on 10/27/2017
order by personID, startDate
OPEN service_cursor
FETCH NEXT FROM service_cursor into @ELL,@PRS, @personID,@startDate
set @PREV_ELL = 'EMPTY'
set @PREV_PRS = 3
set @PrevPersonID = 0
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@ELL <> @PREV_ELL or @personID <> @PrevPersonID or @PREV_PRS <> @PRS)
BEGIN
insert into #ELL values (@ELL,@PRS,@personID,@startDate)
END
set @PREV_ELL = @ELL
set @PREV_PRS = @PRS
set @PrevPersonID = @personID
FETCH NEXT FROM service_cursor into @ELL,@PRS, @personID,@startDate
END
CLOSE service_cursor
DEALLOCATE service_cursor
/*** reload ELL history table with output ****************************************/
truncate table dbo.etl_history_ell
insert into dbo.etl_history_ell
(personID, StartDate, ell_status, ell_program, [parent_refused_services])
select personID, StartDate, etl.ell_status, etl.ell_program,
case
when ParentRefused = 0
then 'No Refusal'
else 'Parent Refused Services'
end
from #ELL ell
inner join [dbo].[etl_ell_program] etl
on ell.ELL = etl.ELL
and ell.ParentRefused = etl.ParentRefusedServices
order by personID, startDate
update dbo.etl_history_ell
set parent_refused_services = 'N/A'
where ell_status in ('Post-FEP Year 1','Post-FEP Year 2','Post-FEP Year 3','Post-FEP Year 4')
/*______________Step: 2_______________*/
/*** create post-ELL records based on LEP table with exited status ******************************/
-- 07/24/2018 - This step eliminated. All POST-FEP records should now be in the LEP service records.
/*______________Step: 3_______________*/
/*** create FEP on Entry records based on LEP table with Not Lep status ******************************/
SELECT personID, programStatus, startDate, 'FEP on Entry' as ELLstatus, 'Non-ELL' as Program, 'N/A' as PRS
INTO #FEPONENTRY
FROM DWSTAGE.dbo.staging_etl_history_ell
WHERE programStatus = 'Not Lep'
AND processStep = 3
INSERT INTO dbo.etl_history_ell (personID, StartDate, ell_status, ell_program, parent_refused_services)
SELECT personID
, startDate
, ELLstatus
, Program
, PRS
FROM #FEPONENTRY
/*** update end dates that are one day before next item in person's sequence ****************/
select T1.personID, T1.StartDate, dateadd(n,-1,T2.StartDate) as EndDate
into #ENDDATE
from dbo.etl_history_ell T1
inner join dbo.etl_history_ell T2
on T1.personID = T2.personID
--and T1.StartDate < T2.StartDate
and T2.StartDate = (select min(StartDate) from dbo.etl_history_ell T3
where T3.StartDate > T1.StartDate
and T1.personID = T3.personID)
order by T1.personID, T1.StartDate
update dbo.etl_history_ell
set EndDate = ED.EndDate
from dbo.etl_history_ell e, #ENDDATE ED
where e.personID = ED.personID
and e.StartDate = ED.StartDate
/*** update end date for last record in each person's sequence **********/
update dbo.etl_history_ell
set EndDate = '6/30/2050 23:59'
where EndDate is null
/*** update ell_id from dimension table id **********************/
update dbo.etl_history_ell
set ell_id = d.ell_id
from dbo.etl_history_ell e
inner join dbo.dim_ell d
on e.ell_status = d.ell_status
and e.ell_program = d.ell_program
and e.parent_refused_services = d.parent_refused_services
/*** cleanup *******************/
drop table #ELL
drop table #ENDDATE
drop table #FEPONENTRY
Yes cursors work perfectly in custom SQL scripts. We use them quite often.
CTEs work also as an alternative to temp tables and cursors. They allow quick recursion for example as an added benefit.
+5
Yes CTE works as well, I explain how to use them in the article link I have in the top paragraph.
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
