All Forums Database
PatYak 1 post Joined 10/12
21 Nov 2014
Temporary Tables

I am fairly new to using Teradata SQL. I use other versions of SQL regularly, but only for the last year or so. 
Heres the question- I have a table of technician dispatches, from that table I need to get all of the cancelled dispatches. Which I have done.
Then I need to find all of the dispatched for the same customer that occured within 30 days of the cancelled dispatch. I believe i need to create a temporary table housing the cancelled dispatches and join the temp. table to the rest....ast this point I'm lost
This code returns top 1000 cancelled dispatches:
select top 1000
v485.area_nm_turf
,v485.ext_key1
,v485.ban
,v485.comp_can_dt
,v487.evnt_id
,v487.wr_id
,v487.evnt_ts
,v487.actn
,v487.evnt_wr_sts
from ASKME_CPNI_UNRESTRICTED_VIEWS.VCTD485 v485 left join
ASKME_CPNI_UNRESTRICTED_VIEWS.VCTD488 v488 on v485.src_cd=v488.src_cd and v485.wr_id=v488.wr_id and v485.ext_key1=v488.ext_key1 and v485.comp_can_dt=v488.comp_can_dt left join
ASKME_CPNI_UNRESTRICTED_VIEWS.VCTD487 v487 on v485.src_cd=v487.src_cd and v485.wr_id=v487.wr_id and v485.ext_key1=v487.ext_key1 and v485.comp_can_dt=v487.comp_can_dt
where v485.comp_can_dt between '2014-10-01' and '2014-10-31'
and v485.wr_sts='CANCEL'
and v487.actn = 'CANCEL'
and v488.wr_id is null -- no dispatch
and v485.src_cd='MWEDGE1'
 
 

Raja_KT 1246 posts Joined 07/09
21 Nov 2014

Do you mean to say that you want to insert into volatile table like for example:
create volatile table rkt as
(select ..... from ....and v485.src_cd='MWEDGE1')
with data primary index(xyz) on commit preserve rows;
and then do a select from this populated table?

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

ToddAWalter 316 posts Joined 10/11
22 Nov 2014

It is not necesssary to create a temprary table, that portion of your query can be written as a derived table in the FROM clause and then the rest of your logic can be applied between that derived table and the rest of the data that you want to link to.
 
In this case you need to join it back to the dispatches by customerid and then apply your qualification to get only the ones in the past 30 days.

You must sign in to leave a comment.