Learn about sql oracle case statement in where clause, we have the largest and most updated sql oracle case statement in where clause information on alibabacloud.com
Oracle Decode () functions and case statements are all common use, so what is the difference between them? Here's a detailed description of
The difference between the Oracle Decode () function and the case statement for your reference.
First, give 2 simple examples to comp
SQL server's case when then statement, sqlcase
-- Select DataName,-- Case Code---- When 'w01' then 'male'---- When 'w07 'then' female'---- Else 'others'---- End as Code-- From dbo. HistoryData
Select DataName,(CaseWhen Code = 'w01' then 'male'When Code = 'w07 'then' female'Else 'others'End)From HistoryData
The access d
WHEN salary> 500 AND salary WHEN salary> 600 AND salary WHEN salary> 800 AND salary Else null end;
3. Other IF-THEN-ELSE implementations3.1 DECODE () functionCopy codeThe Code is as follows: select decode (sex, 'M', 'male', 'F', 'female ', 'unknown ')From employees;
It seems that only Oracle provides this function, and ansi SQL is not supported, and the syntax is not C
Case has two formats. Simple case functions and case search functions.1. Simple case Functions
Case sex when '1' then' male 'when' 2' then' female 'else' others' end
2. Case search functions
principle, the connection between tables must be written to the left of other where conditions , and those conditions that can filter out the maximum number of records must be written to the right of the WHERE clause .Example: Query employee's number, name, salary, department nameSelect Emp.empno,emp.ename,emp.sal,dept.dname from emp,dept where (Emp.deptno = Dept.deptno) and (Emp.sal > 1 500)Avoid using the * number in the SELECT clauseDuring the par
, using EXISTS (or not EXISTS) will usually improve the efficiency of the query. In a subquery, the NOT IN clause performs an internal sort and merge. In either case, not in is the least effective (because it performs a full table traversal of the table in the subquery). To avoid using not, we can change it to an outer join (Outer Joins) or not EXISTS. Example: (efficient) SELECT * from EMP (base table) wh
Keyword: case
Case has two formats. Simple Case functions and Case search functions.
-- Simple Case Function
CASE sex
WHEN '1' THEN 'male'
WHEN '2' THEN 'female'
ELSE 'others' END
-- Case
computed.
10. Reduce table queries:
In SQL statements containing subqueries, pay special attention to reducing the number of queries to the table.
Select tab_name from tables where (tab_name, db_ver) = (select tab_name, db_ver from tab_columns where version = 604)
11. Use the table alias:
Reduces SQL statement parsing time and syntax errors caused by the same Co
You may be familiar with the IF Else statement, which is used to control the process. A CASE statement statement has a similar effect in the world of SQL. The following is a simple introduction to the use of case statements. Consi
Analysis of two methods of SQL statement row/column conversion case... the application of the when function and the limit function bitsCN.com
/* Create a database */Create database tmpGoUSE tmpGo
/* Create a database Test table */Create table [Scores]([ID] int identity (1, 1)Primary key,[Student] VARCHAR (20 ),[Subject] VARCHAR (30 ),[Score] FLOAT)
Go
Truncate ta
Label:Youdao Written Questions: Server Monitoring table Server_status, when the server state occurs server_status changes in the data table will be inserted a record, state 0 indicates that the shutdown 1 is normal, SQL query Server A's downtime start time and end time, There is a status record for more than one server in the table.
svr_id
Svr_name
Status_time
STATUS
1
A
2013-10-31 00:00:00
--(' table ', ' line ', ' ods_xx ') all the tables and users in this case need to be capitalized.If the table name user name is not written, the error is reported: the object "EMP" is of type table, not found in the scheme "Scott"Pro-Test code:Set long 90000;Set Linesize 4000;Set pagesize 4000;Spool Table.sqlSelect Dbms_metadata.get_ddl (' TABLE ', ' line ', ' ods_xx ') from dual;Spool off1, once want to query the table additional log: dbms_metadata.g
incorrect execution plan and affect the database performance.
It should be noted that, although the CBO feature is becoming more and more powerful with the launch of the new Oracle version, it is not a panacea for treating all kinds of diseases, otherwise DBA is no longer needed, then I am miserable !!! In fact, the execution plan of a statement may change with the hardware environment and application data
Label:Keyword is case insensitiveFor example, select, from, case can beIdentifiers are case-sensitiveFor example, table name, column nameIf the identifier is not double-quoted, the default is uppercaseIdentifiers are executed in the original case if double quotation marks are addedHowever, when the table name is enclos
The table game_info is known as follows
Date_info
Result_info
2018-2-4
Win
2018-2-4
Lose
2018-2-4
Win
2018-2-4
Lose
2018-2-5
Lose
2018-2-5
Lose
2018-2-5
Lose
Q. How do I find the following results?
Date_info
Win
Lose
2018-2-4
2
2
2018-2-5
0
3
Oracle SQL statement execution sequenceThe SQL syntax is analyzed from right to left.1. SQL statement execution steps:1) analyze the syntax and analyze whether the syntax of the statement
This document is compiled and summarized based on relevant information. It mainly describes the most authoritative and correct methods and steps for obtaining SQL statement execution plans in Oracle databases.
This document is compiled and summarized based on relevant information. It mainly describes the most authoritative and correct methods and steps for obtain
time the annual salary SELECT ename, job, (sal+300 *12+sal income from emp;--Company will make a one-month base salary SELECT empno at the end of each year | ',' || Ename from emp;--can also use the "| |" in a simple query
The field of the connection query. SELECT ' Employee number is: ' | | Empno | | ' The employee's name is: ' | | ename | | ', the basic salary is: ' | | Sal | | ', the position is: ' | | Job | | '!
' Employee information from emp;--requires that the current database be disp
Java
The date indicated by the previous write Java code is YYYY-MM-DD hh: mm: SS
Mm (uppercase for month) and mm (lowercase for minutes)
SQL
But in SQL, the situation is completely different.
(Cause: the reason is that SQL statements are case-insensitive, so both mm and mm cannot be used, and the month should be def
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.