![]() ![]() In order to transform this result set into a single row with fourĬolumns, we need to fabricate a column for each quarter of the yearĪnd, within each column, sum only those records whose order dateįalls in the desired quarter. SELECT TO_CHAR(order_dt, 'Q') sales_quarter, SUM(sale_price) tot_sales FROM cust_order WHERE order_dt >= TO_DATE('0','DD-MON-YYYY') AND order_dt < TO_DATE('0','DD-MON-YYYY') GROUP BY TO_CHAR(order_dt, 'Q') ORDER BY 1 The week or months of the year, but you want the result set toĬontain one row with N columns rather than N rows with two columns.Ĭonsider the following query, which aggregates sales data for each SELECT DECODE(10,10,20,30) INTO A FROM DUAL ĭbms_output.Performing aggregations over a finite set of values, such as days of To call decode statement using Plsql, we have to assign decode function to a variable to store the result. If we try to call decode statement directly with in the pl-sql block, it throws exception stating that PLS-00221: ‘DECODE’ is not a procedure or is undefined. 00000 - "column not allowed here" Calling DECODE statement using pl-sql block DECLARE Output SQL Error: ORA-00984: column not allowed hereĠ0984. If we observe the above insertion query with decode function, we are trying to use FEE column name in decode function which is illegal. if expression is allowed to convert then decode will returns the result otherwise throws the exception stating as above output.Įxample queries to use DECODE function in DML statements with where condition select * from std where fee=decode(fee,1500,1500,28500) UPDATE std SET FEE=DECODE(fee,1500,1000,28500) WHERE fee=decode(fee,1500,1500,28500) DELETE FROM STD WHERE fee=decode(fee,1500,1500,28500) INSERT INTO STD (STDNO, When we try to compare expression of one data type with another data type using decode function, it will try to convert the first expression into searchable expression type format. SELECT DECODE((SELECT NULL FROM DUAL),(SELECT NULL FROM DUAL),(SELECT 10 FROM DUAL),(SELECT 20 FROM DUAL)) RESULT FROM DUAL result SELECT DECODE(NULL,NULL,(SELECT 10 FROM DUAL),(SELECT 20 FROM DUAL)) RESULT FROM DUAL It will not be true if we compare NULL with other NULL value using any other statements or functions because NULL cannot be compared with another NULL value. SELECT DECODE(NULL,NULL,10,20) RESULT FROM DUAL resultĬomparing a NULL with another NULL will return true using Decode statement. In the above query expression 10 is not matching with any other expression in the decode function and returning default value 60 as output. ![]() 2, 40 are the searchable expressions and 60 is the default value that returns if expression is not matches any other searchable expression in the Decode function.In the above query 10 is the expression that is trying search for matching expression in the decode function.If comparison value of expression not matches with any other expression then it results default value as output.Decode functions compare the expression with other expressions like search1, search2, … searchN till the expression matches with other expression and returns the result that matches with expression.SELECT DECODE(10,50,20,30) result FROM DUAL resultĭECODE(expression,search1, result1, search2, result2,…., searchN, resultN, default) SELECT DECODE(10,10,20,30) result FROM DUAL If expr1 equals to expr2 then decode functions returns Result 1 otherwise Result 2 as output. Syntax: DECODE(expr1,expr2,result 1, result2) DECODE function cannot call directly with in PL-SQL block.and in insertion statement when try to insert values into a table Decode can also use with where condition in select, update and Delete statements.Select query can be used with in decode function.DECODE considers two nulls to be equivalent.DECODE result type is first decoded expression type, all others are.Decode statement can be used with only SQL DML statements like SELECT, INSERT, UPDATE, DELETE.Decode Function is used to compare values in the decode function and returns the result that match with comparison value.are the expression that evaluates to true or false with some comparison operators like, ,=,=,!=, IS null, is not null.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |