Search This Blog

2011-01-12

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;

No comments:

Blog Archive