SQL Sample #1

Example of using complex inline SQL. Very useful in report designing, and off-load to DB server

SELECT distinct pa.podname, pa.company, pa.email, pa.firstname, pa.lastname,

(SELECT AOL.username

FROM tcc.accounts AOL

WHERE AOL.im_id = pt.im_id

AND AOL.transportid = 'AOL' ) as "aol",

(SELECT msn.username

FROM tcc.accounts msn

WHERE msn.im_id = pt.im_id

AND msn.transportid = 'MSN' ) as "msn",

(SELECT yho.username

FROM tcc.accounts yho

WHERE yho.im_id = pt.im_id

AND yho.transportid = 'YHO' ) as "yho",

(SELECT count(tot.username)

FROM tcc.accounts tot

WHERE tot.im_id = pt.im_id ) as "tot"

FROM iccs.im_acct pa, tcc.accounts pt

WHERE pa.im_id = pt.im_id

AND pt.im_id IN ()

ORDER BY pa.company, pa.podname;