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


------------untuk cek table

SELECT      *

FROM  dbc.TABLEs

WHERE tablename=nama table'