SQL JOIN

 

POSTGRSQL - DROP TRIGGERS

SELECT 

'DROP TRIGGER abc.' || trigger_name || ' ON ' || event_object_table || ';'

FROM information_schema.triggers

WHERE trigger_schema = 'abc';

SQL - FIND DUPLICATE RECORDS

SELECT offset, COUNT(offset)  

FROM log 

GROUP BY offset  

HAVING COUNT(offset) > 1;  

CALCULATE TIME DIFFERENCE BETWEEN TWO CONTINUOUS ROWS

SELECT A.id, A.ts, TIMEDIFF (B.ts,A.ts) AS timedifference

FROM log A INNER JOIN log B ON B.id = (A.id + 1)

ORDER BY TIMEDIFF (B.ts,A.ts) DESC

SELECT A.id, A.ts, CONVERT(TIMEDIFF (B.ts,A.ts),CHAR) AS timedifference,a.offset,a.input_offset

FROM log A INNER JOIN log B ON B.id = (A.id + 1)

ORDER BY TIMEDIFF (B.ts,A.ts) DESC 

FILE READ - REGULAR EXPRESSION

 (j_dataload)_[0-9]{8}_[0-9]{14}\.log

SHELL SCRIPT TO EXECUTE PENTAHO JOB

 #!/bin/bash -l

sh /home/abcserver/data-integration/kitchen.sh -file="/home/abcserver/etl/j_dataload.kjb" –level:Basic -logfile:/home/abcserver/etl/Logfiles/j_dataload_$(date +"%Y%m%d_%H%M%s").log

CALCULATE DIFFERENCE BETWEEN TWO TIMESTAMP IN POSTGRESQL

EXTRACT(DAY FROM MAX(joindate)-MIN(joindate)) AS DateDifference

EXTRACT(DAY FROM created-updated)

EXTRACT(HOUR FROM (updated - created)) AS HourDifference

CENTURY: It is the number of centuries

DAY: It is the day of the month (1-31) or the number of days

DECADE: It is the decade that is the year divided by 10.

DOW: It is the day of the week Sunday (0) to Saturday (6).

DOY: it is the day of the year that ranges from 1 to 366.

EPOCH: It is the total number of seconds in the interval

HOUR: It is the number of hours

MILLENNIUM: It is the number of millennium

MILLISECONDS: It is the second’s field, including fractional parts,

MINUTE: It is the minute (0-59) or a number of minutes.

MONTH: The number of months (1-12).

SECOND: It is the number of seconds.

WEEK: It is the number of weeks

YEAR: It is the year.