SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Create Or Replace Function Error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Saya22
Starting Member

United Kingdom
2 Posts

Posted - 12/03/2012 :  21:29:49  Show Profile  Reply with Quote
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!

robvolk
Most Valuable Yak

USA
15668 Posts

Posted - 12/03/2012 :  21:39:47  Show Profile  Visit robvolk's Homepage  Reply with Quote
SQLTeam is a Microsoft SQL Server website, try the Oracle forums over at http://dbforums.com/
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 12/04/2012 :  06:02:26  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Remove the single quotes from column alias SESSION

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000