Send Mail To, CC, BCC

Transform in SendMail Component :

include  "~$AB_HOME/include/mail.dml";

/*Prepare message information*/

message::prepare_message(in) =

begin

  let string("\x01") from = read_file("${MAILFROM}");

  let string("\x01") reply_to = read_file("${MAILREPLYTO}");

  let string("\x01") to_list_file = read_file("${MAILTO}");

  let string("\x01") cc_list_file = read_file("${MAILCC}");

  let string("\x01") bcc_list_file = read_file("${MAILBCC}");

  let string("\x01")[] from_tmp_vect = string_split(from,";");

  let string("\x01")[] reply_tmp_vect = string_split(reply_to,";");

  let string("\x01")[] to_tmp_vect = string_split(to_list_file,";");

  let string("\x01")[] cc_tmp_vect = string_split(cc_list_file,";");

  let string("\x01")[] bcc_tmp_vect = string_split(bcc_list_file,";");

  let string("\x01")[] tmp_split;

  let int i = 0;

 

  let record

    string("\x01") readable_name = NULL;

    string("\x01") e_mail_address = NULL;

  end[length_of(from_tmp_vect)] user_from_mail = allocate();

 

  let record

    string("\x01") readable_name = NULL;

    string("\x01") e_mail_address = NULL;

  end[length_of(reply_tmp_vect)] user_reply_mail = allocate();

 

  let record

    string("\x01") readable_name = NULL;

    string("\x01") e_mail_address = NULL;

  end[length_of(to_tmp_vect)] user_to_mail = allocate();

 

  let record

    string("\x01") readable_name = "";

    string("\x01") e_mail_address = "";

  end[length_of(cc_tmp_vect)] user_cc_mail = allocate();

 

  let record

    string("\x01") readable_name = NULL;

    string("\x01") e_mail_address = NULL;

  end[length_of(bcc_tmp_vect)] user_bcc_mail = allocate();

   

  i = 0; 

  for (i, i <= length_of(from_tmp_vect)-1)

  begin

    tmp_split = string_split(from_tmp_vect[i],",");

    user_from_mail[i].readable_name = tmp_split[0];

    user_from_mail[i].e_mail_address = tmp_split[1];

  end;

 

  i = 0; 

  for (i, i <= length_of(reply_tmp_vect)-1)

  begin

    tmp_split = string_split(reply_tmp_vect[i],",");

    user_reply_mail[i].readable_name = tmp_split[0];

    user_reply_mail[i].e_mail_address = tmp_split[1];

  end;

 

  i = 0; 

  for (i, i <= length_of(to_tmp_vect)-1)

  begin

    tmp_split = string_split(to_tmp_vect[i],",");

    user_to_mail[i].readable_name = tmp_split[0];

    user_to_mail[i].e_mail_address = tmp_split[1];

  end;

 

  i = 0; 

  for (i, i <= length_of(cc_tmp_vect)-1)

  begin

    tmp_split = string_split(cc_tmp_vect[i],",");

    user_cc_mail[i].readable_name = tmp_split[0];

    user_cc_mail[i].e_mail_address = tmp_split[1];

  end;

 

  i = 0; 

  for (i, i <= length_of(bcc_tmp_vect)-1)

  begin

    tmp_split = string_split(cc_tmp_vect[i],",");

    user_bcc_mail[i].readable_name = tmp_split[0];

    user_bcc_mail[i].e_mail_address = tmp_split[1];

  end;

  

  message::[record

  envelope [record

             return_path  user_from_mail

             created_date local_now()

             from         user_from_mail

             sender NULL

             reply_to     user_reply_mail

             subject      in.subject

             to           user_to_mail

             cc           user_cc_mail

             bcc          user_bcc_mail

             in_reply_to ""

             message_id ""]

  content [record

             header [record

                       mime_type "text"

                       mime_subtype "plain"

                       parameters [vector]

                       encoding "q"

                       id ""

                       description U""

                       disposition [record

                                      disp_type ""

                                      parameters [vector]]

                       language [vector]

                       location ""]

             text in.text

             parts NULL]]

;

end;

type _message_t=record

  envelope_t envelope;

  content_t content=NULL;

end; /*Metadata for records containing message information*/

type _server_log_t=string('\0'); /*Metadata for records containing server_log information*/

out :: create_output(in, server_log) =

begin

end;

=======================

TextFile for list distribution:

Nama Lengkap,email_saya@hostname.com;Indra Gunawan,indra.stone@gmail.com

FTP Component

Write data to file with maximum record (example : 3000record per file)

Using reformat component

Input port :

