Tag Info

Hot answers tagged

16

You're looking for the analytical function ratio_to_report select agent, round(ratio_to_report(commission) over ()*100) "% Comm." from commissions;


13

You can do this with a normal view, as long as the users involved haven't already got access to the base table. EG: SQL> create user reportuser identified by reportuser; User created. SQL> grant create session to reportuser; Grant succeeded. SQL> grant create synonym to reportuser; Grant succeeded. SQL> select user from dual; USER ...


4

with data as ( select level as foo, mod(ora_hash(level),4) as bar from dual connect by level<9 ) select foo, bar, prev_foo from data model dimension by (foo, bar) measures (cast (null as number) as prev_foo, cast (null as number) as store_foo ) rules ( store_foo[any, ...


4

I guess that using subqueries inside functions is the main performance problem. It seems that the functions are fairly simple and you could drop them and easily rewrite the query with joins only: SELECT loan.loan_number, loan.borrower_name, ... CASE WHEN loan.date_waiver_ordered IS NULL THEN 'Not Ordered' WHEN ...


4

You wrote: After this rename process , I will try to restore database from rman backup. But this backup is not piece by piece copy. Then this is not the method for doing this. In order to use the ALTER DATABASE RENAME FILE, the file have to already exist at the new location. Instead of this, you can (and should) handle this in RMAN: sqlplus / as ...


3

It depends on the configuration you have in place. Oracle uses log shipping (as default) to feed the transactions into the standby database. In order to make sure that ALL transactions are recovered on the standby database, we should make sure that the primary database is running in FORCE LOGGING mode. Switching FORCE LOGGING may cause a difference in ...


3

To return all agents with their commissions and commission percentages use an analytic function with no analytic clause so that the partition is over the whole table: SELECT Agent, commission, 100* commission / (SUM(commission) OVER ()) "% Commission" FROM commissions; As I learned from René Nyffenegger (+1) the ratio_to_report function tightens this ...


3

Here is one way, not using analytics so pretty inefficient: with w as (select level as foo, mod(ora_hash(level),4) as bar from dual connect by level<9) select foo, bar, (select max(foo) from w where foo<ww.foo and bar>ww.bar) from w ww;


2

You could try the following query, sum(commission) will only be calculated once: WITH TOTAL_COMMISSION AS (SELECT SUM(COMMISSION) AS TOTAL FROM AGENTS) SELECT A.AGENT_NAME, A.COMMISSION, ((A.COMMISSION/T.TOTAL)*100) AS "% COMMISSION" FROM AGENTS A, TOTAL_COMMISSION T;


1

I found this just before posting. The difference is the linux environment setting for NLS_LANG. It was set to American_America.UTF8 on the system it added padding to. Unsetting this on one and setting this on the other swapped where the behavior.


1

The reason the task was hanging was that I had run a separate update statement against MY_TABLE in a SQL Developer client window, and had not completed the transaction. Because the transaction was still open, the Execute SQL Task could not complete. Committing the transaction in SQL Developer allowed the SSIS package to complete, however, it still generated ...


1

Here is another answer that works for nine rows, but not for more. I'm posting it in case it sparks an idea. WITH w AS (SELECT level as foo, mod(ora_hash(level),4) as bar FROM dual CONNECT BY Level<9) SELECT foo, bar, Case When Highest > Bar Then MAX(foo) OVER (PARTITION BY Highest ORDER BY foo RANGE BETWEEN UNBOUNDED PRECEDING AND 1 ...


1

Untested: with W as (select level as foo, mod(ora_hash(level),4) as bar from dual connect by level<9) select foo, bar, case when prev_bar > bar then prev_foo else null end as prev_bar from (select foo, bar, lag(foo) over (order by foo) as prev_foo, lag(bar) over (order by foo) as prev_bar from ...



Only top voted, non community-wiki answers of a minimum length are eligible