join

OUTER JOIN with Sub Query

When you are creating a SubQuery (i.e. getting Max EFFDT and/or Max EFFSEQ) and you are using an OUTER JOIN. Instead of creating a new view and then joining the view to your SQL statement, try placing the view’s SQL directly into the main SQL statement. This is the syntax required to accomplish this: The […]

Read More

group by

GROUP BY Clause for Oracle PL/SQL

GROUP BY Clause Oracle PL/SQL Normal GROUP BY A few things to remember about the GROUP BY clause for Oracle. Any fields not part of an aggregate function (i.e. MAX() , SUM()  ) in the SELECT clause must be listed in the GROUP BY  clause. Limiting data returned for any aggregate function must be listed in the HAVING clause and not […]

Read More

multiple rows

Multiple rows into a comma delimited list in PL/SQL

Multiple rows One common thing to request is data from multiple rows into a comma delimited list in PL/SQL. If you are using Oracle 11g or greater there are a couple of options. The first is pretty verbose and difficult to follow but it is here for reference and posterity.

LISTAGG Kind of messy […]

Read More