Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Create Or Replace Function Error Expand / Collapse
Author
Message
Posted Monday, December 3, 2012 8:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 3, 2012 11:13 PM
Points: 4, Visits: 2
Hello,

I'm doing text mining using Oracle SQL Developer: ODMiner.. I imported the data "WEBLOG" into a table.. This weblog data consist of users activity, date, time, url, etc. The first step I took was to use a function to transform date and time that I have in the data table, into a number representing the 40 mins since 01-01-1990. I did this by dividing it by 2400 (seconds in 40 mins). The main purpose is to have a time frame for the sessions.
I used the following code,

CREATE OR REPLACE FUNCTION ssnDate(
DATE IN VARCHAR2 DEFAULT 03-01-18,
TIME IN VARCHAR2
) RETURN NUMBER
AS
BEGIN
RETURN TRUNC((to_date(DATE||' '||TIME, 'DD-MM-YY HH:MM:SS')- to_date('01-JAN-1990','DD-MON-YYYY')) * (86400/2400);
END ssnDate;

This was what appeared in the log after running the statement,

FUNCTION ssnDate compiled
Warning: execution completed with warning

After this, I tried to create a VIEW to transform the DATE and TIME with the ssnDate that was created earlier on, and concatenate the CS_URI_STEM (which is the resource accessed), and CS_URI_QUERY (which is the the query, if any, the client was trying to perform)into a new field called WEB_LINK.

This is the code used,

CREATE OR REPLACE VIEW WEBLOG_VIEWS("C_IP", "WEB_LINK", "CS_USER_AGENT", "SESSION")
AS
SELECT ssnDate(LOG_DATE, LOG_TIME) AS 'SESSION',
C_IP,
CS_USER_AGENT,
(CS_URI_STEM||'?'||CS_URI_QUERY) AS WEB_LINK
FROM WEBLOG;

Now from this I got the following error..

Error starting at line 1 in command:
CREATE OR REPLACE VIEW WEBLOG_VIEWS("C_IP", "WEB_LINK", "CS_USER_AGENT", "SESSION")
AS
SELECT ssnDate(LOG_DATE, LOG_TIME) AS 'SESSION',
C_IP,
CS_USER_AGENT,
(CS_URI_STEM||'?'||CS_URI_QUERY) AS WEB_LINK
FROM WEBLOG
Error at Command Line:3 Column:38
Error report:
SQL Error: ORA-00923: FROM keyword not found where expected
00923. 00000 - "FROM keyword not found where expected"
*Cause:
*Action:

I don't get where I'm going wrong with this.. This is the data preparation stage which requires me to prep the data before applying modeling techniques or algorithms.. The next step would be grouping the data, based on the session time, ip and the user agent of each session along with the web_links fields visited by the user in that session.

I would really be grateful for any inputs on where I'm going wrong and any solutions for that!
Post #1392230
Posted Monday, December 3, 2012 9:01 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:34 PM
Points: 20,676, Visits: 32,269
Duplicate post.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1392237
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse