| ORA-01481: invalid number format model [message #605761] |
Tue, 14 January 2014 07:36  |
 |
jackxu
Messages: 2
Registered: January 2014
|
Junior Member |
|
|
I ran the SQL below and was warned that "ORA-01481: invalid number format model". I went through my script a couple of times and yet got a clue on it. Can anyone help me out please? Thanks!
Script attached:
SELECT
TO_CHAR(OWD2.DEMAND_CONSUMER_ID, 'YYYY/MM/DD HH24:MI:SS')
,OWD2.WHSE_DEMAND_ID
,ss.SHIPMENT_ID
,ss.OLD_EVENT_CD
,ss.NEW_EVENT_CD
,TO_CHAR(ss.EVENT_DATETIME, 'YYYY/MM/DD HH24:MI:SS')
FROM
ORDER_PIPELINE_EVENTS ss
LEFT JOIN BOOKER.O_WHSE_DEMANDS OWD2
ON ss.SHIPMENT_ID = OWD2.DEMAND_CONSUMER_ID
WHERE
ss.REGION_ID = 3
AND ss.WAREHOUSE_ID = 'XXXX'
AND OWD2.WAREHOUSE_ID = 'XXXX'
AND OWD2.NEED_BY_DATE BETWEEN TO_DATE('2014/01/12 06:00:00','YYYY/MM/DD HH24:MI:SS') AND TO_DATE('2014/01/13 06:00:00','YYYY/MM/DD HH24:MI:SS')
AND ss.SHIPMENT_ID IN
(
SELECT
OWD.DEMAND_CONSUMER_ID
FROM
BOOKER.O_WHSE_DEMANDS OWD
LEFT JOIN ORDER_PIPELINE_EVENTS s
ON s.SHIPMENT_ID = OWD.DEMAND_CONSUMER_ID
WHERE
s.REGION_ID = 3
AND s.OLD_EVENT_CD ='1007'
AND s.NEW_EVENT_CD ='1013'
AND s.WAREHOUSE_ID = 'XXXX'
AND OWD.WAREHOUSE_ID = 'XXXX'
AND OWD.NEED_BY_DATE BETWEEN TO_DATE('2014/01/12 06:00:00','YYYY/MM/DD HH24:MI:SS') AND TO_DATE('2014/01/13 06:00:00','YYYY/MM/DD HH24:MI:SS')
AND TO_DATE(s.EVENT_DATETIME, 'YYYY/MM/DD HH24:MI:SS') + 5/24/60 >= TO_DATE(OWD.NEED_BY_DATE, 'YYYY/MM/DD HH24:MI:SS')
)
|
|
|
|
|
|
|
|
| Re: ORA-01481: invalid number format model [message #605784 is a reply to message #605783] |
Tue, 14 January 2014 09:39  |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
OK, not sure what yu expect anyone to be able to do with that level of information.
What I can tell you is that your outer join in your subquery is negated (i.e. it will behave as an inner join) by your filters in the where clause.
Run the subquery, view the results, is it what you expect? If not, adapt the query to fit your needs. Once you get the results from the subquery correct, add it to the outer query and reassess your results.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
