Home » SQL & PL/SQL » SQL & PL/SQL » Oracle SQL is breaking
Oracle SQL is breaking [message #217556] Fri, 02 February 2007 15:03 Go to next message
dpong
Messages: 73
Registered: January 2007
Member
I've got a table, which is externally imported from a sas datasets.

Here is the desc of the score_date
Column Name ID Pk Null? Data Type Default

SCORE_DATE 33 Y VARCHAR2 (9)

If I do the following, it'd give me the result fine, as shown below.

SELECT TO_DATE(SCORE_DATE, 'ddMONyyyy') score_date from alm_leads_contact_history 
---------
SCORE_DATE

2/13/2006
2/13/2006
2/13/2006



However, as I tried to create a table out of it... it won't let me

CREATE TABLE alm_leads_history nologging as
	   SELECT 
	   		   BATCH_ID, LEAD_ID, INDIVIDUAL_ID, 
			   FIRST_NAME, LAST_NAME, CITY, 
			   STATE, COUNTRY_ID, WORK_PHONE_NO, 
			   JOB_ROLE_FUNCTION_GIVEN, TITLE_GIVEN, STANDARD_TITLE, 
			   NEW_STANDARD_TITLE, TIER3_AUDIENCE, FILTER_FLG, 
			   COMPANY_GIVEN, DUNS_NUMBER, MU_ATS_INDUSTRY, 
			   MU_EMPLOYEES_HERE, IND_LAST_ACTIVITY, LAST_ACTIVITY, 
			   IND_TENURE_IN_MONTHS, OMO_SCHEDULE_CODE, OMO_SCHEDULE_NAME, 
			   OMO_CAMPAIGN_NAME, OMO_PROGRAM_NAME, EST_PROB, 
			   MODEL_DECILE_SCORE, SEND_TO_OTS, DISPOSITION, 
			   NOTES, ACTIVITY_DATE,  TO_DATE(SCORE_DATE, 'ddMONyyyy') score_date,
			   ASSIGNED_SALES_CHNL 

		FROM DM_METRICS.alm_leads_contact_history
		;

The error message I got is
ORA-01858: a non-numeric character was found where a numeric was expected

This is so weired! But maybe I found a bug for my fellows. Laughing



Re: Oracle SQL is breaking [message #217559 is a reply to message #217556] Fri, 02 February 2007 15:16 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>But maybe I found a bug for my fellows.
It is PEBKAC.
You could try reading the fine SQL Reference Manual to see what TO_DATE actually does & how to use it from httP://tahiti.oracle.com
P.S.
This is NOT an EXPERT level question.

Stop cross/multi-posting
http://www.orafaq.com/forum/t/45456/74940/

[Updated on: Fri, 02 February 2007 15:32] by Moderator

Report message to a moderator

Re: Oracle SQL is breaking [message #217562 is a reply to message #217559] Fri, 02 February 2007 15:35 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
well, if you've looked into my post closely. Score_date isn't a date field. It's of type TEXT, as it's imported from SAS.

I think this is abnormal enough that even experts won't have a clue in solving.

I realized I mistakenly posted my response to a different message, to which I never intended. I've already deleted that message. I apologize for that.



[Updated on: Fri, 02 February 2007 15:38]

Report message to a moderator

Re: Oracle SQL is breaking [message #217582 is a reply to message #217562] Fri, 02 February 2007 21:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You used to_date on a varchar2 field that might contain values that do not follow your ddMONyyyy format.
Did you run your first query and let it return every row, or did you use a graphical front-end that displays the first n rows (like TOAD, PL/SQL Developer etc)?
Re: Oracle SQL is breaking [message #217593 is a reply to message #217582] Fri, 02 February 2007 22:59 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
exactly. That's what I figured. I solved the problem a couple hours ago. Great thinking.

I basically did a query something like the following.
SELECT DISTINCT score_date FROM [TABLE_NAME]
WHERE SUBSTR(score_date, 1, 9) = 'the very first date I saw'
...
and found out that there were a '.' in a bunch of the entries, which is caused by SAS. A null value in SAS (numeric null) is represented by '.'. Since SAS has date as number, they totally make sense altogether.

Well, this is not a rudimentary problem, as suggested by someone. I think this really takes some figuring and understanding of what SQL compiler is looking for /doing in the underlying picture.

Thanks, Frank, even though I solved the problem already.

Re: Oracle SQL is breaking [message #217826 is a reply to message #217593] Mon, 05 February 2007 15:16 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Sorry to disappoint you, but to_date conversions are generally considered basic SQL.
Re: Oracle SQL is breaking [message #217829 is a reply to message #217826] Mon, 05 February 2007 15:25 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
it totally wasn't because I was wrong with the syntax to begin with Cool
Re: Oracle SQL is breaking [message #217928 is a reply to message #217593] Tue, 06 February 2007 02:29 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, I just read through this thread, and by the time I got to the third post, I had a bet going with myself that it was going to be yet another 'Oh - the data isn't what I thought'

Sorry to disapoint, @dpong, but this isn't some arcane and complex point of syntax, or " is abnormal enough that even experts won't have a clue in solving"
Previous Topic: where we will use hints in SQL ?and Why? plz tell me with the example ?
Next Topic: pls_integer
Goto Forum:
  


Current Time: Sun Dec 11 04:37:12 CST 2016

Total time taken to generate the page: 0.11196 seconds