Home » SQL & PL/SQL » SQL & PL/SQL » Problem with date values using NULLIF and (Apex, Oracle 10g)
| Problem with date values using NULLIF and [message #523643] |
Mon, 19 September 2011 10:42  |
 |
wakproductions_fdb
Messages: 15
Registered: September 2011
Location: Indiana
|
Junior Member |
|
|
Hi All,
I am having a date formatting problem with an INSERT INTO command in Oracle 10g Express edition. I have a table where I am trying to add a DateTime value. At times this DateTime value can be '00000000', which I want to convert to a null in that case using NULLIF. Here is my sample code:
INSERT INTO RMCRPB0_MEDICARE_HCPC_PB_PRICE (HCPC, NDC, HCPC_PBC1, HCPC_PBP1, HCPC_PBC2, HCPC_PBP2)
SELECT
'J0895',
'00000000000',
TO_DATE(NULLIF('20110701', '00000000'), 'YYYYMMDD'),
9.786,
TO_DATE(NULLIF('20110401', '00000000'), 'YYYYMMDD'),
0
FROM ET_RMCRPB0_MEDICARE_HCPC_PB_PR WHERE HCPC IN ('J0895');
The problem is that the data being entered into the table RMCRPB0_MEDICARE_HCPC_PB_PRICE for HCPC_PBC1 and HCPC_PBC2 becomes 01-Jul-36 (1936) and 01-Apr-36 (1936) respectively.
When I remove the NULLIFs, this problem mysteriously disappears and the correct dates show.
I've been having a really difficult time troubleshooting this change of date values. I checked NLS_SESSION_PARAMETERS.NLS_DATE_FORMAT and found it to be "DD-MON-RR". Not sure if this has anything to do with the problem.
Any help pointing me into the direction I should look would be much appreciated.
Thanks.
-Winston Kotzan
|
|
|
|
| Re: Problem with date values using NULLIF and [message #523645 is a reply to message #523643] |
Mon, 19 September 2011 10:50  |
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Can you please read and follow How to use [code] tags and make your code easier to read?
I can only assume the problem is with different data to what you think is causing it. The lack of a 3 or 6 in either data should make it impossible for your examples to give the erronous output, no matter what date formats are used.
What does this give:
SELECT
TO_DATE(NULLIF('20110701', '00000000'), 'YYYYMMDD') nullif1,
TO_DATE('20110701', 'YYYYMMDD') date1,
TO_DATE(NULLIF('20110401', '00000000'), 'YYYYMMDD') nullif2,
TO_DATE('20110401', 'YYYYMMDD') date2
FROM dual;
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Problem with date values using NULLIF and [message #523653 is a reply to message #523649] |
Mon, 19 September 2011 11:47 |
|
wakproductions_fdb
Messages: 15
Registered: September 2011
Location: Indiana
|
Junior Member |
|
|
I'm still an Oracle n00b, so I don't know what a DB trigger is. I do not believe I have any on the table.
Using a CASE seems to take care of the problem. Here is some new code I created with the respective output:
INSERT INTO RMCRPB0_MEDICARE_HCPC_PB_PRICE (HCPC, NDC, HCPC_PBC1, HCPC_PBP1, HCPC_PBC2, HCPC_PBP2)
(SELECT
HCPC,
NVL(NDC, '00000000000'),
TO_DATE(NULLIF('20110701', '00000000'), 'YYYYMMDD'),
HCPC_PBP1,
CASE '20110401'
WHEN '00000000' THEN null
ELSE TO_DATE('20110401', 'YYYYMMDD')
END,
HCPC_PBP2
FROM ET_RMCRPB0_MEDICARE_HCPC_PB_PR WHERE HCPC IN ('J0895', 'J1050'));
Values found in the table:
HCPC NDC HCPC_PBC1 HCPC_PBP1 HCPC_PBC2 HCPC_PBP2
J0895 00000000000 01-JUL-36 9.786 01-APR-11 10.672
J1050 00000000000 01-JUL-36 0 01-APR-11 9.96
As you can see, that NULLIF on HCPC_PBC1 remains problematic. Could this be some kind of bug within Oracle?
Thanks for the help!
|
|
|
|
|
|
| Re: Problem with date values using NULLIF and [message #523656 is a reply to message #523655] |
Mon, 19 September 2011 12:21 |
|
wakproductions_fdb
Messages: 15
Registered: September 2011
Location: Indiana
|
Junior Member |
|
|
Here is a test case. The SQL code is the following:
CREATE TABLE RMCRPB0_MEDICARE_HCPC_PB_PRICE (
HCPC VARCHAR(5) NOT NULL,
NDC VARCHAR(11) NOT NULL,
HCPC_PBC1 DATE,
HCPC_PBP1 NUMBER(16,5),
HCPC_PBC2 DATE,
HCPC_PBP2 NUMBER(16,5),
HCPC_PBC3 DATE,
HCPC_PBP3 NUMBER(16,5),
HCPC_PBC4 DATE,
HCPC_PBP4 NUMBER(16,5),
HCPC_PBC5 DATE,
HCPC_PBP5 NUMBER(16,5),
HCPC_PBC6 DATE,
HCPC_PBP6 NUMBER(16,5),
HCPC_PBC7 DATE,
HCPC_PBP7 NUMBER(16,5),
HCPC_PBC8 DATE,
HCPC_PBP8 NUMBER(16,5),
CONSTRAINT PK_RMCRPB0_MEDICARE_HCPC_PB_PR PRIMARY KEY (HCPC, NDC)
);
CREATE OR REPLACE DIRECTORY xtern_data_dir AS 'C:/TEMP/';
CREATE TABLE ET_RMCRPB0_MEDICARE_HCPC_PB_PR (
HCPC VARCHAR(5),
NDC VARCHAR(11),
HCPC_PBC1 CHAR(8),
HCPC_PBP1 NUMBER(16,5),
HCPC_PBC2 CHAR(8),
HCPC_PBP2 NUMBER(16,5),
HCPC_PBC3 CHAR(8),
HCPC_PBP3 NUMBER(16,5),
HCPC_PBC4 CHAR(8),
HCPC_PBP4 NUMBER(16,5),
HCPC_PBC5 CHAR(8),
HCPC_PBP5 NUMBER(16,5),
HCPC_PBC6 CHAR(8),
HCPC_PBP6 NUMBER(16,5),
HCPC_PBC7 CHAR(8),
HCPC_PBP7 NUMBER(16,5),
HCPC_PBC8 CHAR(8),
HCPC_PBP8 NUMBER(16,5)
)
ORGANIZATION EXTERNAL (
default directory xtern_data_dir
ACCESS PARAMETERS
(
records delimited by newline
badfile xtern_log_dir:'RMCRPB0_MEDICARE_HCPC_PB_PRICE.bad'
logfile xtern_log_dir:'RMCRPB0_MEDICARE_HCPC_PB_PRICE'
discardfile xtern_log_dir:'RMCRPB0_MEDICARE_HCPC_PB_PRICE'
fields terminated by '|'
MISSING FIELD VALUES ARE NULL
)
location ('HCPC_PB_PRICE_TESTCASE')
);
INSERT INTO RMCRPB0_MEDICARE_HCPC_PB_PRICE (HCPC, NDC, HCPC_PBC1, HCPC_PBP1, HCPC_PBC2, HCPC_PBP2, HCPC_PBC3, HCPC_PBP3, HCPC_PBC4, HCPC_PBP4, HCPC_PBC5, HCPC_PBP5, HCPC_PBC6, HCPC_PBP6, HCPC_PBC7, HCPC_PBP7, HCPC_PBC8, HCPC_PBP8) (SELECT HCPC, NVL(NDC, '00000000000'), TO_DATE(NULLIF(HCPC_PBC1, '00000000'), 'YYYYMMDD'), HCPC_PBP1, TO_DATE(NULLIF(HCPC_PBC2, '00000000'), 'YYYYMMDD'), HCPC_PBP2, TO_DATE(NULLIF(HCPC_PBC3, '00000000'), 'YYYYMMDD'), HCPC_PBP3, TO_DATE(NULLIF(HCPC_PBC4, '00000000'), 'YYYYMMDD'), HCPC_PBP4, TO_DATE(NULLIF(HCPC_PBC5, '00000000'), 'YYYYMMDD'), HCPC_PBP5, TO_DATE(NULLIF(HCPC_PBC6, '00000000'), 'YYYYMMDD'), HCPC_PBP6, TO_DATE(NULLIF(HCPC_PBC7, '00000000'), 'YYYYMMDD'), HCPC_PBP7, TO_DATE(NULLIF(HCPC_PBC8, '00000000'), 'YYYYMMDD'), HCPC_PBP8 FROM ET_RMCRPB0_MEDICARE_HCPC_PB_PR);
RMCRPB0_MEDICARE_HCPC_PB_PRICE is a plain text file (no extension) containing the following data:
J1850||20100401|000001.15300|20100101|000001.15304|20091001|000000.53200|20090701|000000.57600|20090401|000000.64400|20090101|000000.65300|20081001|000000.68300|20080701|000000.71100
J1885||20110701|000000.28000|20110401|000000.27100|20110101|000000.28600|20101001|000000.30200|20100701|000000.28900|20100401|000000.23400|20100101|000000.26377|20091001|000000.26600
J1890||20060101|000000.00000|20050101|000008.64000|20040101|000009.18000|20030101|000010.26000|00000000|000000.00000|00000000|000000.00000|00000000|000000.00000|00000000|000000.00000
J1910||20050101|000000.00000|20040617|000013.88000|20040101|000000.00000|20030701|000015.52000|20030101|000014.92000|00000000|000000.00000|00000000|000000.00000|00000000|000000.00000
J1930||20110701|000031.25900|20110401|000030.50000|20110101|000030.40100|20101001|000030.03600|20100701|000029.26600|20100401|000029.30600|20100101|000027.13450|20091001|000029.20500
J1931||20110701|000026.83700|20110401|000025.56000|20110101|000025.55900|20101001|000025.55300|20100701|000025.55500|20100401|000025.56400|20100101|000025.56610|20090401|000025.56500
J1940||20110701|000001.44600|20110401|000001.34900|20110101|000000.57900|20101001|000000.29300|20100701|000000.24700|20100401|000000.22400|20100101|000000.23243|20090701|000000.20700
J1945||20110701|000305.17000|20110401|000305.32500|20110101|000305.17600|20101001|000280.31000|20100701|000234.30500|20100401|000234.37400|20100101|000231.26700|20091001|000177.86700
J1950||20110701|000599.03500|20110401|000558.06800|20110101|000552.81600|20101001|000525.44200|20100701|000526.35100|20100401|000516.08500|20100101|000492.70200|20091001|000489.43000
J1953||20110701|000000.34000|20110401|000000.45800|20110101|000000.58200|20101001|000000.54400|20100701|000000.52800|20100401|000000.48400|20100101|000000.46150|20091001|000000.74500
The output I am receiving is as follows... Please note that the date fields contain the wrong values:
HCPC NDC HCPC_PBC1 HCPC_PBP1 HCPC_PBC2 HCPC_PBP2 HCPC_PBC3....
J1850 0 1-Apr-80 1.153 1-Jan-80 1.15304 1-Oct-24
J1885 0 1-Jul-36 0.28 1-Apr-36 0.271 1-Jan-36
J1890 0 1-Jan-56 0 1-Jan-00 8.64 1-Jan-44
J1910 0 1-Jan-00 0 17-Jun-44 13.88 1-Jan-44
J1930 0 1-Jul-36 31.259 1-Apr-36 30.5 1-Jan-36
J1931 0 1-Jul-36 26.837 1-Apr-36 25.56 1-Jan-36
J1940 0 1-Jul-36 1.446 1-Apr-36 1.349 1-Jan-36
J1945 0 1-Jul-36 305.17 1-Apr-36 305.325 1-Jan-36
J1950 0 1-Jul-36 599.035 1-Apr-36 558.068 1-Jan-36
J1953 0 1-Jul-36 0.34 1-Apr-36 0.458 1-Jan-36
I ran the above code on the command line using the following command in SQL*Plus:
SQL> start "[pathname]\oracle_testcase.sql"
I hope this helps!
-Winston
[Updated on: Mon, 19 September 2011 12:25] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Problem with date values using NULLIF and [message #523659 is a reply to message #523656] |
Mon, 19 September 2011 12:37 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
wakproductions_fdb wrote on Mon, 19 September 2011 13:21TO_DATE(NULLIF(HCPC_PBC1, '00000000'), 'YYYYMMDD'),
HCPC_PBP1, TO_DATE(NULLIF(HCPC_PBC2, '00000000'), 'YYYYMMDD'),
HCPC_PBP2, TO_DATE(NULLIF(HCPC_PBC3, '00000000'), 'YYYYMMDD'),
HCPC_PBP3, TO_DATE(NULLIF(HCPC_PBC4, '00000000'), 'YYYYMMDD'),
HCPC_PBP4, TO_DATE(NULLIF(HCPC_PBC5, '00000000'), 'YYYYMMDD'),
HCPC_PBP5, TO_DATE(NULLIF(HCPC_PBC6, '00000000'), 'YYYYMMDD'),
HCPC_PBP6, TO_DATE(NULLIF(HCPC_PBC7, '00000000'), 'YYYYMMDD'), HCPC_PBP7, TO_DATE(NULLIF(HCPC_PBC8, '00000000'), 'YYYYMMDD'),
NULLIF requires that both arguments be of the same datatype, of which these are not.
SQL> create table xx (xx date);
Table created.
SQL> select to_date(nullif(xx,'00000000'),'YYYYMMDD') from xx;
select to_date(nullif(xx,'00000000'),'YYYYMMDD') from xx
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got CHAR
I do know how your code can even execute.
[Updated on: Mon, 19 September 2011 12:56] by Moderator Report message to a moderator |
|
|
|
| Re: Problem with date values using NULLIF and [message #523660 is a reply to message #523658] |
Mon, 19 September 2011 12:38 |
|
wakproductions_fdb
Messages: 15
Registered: September 2011
Location: Indiana
|
Junior Member |
|
|
Sorry about the line size. Here is a more condensed version of the code:
CREATE TABLE RMCRPB0_MEDICARE_HCPC_PB_PRICE (
HCPC VARCHAR(5) NOT NULL,
NDC VARCHAR(11) NOT NULL,
HCPC_PBC1 DATE,
HCPC_PBP1 NUMBER(16,5),
HCPC_PBC2 DATE,
HCPC_PBP2 NUMBER(16,5),
HCPC_PBC3 DATE,
CONSTRAINT PK_RMCRPB0_MEDICARE_HCPC_PB_PR PRIMARY KEY (HCPC, NDC)
);
INSERT INTO RMCRPB0_MEDICARE_HCPC_PB_PRICE (HCPC, NDC, HCPC_PBC1, HCPC_PBP1, HCPC_PBC2, HCPC_PBP2, HCPC_PBC3)
(SELECT
'JXXX',
'00000000000',
TO_DATE(NULLIF('20110701', '00000000'), 'YYYYMMDD'),
1.01,
CASE '20110701'
WHEN '00000000' THEN null
ELSE TO_DATE('20110701', 'YYYYMMDD')
END,
2.02,
TO_DATE('20110701', 'YYYYMMDD')
FROM dual);
The data in the table is as follows:
SQL> select * from rmcrpb0_medicare_hcpc_pb_price;
HCPC NDC HCPC_PBC1 HCPC_PBP1 HCPC_PBC2 HCPC_PBP2 HCPC_PBC3
----- ----------- --------- ---------- --------- ---------- ---------
JXXX 00000000000 01-JUL-36 1.01 01-JUL-11 2.02 01-JUL-11
|
|
|
|
| Re: Problem with date values using NULLIF and [message #523663 is a reply to message #523659] |
Mon, 19 September 2011 12:49 |
|
wakproductions_fdb
Messages: 15
Registered: September 2011
Location: Indiana
|
Junior Member |
|
|
Quote:NULLIF requires that both arguments be of the same datatype, of which these are not.
I am trying to convert a character string to a date value. The reason is because in my external table, there are some dates formatted as '00000000', an invalid date value. By specifying those fields as CHAR( , I can use NULLIF to filter out the invalid dates and use TO_DATE in my INSERT INTO statement.
i.e.)
INSERT INTO RMCRPB0_MEDICARE_HCPC_PB_PRICE (HCPC, NDC, HCPC_PBC1)
(SELECT
'JXXX',
'00000000000',
TO_DATE(NULLIF('20110701', '00000000'), 'YYYYMMDD'),
FROM dual);
HCPC_PBC1 is a CHAR that I'm using TO_DATE to convert into a DateTime value for insertion into the actual table. The problem is that when I use the NULLIF the converted date value has the wrong year.
Thanks!
|
|
|
|
|
|
| Re: Problem with date values using NULLIF and [message #523678 is a reply to message #523666] |
Mon, 19 September 2011 16:55 |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You can handle the date problem in the external table creation using:
"HCPC_PBC1" CHAR(8)
DATE_FORMAT DATE MASK 'yyyymmdd'
NULLIF ("HCPC_PBC1" = "00000000"
as demonstrated below.
SCOTT@orcl_11gR2> CREATE TABLE RMCRPB0_MEDICARE_HCPC_PB_PRICE (
2 HCPC VARCHAR(5) NOT NULL,
3 NDC VARCHAR(11) NOT NULL,
4 HCPC_PBC1 DATE,
5 HCPC_PBP1 NUMBER(16,5),
6 HCPC_PBC2 DATE,
7 HCPC_PBP2 NUMBER(16,5),
8 HCPC_PBC3 DATE,
9 HCPC_PBP3 NUMBER(16,5),
10 HCPC_PBC4 DATE,
11 HCPC_PBP4 NUMBER(16,5),
12 HCPC_PBC5 DATE,
13 HCPC_PBP5 NUMBER(16,5),
14 HCPC_PBC6 DATE,
15 HCPC_PBP6 NUMBER(16,5),
16 HCPC_PBC7 DATE,
17 HCPC_PBP7 NUMBER(16,5),
18 HCPC_PBC8 DATE,
19 HCPC_PBP8 NUMBER(16,5),
20 CONSTRAINT PK_RMCRPB0_MEDICARE_HCPC_PB_PR PRIMARY KEY (HCPC, NDC)
21 );
Table created.
SCOTT@orcl_11gR2>
SCOTT@orcl_11gR2> CREATE OR REPLACE DIRECTORY xtern_data_dir AS 'C:/TEMP/';
Directory created.
SCOTT@orcl_11gR2> CREATE OR REPLACE DIRECTORY xtern_log_dir AS 'C:/TEMP/';
Directory created.
SCOTT@orcl_11gR2>
SCOTT@orcl_11gR2> CREATE TABLE ET_RMCRPB0_MEDICARE_HCPC_PB_PR (
2 HCPC VARCHAR(5),
3 NDC VARCHAR(11),
4 HCPC_PBC1 DATE,
5 HCPC_PBP1 NUMBER(16,5),
6 HCPC_PBC2 DATE,
7 HCPC_PBP2 NUMBER(16,5),
8 HCPC_PBC3 DATE,
9 HCPC_PBP3 NUMBER(16,5),
10 HCPC_PBC4 DATE,
11 HCPC_PBP4 NUMBER(16,5),
12 HCPC_PBC5 DATE,
13 HCPC_PBP5 NUMBER(16,5),
14 HCPC_PBC6 DATE,
15 HCPC_PBP6 NUMBER(16,5),
16 HCPC_PBC7 DATE,
17 HCPC_PBP7 NUMBER(16,5),
18 HCPC_PBC8 DATE,
19 HCPC_PBP8 NUMBER(16,5)
20 )
21 ORGANIZATION EXTERNAL (
22 default directory xtern_data_dir
23 ACCESS PARAMETERS
24 (
25 records delimited by newline
26 badfile xtern_log_dir:'RMCRPB0_MEDICARE_HCPC_PB_PRICE.bad'
27 logfile xtern_log_dir:'RMCRPB0_MEDICARE_HCPC_PB_PRICE'
28 discardfile xtern_log_dir:'RMCRPB0_MEDICARE_HCPC_PB_PRICE'
29 fields terminated by '|'
30 MISSING FIELD VALUES ARE NULL
31 (
32 "HCPC",
33 "NDC",
34 "HCPC_PBC1" CHAR(8)
35 DATE_FORMAT DATE MASK 'yyyymmdd'
36 NULLIF ("HCPC_PBC1" = "00000000"),
37 "HCPC_PBP1",
38 "HCPC_PBC2" CHAR(8)
39 DATE_FORMAT DATE MASK 'yyyymmdd'
40 NULLIF ("HCPC_PBC2" = "00000000"),
41 "HCPC_PBP2",
42 "HCPC_PBC3" CHAR(8)
43 DATE_FORMAT DATE MASK 'yyyymmdd'
44 NULLIF ("HCPC_PBC3" = "00000000"),
45 "HCPC_PBP3",
46 "HCPC_PBC4" CHAR(8)
47 DATE_FORMAT DATE MASK 'yyyymmdd'
48 NULLIF ("HCPC_PBC4" = "00000000"),
49 "HCPC_PBP4",
50 "HCPC_PBC5" CHAR(8)
51 DATE_FORMAT DATE MASK 'yyyymmdd'
52 NULLIF ("HCPC_PBC5" = "00000000"),
53 "HCPC_PBP5",
54 "HCPC_PBC6" CHAR(8)
55 DATE_FORMAT DATE MASK 'yyyymmdd'
56 NULLIF ("HCPC_PBC6" = "00000000"),
57 "HCPC_PBP6",
58 "HCPC_PBC7" CHAR(8)
59 DATE_FORMAT DATE MASK 'yyyymmdd'
60 NULLIF ("HCPC_PBC7" = "00000000"),
61 "HCPC_PBP7",
62 "HCPC_PBC8" CHAR(8)
63 DATE_FORMAT DATE MASK 'yyyymmdd'
64 NULLIF ("HCPC_PBC8" = "00000000"),
65 "HCPC_PBP8"
66 )
67 )
68 location ('HCPC_PB_PRICE_TESTCASE')
69 );
Table created.
SCOTT@orcl_11gR2> column HCPC_PBC1 newline
SCOTT@orcl_11gR2> column HCPC_PBC4 newline
SCOTT@orcl_11gR2> column HCPC_PBC7 newline
SCOTT@orcl_11gR2> select * from ET_RMCRPB0_MEDICARE_HCPC_PB_PR
2 /
HCPC NDC
----- -----------
HCPC_PBC1 HCPC_PBP1 HCPC_PBC2 HCPC_PBP2 HCPC_PBC3 HCPC_PBP3
--------- ---------- --------- ---------- --------- ----------
HCPC_PBC4 HCPC_PBP4 HCPC_PBC5 HCPC_PBP5 HCPC_PBC6 HCPC_PBP6
--------- ---------- --------- ---------- --------- ----------
HCPC_PBC7 HCPC_PBP7 HCPC_PBC8 HCPC_PBP8
--------- ---------- --------- ----------
J1850
01-APR-10 1.153 01-JAN-10 1.15304 01-OCT-09 .532
01-JUL-09 .576 01-APR-09 .644 01-JAN-09 .653
01-OCT-08 .683 01-JUL-08 .711
J1885
01-JUL-11 .28 01-APR-11 .271 01-JAN-11 .286
01-OCT-10 .302 01-JUL-10 .289 01-APR-10 .234
01-JAN-10 .26377 01-OCT-09 .266
J1890
01-JAN-06 0 01-JAN-05 8.64 01-JAN-04 9.18
01-JAN-03 10.26 0 0
0 0
J1910
01-JAN-05 0 17-JUN-04 13.88 01-JAN-04 0
01-JUL-03 15.52 01-JAN-03 14.92 0
0 0
J1930
01-JUL-11 31.259 01-APR-11 30.5 01-JAN-11 30.401
01-OCT-10 30.036 01-JUL-10 29.266 01-APR-10 29.306
01-JAN-10 27.1345 01-OCT-09 29.205
J1931
01-JUL-11 26.837 01-APR-11 25.56 01-JAN-11 25.559
01-OCT-10 25.553 01-JUL-10 25.555 01-APR-10 25.564
01-JAN-10 25.5661 01-APR-09 25.565
J1940
01-JUL-11 1.446 01-APR-11 1.349 01-JAN-11 .579
01-OCT-10 .293 01-JUL-10 .247 01-APR-10 .224
01-JAN-10 .23243 01-JUL-09 .207
J1945
01-JUL-11 305.17 01-APR-11 305.325 01-JAN-11 305.176
01-OCT-10 280.31 01-JUL-10 234.305 01-APR-10 234.374
01-JAN-10 231.267 01-OCT-09 177.867
J1950
01-JUL-11 599.035 01-APR-11 558.068 01-JAN-11 552.816
01-OCT-10 525.442 01-JUL-10 526.351 01-APR-10 516.085
01-JAN-10 492.702 01-OCT-09 489.43
J1953
01-JUL-11 .34 01-APR-11 .458 01-JAN-11 .582
01-OCT-10 .544 01-JUL-10 .528 01-APR-10 .484
01-JAN-10 .4615 01-OCT-09 .745
10 rows selected.
SCOTT@orcl_11gR2> INSERT INTO RMCRPB0_MEDICARE_HCPC_PB_PRICE
2 (HCPC, NDC,
3 HCPC_PBC1, HCPC_PBP1, HCPC_PBC2, HCPC_PBP2, HCPC_PBC3, HCPC_PBP3, HCPC_PBC4, HCPC_PBP4,
4 HCPC_PBC5, HCPC_PBP5, HCPC_PBC6, HCPC_PBP6, HCPC_PBC7, HCPC_PBP7, HCPC_PBC8, HCPC_PBP8)
5 SELECT HCPC, NVL(NDC, '00000000000'),
6 HCPC_PBC1, HCPC_PBP1,
7 HCPC_PBC2, HCPC_PBP2,
8 HCPC_PBC3, HCPC_PBP3,
9 HCPC_PBC4, HCPC_PBP4,
10 HCPC_PBC5, HCPC_PBP5,
11 HCPC_PBC6, HCPC_PBP6,
12 HCPC_PBC7, HCPC_PBP7,
13 HCPC_PBC8, HCPC_PBP8
14 FROM ET_RMCRPB0_MEDICARE_HCPC_PB_PR
15 /
10 rows created.
SCOTT@orcl_11gR2> select * from RMCRPB0_MEDICARE_HCPC_PB_PRICE
2 /
HCPC NDC
----- -----------
HCPC_PBC1 HCPC_PBP1 HCPC_PBC2 HCPC_PBP2 HCPC_PBC3 HCPC_PBP3
--------- ---------- --------- ---------- --------- ----------
HCPC_PBC4 HCPC_PBP4 HCPC_PBC5 HCPC_PBP5 HCPC_PBC6 HCPC_PBP6
--------- ---------- --------- ---------- --------- ----------
HCPC_PBC7 HCPC_PBP7 HCPC_PBC8 HCPC_PBP8
--------- ---------- --------- ----------
J1850 00000000000
01-APR-10 1.153 01-JAN-10 1.15304 01-OCT-09 .532
01-JUL-09 .576 01-APR-09 .644 01-JAN-09 .653
01-OCT-08 .683 01-JUL-08 .711
J1885 00000000000
01-JUL-11 .28 01-APR-11 .271 01-JAN-11 .286
01-OCT-10 .302 01-JUL-10 .289 01-APR-10 .234
01-JAN-10 .26377 01-OCT-09 .266
J1890 00000000000
01-JAN-06 0 01-JAN-05 8.64 01-JAN-04 9.18
01-JAN-03 10.26 0 0
0 0
J1910 00000000000
01-JAN-05 0 17-JUN-04 13.88 01-JAN-04 0
01-JUL-03 15.52 01-JAN-03 14.92 0
0 0
J1930 00000000000
01-JUL-11 31.259 01-APR-11 30.5 01-JAN-11 30.401
01-OCT-10 30.036 01-JUL-10 29.266 01-APR-10 29.306
01-JAN-10 27.1345 01-OCT-09 29.205
J1931 00000000000
01-JUL-11 26.837 01-APR-11 25.56 01-JAN-11 25.559
01-OCT-10 25.553 01-JUL-10 25.555 01-APR-10 25.564
01-JAN-10 25.5661 01-APR-09 25.565
J1940 00000000000
01-JUL-11 1.446 01-APR-11 1.349 01-JAN-11 .579
01-OCT-10 .293 01-JUL-10 .247 01-APR-10 .224
01-JAN-10 .23243 01-JUL-09 .207
J1945 00000000000
01-JUL-11 305.17 01-APR-11 305.325 01-JAN-11 305.176
01-OCT-10 280.31 01-JUL-10 234.305 01-APR-10 234.374
01-JAN-10 231.267 01-OCT-09 177.867
J1950 00000000000
01-JUL-11 599.035 01-APR-11 558.068 01-JAN-11 552.816
01-OCT-10 525.442 01-JUL-10 526.351 01-APR-10 516.085
01-JAN-10 492.702 01-OCT-09 489.43
J1953 00000000000
01-JUL-11 .34 01-APR-11 .458 01-JAN-11 .582
01-OCT-10 .544 01-JUL-10 .528 01-APR-10 .484
01-JAN-10 .4615 01-OCT-09 .745
10 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
| Re: Problem with date values using NULLIF and [message #523679 is a reply to message #523678] |
Mon, 19 September 2011 17:01 |
|
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Your test case does not produce the same results in my version, so if that is what you are getting then it looks like a bug that would be fixed by upgrading. You might check to see if there is a patch.
SCOTT@orcl_11gR2> select * from v$version
2 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
5 rows selected.
SCOTT@orcl_11gR2> CREATE TABLE RMCRPB0_MEDICARE_HCPC_PB_PRICE (
2 HCPC VARCHAR(5) NOT NULL,
3 NDC VARCHAR(11) NOT NULL,
4 HCPC_PBC1 DATE,
5 HCPC_PBP1 NUMBER(16,5),
6 HCPC_PBC2 DATE,
7 HCPC_PBP2 NUMBER(16,5),
8 HCPC_PBC3 DATE,
9 CONSTRAINT PK_RMCRPB0_MEDICARE_HCPC_PB_PR PRIMARY KEY (HCPC, NDC)
10 );
Table created.
SCOTT@orcl_11gR2>
SCOTT@orcl_11gR2> INSERT INTO RMCRPB0_MEDICARE_HCPC_PB_PRICE (HCPC, NDC, HCPC_PBC1, HCPC_PBP1, HCPC_PBC2, HCPC_PBP2, HCPC_PBC3)
2 (SELECT
3 'JXXX',
4 '00000000000',
5 TO_DATE(NULLIF('20110701', '00000000'), 'YYYYMMDD'),
6 1.01,
7 CASE '20110701'
8 WHEN '00000000' THEN null
9 ELSE TO_DATE('20110701', 'YYYYMMDD')
10 END,
11 2.02,
12 TO_DATE('20110701', 'YYYYMMDD')
13 FROM dual);
1 row created.
SCOTT@orcl_11gR2> select * from RMCRPB0_MEDICARE_HCPC_PB_PRICE
2 /
HCPC NDC HCPC_PBC1 HCPC_PBP1 HCPC_PBC2 HCPC_PBP2 HCPC_PBC3
----- ----------- --------- ---------- --------- ---------- ---------
JXXX 00000000000 01-JUL-11 1.01 01-JUL-11 2.02 01-JUL-11
1 row selected.
SCOTT@orcl_11gR2>
|
|
|
|
| Re: Problem with date values using NULLIF and [message #523806 is a reply to message #523679] |
Tue, 20 September 2011 08:02 |
|
wakproductions_fdb
Messages: 15
Registered: September 2011
Location: Indiana
|
Junior Member |
|
|
Hi Barbara,
Thanks for the reply. I didn't realize I could perform a nullif on the external table. More importantly, at least you have confirmed that the 1936 date value is some kind of a bug in the underlying DB software - Oracle 10g Express. I guess for now the best workaround would be to use a different methodology with CASE instead of NULLIF.
-Winston
|
|
|
|
Goto Forum:
Current Time: Mon Aug 18 13:13:42 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
