Select sum(qty) from t_imported_raw_matl Select sum(qty) from t_indegenious_raw_matl SUM(CASE WHEN TRUNC(DATE_OF_CONS,'MM')=TRUNC(SYSDATE,'MM') Select SUM(CASE WHEN TRUNC(DATE_OF_CONS)=TRUNC(SYSDATE) Other 2 tables are 1)t_indegenious_raw_matl.ġ)select sum(qty) from t_indegenious_raw_matl Ģ)select sum(qty) from t_imported_raw_matl įor example i got table t_raw_matl in which i have to insert. Iam trying to insert into single table from 2 select statements. PL/SQL procedure successfully select (case (select demo_pkg.f(deptno)Īnd there is the searched case expression that gets it back to normal. Here, we have 2x the calls (but still not exec demo_pkg.cnt := 0 PL/SQL procedure successfully select (caseĤ where deptno = emp.deptno) = 10 then 'a'ħ where deptno = emp.deptno) = 20 then 'b' It apparently built a hash table out there (or some other structure) and just joined to exec demo_pkg.cnt := 0 We might have expected it to do this 500 times but no - only 10. So here, it executed our query 10 times - the number of dept rows (if you vary the number of rows in dept - this count will vary accordingly). PL/SQL procedure successfully select decode( (select demo_pkg.f(deptno)ģ where deptno = emp.deptno), 10, 'a', 20, 'b', 'rest' ) as testĤ826 bytes received via SQL*Net from clientĥ00 rows exec dbms_output.put_line( 'cnt = ' || demo_pkg.cnt ) Package create or replace package body demo_pkgĤ function f( x in number ) return numberģ select rownum empno, mod(rownum,10) deptnoĥ where rownum set autotrace exec demo_pkg.cnt := 0 In any case, the decode you have would be superior to the case statement performance wise HOWEVER, in 9i, there is a searched case (demonstrated below) which corrects that.Ĭonsider this create or replace package demo_pkgĤ function f( x in number ) return number The answer is "none of the above" - it apparently doesn't do the case per record as you and I understand it. Well, here is a pretty cool optimization. I would still go with CASE select empno, ename, SAL,Ĩ 2500, 'Very Less', 'Above 2500' ), 'Above 4000' ),ĮMPNO ENAME SAL DECODE(SIG DECODE(GRE CASEWHENSAħ369 SMITH 800 Very Less Very Less VERY LESSħ499 ALLEN 1600 Very Less Very Less VERY LESSħ521 WARD 1250 Very Less Very Less VERY LESSħ566 JONES 2975 Above 2500 Above 2500 ABOVE 2500ħ654 MARTIN 1250 Very Less Very Less VERY LESSħ698 BLAKE 2850 Above 2500 Above 2500 ABOVE 2500ħ782 CLARK 2450 Very Less Very Less VERY LESSħ788 SCOTT 3000 Above 2500 Above 2500 ABOVE 2500ħ839 KING 5000 Above 4000 Above 4000 ABOVE 4000ħ844 TURNER 1500 Very Less Very Less VERY LESSħ876 ADAMS 1100 Very Less Very Less VERY LESSħ900 JAMES 950 Very Less Very Less VERY LESSħ902 FORD 3000 Above 2500 Above 2500 ABOVE 2500ħ934 MILLER 1300 Very Less Very Less VERY LESS
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |