a little philosophy in code
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…
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…
CASE Statements can be tricky in most languages. They can be even more difficult to get right in SQL. Here are some examples of CASE statements in PL/SQL to get you on the right path.…