b1nary0mega
3/1/2015 - 6:54 PM

Get all (active) NP publications for valid data (exclude anything that doesn't have an author listed)

Get all (active) NP publications for valid data (exclude anything that doesn't have an author listed)

SELECT EMPL.DEPT,
  IERP.EMPLOYEE_ID,
  EMPL.LAST_NAME,
  EMPL.FIRST_NAME,
  EMPL.POS_CODE,
  EMPL.TITLE AS POS_TITLE,
  IERP.ROW_ID,
  IERP.DATE_MODIFIED,
  IERP.PUB_DATE,
  IERP.AUTHORS,
  IERP.TITLE,
  IERP.PUB_NAME,
  IERP.PG_NUMBERS,
  IERP.LINK,
  IERP.PUB_TYPE,
  IERP.CH_TITLE,
  IERP.EDITOR,
  IERP.PUBHOUSE_NAME,
  IERP.PUBHOUSE_CITY,
  IERP.PUBHOUSE_STATE,
  IERP.ISSUE_NUMBER,
  IERP.FACILITY
FROM IER_PUBLICATIONS IERP
JOIN ACTIVE_UNIQUE_SMH_EMPLS EMPL
ON IERP.EMPLOYEE_ID        = EMPL.EMPLOYEE_ID
WHERE EMPL.POS_CODE       IN (8464,8471)
AND lower(IERP.AUTHORS) NOT IN ('na','n/a','n\a')
AND IERP.TITLE NOT LIKE ('Verified%by%')
ORDER BY IERP.PUB_DATE, EMPL.DEPT, EMPL.POS_CODE, EMPL.LAST_NAME;