Category: SQL

SQL STUFF I FORGET

String Functions

Function Description CHAR(N) Returns the the string representation of the
number N.
CHAR_LENGTH(S) Returns the length of a string.
CONCAT(S1, S2) Concatenates S1 with S2.
SUBSTR(S, I, L) Returns a substring of S, starting at index I of lenght L.
LOWER(S) Returns the lowercase representation of S.
UPPER(S) Returns the uppercase representation of S.
TRIM(S) Removes spaces from the beggining and and of S.
RTRIM(S)
LTRIM(S)		 Removes spaces at the begging (right) or end (left) of S.

Date and Time Functions

Function Description CURDATE()
CURTIME()		 Returns the system’s current date/time.
DATE(D)
DATE(T)		 Converts a string representation of a date/time into into a
date/time value.
DAY(D) Returns the day(1-31) from the date D.
WEEK(D) Returns the week (1-54) from the date D.
MONTH(D) Returns the month (1-12) from the date D.
YEAR(D) Returns the year from the date D.
DAYOFWEEK(D) Returns the week day (1-7) from the date D where 1 is Sunday.
DAYOFWEEK_ISO(D) Returns the week day (1-7) from the date D where 1 is Monday.
DAYOFYEAR(D) Returns the number of the day, in a year (1-366).
HOUR(T) Returns the hour (0-24) from the time T.
MINUTE(T) Returns the minute from the time T.
SECOND(T) Returns the second from the time T.
MICROSECOND(T) Returns the microsecond from the time

Inserted records from file.a into file.b where not exists

Don’t you hate when your copy file with *ADD fails due to duplicate key?  I used this sql to push the rest in.

INSERT INTO DVDTA/DWXP010

SELECT table1.* FROM masonm/xdwxp010 table1 left join dvdta/dwxp010
table2 on (table1.acctno = table2.acctno and table1.policy =
table2.policy and table1.effdte = table2.effdte and table1.edsno =
table2.edsno) WHERE table2.acctno is null
46 rows inserted in DWXP010 in DVDTA.

SQL LEFT JOIN (resembles not exist)

SELECT table1.* FROM masonm/xdwxp010 table1 left join dvdta/dwxp010
table2 on (table1.acctno = table2.acctno and table1.policy =
table2.policy and table1.effdte = table2.effdte and table1.edsno =
table2.edsno) WHERE table2.acctno is null

I prefer not exists

select * from table1
where not exists (select field2 from table2 where table2.field2 = table1.field1)

SQL INSERT INTO SELECT example

INSERT INTO MASONM/XDWXP010 (POLICY, EFFDTE, EDSNO, EDSDTE, ACTDTE,
COVEND, EXPDTE, CANDTE, CANPND, POLTRM, CANOPT, CANRSN, CANMTD,
CANSTE, NONPAY, INSNAM, INSAD1, INSAD2, INSAD3, INSCTY, INSST,
INSZIP, INSTEL, SERCH, CO, AGENT, SUBPRO, AGYTYP, AGYCOM, ACCTNO,
DIRASS, POLTYP, INSTYP, PROD, EDMNTS, PRVPOL, RELPOL, PRIPOL,
CLMOCC, POLPRT, REINST, NONREN, STATS1, STATS2, STATS3, STATS4,
STATS5, TRANS, TRDATE, TRTIME, IDADD, MAILDT, POLPRE, ASSRSK,
REINFL, NRWRSN, PRTF1, FLAGI1, FLAGI2, FLAGI3, REFQOT, UDDR,
UDDROD, UDDRDT, MVRHLD, RNIMAG, PAYPLN, FACTOR, APRP, INTCHG, COMM,
TOTDUE, TOTFAC, RTEFLG, AUDFRQ, AUDTYP, AUDEND, FINAUD, RETRTE,
INTRSK, PRDFLG, FIXFLD, SUBCAN, SUBEDS, NBRENTY, GLCLAS, ORPOYR,
RETDTE, TAIDTE, FLAGI4, FLAGI5, FLAGI6, POLCFG, FUTUR3, FUTUR4,
FUTUR5, ORGEFF, TRUEXP, FIELDA, FIELDB, FIELDC, FIELDD, FIELDE,
FIELDF, CMOVFG, CMOVPC, COUTRY, RTEMTD, CNVPOL, COMWOP, FACPRM,
DUPNAM, INDIC1, INDIC2, INDIC3, INDIC4, INDIC5, INDIC6, MINPRE,
FLD003, FLD004, NYFTCL, NYFTCD, FLD005, FLD006, AUDDTE, SICCDE,
INTPOL, FLD008, REDEPT, MAXLOS, FLD010, FLTPOL, GRPPGH, GRPDIS,
USELOC, PRGMID, TOTPRM, TYCVBS, NMADIN, BUSDSC, FINCCO, RWTFLG,
RWTPOL, PGMFLG, SHTRSN, UNDRWR, TYPBIZ, HAZGRP, FUTF11, FUTF12,
FUTF13, FUTF14, FUTF31, FUTF51, DSPRET, SIPFLG, EMPLPF, PCERTN,
POLPLI, WRAPUP, OLNINS, APYWAV, DIRHLD, BKTRAN, CLNTID, PREDST,
WAVAMT, RTEFDT, WAVPRM, REFOVR, RLPATP, PNDRFG, RLPCOM, FACRIN,
NXTRVW, REFLOS, TRECDT, AGNDDT, QTAGDT, QTFLDT, DCCLDT, QTFLAG,
SMSTCD, SMSTTX) SELECT xdwxp010.* FROM masonm/zdwxp010
xdwxp010,masonm/webfl100 WHERE XDWXP010.AGENT = WEBFL100.AGENT and
XDWXP010.ACCTNO = WEBFL100.ACCTNO and XDWXP010.POLICY =
WEBFL100.POLICY and XDWXP010.EFFDTE = WEBFL100.EFFDTE and
XDWXP010.EDSNO = WEBFL100.EDSNO
83 rows inserted in XDWXP010 in MASONM.