Select Into 1

[jontout]

Hi everyone.
I've been staring at this piece of code for a couple of hours now, and I can't see a reason as to why this would fail. I've based the code on something I've used on other platforms, but I'm still fairly new to Oracle...

SELECT LOG_KEY, LOG_USER_CDE, LOG_SHORT_DESC, LOG_CRT_DATE
INTO JT_EVTLOG
FROM EVENTLOG
WHERE LOG_CRT_DATE LIKE TO_DATE ('18-FEB-2011 00:00:00', 'DD-MM-YYYY HH24:MI:SS')

If I remove the "INTO JT_EVTLOG" line, the Select code works a charm, I just can't get the output into the other table, with the error, "Missing Keyword"

As far as I can tell, the JT_EVTLOG table is constructed appropriately to recieve the intended data so I'm stumped as to where to go next, other than continuing my searching, can anyone point me in the next direction?

For completion, here's the table create script.

CREATE TABLE "INSTEST"."JT_EVTLOG"
  (
    "LOG_KEY"        NUMBER(10,0) NOT NULL ENABLE,
    "LOG_USER_CDE"   NUMBER(10,0),
    "LOG_SHORT_DESC" VARCHAR2(50 BYTE),
    "LOG_CRT_DATE" DATE
  )
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
  )
  TABLESPACE "USERS" ;

Thanks in advance,

Jon

 

[Dagon]

The INTO syntax is for retrieving data into a variable, which you can only do in a programmic interface like PL/SQL. To insert the data to a table use INSERT INTO..SELECT. Also, you can't really use LIKE in a DATE comparison. If your date contains a time component and you want to get rid of that, then truncate it. I think your date format mask is also incorrect (DD-MM-YYYY = 18-02-2011).

INSERT INTO JT_EVTLOG (LOG_KEY, LOG_USER_CDE, LOG_SHORT_DESC, LOG_CRT_DATE)
SELECT LOG_KEY, LOG_USER_CDE, LOG_SHORT_DESC, LOG_CRT_DATE
FROM EVENTLOG
WHERE TRUNC(LOG_CRT_DATE) =TO_DATE ('18-FEB-2011 00:00:00', 'DD-MON-YYYY HH24:MI:SS')

For Oracle-related work, contact me through Linked-In.

 

[jontout]

Thanks Dragon, that worked a treat, and thanks for the note with the Date criteria, hadn't really realised that before!

The date format in the database I'm working with shows dates as DD-MMM-TT, so 22-FEB-11 would show for today.

Thanks again.

Cheers,

Jon

 

[carp]

Jon -
Are you sure about that format? When I try it, I get an error (as expected):

select to_char(sysdate,'DD-MMM-YY') FROM dual
                       *
ERROR at line 1:
ORA-01821: date format not recognized

Of course, I'm on a 10g database, so maybe 11g recognizes MMM.

 

[SantaMufasa]

Carp and Jon,

I'm connected to an 11.1 instance, and neither "MMM" nor "TT" appear acceptable to 11g.

What DATE behaviour, Jon, were you hoping for with those two format models?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[jontout]

Hi Carp / Mufasa,

Sorry, typo on my part - the sql is actually

WHERE TRUNC(LOG_CRT_DATE) =TO_DATE ('18-FEB-2011 00:00:00', 'DD-MON-YYYY HH24:MI:SS');

Not really sure what I'm expecting to recieve in the dataset, nor am I overly 'aware' of what the datatype DATE should store. All of the data I've seen and retrieved (I've not created anything) is in the format of 18-JAN-11, so I'd guess that's the type of data I shold expect?

I wasn't sure, outside of MS SQL, that there would be a visible time part to the data and to be honest, I've been given the date coding by the product supplier, so I've ran with it. I guess when I have more time to spend learning what Oracle does.

 

[Dagon]

By default, Oracle doesn't show the date in queries but DATE fields always have time included in them (but it's usually truncated to the start of the day if it's not used). To see if there is anything in the time fields, you can use something like:

select TO_CHAR(log_crt_date, 'DD-MON-YYYY HH24:MI:SS')
from eventlog

This is the converse of the TO_DATE function you've already used - it converts a date held in Oracle's internal format to a string in a particular format.



For Oracle-related work, contact me through Linked-In.

 

[Dagon]

Should read "doesn't show the time".

For Oracle-related work, contact me through Linked-In.

 

[jontout]

Thank for the tips Dagon.