Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2013
    Posts
    4

    Syntax error on CASE WHEN, DECLARE,SET

    Hi, here is a code
    CREATE FUNCTION "DBA"."PrzychodyDTMR"(IN okres character(1))
    RETURNS numeric(10,2)
    BEGIN
    DECLARE suma numeric(10,2);
    DECLARE IDZamowienieTemp integer;
    DECLARE okresTMR date;

    CASE okres
    WHEN 'D' THEN
    DECLARE kursor CURSOR FOR (SELECT Z.IDzamowienie
    FROM ZAMOWIENIE Z
    WHERE Z.DATA_ZAMOWIENIA=Convert(datetime, Convert(int, GetDate())));
    WHEN 'T' THEN

    SET okresTMR = DATEADD(week,-1,GETDATE())
    DECLARE kursor CURSOR FOR (SELECT Z.IDzamowienie
    FROM ZAMOWIENIE Z
    WHERE Z.DATA_ZAMOWIENIA BETWEEN okresTMR AND Convert(datetime, Convert(int, GetDate())))
    WHEN 'M' THEN

    SET okresTMR = DATEADD(month,-1,GETDATE())
    DECLARE kursor CURSOR FOR (SELECT Z.IDzamowienie
    FROM ZAMOWIENIE Z
    WHERE Z.DATA_ZAMOWIENIA BETWEEN okresTMR AND Convert(datetime, Convert(int, GetDate())))
    WHEN 'R' THEN

    SET okresTMR = DATEADD(year,-1,GETDATE())
    DECLARE kursor CURSOR FOR (SELECT Z.IDzamowienie
    FROM ZAMOWIENIE Z
    WHERE Z.DATA_ZAMOWIENIA BETWEEN okresTMR AND Convert(datetime, Convert(int, GetDate())))
    END CASE;

    SET suma = 0;
    OPEN kursor;
    petla : LOOP
    FETCH NEXT kursor INTO IDZamowienieTemp;
    IF SQLCODE <> 0 THEN LEAVE petla END IF;
    SET suma = suma + WartoscZamowienia(IDZamowienieTemp);
    END LOOP;
    CLOSE kursor;

    RETURN suma;
    END


    Sybase points error on first DECLARE (after WHEN 'D' THEN) but i dont have idea why. I was checking almost everything. What is more,i was trying to do this in other way, to start like this:
    CREATE FUNCTION "DBA"."PrzychodyDTMR"(IN okres character(1))
    RETURNS numeric(10,2)
    BEGIN
    DECLARE suma numeric(10,2);
    DECLARE IDZamowienieTemp integer;
    DECLARE okresTMR date;
    DECLARE kursor CURSOR FOR (SELECT Z.IDzamowienie
    FROM ZAMOWIENIE Z
    WHERE Z.DATA_ZAMOWIENIA=Convert(datetime, Convert(int, GetDate())));
    SET okresTMR = DATEADD(week,-1,GETDATE());
    DECLARE kursor CURSOR FOR (SELECT Z.IDzamowienie
    FROM ZAMOWIENIE Z
    WHERE Z.DATA_ZAMOWIENIA BETWEEN okresTMR AND Convert(datetime, Convert(int, GetDate())));
    SET okresTMR = DATEADD(month,-1,GETDATE());
    DECLARE kursor CURSOR FOR (SELECT Z.IDzamowienie
    FROM ZAMOWIENIE Z
    WHERE Z.DATA_ZAMOWIENIA BETWEEN okresTMR AND Convert(datetime, Convert(int, GetDate())));
    SET okresTMR = DATEADD(year,-1,GETDATE());
    DECLARE kursor CURSOR FOR (SELECT Z.IDzamowienie
    FROM ZAMOWIENIE Z
    WHERE Z.DATA_ZAMOWIENIA BETWEEN okresTMR AND Convert(datetime, Convert(int, GetDate())));

    but again i got error or on first DECLARE or on first SET....

    Can anyone help me?

    Greetings, 1love

    PS: Sorry for my english, it isnt my native language

  2. #2
    Join Date
    Jan 2004
    Location
    The Hague/Utrecht, NL
    Posts
    516
    Have you tried using if-then-else to identify the value of OKRES?
    And what is the exact error you get?
    I'm not crazy, I'm an aeroplane!

  3. #3
    Join Date
    Jan 2013
    Posts
    4
    I didnt try, i will but im afraid i will get still get error like in the second version of my code. I get simple syntax error on 'DECLARE' at line 9. If i use 2nd version and i will delete ';' after DECLARE at line 9, im getting syntax error on SET at next line.

  4. #4
    Join Date
    Jan 2004
    Location
    The Hague/Utrecht, NL
    Posts
    516
    I think I missed something the first time: declaring variables is done with declare @okresTMR date. In the rest of your script, use @okresTMR if you are referring to the variable.

    Also, you're using SET without an UPDATE-command.
    If you want to assign a value to a variable, use select @okresTMR = DATEADD(month,-1,GETDATE())
    I'm not crazy, I'm an aeroplane!

  5. #5
    Join Date
    Jan 2013
    Posts
    4
    I corrected code following your advices but there are still syntax error there:

    ALTER FUNCTION "DBA"."PrzychodyDTMR"(IN okres character(1))
    RETURNS numeric(10,2)
    BEGIN
    DECLARE @suma numeric(10,2);
    DECLARE @IDZamowienieTemp integer;


    IF okres='D' THEN
    BEGIN
    DECLARE kursor CURSOR FOR (SELECT Z.IDzamowienie
    FROM ZAMOWIENIE Z
    WHERE Z.DATA_ZAMOWIENIA=Convert(datetime, Convert(int, GetDate())));
    END
    ELSE IF okres='T' THEN
    BEGIN
    DECLARE kursor CURSOR FOR (SELECT Z.IDzamowienie
    FROM ZAMOWIENIE Z
    WHERE Z.DATA_ZAMOWIENIA BETWEEN Convert(datetime, Convert(int, DATEADD(week,-1,GETDATE()))) AND Convert(datetime, Convert(int, GetDate())));
    END
    ELSE IF okres='M' THEN
    BEGIN
    DECLARE kursor CURSOR FOR (SELECT Z.IDzamowienie
    FROM ZAMOWIENIE Z
    WHERE Z.DATA_ZAMOWIENIA BETWEEN Convert(datetime, Convert(int, DATEADD(month,-1,GETDATE()))) AND Convert(datetime, Convert(int, GetDate())));
    END
    ELSE IF okres='R' THEN
    BEGIN
    DECLARE kursor CURSOR FOR (SELECT Z.IDzamowienie
    FROM ZAMOWIENIE Z
    WHERE Z.DATA_ZAMOWIENIA BETWEEN Convert(datetime, Convert(int, DATEADD(year,-1,GETDATE()))) AND Convert(datetime, Convert(int, GetDate())));
    END

    SELECT @suma = 0;
    OPEN kursor;
    petla : LOOP
    FETCH NEXT kursor INTO @IDZamowienieTemp;
    IF SQLCODE <> 0 THEN LEAVE petla END IF;
    SELECT @suma = @suma + WartoscZamowienia(IDZamowienieTemp);
    END LOOP;
    CLOSE kursor;

    RETURN @suma;
    END


    now Sybase shows error: item kursor already exsists. Any ideas?

    Forgive me for next question but is it really correct to use SELECT @variable = ...... instead of SET @variable = ...... to update its value? However i tried to use SET instead of SELECT and error still apears so i remove variable and i used DATEADD in covert function as u can see.
    Last edited by 1love; 01-03-13 at 10:51.

  6. #6
    Join Date
    Jan 2004
    Location
    The Hague/Utrecht, NL
    Posts
    516
    Sorry, my bad. set @variable = <value> works too.

    I am not sure why you are getting the error about the cursor though. Maybe because you have multiple declare-cursor statements with the same cursorname. Although only one of those should be executed at either one time.

    You can see if that indeed is the problem by using a different cursor-name in each else-block. I know you need an identical cursorname for your open-cursor-statement at the end, but at least you will know if this is the source of the problem.
    I'm not crazy, I'm an aeroplane!

  7. #7
    Join Date
    Jan 2013
    Posts
    4
    Yesterday i rewrite my code in this way:

    ALTER FUNCTION "DBA"."PrzychodyDTMR"(IN okres character(1))
    RETURNS numeric(10,2)
    BEGIN
    DECLARE suma numeric(10,2);
    DECLARE IDZamowienieTemp integer;


    IF okres='D' THEN
    BEGIN
    DECLARE kursord CURSOR FOR (SELECT Z.IDzamowienie
    FROM ZAMOWIENIE Z
    WHERE Z.DATA_ZAMOWIENIA=Convert(datetime, Convert(int, GetDate())));
    SET suma = 0;
    OPEN kursord;
    petla : LOOP
    FETCH NEXT kursord INTO IDZamowienieTemp;
    IF SQLCODE <> 0 THEN LEAVE petla END IF;
    SET suma = suma + WartoscZamowienia(IDZamowienieTemp);
    END LOOP;
    CLOSE kursord;

    RETURN suma;
    END
    ENDIF;
    IF okres='T' THEN
    BEGIN
    DECLARE kursort CURSOR FOR (SELECT Z.IDzamowienie
    FROM ZAMOWIENIE Z
    WHERE Z.DATA_ZAMOWIENIA BETWEEN Convert(datetime, Convert(int, DATEADD(week,-1,GETDATE()))) AND Convert(datetime, Convert(int, GetDate())));
    SET suma = 0;
    OPEN kursort;
    petla : LOOP
    FETCH NEXT kursort INTO IDZamowienieTemp;
    IF SQLCODE <> 0 THEN LEAVE petla END IF;
    SET suma = suma + WartoscZamowienia(IDZamowienieTemp);
    END LOOP;
    CLOSE kursort;

    RETURN suma;
    END
    endif;
    IF okres='M' THEN
    BEGIN
    DECLARE kursorm CURSOR FOR (SELECT Z.IDzamowienie
    FROM ZAMOWIENIE Z
    WHERE Z.DATA_ZAMOWIENIA BETWEEN Convert(datetime, Convert(int, DATEADD(month,-1,GETDATE()))) AND Convert(datetime, Convert(int, GetDate())));
    SET suma = 0;
    OPEN kursorm;
    petla : LOOP
    FETCH NEXT kursorm INTO IDZamowienieTemp;
    IF SQLCODE <> 0 THEN LEAVE petla END IF;
    SET suma = suma + WartoscZamowienia(IDZamowienieTemp);
    END LOOP;
    CLOSE kursorm;

    RETURN suma;
    END
    endif;
    IF okres='R' THEN
    BEGIN
    DECLARE kursorr CURSOR FOR (SELECT Z.IDzamowienie
    FROM ZAMOWIENIE Z
    WHERE Z.DATA_ZAMOWIENIA BETWEEN Convert(datetime, Convert(int, DATEADD(year,-1,GETDATE()))) AND Convert(datetime, Convert(int, GetDate())));
    SET suma = 0;
    OPEN kursorr;
    petla : LOOP
    FETCH NEXT kursorr INTO IDZamowienieTemp;
    IF SQLCODE <> 0 THEN LEAVE petla END IF;
    SET suma = suma + WartoscZamowienia(IDZamowienieTemp);
    END LOOP;
    CLOSE kursorr;

    RETURN suma;
    END
    ENDIF;
    END


    Usually im programming in C/C++ and in my opinion this solution is impossible to accept, looks rly bad. However it works perfektly. Im still wondering why there was error:'item kursor already exsists' if all declarations was in others IF ELSE statements.

    If someone got better idea how solve this problem, please post it. If not i would leave it in this way.

  8. #8
    Join Date
    Jan 2004
    Location
    The Hague/Utrecht, NL
    Posts
    516
    If I understand your script correct, depending on the value of the value OKRES, you need a differen where-clause for the cursor?
    I've looked into dynamic SQL just now, and maybe this might work (you''ll need to check the syntax first, so it matches your compiler requirements)
    Code:
    --declare variables
    declare @okres char(1)
    declare @suma numeric(10,2)
    declare @IDZamowienieTemp int
    declare @whereclause varchar(250)
    declare @cursql varchar(1024)
    
    
    --check value of OKRES and assign appropriate 
    --value to where-clause variable @whereclause
    if @okres = 'D' then
    set @whereclause = "WHERE Z.DATA_ZAMOWIENIA=Convert(datetime, Convert(int, GetDate())))"
    elseif @okres = 'T' then
    set @whereclause = "WHERE Z.DATA_ZAMOWIENIA BETWEEN Convert(datetime, Convert(int, DATEADD(week,-1,GETDATE()))) AND Convert(datetime, Convert(int, GetDate())));"
    elseif @okres = 'M' then
    set @whereclause = "WHERE Z.DATA_ZAMOWIENIA BETWEEN Convert(datetime, Convert(int, DATEADD(month,-1,GETDATE()))) AND Convert(datetime, Convert(int, GetDate())));"
    elseif @okres = 'R' then
    set @whereclause = "WHERE Z.DATA_ZAMOWIENIA BETWEEN Convert(datetime, Convert(int, DATEADD(year,-1,GETDATE()))) AND Convert(datetime, Convert(int, GetDate())));"
    endif
    
    --generate declare cursor sql
    set @cursql = "DECLARE kursor CURSOR FOR (SELECT Z.IDzamowienie FROM ZAMOWIENIE Z "+@whereclause
    --execute declare cursor
    exec(cursql)
    
    --process cursor as usual
    set suma = 0
    open kursor
    petla:LOOP
    fetch next kursor into IDZamowienieTemp
    IF SQLCODE <> 0 THEN LEAVE petla END IF;
    SET suma = suma + WartoscZamowienia(IDZamowienieTemp);
    END LOOP;
    CLOSE kursor;
    It's been a while since I've this much programming, so I hope it works...
    I'm not crazy, I'm an aeroplane!

Posting Permissions

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