Multiple rows into a comma delimited list in PL/SQL
- in 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…
Alter Session Oracle PL/SQL
- in SQL
Change Schema ALTER SESSION can be used with a database has multiple schema owners. Example: a single database instance contains both development and test environments. You need to alter your session in order to be able…
Running Total in PL/SQL
- in SQL
Problem: You need to display a result with a running total in PL/SQL query. The code below will accomplish displaying a running total in PL/SQL:
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT C.VENDOR_ID, C.VENDOR_NAME_SHORT, A.BUSINESS_UNIT, A.VOUCHER_ID, A.INVOICE_ID, A.INVOICE_DT, A.GROSS_AMT, SUM(A.GROSS_AMT) OVER (ORDER BY C.VENDOR_ID, A.BUSINESS_UNIT) "RUNNING TOTAL" FROM PS_VOUCHER A, PS_VENDOR C WHERE (A.PROCESS_MAN_CLOSE = 'N' OR A.CLOSE_STATUS <> 'C') AND A.ENTRY_STATUS <> 'X' AND A.VENDOR_SETID = C.SETID AND A.VENDOR_ID = C.VENDOR_ID AND C.VENDOR_ID = '0000000001' GROUP BY C.VENDOR_ID, C.VENDOR_NAME_SHORT, A.BUSINESS_UNIT, A.VOUCHER_ID, A.INVOICE_ID, A.INVOICE_DT, A.GROSS_AMT ORDER BY A.GROSS_AMT, C.VENDOR_ID, A.BUSINESS_UNIT, A.VOUCHER_ID |
This is a pretty simple query to show the…