record

  decimal("\x01") trx_date = NULL("") /*INTEGER*/;

  decimal("\x01") id_card = NULL("") /*INTEGER*/;

  utf8 string("\x01", maximum_length=40) name = NULL("") /*VARCHAR(20) CHARACTER SET LATIN*/;

  utf8 string("\x01", maximum_length=40) ttype = NULL("") /*VARCHAR(20) CHARACTER SET LATIN*/;

  utf8 string("\x01", maximum_length=10) expired = NULL("") /*VARCHAR(5) CHARACTER SET LATIN*/;

  string(1) newline = "\n";

end

Output Port:

record

utf8 string(unsigned integer(2)) filename;

   record

          utf8 string("\n") message = "";

       end  message;

end;

Transform script:

let unsigned integer(2) file_count = 1;

let unsigned integer(4) rec_count = 1;

  

out :: reformat(in) =

begin

   let unsigned integer(2) temp_rec_count = 0;

   let utf8 string(unsigned integer(2)) temp_filename ='FileName_';

   let record

          utf8 string("\n") message = "";

       end temp_message = allocate();

//temp_filename = temp_filename + '_' + string_lpad((string(""))(decimal(""))file_count, 3, '0');

temp_filename = temp_filename + '_' + (string (8)) (datetime("yyyymmdd"))now1() +'_' + (string(6))(datetime("HH24MISS"))datetime_add(now1(),0,0,0,file_count);

temp_message.message = (string_lrtrim(((string(30))in.id_card))+";OKE;"+in.name);

    

   rec_count = rec_count + 1;

  

   if (rec_count > 3000)

   begin

      file_count = file_count + 1;

      rec_count = 1;     

   end;

     

   out.filename:: temp_filename;

  out.message :: temp_message;

end;

write to file using Write Multiple Files component

input port : propagate from reformat

Transform script :

type output_type= record

   utf8 string("\n") message;

end; /*Metadata for records written to output files*/

filename :: get_filename(in) =

begin

  filename :: string_concat("${FOLDER_NAME}/", in.filename, ".txt");

end;

write :: reformat(in) =

begin

  write :: in.message;

end;

Printilan Ab Initio

dalam insert table ke database teradata mendapatkan error:

**** 13:19:16 Current CLI or RDBMS allows maximum row size: 64K
atau
========= Too many load/unload tasks running: try again later


hal tersebut dikarenakan bug di workload management Teradata


solusi sementara di Ab Inition


isi di parameter komponen input table
SLEEP = 30
TENACITY = 1


========session_error=================





**** 11:28:52 Teradata Database Release: 13.00.00.14
**** 11:28:52 Teradata Database Version: 13.00.00.15
**** 11:28:52 Current CLI or RDBMS allows maximum row size: 64K
**** 11:28:52 Character set for this job: ASCII
**** 11:31:54 CLI Error 301: CLI2: SESSOVER(301): Exceeded max number
              of sessions allowed.
**** 11:31:57 Number of FastLoad sessions requested = *
**** 11:31:57 Number of FastLoad sessions connected = 298
**** 11:31:57 FDL4808 LOGON successful


======== session di komponen table usahakan jangan bintang "*" kasih 16 aja atau 1 1 (1 space 1), karena kalau bintang akan meminta banyak session jika server tidak mencukupi maka akan muncul error seperti di atas=========



====penggunaan separator antar karakter===========



record
  decimal("\x07",0, maximum_length=15, sign_reserved) subs_no = NULL("") /*DECIMAL(14)*/;
  date("YYYY-MM-DD")("\x07") ddate = NULL("") /*DATE*/;
  utf8 string("\x07", maximum_length=400) topic = NULL("") /*VARCHAR(200) CHARACTER SET LATIN*/;
  utf8 string("\x07", maximum_length=1000) comments = NULL("") /*VARCHAR(500) CHARACTER SET LATIN*/;
  utf8 string("\x07", maximum_length=100) region = NULL("") /*VARCHAR(50) CHARACTER SET LATIN*/;
  string(1) newline = "\n";
end;


===============pemisah ditandai dengan karakter "\x07" biasanya "\x01", apapun boleh lah yang penting bisa mendapatkan data yg di inginkan====================






====komponen run program====
${SERIAL_DIR} -> is a directory in ab initio
${LANDING_DIR} -> is a directory in ab initio


find ${SERIAL_DIR}${LANDING_DIR} -size +22c -name 'Filename_*.zip' -exec ls {} \;


====getting file_list==========


to run command in abinitio klik F8 and write "echo ${SERIAL_DIR}"




===================================



In this example, string_filter_out compares the input strings "Apt. #2" and ".#,%". The function filters "." and "#" from the first string, returning "Apt 2":
string_filter_out("Apt. #2", ".#,%")   "Apt 2" 


====================================

Only for Teradata

code2 printilan buat teradata

--display end of date for current month
select add_months((current_date - extract(day from current_date)+1),1)-1

buat uppercase data
CAST(EMP_NAME AS VARCHAR(200) UPPERCASE) AS NAME

