Results 1 to 7 of 7

Thread: Query problem

  1. #1
    Join Date
    Feb 2004
    Posts
    5

    Unanswered: Query problem

    I have created the following stored procedure to insert data. When i run the following query "CALL Ry385insert_page('EPT-CSG','SIEP','RIJ-KES','page1','2004-03-16')" it creates three lines with a count value of '1'. Then when i run the same query again it update all three line to a count values of 2. But when to try to run it third time nothing happens. The values stay 2 and i don't get any errors.

    Can anyone tell me why it won't go beyond the values 2. I tried to enter a values bigger then 2 manually and that is no problem

    CREATE OR REPLACE PROCEDURE Ry385insert_page (refind VARCHAR, ou VARCHAR, country VARCHAR, pagename VARCHAR, DATE VARCHAR)

    IS
    ou_exists NUMBER(10,0);
    ref_exists NUMBER(10,0);
    country_exists NUMBER(10,0);

    BEGIN

    SELECT COUNT INTO ou_exists FROM RY385REPORTING_PAGE_USAGE WHERE datetime = TO_DATE(DATE, 'YYYY MM DD') AND TYPE = 'OU' AND value = ou ;
    SELECT COUNT INTO ref_exists FROM RY385REPORTING_PAGE_USAGE WHERE datetime = TO_DATE(DATE, 'YYYY MM DD') AND TYPE = 'OU' AND value = ou ;
    SELECT COUNT INTO country_exists FROM RY385REPORTING_PAGE_USAGE WHERE datetime = TO_DATE(DATE, 'YYYY MM DD') AND TYPE = 'OU' AND value = ou;

    IF (ou_exists = 0) THEN
    INSERT INTO RY385REPORTING_PAGE_USAGE VALUES (TO_DATE(DATE, 'YYYY MM DD') ,pagename, 'OU',ou,1);
    ELSE
    ou_exists := ou_exists + 1;
    UPDATE RY385REPORTING_PAGE_USAGE SET COUNT = ou_exists WHERE page = pagename AND TYPE = 'OU' AND value = ou ;
    END IF;

    IF (ref_exists = 0) THEN
    INSERT INTO RY385REPORTING_PAGE_USAGE VALUES (TO_DATE(DATE, 'YYYY MM DD') ,pagename, 'REFIND',refind, 1);
    ELSE
    ref_exists := ref_exists + 1;
    UPDATE RY385REPORTING_PAGE_USAGE SET COUNT = ref_exists WHERE page = pagename AND TYPE = 'REFIND' AND value = refind ;
    END IF;

    IF (country_exists = 0) THEN
    INSERT INTO RY385REPORTING_PAGE_USAGE VALUES (TO_DATE(DATE, 'YYYY MM DD') ,pagename, 'COUNTRY',country,1);
    ELSE
    country_exists := country_exists + 1;
    UPDATE RY385REPORTING_PAGE_USAGE SET COUNT = country_exists WHERE page = pagename AND TYPE = 'COUNTRY' AND value = country;
    END IF;

    END;


    CREATE TABLE RY385REPORTING_PAGE_USAGE (
    DATETIME DATE NOT NULL,
    PAGE VARCHAR2 (100) NOT NULL,
    TYPE VARCHAR2 (100) NOT NULL,
    VALUE VARCHAR2 (100) NOT NULL,
    COUNT NUMBER (10) NOT NULL,
    CONSTRAINT RY385_PK_PAGE_USAGE
    PRIMARY KEY ( DATETIME, PAGE, TYPE, VALUE ) );

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Query problem

    PL/SQL is getting confused by your use of the reserved word COUNT as a column name. Instead of returning the value of the column, it is returning you the count of matching records. In fact, if you renamed the column (or even used a table alias in the query like r.count) you would find that the procedure wouldn't work at all the first time - it would raise NO_DATA_FOUND.

    DATE and TYPE are also reserved words and should therefore also be avoided.

  3. #3
    Join Date
    Feb 2004
    Posts
    5

    update

    i changed the code but now it does nothing, no errors no inserts nothing

    CREATE OR REPLACE PROCEDURE Ry385insert_Page (refind VARCHAR, ou VARCHAR, country VARCHAR, pagename VARCHAR, opendate VARCHAR)

    IS
    ou_exists NUMBER(10,0);
    ref_exists NUMBER(10,0);
    country_exists NUMBER(10,0);
    ou_num NUMBER(10,0);
    ref_num NUMBER(10,0);
    country_num NUMBER(10,0);

    BEGIN

    SELECT COUNT(counter) INTO ou_exists FROM RY385REPORTING_PAGE_USAGE WHERE datetime = TO_DATE(opendate , 'YYYY MM DD') AND TYPEDATA = 'OU' AND value = ou ;
    SELECT COUNT(counter) INTO ref_exists FROM RY385REPORTING_PAGE_USAGE WHERE datetime = TO_DATE(opendate , 'YYYY MM DD') AND TYPEDATA = 'REFIND' AND value = refind ;
    SELECT COUNT(counter) INTO country_exists FROM RY385REPORTING_PAGE_USAGE WHERE datetime = TO_DATE(opendate , 'YYYY MM DD') AND TYPEDATA = 'COUNTRY' AND value = country;
    SELECT counter INTO ou_num FROM RY385REPORTING_PAGE_USAGE WHERE datetime = TO_DATE(opendate , 'YYYY MM DD') AND TYPEDATA = 'OU' AND value = ou ;
    SELECT counter INTO ref_num FROM RY385REPORTING_PAGE_USAGE WHERE datetime = TO_DATE(opendate , 'YYYY MM DD') AND TYPEDATA = 'REFIND' AND value = refind ;
    SELECT counter INTO country_num FROM RY385REPORTING_PAGE_USAGE WHERE datetime = TO_DATE(opendate , 'YYYY MM DD') AND TYPEDATA = 'COUNTRY' AND value = country;

    IF (ou_exists = 0)
    THEN INSERT INTO RY385REPORTING_PAGE_USAGE VALUES (TO_DATE(opendate , 'YYYY MM DD') ,pagename, 'OU',ou,1);
    ELSE
    ou_num := ou_num + 1;
    UPDATE RY385REPORTING_PAGE_USAGE SET counter = ou_num WHERE datetime = TO_DATE(opendate , 'YYYY MM DD') AND page = pagename AND TYPEDATA = 'OU' AND value = ou ;
    END IF;

    IF (ref_exists = 0)
    THEN INSERT INTO RY385REPORTING_PAGE_USAGE VALUES (TO_DATE(opendate , 'YYYY MM DD') ,pagename, 'REFIND',refind, 1);
    ELSE
    ref_num := ref_num + 1;
    UPDATE RY385REPORTING_PAGE_USAGE SET counter = ref_num WHERE datetime = TO_DATE(opendate , 'YYYY MM DD') AND page = pagename AND TYPEDATA = 'REFIND' AND value = refind ;
    END IF;

    IF (country_exists = 0)
    THEN INSERT INTO RY385REPORTING_PAGE_USAGE VALUES (TO_DATE(opendate , 'YYYY MM DD') ,pagename, 'COUNTRY',country,1);
    ELSE
    country_num := country_num + 1;
    UPDATE RY385REPORTING_PAGE_USAGE SET counter = country_num WHERE datetime = TO_DATE(opendate , 'YYYY MM DD') AND page = pagename AND TYPEDATA = 'COUNTRY' AND value = country ;
    END IF;

    END;

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: update

    You should get this error now as I do (and would expect when table is empty):

    SQL> exec Ry385insert_page('EPT-CSG','SIEP','RIJ-KES','page1','2004-03-16')
    begin Ry385insert_page('EPT-CSG','SIEP','RIJ-KES','page1','2004-03-16'); end;

    *
    ERROR at line 1:
    ORA-01403: no data found
    ORA-06512: at "TANDREWS.RY385INSERT_PAGE", line 16
    ORA-06512: at line 1

  5. #5
    Join Date
    Feb 2004
    Posts
    5

    Cool solved

    got i now, thnx for you help

  6. #6
    Join Date
    Feb 2004
    Location
    India
    Posts
    16
    But dont you think the procedure was using DATE keyword for variable, is that allowed

  7. #7
    Join Date
    Feb 2004
    Posts
    5

    re:

    I used freetoad to compile the stored procedure without any errors. And the first version posted here worked (not correct). So using words as TYPE or DATE doesn't see to be a problem.

    I won't recommend it tough. It doesnt make the script very clear.

    Originally posted by myaman
    But dont you think the procedure was using DATE keyword for variable, is that allowed

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •