Firstly I am on a 10.2.0.4 database.
If I run this query with less inlist values, then the query works. If I run the subquery on it's own with all values in inlist it works but when I run the whole query with all the inlist values I get ORA-01841 : (full) year must be between -4713 and +9999, and not be 0
select * from (
SELECT
To_Date(To_Char(sec."Theta"),'yyyymmdd.HH24MISS') A,
To_Date('20080707', 'yyyymmdd') B
FROM securities sec
WHERE SI IN (519, 2056, 562, 2084, 7763, 6456, 12126, 2038, 3180, 14193, 7958, 14185, 12282, 7115, 11945, 12265, 11719, 12128, 14186, 11709, 8595, 800, 489, 793, 14179, 6588, 2856, 779, 22410, 12224, 14255, 22535, 10192, 10477, 768, 865, 6479, 6594, 12256, 28056, 879, 33596, 12194, 35884, 28153, 35889, 35890, 35894, 2068, 26981, 33779, 35905, 35886, 35875, 2089, 35851, 4161, 31473, 29638, 24911, 6509, 30930, 7117, 33593, 29643, 31355, 719, 829, 27045, 11921, 11120, 26997, 27411, 20061, 29689, 35839, 5479, 2091, 5929, 12267, 2051, 21727, 28051, 28052, 28058, 30381, 35335, 2197, 28055, 21573, 25030, 27052, 14724, 28579, 2156, 28057, 11739, 28053, 25312, 2039, 11926, 8317, 16822, 6445, 6682, 12100, 12144, 452, 11980, 14408, 12084, 718, 11903, 27646, 28414, 591, 774, 12062, 11741, 8528, 11781, 12167, 11733, 469, 12271, 37337, 31427, 15217, 6615, 11922, 11711, 7323, 6917, 27634, 11865, 11775, 6940, 11259, 14799, 22438, 10347, 14315, 23099, 35790, 14810, 21392, 27159, 500, 11019, 868, 10484, 773, 790, 4263, 717, 702, 12022, 648, 508, 848, 3584, 5721, 7310, 14718, 14725, 14743, 14762, 14759, 3363, 20643, 14307, 14788, 36503, 2402, 14766, 6484, 14312, 14794, 12221, 12188, 456, 28308, 28309, 28312, 28313, 28314, 28315, 28572, 32649, 36644, 38646, 38645, 10923, 15075, 26562, 28154, 33588, 35705, 35844, 35857, 4174, 35876, 35877, 28302, 14562, 14464, 8759, 11717, 12113, 14416, 27860, 31949, 11949, 6010, 14445, 14576, 2592, 875, 2874, 14264, 12170, 11749, 21553, 10403, 658, 27171, 11875, 11977, 11893, 19757, 8548, 10237, 31402, 21490, 35328, 35778, 27635, 29677, 29666, 37266, 11817, 28177, 28408, 12094, 2050, 28080, 28084, 3212, 28085, 28090, 28127, 35505, 36647, 28091, 28083, 14191, 11972, 9829, 728, 3559, 6562, 7085, 15175, 37361, 24932, 23140, 8081, 8771, 29651, 31476, 31479, 30931, 29649, 35323, 31483, 31456, 29681, 29652, 29642, 29655, 31491, 31488, 31489, 29654, 35324, 35329, 28210, 36369, 26670, 32650, 29667, 36370, 35779, 30929, 28317, 31464, 2728, 31484, 29645, 31501, 36368, 31494, 31463, 3610, 29670, 15213, 31462, 26983, 31461, 31471, 2381, 8103, 12071, 28081, 28082, 2218, 28092, 28094, 2513, 28095, 2228, 28097, 28098, 28100, 28101, 28102, 28103, 28104, 28107, 28108, 28109, 30392, 31363, 33701, 37587, 14539, 28099, 12205, 28106, 5621, 20527, 3352, 28111, 681, 12098, 19884, 28117, 28116, 11822, 28119, 28120, 14182, 20388, 28123, 32027, 818, 11825, 28093, 11801, 23100, 14575, 11991, 12108, 11954, 2347, 14225, 1949, 586, 28115, 31370, 28110, 28114, 28118, 28121, 11797, 27381, 5601, 27375, 27389, 27386, 33714, 33716, 27374, 20054, 30933, 33718, 27390, 28304, 2692, 27394, 11663, 26979, 14309, 27399, 7940, 33712, 12075, 28298, 28301, 11746, 36231, 27382, 33715, 26522, 6896, 28300, 12147, 27393, 27383, 14162, 33717, 27384, 28303, 27385, 7960, 36230, 769, 27392, 570, 601, 644, 806, 3365, 6494, 9390, 9762, 14174, 14870, 20501, 20525, 28180, 28181, 28182, 11996, 28188, 8767, 28184, 28189, 28206, 28186, 29729, 28207, 2229, 12169, 11989, 26978, 7337, 35859, 26991, 19753, 37262, 585, 14253, 674, 12131, 2755, 2264, 6715, 629, 11840, 3519, 15073, 14412, 15166, 12030, 11745, 2757, 12066, 12209, 14518, 12043, 29653, 730, 830, 7776, 2746, 6426, 6905, 10229, 11814, 11864, 12163, 12279, 14438, 14470, 25310, 35372, 29326, 25314, 19754, 15177, 14699, 12225, 12089, 11914, 11834, 6720, 2846, 2779, 2772, 2252, 857, 614, 466, 11880, 12236, 12118, 29342, 29348, 11051, 29349, 25313, 29353, 25311, 29335, 29336, 2205, 14547, 27338, 29346, 29351, 2250, 2369, 2335, 29334, 29339, 29338, 3616, 27617, 29340, 29350, 29337, 29343, 29345, 29352, 29354, 29341, 29344, 29347, 3322, 3317, 20954, 37663, 37665, 29674, 29684, 29675, 29685, 36490, 29671, 29673, 29676, 29678, 26985, 16467, 26995, 28150, 28724, 11979, 27344)
AND sec."SecurityType" <> 16
AND sec."DeadType" = 0
AND sec."Gamma" IS NOT NULL
ORDER BY "Theta" DESC
) where A > B
Edited by: kam on Jul 8, 2009 10:46 AM
Edited by: kam on Jul 8, 2009 10:46 AM
Edited by: kam on Jul 8, 2009 10:48 AM