create or replace view view_fee_costitem_msearch as
select a.serialno SERIALNO,c.traf_spec TRANS_MODEL, b.fee TOTAL_COST ,
to_date(decode(ie_type,'DIRECT_IMPORT',atad,
'INDIRECT_IMPORT',c_date,
'SD_IMPORT',atad,
'DIRECT_EXPORT',c_date,
'INDIRECT_EXPORT',c_date,
'SD_EXPORT',c_date,0),'YYYYMMDD') as t_date
from ie_head a,ie_fee b,bdtransf c
where a.serialno=b.serialno and a.trans_mode=c.traf_code;我这视图生成后 不能以t_date 进行查询select * from view_fee_costitem_msearch t where T_DATE<to_date('20110901','YYYYMMDD')
输入值对于日期格式不够长?
select a.serialno SERIALNO,c.traf_spec TRANS_MODEL, b.fee TOTAL_COST ,
to_date(decode(ie_type,'DIRECT_IMPORT',atad,
'INDIRECT_IMPORT',c_date,
'SD_IMPORT',atad,
'DIRECT_EXPORT',c_date,
'INDIRECT_EXPORT',c_date,
'SD_EXPORT',c_date,0),'YYYYMMDD') as t_date
from ie_head a,ie_fee b,bdtransf c
where a.serialno=b.serialno and a.trans_mode=c.traf_code;我这视图生成后 不能以t_date 进行查询select * from view_fee_costitem_msearch t where T_DATE<to_date('20110901','YYYYMMDD')
输入值对于日期格式不够长?
to_date(decode(ie_type,'DIRECT_IMPORT',atad,
'INDIRECT_IMPORT',c_date,
'SD_IMPORT',atad,
'DIRECT_EXPORT',c_date,
'INDIRECT_EXPORT',c_date,
'SD_EXPORT',c_date,0),'YYYYMMDD')
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /Table created.SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('01','Jason', 'Martin', to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto', 'Programmer')
3 /1 row created.SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('02','Alison', 'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')
3 /1 row created.SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('03','James', 'Smith', to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver','Tester')
3 /1 row created.SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('04','Celia', 'Rice', to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver','Manager')
3 /1 row created.SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('05','Robert', 'Black', to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver','Tester')
3 /1 row created.SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('06','Linda', 'Green', to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78,'New York', 'Tester')
3 /1 row created.SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('07','David', 'Larry', to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78,'New York', 'Manager')
3 /1 row created.SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('08','James', 'Cat', to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78,'Vancouver', 'Tester')
3 /1 row created.SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester8 rows selected.SQL>
SQL>
SQL> SELECT count(*), DECODE(city,null,'Null',city) Citys
2 FROM employee
3 GROUP BY city; COUNT(*) CITYS
---------- ----------
2 New York
1 Toronto
5 VancouverSQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /Table dropped.SQL>
SQL>2. DECODE(x, search, result, default)
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /Table created.SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('01','Jason', 'Martin', to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto', 'Programmer')
3 /1 row created.SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('02','Alison', 'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')
3 /1 row created.SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('03','James', 'Smith', to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver','Tester')
3 /1 row created.SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('04','Celia', 'Rice', to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver','Manager')
3 /1 row created.SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('05','Robert', 'Black', to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver','Tester')
3 /1 row created.SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('06','Linda', 'Green', to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78,'New York', 'Tester')
3 /1 row created.SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('07','David', 'Larry', to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78,'New York', 'Manager')
3 /1 row created.SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('08','James', 'Cat', to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78,'Vancouver', 'Tester')
3 /1 row created.SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester8 rows selected.SQL>
SQL>
SQL> -- DECODE(x, search, result, default)
SQL>
SQL> SELECT id,
2 decode(id,
3 01, 'IBM',
4 02, 'Microsoft',
5 03, 'Hewlett Packard',
6 'Gateway') result
7 FROM employee;ID RESULT
---- ---------------
01 IBM
02 Microsoft
03 Hewlett Packard
04 Gateway
05 Gateway
06 Gateway
07 Gateway
08 Gateway8 rows selected.SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /Table dropped.SQL>
SQL>
SQL>
SQL>
SQL> --Using the DECODE() Function
SQL>
SQL> --You use DECODE(value, search_value, result, default_value) to compare value with search_value. If the values are equal, DECODE() returns result, otherwise default_value is returned.
SQL>
SQL> --DECODE() allows you to perform if-then-else logic in SQL without having to use PL/SQL.
SQL>
SQL>
SQL> SELECT DECODE(1, 1, 2, 3)
2 FROM dual;DECODE(1,1,2,3)
---------------
2SQL>4. DECODE() to compare 1 to 2, and since they are not equal 3 is returned
SQL>
SQL> --DECODE() to compare 1 to 2, and since they are not equal 3 is returned:
SQL>
SQL> SELECT DECODE(1, 2, 1, 3)
2 FROM dual;DECODE(1,2,1,3)
---------------
3SQL>
5. If city equals 'New York', the string 'You are in New York' is returned, otherwise the string 'Not' is returned
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /Table created.SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('01','Jason', 'Martin', to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto', 'Programmer')
3 /1 row created.SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('02','Alison', 'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')
3 /1 row created.SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('03','James', 'Smith', to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver','Tester')
3 /1 row created.SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('04','Celia', 'Rice', to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver','Manager')
3 /1 row created.SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('05','Robert', 'Black', to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver','Tester')
3 /1 row created.SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('06','Linda', 'Green', to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78,'New York', 'Tester')
3 /1 row created.SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('07','David', 'Larry', to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78,'New York', 'Manager')
3 /1 row created.SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('08','James', 'Cat', to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78,'Vancouver', 'Tester')
3 /1 row created.SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /ID FIRST_NAME LAST_NAME START_DATE END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- ----------- ----------- ---------- ---------- ---------------
01 Jason Martin 1996-JUL-25 2006-JUL-25 1234.56 Toronto Programmer
02 Alison Mathews 1976-MAR-21 1986-FEB-21 6661.78 Vancouver Tester
03 James Smith 1978-DEC-12 1990-MAR-15 6544.78 Vancouver Tester
04 Celia Rice 1982-OCT-24 1999-APR-21 2344.78 Vancouver Manager
05 Robert Black 1984-JAN-15 1998-AUG-08 2334.78 Vancouver Tester
06 Linda Green 1987-JUL-30 1996-JAN-04 4322.78 New York Tester
07 David Larry 1990-DEC-31 1998-FEB-12 7897.78 New York Manager
08 James Cat 1996-SEP-17 2002-APR-15 1232.78 Vancouver Tester8 rows selected.
SQL>
SQL>
SQL> --If city equals 'New York', the string 'You are in New York' is returned, otherwise the string 'Not' is returned:
SQL>
SQL> SELECT id, city,
2 DECODE(city, 'New York', 'You are in New York',
3 'Not')
4 FROM employee;ID CITY DECODE(CITY,'NEWYOR
---- ---------- -------------------
01 Toronto Not
02 Vancouver Not
03 Vancouver Not
04 Vancouver Not
05 Vancouver Not
06 New York You are in New York
07 New York You are in New York
08 Vancouver Not8 rows selected.SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /Table dropped.SQL>
SQL>6. Multiple search and result parameters to DECODE()
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /Table created.SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('01','Jason', 'Martin', to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto', 'Programmer')
3 /1 row created.SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('02','Alison', 'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')
3 /1 row created.SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('03','James', 'Smith', to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver','Tester')
3 /1 row created.SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('04','Celia', 'Rice', to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver','Manager')
3 /1 row created.SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('05','Robert', 'Black', to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver','Tester')
3 /1 row created.SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('06','Linda', 'Green', to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78,'New York', 'Tester')
3 /1 row created.SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('07','David', 'Larry', to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78,'New York', 'Manager')
3 /1 row created.SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('08','James', 'Cat', to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78,'Vancouver', 'Tester')
3 /1 row created.SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /ID FIRST_NAME LAST_NAME START_DATE END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- ----------- ----------- ---------- ---------- ---------------
01 Jason Martin 1996-JUL-25 2006-JUL-25 1234.56 Toronto Programmer
02 Alison Mathews 1976-MAR-21 1986-FEB-21 6661.78 Vancouver Tester
03 James Smith 1978-DEC-12 1990-MAR-15 6544.78 Vancouver Tester
04 Celia Rice 1982-OCT-24 1999-APR-21 2344.78 Vancouver Manager
05 Robert Black 1984-JAN-15 1998-AUG-08 2334.78 Vancouver Tester
06 Linda Green 1987-JUL-30 1996-JAN-04 4322.78 New York Tester
07 David Larry 1990-DEC-31 1998-FEB-12 7897.78 New York Manager
08 James Cat 1996-SEP-17 2002-APR-15 1232.78 Vancouver Tester8 rows selected.SQL>
SQL>
SQL> --Multiple search and result parameters to DECODE()
SQL>
SQL> SELECT id, first_name,
2 DECODE(id,
3 1, 'A',
4 2, 'B',
5 3, 'C',
6 4, 'D',
7 'Rest')
8 FROM employee;ID FIRST_NAME DECO
---- ---------- ----
01 Jason A
02 Alison B
03 James C
04 Celia D
05 Robert Rest
06 Linda Rest
07 David Rest
08 James Rest8 rows selected.SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /Table dropped.SQL>
SQL>
SQL>
'INDIRECT_IMPORT', c_date,
'SD_IMPORT', atad,
'DIRECT_EXPORT', c_date,
'INDIRECT_EXPORT', c_date,
'SD_EXPORT', c_date,
0 )-- "0"是数值类型,不是日期类型,不能将“0”插入一个日期类型字段,更不能将“0”与日期数据去比较,
-- 这就是出错的原因!-- 解决办法:将“0”改成 NULL (空值) 或一个 日期类型的数值!
你看一下你 decode 的几个判断,有 3 种值,atad, c_date, 0
这 3 种值当中,至少 0 是不可能使用 to_date 函数的,
你必须保证 decode 的所有返回值类型相同,并且也是 YYYYMMDD 这样的字符串,否则你 to_date 会出问题
-- 还有:你的 atad、c_date 字段是字符串表示日期的话,其格式要统一:'YYYYMMDD'