sql oracle case statement in where clause

The oracle SQL statement reads data from this month and month. [SQL] -- from Monday to Sunday in China is from Sunday to Saturday select to_char (sysdate-1, 'D') from dual; -- Take the day of the week in China minus one week in foreign countries -- [SQL] -- select * from

Oracle Link Http://www.eygle.com/case/sql_trace_2.htm Problem Description: Many times When we do database operations such as drop User,drop table and so on, often encounter such a mistake Ora-00604:error occurred at recursive SQL level 1. Such hints, many times, do not have the slightest use. This case provides

Oracle queries table space usage SQL statement SQL1: www.2cto.com -- 1. queries the table space name and size SELECT t. tablespace_name, round (SUM (bytes/(1024*1024), 0) ts_size FROM dba_tablespaces t, dba_data_files d WHERE t. tablespace_name = d. tablespace_name group by t. tablespace_name; -- 2. view the name and size of the tablespace physical file. SELECT t

Create or Replace procedure Sum_info is I integer;temp1 varchar2 (100) temp2 varchar2 T1 DATE;T2 ); v_sqm varchar2 (+); U_name t_temp.u_name%type;t_name t_temp.t_name%type;c_name t_temp.c_name%type;cursor tb_list Is select U_name,t_name,c_name from T_temp;beginopen tb_list; Loopt1:=sysdate;fetch tb_list into u_name,t_name,c_name;v_sql:= ' select min (' | | c_name| | ') From ' | | u_name| | '. ' | | t_name;v_sqm:= ' select Max (' | | c_name| | ') From ' | | u_name| | '. ' | | T_name;--dbms_output

order.30, inquires the elective number is "3-105" and the result is higher than the elective number is "3-245" the course schoolmate's CNO, Sno and degree.31. Check the name, sex and birthday of all teachers and classmates.32, query all "female" teacher and "female" classmate's name, Sex and birthday.33. Check the scores of students who have a lower average score than the course.34. Check the Tname and depart of all classroom teachers.Check the Tname and depart of all teachers who have not lec

service life, the oldest employees in the front rowTen to find the employee who was hired on the third day of the month (e.g. 2009-5-29) One and find out who they hired 25 years ago.12. All employee names are preceded by Dear, and the first letter of the name is capitalized - and find employees with names of 5 letters14. Find employees whose names do not have the letter R the , display the first word of all employees ' names - , the average wage of employees under each department, >50017, stati

Alter session Set Nls_date_format = ' Yyyy-mm-dd,hh24:mi:ss '; set linesize 400col sql_id format a20col name format A20col da tatype_string format a14col value_string format A20select sql_id,name, datatype_string, last_captured,value_string from V$sql_bind_capture where sql_id= ' DXFCACN4T4PPW ' ORDER by last_captured,position;--is executed on the problematic instance, please replace the sql_id yourself, At this point the SQL is still in the shared po

Tags: Demand alert tle Export data CAL name ROM using modelSome time ago there is a need to export the database of 500 table structure, using the Plsqldev tool can also export the build table statements, but need to manually a table one table, very laborious. I wrote a splice of SQL.Select ' Select Dbms_metadata.get_ddl (' | | segment_type| | "", "" | | segment_name| | "", "" | | Owner | | From dual, ' from dba_segments where segment_name= ' Ale_criticaldegree 'or Segment_name=trim (' Ale_critic

Sort after adding nulls last in descending order, put null in the finalSelect To_char (sysdate, ' Q ') from dual;Dual pseudo-table public synonym because it has a record for easy readingSelect RowNum, ' A ' | | Rownum,mod (rownum,2) from dual connect by rownumSelect Rownum,to_date (' 2016-05-01 ', ' YYYY-MM-DD ') from dual connect by rownumSelect sysdate,sysdate+1/24,sysdate+1/1440 from dual;Select To_char (sysdate, ' Fmyyyy-mm-dd h24:mi:ss ') from dual;Select 100+NVL (null,0) from dual;Select c

