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 […]
Month: February 2018
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 […]
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.
|
1 2 3 4 5 6 7 8 9 |
SELECT SUBSTR(SYS_CONNECT_BY_PATH (COLUMN_NAME, ','). 2) CSV FROM (SELECT COLUMN_NAME, ROW_NUMBER() OVER (ORDER BY COLUMN_NAME) RN, COUNT(*) OVER () CNT FROM YOUR_TABLE) WHERE RN = CNT START WITH RN = 1 CONNECT BY RN = PRIOR RN + 1 |
LISTAGG Kind of messy […]