Help with: WITH and CAST Functions

[jen123]

I was given a program (written by someone else) to run.  My SAS/SQL skills are beginner-intermediate.  I have never worked with WITH and CAST functions before.  When I tried to run this program, it would take hours until it times out.  I put the option to bring back only 10 OBS - theoretically it shouldn't take long.

Thoughts?  It seems like the program is running in loops

OPTIONS OBS=10;

OPTIONS COMPRESS=YES;

  proc sql;

  connect to teradata(database=xyz tdpid=edwprod user=xxxx password="xxxx");

  create table agent as select *  from connection to teradata (

  WITH ahist (contact_id, duration, account_id, per_num) as

  (

  SELECT distinct

  ahist.contact_id,

  ahist.duration,

  ahist.account_id,

  ahist.per_num

  FROM acct_data ahist

  where ahist.per_num = 6368

  )

  SELECT

  ecm.cl_tid,

  ahist.account_id,

  ahist.duration,

  agent.ecmcontact_id,

  agent.per_num,

  agent.user_role_dsc,

  agent.start_ts,

  artf.create_ts,

  note.history_key_nm,

  cast(agent.start_ts as date) as emp_date

  FROM ecmcontact agent

  join ahist

  on ahist.contact_id=agent.ecmcontact_id

  and ahist.per_num=agent.per_num

  left outer join ecm_account ecm

  on ecm.account_id=ahist.account_id

  left outer join ecmartifact artf

  on ahist.contact_id=artf.contact_id

  and ahist.per_num=artf.per_num

  left outer join citiecmnote note

  on ahist.per_num=note.per_num

  and note.ecm_artifact_id = artf.ecmartifact_id

  ORDER BY ecm.cl_tid, artf.create_ts

  );

  quit;

[Reeza]

You're using something called SQL Pass Through, which means the syntax after the  section below must be valid Terabase SQL. I don't know enough about Terabase to help, but you may be better off in posting to a Terabase forum or checking those out at least.  WITH looks like a sub query to me, so you could potentially rewrite the code very easily, but I'd test it thoroughly.

create table agent as select *  from connection to teradata (

This may work, if I understand the above correctly, which I'm not sure I do:

OPTIONS OBS=10;

OPTIONS COMPRESS=YES;

  proc sql;

  connect to teradata(database=xyz tdpid=edwprod user=xxxx password="xxxx");

  create table agent as select *  from connection to teradata (

  SELECT

  ecm.cl_tid,

  ahist.account_id,

  ahist.duration,

  agent.ecmcontact_id,

  agent.per_num,

  agent.user_role_dsc,

  agent.start_ts,

  artf.create_ts,

  note.history_key_nm,

  cast(agent.start_ts as date) as emp_date

  FROM ecmcontact agent

  join (

SELECT distinct

  ahist.contact_id,

  ahist.duration,

  ahist.account_id,

  ahist.per_num

  FROM acct_data ahist

  where ahist.per_num = 6368) ahist

  on ahist.contact_id=agent.ecmcontact_id

  and ahist.per_num=agent.per_num

  left outer join ecm_account ecm

  on ecm.account_id=ahist.account_id

  left outer join ecmartifact artf

  on ahist.contact_id=artf.contact_id

  and ahist.per_num=artf.per_num

  left outer join citiecmnote note

  on ahist.per_num=note.per_num

  and note.ecm_artifact_id = artf.ecmartifact_id

  ORDER BY ecm.cl_tid, artf.create_ts

  );

  quit;

[TomKari]

I can help out with the CAST part at least; in SQL, that's how data conversions are done. So I'm fairly sure your "cast(agent.start_ts as date) as emp_date" is just converting agent.start _ts into whatever Teradata's internal date format is, from where I assume it will be transformed into SAS's date format.

Tom

[Tom]

My Teradata programming friends tell me that you get better performance using GROUP BY instead of SELECT DISTINCT.

So your first sub-query could be re-written as:

SELECT ahist.contact_id

     , ahist.duration

     , ahist.account_id

     , ahist.per_num

FROM acct_data ahist

WHERE ahist.per_num = 6368

GROUP BY 1,2,3,4