![]() ![]() We need to send this query a date.and this is what it should do: 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 ‘None of the above’ is our default value, in case our expression is not equal to any search value.Yes, it is not any more readable. The DECODE function compares our expression with all the search values and returns the result corresponding to that search value. In this query, our expression is 1 + 1 and 1, 2, 3 are search values while ‘One’, ‘Two’, ‘Three’ are results corresponding to each search value. Lets understand how a DUAL function works with the help of a simple example: Examples 1.DECODE Function with DUAL Table If default value is omitted from the query, the DECODE function returns NULL. If expression does not match any of the search value, then the query returns the default value. The data type of the returned value is the data type of the first result value that is returned. If we don’t provide default value then query returns NULL. ![]() If expression is equal to search1 is returned, if it equal to search2 then result2 is returned and so on.ĭefault – If expression is not equal to any search value, then the query returns the default value. ![]() Result – If expression value is equal to the search value, then the DECODE function returns result. The data types of all subsequent search values are converted to the data type of first search value. Search – These are the values against which expression is compared. The data type of this value is converted to the data type of the first search value before comparing. Expression – This is the value that we provide to compare with other search values. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |