If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Sybase > Syntax error on CASE WHEN, DECLARE,SET

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Jan 2004
Location: The Hague/Utrecht, NL
Posts: 503
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!
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jan 2004
Location: The Hague/Utrecht, NL
Posts: 503
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!
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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 09:51.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jan 2004
Location: The Hague/Utrecht, NL
Posts: 503
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!
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Jan 2004
Location: The Hague/Utrecht, NL
Posts: 503
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!
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On