buat row_num, karena di teradata gak ada row num seperti di oracle jadi harus gunain ini
ROW_NUMBER() OVER(ORDER BY EMP_ID ASC) NO_URUT

select 10 rows top
select top 10 *  from table_name

SELECT * FROM sys_calendar.calendar b
WHERE CAST(CAST(calendar_date AS FORMAT 'yyyy')AS VARCHAR(4))='2011'

where date
WHERE tanggal BETWEEN (CAST(CAST(CAST((CAST( '20110424' AS DATE FORMAT 'yyyymmdd')-INTERVAL '52' DAY)AS FORMAT 'yyyymmdd')AS VARCHAR(8))AS BIGINT))
AND (CAST(CAST(CAST((CAST( '20110424' AS DATE FORMAT 'yyyymmdd')-INTERVAL '7' DAY)AS FORMAT 'yyyymmdd')AS VARCHAR(8))AS BIGINT))

jika ada yang double datanya maka diambil record yg pertama.
gunakan :
QUALIFY (ROW_NUMBER () OVER (PARTITION BY trx_a.subs_no ORDER BY trx_a.d_day DESC))=1

SELECT DISTINCT  trx_a.d_day,trx_a.subs_no,trx_b.pps_amt,
case
    when prev_reg_date is null then 'N'
    else 'Y'   
end IS_EXIST
FROM
(SELECT DISTINCT subs_no,subs_key,d_day
FROM table
) trx_a
LEFT JOIN
(SELECT DISTINCT subs_no,d_day
FROM table
) trx_b
ON trx_a.subs_no=trx_b.subs_no
ORDER BY trx_a.subs_key ASC,trx_b.pps_amt DESC
QUALIFY (ROW_NUMBER () OVER (PARTITION BY trx_a.subs_no ORDER BY trx_a.d_day DESC))=1

--------------

OALESCE(ColumnName,0) = return col1 if not NULL, 0 if NULL

--------------

menghitung menit dari 2 date
SELECT SUBS_NO,MIN_DIFF FROM (
 SELECT SUBS_NO,(SessionEndTime - SessionStartTime DAY(4) TO MINUTE) AS tsdiff,
   (EXTRACT(DAY FROM tsdiff) * 1440)
   + (EXTRACT(HOUR FROM tsdiff) * 60)
   + EXTRACT(MINUTE FROM tsdiff) AS min_diff
   FROM session_table
   ) SES

------------
running sql from unix

-------------
/usr/bin/bteq << EOF

.LOGON 10.10.10.10/user,pass
.Set Echoreq off
.Set Titledashes off
.Set Separator '|'
.Set Format off
.Export REPORT File = 'home/indra/date.txt'
select cast(calendar_date as integer)+19000000 from sys_calendar.calendar
where calendar_date>=${curr_date}-19000000 and calendar_date-2 <= ${curr_date}-19000000
order by calendar_date;
.Export Reset
.Logoff
.Quit

EOF

-------------

another bteq use

-----------


bteq << !!EOF!!

.LOGON 10.24.125.49/xl_optprod,prod
.RUN FILE=runTDsql.sql

.LOGOFF
.exit
!!EOF!!


-----another bteq again-----------



/usr/bin/bteq << EOF
.SET SESSIONS 16
.LOGON 10.24.125.48/xl_optprod,prod

delete;
insert;
select;



.Logoff
.Quit

EOF

Ajax Open page without loading (whatever lah for the subject just see the script)

note : please dont use "\" in script cause error while write the blog.

Ajax Open page without loading (whatever lah for the subject just see the script)

this code is for get data from server without refreshing or move to other page.

this script is basic for every ajax web application.

---Start script---

<\script type='text/javascript'>
function createRequestObject() {
var ro;
var browser = navigator.appName;
if(browser == "Microsoft Internet Explorer"){
ro = new ActiveXObject("Microsoft.XMLHTTP");
}else{
ro = new XMLHttpRequest();
}
return ro;
}

var xmlhttp = createRequestObject();

function setData(frm,combobox)
{
alert(combobox.value);
return false;
var kode = combobox.value;
var form = frm;
if (!kode) return;
xmlhttp.open('get', form+'.jsp?critID='+kode, true);
xmlhttp.onreadystatechange = function() {
if ((xmlhttp.readyState == 4) && (xmlhttp.status == 200))
document.getElementById("dataMetric").innerHTML = xmlhttp.responseText;
return false;
}
xmlhttp.send(null);
}
<\/script>
---EndScript---

how to use?

<\div id="dataMetric"><\/div>
<\script language="javascript1.2">setData("frm","1");<\/script>

ROW_NUM for paging table

select * from (

select id,name,ROW_NUMBER() OVER(ORDER BY id) no_urut

from table_master) a

where a.no_urut between 3 and 5