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
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 v487.actn = 'CANCEL'
and v488.wr_id is null -- no dispatch
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?
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.