Label:--SDE User Login Execute the following statementGrant INSERT, UPDATE, delete on sde.table_registry to public; Grant INSERT, UPDATE, delete on sde.column_registry to public; Grant INSERT, UPDATE, delete on sde.geometry_columns to public; Grant INSERT, UPDATE, delete on sde.layers to public; Grant INSERT, UPDATE, delete on sde.spatial_references to public; Grant INSERT, UPDATE, delete on sde.st_spatial_references to public;Open permissions SQL

Instance description Data Table Structure (primary key in red) ==>Master table: test_part_cost_tab (item table)Part_no varchar2 (20)Part_name varchar2 (50) Slave table: test_part_cost_dt_tab (item cost table)Part_no varchar2 (10)Cost_id varchar2 (5)Cost number Data ==>Master table data:Part_no part_name1 1000 name10002 1001 name1001Slave table:Part_no cost_id cost1 1000 100 1.12 1000 200 1.23 1000 300 1.34 1000 321 1.3215 1001 100 2.1 crosstab chart ==> part_no part_name cost_100 cost_200

PL/SQL block to display detailed information of the employee with the given employee number 1 SQL> Declare 2 v_empno emp. empno % Type;/* declare the variable v_empno, % type: Make the Type of the variable the same as the empno type in the emp table */ 3 v_emprecord emp % Rowtype;/* declare the variable v_emprecord, % rowtype: Make the variable of the same type as the entire row in the emp table */ 4 Begin

statement, the statement formulated by the reserved word else is executed when the related condition evaluates to NULL.4. ElseIf statementsElseIf structureIF Conditiono 1 ThenSTATEMENT 1;ELSEIF CONDITION 2 ThenSTATEMENT2;ELSEIF CONDITION 3THENSTATEMENT3;....ELSESTATEMENT N;END IF;The reserved word if identifies the ElseIf structure to begin with. Condidtion 1 to Condidtion N is a series of conditions in wh

Query the connection information in the database. SQL _COMMAND is an SQL statement that can release A process: selectA. SID, B. SPID, a. SERIAL #, A. lockwait, A. USERNAME,. Query the connection information in the database. SQL _COMMAND is an SQL

Oracle database, your queries start to use the memory. The number of idle records divided by the total number of records in the x $ BH table (that is, the total number of allocated data block buffers. Note that you must run the query with the Sys permission. In addition, having a large number of idle buffers is not necessarily the best environment. 5%-10% is the best. When the idle ratio is higher than 25%, the data buffer is too large and may waste

In actual application, multiple insert statements may be executed at a time. I know two ways to share with you: 1. Write the statement block in tools such as PLSQL between begin end, for example: Begin Insert into table values (....); Insert into table values (....); Insert into table values (....); Insert into table values (....); End; Remember to add a semicolon after end 2. Run the SQL file by u

Label:1. Execute sql:select/*+ Parallel (a,4) * * from Tf_f_user a where rownum2. How to view the parallel number of the SQL statement:Select T.sql_text,t.sql_id,s.sid from V$sql t,v$session s where t.sql_id=s.sql_id and t.sql_text like '%rownumExecuting the above SQL statement found to be 4 articles.3, if the executio

Select NVL (SUM (case create_date_time>=to_date (' 2014-11-24 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ') and Create_date_ Timeand Create_date_timeFrom T_pay;Suppose today is 2014-11-24Oracle one SQL statement statistics top-up list today, yesterday, three days before the day before the recharge record

In the case of a large record, the not in query will certainly be slow and unacceptable. Like what:SELECT A.* fromWHERE A.user_id not in (SELECT B.user_id from Table_b)the best way to find it now is to check it in seconds. For example, query the DEPT_ID data with table A and b not available:SELECT A.*from tb_dept A, Tb_dept_lev BWHERE= b.dept_id (+) and is NULLThe (+) number can be understood as: + is added, which table has a plus sign, and this t

Go to sqlplusSQL> set timing onSQL>SQL> select count (*) from comm_human_role;COUNT (*)----------866Elapsed: 00:00:00. 05The preceding digital outputs are: Hours: Minutes: Seconds. Milliseconds.It takes 0.05 seconds to execute, which is equivalent to 50 milliseconds.If the time is as follows: 00: 03: 235.78The total time is 235.78 seconds, less than 4 minutes, so it is 3 minutes (235.78 seconds is about 3 minutes 55.78 seconds)# Set screen row widthSQ